Database Unit Testing Part 2

The previous article focused on basic capabilities of Unit Tests which are provided to us by SQL Server Data Tools.

Another option for tests automation on which I would like to focus in this article is tSQLt, which can be downloaded for free from here: http://tsqlt.org/.

Let’s look closely at what tSQLt provides us. We get a sophisticated, complete solution, which becomes the integral part of the database.

Let’s start working with tSQLt. In the provided package there is a set of scripts used to deploy the tSQLt framework. So, the first step is to run a creation script. The creation script deploys a set of stored procedures and tables which are being located in the [tsqlt] schema.

The creation script sets up the basic environment, but before the library can be used, we have to change the database settings. We need to enable support for SQLCLR and set the Trustworthy database property to on.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);

Afterwards we have to launch the script named tSQLt.class.sql, and we are done, the environment is configured.

To create a test, we have to create the test schema inside our database. If it is not in the database, framework won’t be able to detect it and run appropriate test procedures. tSQLt is only able to run those tests which are located in the registered test schemas.

It is impossible to create a test schema manually, one has to use the [tsqlt].[NewTestClass] procedure. The only parameter which [tsqlt].[NewTestClass] requires is the name of the test schema. Then, the only thing left is to write a test which is just a stored procedure. For the procedure to be detected by the framework as a test, it must have a name starting from test (…), for example [test 1 should be equal to 1].

To run unit tests we can use all procedures that begin with the word 'Run’. Their names simply express their purpose. Executing the [tsqlt].[RunAll] procedure we start all tests.

What does the framework do?

• Finds all test schemas
• Searches for all stored procedures starting with the test word within each test schema
• Runs every found test, and in a case of any error an appropriate information is written in one of the special, dedicated tables : [tsqlt].[TestResult]

In order to give you a better idea of the basic capabilities of this framework I will make two example unit tests.

Step 1:
Firstly, we create the testing schema named UnitTests:

EXEC   [tSQLt].[NewTestClass] @ClassName = N'UnitTests'

Secondly, we define a procedure inside the UnitTests schema:

CREATE PROCEDURE [UnitTests].[test one equals one]
AS
BEGIN
 EXEC  [tSQLt].[AssertEquals]
             @Expected = 1,
             @Actual = 1,
             @Message = N'1 == 1'
END;

The only fragment of TSQL here is the one responsible for calling the assertion checking if 1 equals 1.

Using the command below we run all tests:

EXEC   [tSQLt].[RunAll]

No information occurs on the screen, which can be very misleading. Let’s create second test procedure so that the assertion fails with an error.

CREATE PROCEDURE [UnitTests].[test one equals zero]
AS
BEGIN
 EXEC  [tSQLt].[AssertEquals]
             @Expected = 1,
             @Actual = 0,
             @Message = N'1 == 0'
END;

And this is the result of executing RunAll procedure:

[UnitTests].[test one equals zero] failed: (Failure) 1 == 0 Expected:  but was: 
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name                     |Result |
+--+-----------------------------------+-------+
|1 |[UnitTests].[test one equals one] |Success|
|2 |[UnitTests].[test one equals zero]|Failure|
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

Let’s have a look at the most interesting possibilities of this framework.

First and foremost we do not have to worry about the content of the tested database thanks to the framework which ensures running all tests in transactions being in complete isolation from each other. Thanks to this mechanism we do not have to clean up after ourselves. Automatic rollback does all the job for us.

Besides aforementioned assertion [tSQLt].[AssertEquals] we have an access to many different procedures with various functionalities.

• [tSQLt].[AssertEmptyTable]
• [tSQLt].[AssertEqualsString]
• [tSQLt].[AssertEqualsTable]
• [tSQLt].[AssertNotEquals]
• [tSQLt].[AssertObjectExists]
• [tSQLt].[AssertLike]

If needed, we can fail the test on our own using the TSQL’s [tSQLt].[Fail] procedure.

One of the more interesting things is the [tSQLt].[FakeTable] procedure. A common situation occurs when two tables are in some relation, for example, let’s suppose that table A has the foreign key that references primary key in table B. If table B has no rows and we want to test table A, we can use the [FakeTable] procedure instead of trying to adapt to the given constraints. [FakeTable] procedure makes a copy of the tables while the test takes place and this copy does not have any limitations, references or constraints which the original table had. This fake table exists only for the duration of this test.

This solution works very well for me. It seems more intuitive than SSDT and gives me more possibilities. I would recommend to try both approaches and afterwards decide which proves to be better in your situation.