SQL unit testing with Redgate and tSQLt

Published in

CodeX

9 min readAug 5, 2021

When my team and I deal with a hairy piece of logic, we often find it useful to write unit tests for the relevant code. This helps us define and document our expectations, lifts the burden of manual testing, and gives us peace of mind that new code changes won’t break what we have so far. Up until recently, I’d done unit tests in C#, JavaScript, Java, and Delphi, but never in SQL.

Redgate is a company that offers a broad range of products that make various aspects of working with databases easier. One of their products, SQL Test, allows developers to do unit testing in T-SQL. It’s based on the open-source tSQLt framework, and it’s what we turned to for unit testing our stored procedure in SQL Server.

Setup

Installing SQL Test

We installed SQL Test as part of the SQL Toolbelt, although it looks like it’s available as a standalone product as well. You can start a 28-day free trial, but be aware the Redgate doesn’t come cheap.

Installing tSQLt Framework

To install tSQLt onto a database, launch SQL Server Management Studio (SSMS) and click the SQL Test button in your SQL Toolbelt. If you don’t have any databases under test yet, this brings up an empty list. Click the “Add database…” button, which prompts you to select one of your databases. Choose a database and click “Add Database”. This brings up the following dialogue:

Note first the changes that SQL Test warns it will make to your database. Unfortunately, these changes are required for the framework to be installed. I suggest using a development copy of your database so that you do not affect your other environments.

Secondly, notice that you have the option to add SQL Cop static analysis tests. This is a set of prefabricated tests that check your database against a set of standards, e.g. that all tables have primary keys and that all occurrences of the VARCHAR type have a size specified. These tests are added to their own schema, namely SQLCop.

For example, if I run the SQL Cop tests on my database, the “Tables without any data” test fails. Clicking on the failing test reveals the test output in the right-hand pane, which in my case writes out the names of the empty tables:

Test Procedure: [SonicTheHedgehog].[SQLCop].[test Tables without any data] on STEPHENB-LT02
[SQLCop].[test Tables without any data] failed: (Failure)
Empty tables in your database:[sth].[Character]
[sth].[GameCharacters]
[sth].[Zone]

Regardless of whether you installed SQL Cop, you will see objects created under the schema tSQLt. These objects will be used implicitly by the test framework or explicitly by you when you write your unit tests.

Creating unit tests

Clicking the “New test…” button (the one with the big plus icon) brings up the window below, which prompts you for a test name and a test class.

The test name will be used as the name for the stored procedure that underlies your test (prefixed with the word “test” and a space). The test class will determine the name of the underlying schema and is used to group tests in the UI.

Once you have created a new test, you are presented with a SQL script that alters your newly created test procedure. The template follows the popular Arrange–Act–Assert pattern (although the term “assemble” is used instead of “arrange”).

Arrange/assemble: Setup prerequisites for the code you’re testing. This might include faking a table (more below) and inserting some data.
Act: Run the code under test. This could be executing a stored procedure or calling a function.
Assert: Inspect the actual results of running the code and compare them to the expected results. tSQLt provides a number of assert functions for this purpose.

This structure prompts you to carefully consider the preconditions, the subject, and the postconditions of our test. From this point on, making changes to your test means making alterations to this stored procedure.

Rerunnable tests

SQL Test runs each unit test within a transaction that gets rolled back automatically after execution. This means that tests can be run repeatedly and behave consistently, but it also means your test code won’t make lasting changes to your data.

Faking database objects

A common challenge with writing unit tests is isolating dependencies. We want our test code to focus on the functionality being tested. We also want our tests to be robust, i.e. we don’t want them to fail because of changes to unrelated parts of the system.

In programming languages like Java and C#, this problem is often solved by mocking or stubbing interfaces. The tSQLt framework provides utility procedures that assist with “faking” tables or functions.

In the case of FakeTable, a given table is replaced with an empty table that is a column-by-column duplicate of the original. However, this duplicate does not copy any constraints, which means you don’t have to worry about relational data when setting up your preconditions. For example, the following table stores the Zones that appear in each Sonic the Hedgehog game:

CREATE TABLE sth.GameZone
(
  Id INT NOT NULL,
  ZoneName VARCHAR(50) NOT NULL,
  Acts INT NOT NULL DEFAULT 2,
  GameId INT NOT NULL,
  PRIMARY KEY (Id),
  FOREIGN KEY (GameId) REFERENCES sth.Game (Id)
)

Because of the foreign key to sth.Game, before we can insert rows into sth.GameZone, we first need to insert related data into the sth.GameZone table. Under normal circumstances, this is a good thing, but here it only inflates our test code. Therefore, we call FakeTable on it:

EXEC tSQLt.FakeTable @TableName = 'sth.GameZone'

Internally, this renames the original table and creates a fake table in its place for the duration of the test. Now we can insert into sth.GameZone without worrying about the foreign key or any other constraint — all we care about is how it affects the code under test.

