This article is the first in a short series on tSQLt:

1. Introduction
2. Anatomy of a tSQLt test
3. Practical considerations
4. Dealing with transactions

Introduction

This is the introduction to the introduction!  tSQLt lets you unit test stored procedures (including functions) on SQL Server.  For why unit testing is a good idea, see my article on unit testing.  If you’re in too much of a hurry to look at lots of words and pictures, go to the tSQLt quick start guide.  This article will give an overview rather than technical details, so you can decide whether tSQLt is for you or not, and so you’ll know whether it’s worth the bother to read the other articles for the details.  If these articles don’t explain things in the right way for you, I recommend the tSQLt articles at DataCentricity.

Unit testing is a bit like pulling one cog out of a complex clockwork and somehow testing it in isolation.  In all unit testing, code <-> code dependencies resist the extraction of one cog from the rest.  In database unit testing, there are also table <-> table dependencies to deal with such as foreign keys.

Imagine that you are trying to test a stored procedure that needs data to exist in table A.  However, table A has a foreign key relationship to table B, which has a foreign key relationship to table C and so on.  This means that getting the database in the correct starting state can get very tedious. Chasing down all these relationships leads to an explosion in the quantity of data you need to get just so before testing can even start.

tSQLt has fairly painless mechanisms for mocking both stored procedures and tables (in a way that lets you choose which foreign keys you worry about).  This is covered in a bit more detail in How the mocking works below, and then the technical stuff is in the article Anatomy of a test.  The practical result of this is that in the example above the test would need only data in table A.

There is also a nice and simple way to compare two tables in a single short command, which lets you check expected results against actual results – again, more details in the article Anatomy of a test. (There are also asserts to compare pairs of things like ints or strings.)

Unit testing with tSQLt would enable other things, like Test-Driven Development (TDD) for databases and Continuous Integration etc. for databases.  However, you can still get a lot of value from tSQLt if you do neither of these other things and I recommend you give it a go.

Installing

This is very simple – turn on trustworthiness like this:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);

and run the tSQLt script to enable CLRs (SetClrEnabled.sql) and the other to add the internal tSQLt stored procedures to your database (tSQLt.class.sql).

Benefits

You can easily exercise all your stored procedures in the unit tests, rather than e.g. having to do exactly the correct series of clicking and typing on your website to get to the point where a particular stored procedure is invoked.

The tests are isolated from each other via mocking, so tests will be less brittle than those that depend on the true versions of all stored procedures in a call stack.  Such tests would be brittle to both code changes and schema changes.

A consequence of both of these two is that you have a decent chance of detecting the scope of the impact on your system by e.g. a schema change.  Which tests break?  You can run tests for all stored procedures, and each one will report errors due to just local problems.  There will be no noise due to problems cascading up from lower in the call stack.

You can test the schema itself, i.e. check that a table exists, has particular columns etc.  I haven’t done this yet, but it’s possible.  So far, all my tests have been on functions and stored procedures.

Tests of tSQL stored procedures are themselves tSQL stored procedures.  This means that the technology and tools for tests are already available and familiar.  While tests using e.g. C# might be more powerful or in other ways better, you would need to switch between technologies and possibly tools.  For some people this is possible and easy, for others it won’t be.

Limitations

Unlike e.g. C# unit testing, I have found no way of calculating code coverage metrics.  If you name tests in a way that reflects the stored procedure they’re testing, have a separate file per test suite with a sensible name, then you can do a limited check by matching up file names, but it’s not brilliant.

You can’t test stored procedures that do things like import files.

There are two options and you can do one, the other, or both:

1. Write your own integration test for just that stored procedure in e.g. PowerShell.
2. Split the stored procedure up so that there is a stored procedure that does nothing but the file import, and everything else is in one or more other stored procedures, e.g. reporting the status of the file import to the rest of the system, preparing a dynamic SQL string for the file import stored procedure to execute etc. These other stored procedures can be tested by tSQLt, so you would be limiting the amount of code that tSQLt didn’t cover to the minimum.

Each time it starts a new test case, tSQLt starts a new database transaction and then rolls this back at the of the test.  For stored procedures that don’t involve transactions then there’s no problem.  It is possible to test stored procedures that involve transactions, but it’s a bit of effort.  This is covered in Dealing with transactions.

