Test Driven Database Development with tSQLt

I’ve been building up quite a collection of posts on tSQLt, the open source unit testing framework for SQL Server 2005+ and although they are all tagged and linked to each other, I thought this page might be useful if you are trying to quickly find out how to do something specific. I’ve included all the tutorials in order plus any other related, and hopefully, useful posts.

If you are just getting started with test-driven database development, or are trying to persuade your manager that Database TDD might be a good thing to implement, or at least trial, you might find this article, published on Simple-Talk, helpful: Test-driven Database Development – Why Bother?.

Simple-Talk have also published another of my articles Test-driven Database Development – Why tSQLt?. This is a detailed look at how tSQLt measures up to the standard TDD patterns and practices honed over many years in application development. I consider how features such as the ability to mock database objects, built-in set-up, automatic tear-down, and the ability to fully automate tests in a continuous integration setting mean that database developers can become fully paid up members of the TDD community.

tSQLt Tutorials and Examples

Part 1 covers the very simple process to install and, if necessary, roll back the tSQLt implementation.

Part 2 talks about creating your first test class and unit tests. In particular, this part covers tSQLt.AssertObjectExists and also using tSQLt.AssertResultSetsHaveSameMetaData to validate the complete table structure in a single test. You might think this violates some TDD principle but I would argue that as the table, it’s columns together with their data types and nullability are created in a single statement, it makes since to test those things in a single test.

Part 3 examines the approaches available in testing table constraints, specifically primary keys and default constraints. It also describes how to go about testing the IDENTITY property of a column. In this part I demonstrate the use of tSQLt.assertEquals to compare two integers – the expected and actual error numbers during an insert that violates a primary key. I also touch on using tSQLt.Fail to cause a test to fail when the framework has no built-in assertion available

Part 4 details examples of tests you might want to run against an INSERT procedure. Id describes using tSQLt.assertEqualsTable to test that parameter values get placed in the correct columns. It also describes writing tests for output parameters and using dynamic SQL to create a procedure on the fly that then calls the procedure being tested.

Part 5 covers the use of tSQLt.SpyProcedure to test that one stored procedure calls another and passes the correct values.

Part 6 talks about testing a stored procedure that returns data including that results are returned in the correct order. It includes more examples of using tSQLt.FakeTable and also explains how to take advantage of the test class-wide “SetUp” method available in tSQLt. Finally, this post provides some examples of using the Test Data Builder pattern for database testing. This pattern is also covered in greater detail here.

Part 7 builds on Part 6 by adding some tests for WHERE conditions, specifically date ranges. It also talks about creating custom test setup routines that may be re-used by multiple tests but not all tests in the same class.

Part 8 continues the them started in Part 7, this time testing string predicates in the WHERE clause. It also describes testing the effect of multiple WHERE conditions and provides a very good example of how making a change can break a previous test – in other works an example of why TDD is so useful.

Part 9 examines the kind of tests you might want to implement to validate a foreign key. I talk about the options, whether to test against the real table or use tSQLt.FakeTable to test against fake tables.

Part 10 builds on Part 9 looking at testing the ON DELETE and ON UPDATE properties of a foreign key including when the action is CASCADE.

Part 11 builds on Part 5 delving deeper into mocking stored procedures and explore how to populate output parameters or add a row to a table without any of the intervening complex logic in the procedure being mocked. The same approach can also be used to control or test a stored procedure’s return value.

Part 12 provides some examples on how to unit test views and also how to mock a view in the same way that we can fake a table (see Part 9).

Advanced tSQLt Articles and Samples

Cross-Database testing details what options there are for cross-database testing. This advanced topic covers how to go about writing tests that do work in more than one database on the same instance. In particular, it describes a couple of approaches for mocking tables in two different databases.

This post, not for the faint of heart, is a discussion on what represents good practice when using tSQLt to mock database objects. I don’t necessarily provide the answers in this post but it may help you arrive at your own definition of good practice.

Further Reading

You can also check out the Quick Start Guide or read the official tSQLt User Guide here.

Keep an eye on this page as I will add other articles as they are published or as I find on other blogs.