Unit Testing T-SQL Stored Procedures with tSQLt

Andrew Smith Posted on

tSQLt is a free, open source database unit testing framework for SQL Server.

In this post I give a brief description of how to install it and how it works, and then go on to use it to extend the Continuous Integration build configuration from my last post to include automated testing for the QA environment.

The tSQLt website is at www.tsqlt.org and the tSQLt framework can be downloaded from here. As explained in the Quick Start Guide the SQL Server instance running the framework must have CLR Integration enabled, and any databases using tSQLt must have their TRUSTWORTHY property set to ON.

Once these prerequisites have been addressed, the tSQLt.class.sql script from the download is run for the database to be tested. This creates a new schema in the database called tSQLt, and creates tables, views, stored procedures and functions within that schema. The installation of the framework is now complete, and we’re ready to go.

The first step is to create a test class for our unit tests:

tSQLtTestClass

This basically creates a new schema in the database that tSQLt knows will be used as a container for unit tests. I’ve called mine testContoso, since my units tests will be testing the ContosoRetailDW database that is the subject of my Continuous Integration build.

Now we need to identify a stored procedure that we want to test. I’ve created the following (minimal) stored procedure within the ContosoRetailDW database, which manages updates to the DimAccount table:

tSQLtStoredProc

So we can now write a tSQLt stored procedure in our testContoso schema, that will implement the unit test for the stored procedure above.

A tSQLt unit test will generally be designed with these 3 sections:

(1) Arrange
(2) Act
(3) Assert

In “Arrange” we set up the prerequisites for the test, in “Act” we run the test, and in “Assert” we check to see if the test results are as we expected.

This is the “Arrange” section of my unit test procedure:

tSQLtArrange

tSQLt has a number of helper stored procedures which are explained in its User Guide. I’m using one of them – tSQLt.FakeTable – in the first line above. This creates a new version of the table to be tested, which allows test data and actions to be controlled and isolated. All tSQLt test executions rollback the transaction that the test occurs within, so any actions that tSQLt does behind-the-scenes (such as renaming the original dimAccount table out of the way in this case) are undone by the end of the test.

After creating the fake table above, I then insert 4 rows of test data into it. I then create a temporary table called #Expected that contains the data that should be present in the dimAccount table after my UpdateAccountDetails stored procedure has run. The 4th row in this table includes new data from my test. Note that I’m using 4 rows here rather than just 1 – this covers testing of the case where I forget a WHERE clause in my update stored procedure and all rows in the table get updated instead of just the 1 I want.

Following on from the T-SQL above, I have the “Act” section of my unit test:

tSQLtAct

This simply calls the stored procedure under test. This is followed by the T-SQL for my “Assert” section:

tSQLtAssert

This populates a table with the actual data following the test and uses a tSQLt stored procedure called tSQLt.AssertEqualsTable to assert that my actual table matches my expected table. tSQLt has many different forms of assertions – see the User Guide for the full set.

All the T-SQL above exists within a stored procedure that I’ve created with this name:

tSQLtUnitTestProc

So this is created within the test schema. Note that a prerequisite for tSQLt unit test procedure names is that they must begin with the word “test”.

I can now run the test via SSMS:

tSQLtRun

The form of the run syntax that I’m using here runs all unit tests (which is just 1 in this case) within the testContoso test class. The output in SSMS is:

tSQLtSuccess

If I engineer a failure, the tSQLt output in SSMS is:

tSQLtFailure

So we have informative information about the nature of the failure, and a T-SQL error is thrown.

We’re now ready to include our test as an automated test within the Continuous Integration build configuration that I presented in my last post. I’m following Dave Green’s lead for this, presented in his Simple Talk article on Using SQL Test Database Unit Testing with TeamCity Continuous Integration.

First we create a T-SQL text file called RunTests.sql to run the test (which will be executed by the SQLCMD utility via a TeamCity command line build step):

tSQLtCIScript

This script file is then added to the database project (from my last post) in SSDT:

tSQLtSSDT

As with the Deploy.ps1 script, its Build Action property is to None, since we do not want it executing as part of the Visual Studio build.

Then we add a 3rd build step to our TeamCity project, using the TeamCity Command Line runner to run the SQLCMD utility against the RunTests.sql file:

tSQLtBuildSteps

Note the use of the -b parameter when running SQLCMD – this ensures that if a test fails, the current batch terminates and the error code raised results in a failed build step.

When a developer checks in to the project’s GitHub repository via SSDT, the TeamCity CI build automatically runs, building the Visual Studio solution, applying the DACPAC and running the unit test. When the test succeeds, the TeamCity build log shows this:

tSQLtTCSuccess

and when we engineer a failure, we see the tSQLt diagnostic information in the TeamCity build log:

tSQLtTCFailure

This fails the CI build.

So to conclude, in this post we’ve seen how to implement a unit test using the tSQLt testing framework, and how to include the automated running of this test within a CI build.

For a more detailed introduction to tSQLt, John McLusky’s SQLBits session on Unit Testing Database Code using the tSQLt Framework is a very informative watch.