I haven’t found any short cut for setting up test data – it needs inserting into tables by SQL statements.  You can make this less painful by wrapping the inserts in stored procedures and making use of default parameter values, so a call to the stored procedure only needs to pass values to do with how this row differs from normal.  If a row in table A will often have a matching row in table B the stored procedure can insert both rows, taking parameters for all the data it needs and can’t default, plus possibly a parameter (that defaults to both tables) that says which tables should get a new row.

You probably don’t want to run tests that mock stored procedures or tables on a production database.  The changes it makes during mocking are reversed, but I would still be uneasy.  All tSQLt tests (on schema or code) are implemented as stored procedures, which are added to the database (in the normal way for stored procedures).  You can tidy them away in their own schema, but you would still be adding code to the production database.

How the mocking works

You can mock code or tables, and the mechanisms are similar but different.  Both are based on the separate database transaction around each test case, created by tSQLt at the start and then rolled back by it at the end.

Mocking a table

When you tell tSQLt to mock table A it does the following:

1. Looks up the schema for A
2. Renames A, so that it still exists and contains its data but is now called something else
3. Creates a new table called A, using the schema it looked up earlier but without any of the foreign key relationships or triggers, and with all columns nullable.

Your test can then insert into something that looks just like A, but is actually this temporary stand-in created by tSQLt.  The lack of foreign keys means that the test doesn’t need to worry about any other tables’ data.

The stored procedure under test will continue to be able to do insert, select, update and delete statements on table A as normal, because the stand-in looks just like the original.

At the end of the test, tSQLt rolls the transaction back.  This means that the stand-in table is deleted, and the main table is effectively renamed back to its original name.  I.e. the system is back to its original state.

If you want to test triggers or constraints such as foreign keys you can add them to the clone explicitly and individually.

Mocking a stored procedure

There are two versions of mocking a stored procedure – a simpler one and a more complicated one.

If you have stored procedure A (the one you’re testing) that calls stored procedure B, you will mock stored procedure B.  In the simpler version, tSQLt will:

1. Create a table called B_SpyProcedureLog
2. Temporarily rename B to B’ (like it does when you mock a table)
3. Create a new version of B. The new version of B inserts its parameter values into B_SpyProcedureLog.

So A is now independent of B, but you can still check the ways in which A calls B by querying the table B_SpyProcedureLog.

This is great if B is just a sink of data, e.g. it inserts into a table on behalf of A.  If B feeds data back to A then this isn’t enough – mocking B deprives A of its source of that data.  This is where the more complicated version of mocking stored procedures is necessary.

In the more complicated version of mocking, you also pass an SQL string.  The steps that tSQLt does become:

• Create a table called B_SpyProcedureLog
• Temporarily rename B to B’ (like it does when you mock a table)
• Create a new version of B. The new version of B inserts its parameter values into B_SpyProcedureLog and then executes the SQL string

The SQL string could then e.g. set B’s parameters to the values necessary for the particular test case you’re writing which, assuming that the parameters are OUTPUT parameters, will feed their values back to A.  Don’t make the mistake I made to start with and attempt to get the SQL string to set variables in A as these are not in scope – the SQL string can set B’s parameters which can in turn set A’s variables.

As with mocking tables, at the end of the test tSQLt rolls back the transaction it created at the start, and all these changes are thrown away.

Mocking a function

Mocking a function is simpler to understand than mocking a stored procedure, but a little more effort.  You supply an alternative function to execute, and the stored procedure under test is updated to execute the alternative rather than the original.  It’s a little more effort because you have to write one or more alternative versions of the function you’re mocking.

Alternatives

The only alternative I’ve found (but not tried) is Chris Oldwood’s tool SS-Unit.

Integrations

As you might expect, Red Gate tools talk tSQLt:

• SQL Test (in the SQL Toolbelt)
• Team City

The Devart dbForge product builds on tSQLt

There is a free Visual Studio plug-in.

You can run the tests from the command line using SQLCMD.

UPDATED 3rd and 4th December 2017: Originally this article said that when you mocked a stored procedure, the calling stored procedure was changed.  I discovered the hard way that it is the called stored procedure that’s changed, as the article now says.