SQL Server Unit Testing with tSQLt Framework

In this article, we will talk about the tSQLt framework — a free database unit testing framework for Microsoft SQL Server. You can use tSQLt to create unit tests for SQL Server 2005 (service pack 2 required) and all further versions. The main convenience is that you do not need to switch between various tools to create code and unit tests.

tSQLt Key Features

Tests run automatically within transactions
This keeps tests independent and reduces any cleanup work you need.

Tests are grouped together within a schema
This allows you to organize your tests and use common setup methods.

Output can be generated in plain text or XML
This makes it easier to integrate with a continuous integration tool.

The ability to fake tables and views, and to create stored procedure spies
This allows you to isolate the code which you are testing.

Unit Test Your Database Code in SSMS Instantly

Before we proceed further, I would like to introduce you an extremely useful SSMS plugin from Devart — dbForge Unit Test for SQL Server. The tool provides a user-friendly and intuitive GUI designed to create, organize and execute test cases with a few points and clicks. It completely automates and simplifies the manual and time-consuming process of unit testing. The tool also cuts down the time spent on unit testing and brings significant cost savings.

run-multiple-tests-easily

Installing the framework

tSQLt is the open source framework and is distributed under the Apache 2.0 license. You can download the latest version of the tSQLt framework from the official site

Before installing the framework, you need to setup the SQL Server instance to work with CLR.

Also, you need to set to ON the TRUSTWORTHY database property. This property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, however, it can be set to ON by using the ALTER DATABASEstatement.

The archive contains a script. You need to execute the script on a target database. As a result of execution, the script creates the tSQLt scheme, a CLR assembly and a number of procedures and functions. Some procedures will contain the Private_ prefix. The framework utilizes such procedures for the internal needs.

Creating Test Cases

A test is a stored procedure. For the convenience, tests are combined into “classes” and constitute SQL Server schemes. Schemas have some special properties applied so that tSQLt recognizes it as a test class. You can use the NewTestClass procedure to create a new class.

Each class can have its own SetUp procedure, which will be called before each test.

While creating a new test class, if there is a schema with the same name, all objects in that schema (and the schema itself) are dropped. To create a test case, you need to create a stored procedure on your test class. You can use quotes inside the CREATE PROCEDURE statement to include whitespaces in the test name:

Running Test Cases

You can use the RunAll of Run procedures to run tests all at once (on all classes) or one at a time.

You can call the Run procedure in three different ways:

  1. With a test class name — all tests within the specified class name will be executed.
  2. With the qualified schema name and the test case name
  3. With no parameters

The procedure “remembers” what parameter was used the last time it was called. If the procedure is called with no parameter, it executes the last executed test case or test class.

Isolating Dependencies

If you ever used any framework for unit tests, you might be surprised to discover that tSQLt is no different. One of great features of tSQLt is “test isolation” that is based on the transaction mechanism. In addition, tSQLt provides useful procedures that help to determine what went wrong in the test.

Typical test consists of three parts:

  1. Preparing environment (test data)
  2. Executing code under test
  3. Verifying results

There are some objects that allow you to isolate code which you are testing. If you are testing a complex system, a good practice is to isolate specific parts of the test. The following objects allow you to focus on a specific unit.

Preparing Environment

At this point, you need to prepare the database objects that will be used by the code under test. In other words, you need to substitute those objects with placeholders, stub and mock objects. What you can substitute:

FakeTable creates a copy of a target table with no data:

FakeTable allows tests to be written in isolation of the constraints on a table. FakeTable creates an empty version of the table without the constraints in place of the specified table. Therefore any statements which access the table during the execution of the test case are actually working against the fake table with no constraints. When the test case completes, the original table is put back in place because of the rollback which tSQLt performs at the end of each test case.

By default, calculated fields, default values and identity columns are not saved, however, this can be changed using the optional parameters: @identity, @ComputedColumns и @Defaults. The function can not substitute temporary tables, objects in other databases, as well as it does not preserve foreign keys. The function will create the Stub placeholder that you can fill in with the test data, without the need to change a real object. This will give you the opportunity to run the tests independently (multiple users can simultaneously run tests on a single instance of SQL Server).

FakeFunction will substitute a real function with the Stub placeholder:

Code that calls a function can be difficult to test if that function performs significant logic. We want to isolate the code we are testing from the logic buried in the functions that it calls. To create independent tests, we can replace a called function with a fake function. The fake function will perform much simpler logic that supports the purpose of our test. Often, the fake function will simply return a hard-coded value. Alternatively, the fake function may ‘validate’ the parameters it receives by returning one value if the parameters match expectations, and another value if the parameters do not match expectations. That way the code that calls the function will have a different result and thus the parameter passed to the function can be tested.

SpyProcedure creates the Mock object.

To create independent tests, we can replace the functionality of a stored procedure with a spy. The spy will record the parameters that were passed to it. SpyProcedure allows tests to be written for a procedure in isolation of the other procedures that it calls. SpyProcedure creates a table with the name of @ProcedureName + ‘_SpyProcedureLog’. This table contains an identity column ‘_id_’ and a column for each procedure parameter (except for cursor output parameters). SpyProcedure also replaces the procedure named by @ProcedureName with the command provided in the @CommandToExecute parameter and a command to insert the parameter values into the SpyProcedureLog table. Therefore, whenever the @ProcedureName is executed during the test instead of actually running the procedure, a new log entry is made in the @ProcedureName_SpyProcedureLog table and @CommandToExecute is called.

Executing code under test

This is the easy part. You simply run the code that you want to test. Nore that if you expect the test code creates an exception, you must notify tSQLt by calling the ExpectException procedure.

Verifying Results

There is a number of assertion stored procedures. You can use them to compare values. Also, there is the Fail stored procedure which marks a test case as failed.

Example

The CalcAvgTemperature procedure calculates an average temperature for a specified time span, based on the data in the temperature table. The PrintAvgTemperatureLastFourDaysprocedure uses CalcAvgTemperature to calculate the temperature for the last four days.

Now, we can create a new test class.

Now, we can add one test for each procedure.

To run both tests, we can use the Run procedure and pass it the name of our test class (TemperatureTests).

As it is expected, tests are successful.

Special aspects

Do not forget that when you run a test, tSQLt wraps it in the transaction. Therefore, be careful if your stored procedure uses it’s own transactions. For example, the following test procedure fails:

Although outside the test, the procedure will work without error. The reason is that ROLLBACK will undo both – your and tSQLt transactions. At the end of the procedure, the number of active transactions change. This problem is described here and the solution for this problem is here.

Conclusion

This article gave you a brief introduction to creating test cases for SQL Server. We have discussed the tSQLt unit test framework for SQL Server.

Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.
Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.