DevOps series : tSQLt Framework

Introduction

In one of my former projects I gained a lot of experience with building, testing and releasing with Azure DevOps and I noticed that unit testing in the database is a good thing to do. In my current project we are using tSQLt Framework for testing purposes. This blogpost is about the tSQLt framework, on how to install and how to use. The first basic steps in order to get you (and me) going.

Why is unittesting important? Well, I have noticed during my work as a consultant that releasing code can be very cumbersome and tricky when you're not convinced that everything still works, even the code you have not touched. May be something has changed in the data that will gives errors somewhere else. So running unittests before you release, is a very good thing to do!

tSQLt is free and is downloadable. There are a couple of steps that you have to do to make it work. You have to spend some time in using the stored procedure calls and scripting to understand to working of the tSQLt framework.

For this blogpost I've used some inspiration from sqlshack.

Installation of the tSQLt framework

First download the files from tsqlt.org and unzip it somewhere on your disk, like I've done below. There are a couple of sql files.

The next step is the installation of an example of the framework into SQL Server. That is the example.sql file. Open SSMS and execute the example.sql file.

Executing the tests scripts in the example file succeeds except one. The last unittest gives an error, unfortunately.

Below is the specific error of the test that is executed.

So the test is failed and therefore we need to check whether what went wrong. Is the test not good defined or something else has happened?

I changed to > into >= and execute the testexecution again and now it runs properly.

My first tSQLt testscript

Now the next step I've done is executing my own script (with a little help from sqlshack). I've created a database, a customer table and inserted a record and added a stored procedure that I would like to test.

CREATE DATABASE TestThetSQLtFramework
GO

USE TestThetSQLtFramework;
GO
 
CREATE TABLE [dbo].[Customer] (
    [CustomerId]       INT           IDENTITY (1, 1) NOT NULL,
    [Name]             VARCHAR (40)  NOT NULL,
);

SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT INTO [dbo].[Customer] ([CustomerId], [Name]) VALUES (1, N'Hennie')
SET IDENTITY_INSERT [dbo].[Customer] OFF

SELECT * FROM [Customer]


CREATE PROCEDURE AddCustomer(@Name VARCHAR(40))
AS
BEGIN
  
  INSERT INTO dbo.[Customer] (Name)
  VALUES (@Name)
    
END
GO

The next step is to run the test framework, and it seems nothing is there, and the test process is executed properly. Off course that is because there is no test there.

EXEC tSQLt.RunAll

(0 rows affected)
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name|Dur(ms)|Result|
+--+--------------+-------+------+
-----------------------------------------------------------------------------
Test Case Summary: 0 test case(s) executed, 0 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

The following step is creating a test class (aka a schema) in the database.

EXEC tSQLt.NewTestClass 'CustomerTests';
GO

Then, a test stored procedure has to be created in the database with three steps : Assemble, Act and Assert.

ALTER PROCEDURE [CustomerTests].[TestAddCustomer]
AS
BEGIN
 -- Assemble
        EXEC tSQLt.FakeTable 'dbo.Customer', @Identity = 1

    Create TABLE [CustomerTests].[Expected] 
 (
   [CustomerId] INT NOT NULL,
   [Name] VARCHAR(40) NOT NULL,
 )

 INSERT INTO [CustomerTests].[Expected] (CustomerId,Name)
 VALUES (1,'Hennie')

 -- Act
 EXEC dbo.AddCustomer 'Hennie' 
 SELECT * INTO CustomerTests.Actual FROM dbo.Customer 
 
  -- Assert (compare expected table with actual table results)
 EXEC tSQLt.AssertEqualsTable @Expected='CustomerTests.Expected', 
        @Actual='CustomerTests.Actual'
END;
GO

Executing the stored procedure will result in the following output. All is fine!

EXEC tSQLt.RunAll

(1 row affected)
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                   |Dur(ms)|Result |
+--+---------------------------------+-------+-------+
|1 |[CustomerTests].[TestAddCustomer]|    113|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Now, see what happens when I change the output from the desired output. I changed  Hennie into Hennie2 in the call of the stored procedure.

-- Act
EXEC dbo.AddCustomer 'Hennie2' 
SELECT * INTO CustomerTests.Actual FROM dbo.Customer 

Excuting the RunAll SP of the tSQLt framework will result in an error in the test framework.

EXEC tSQLt.RunAll

(1 row affected)
[CustomerTests].[TestAddCustomer] failed: (Failure) Unexpected/missing resultset rows!
|_m_|CustomerId|Name   |
+---+----------+-------+
|<  |1         |Hennie |
|>  |1         |Hennie2|
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                   |Dur(ms)|Result |
+--+---------------------------------+-------+-------+
|1 |[CustomerTests].[TestAddCustomer]|    127|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 29
Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

Final thoughts

Building tests is very simple in the tSQLt test framework

Hennie