For more information, see the Isolating Dependencies section in the tSQLt documentation.

Asserting expectations

The tSQLt framework provides several helper procedures for asserting the expected results of your code. These include simple asserts that compare two scalar values e.g. AssertEquals and AssertLike. As expected, these procedures will fail your test whenever the condition is not met.

There are also table-level asserts that can be rather useful — among these, we’ve found AssertEqualsTable to be particularly powerful. This procedure compares all the rows in each table, allowing you to specify the “before” and “after” state of your data with great precision. When the table contents are different, the test generates output like the following:

|_m_|Id|Name                |Released               |PlatformId|
+---+--+--------------------+-----------------------+----------+
|<  |3 |Sonic & Knuckles    |1994-10-18 00:00:00.000|1         |
|=  |1 |Sonic the Hedgehog  |1991-06-23 00:00:00.000|1         |
|=  |2 |Sonic the Hedgehog 2|1992-11-21 00:00:00.000|1         |
|>  |3 |Sonic the Hedgehog 3|1994-05-27 00:00:00.000|1         |

Note that this text table uses the columns and values from the compared tables, with the addition of the _m_ column. Here, = (equals) is used to mark rows that were exact matches. The < (less than) symbol marks rows that are present in the expected table but not in the actual table, while > (greater than) signifies the opposite.

In my example, the row for “Sonic & Knuckles” is missing because it was included in the expected result set but absent from the actual table. On the other hand, the row for “Sonic the Hedgehog 3” was unexpected as it was present in the actual table despite not existing in the expected table.

See this page for more information on using AssertEqualsTable.

Bonus: Asserting errors

I wanted to test that under some circumstances my procedure throws an exception (i.e. that it calls RAISERROR). It wasn’t immediately obvious to me how to use ExpectException since any exception would automatically fail my test, regardless of whether or not it was expected.

The solution, I found, was to call both my procedure and the assertion inside a try…catch block, e.g.

CREATE PROCEDURE MyTests.[test ExpectException]
AS
BEGIN
  BEGIN TRY
    -- Call the proc that raises the error
    EXEC endTheWorld

    -- Tell tSQLt to expect the exception
    EXEC tSQLt.ExpectException @ExpectedMessage = 'The apocalypse is here', @ExpectedSeverity = NULL, @ExpectedState = NULL;
  END TRY
  BEGIN CATCH
    -- No need to do anything here
  END CATCH
END;

A simple example

Continuing my earlier Sonic the Hedgehog example, let’s assume my database has the schema illustrated below:

Schema for Sonic the Hedgehog example database

Assume that I have a stored procedure, FixZoneName, which appends the word “Zone” to the end of a zone’s name if it is not already present. Here is a sample of what the unit test might look like:

This test does the following:

Fake the GameZone table so that we don’t have to deal with dependencies on the Game and Platform tables.
Insert test data into the fake GameZone table.
Execute the procedure under test, i.e. FixZoneName.
Query the contents of GameZone and use AssertEqualsString to confirm that the record was updated as expected.

It’s a little simplistic, but it demonstrates the basic structure of a test.

Code coverage

When you run the tests in SQL Test, you will find that aside from test output it also presents a report on code coverage, e.g.

A nifty feature of this report is that the procedure names are clickable. Doing so scrolls you down to the code for that procedure, where SQL Test highlights the statements that are covered by your tests.

Troubleshooting

I’ve found that on certain databases my tests won’t run. Instead, they fail on a System.Data.SqlClient.SqlException with an error message like this:

Cannot access CLR. Assembly might be in an invalid state. Try running EXEC tSQLt.EnableExternalAccess @enable = 0; or reinstalling tSQLt.

In my case, the SQL command suggested by this message did not work. I did find a remedy in this helpful article, however. In addition to giving out some handy tips, the author suggests getting past this problem by changing the database owner to sa.

ALTER AUTHORIZATION ON DATABASE::tSQLt_Example TO sa

Versioning your tests

Since the unit tests form part of the database schema, if you are using SQL Source Control (SOC) or SQL Change Automation (SCA), your tests are automatically covered. The test schema and procedures are treated the same way as any other object in your database.

In the absence of a schema version control system, another option is to save creation scripts for each of the tests. This might work for a small project, but if your database is large/complex, you’ll probably find that this approach gets cumbersome quickly. Still, if you want to try this approach, beware that creating a tSQLt test schema is done differently from creating a normal schema, e.g.

EXEC tSQLt.NewTestClass 'UnitTests';

This is because tSQLt needs to mark the schema as a test class. Kudos to my colleague Teshvier for helping to uncover this one.

This article was an introduction to the basics of SQL unit testing with Redgate and the tSQLt framework. Unit testing is such a useful technique and it’s exciting to see that we’re able to apply this directly at the database level too.

Used for this article

SQL Server Management Studio 18.9.1
SQL Server 15.0.2000.5 (2019)
SQL Test 4.1.1.1259