An Introduction to Database Unit Testing with tSQLt

Last week I was looking through the Recently Published Courses list on Pluralsight and noticed one on Database Unit Testing (check it out). Given that it was quiet at work as not everyone was back from holidays I thought it would be a good time to look into it.

What is tSQLt?

tSQLt (website) is a testing framework for SQL Server, that basically means it provides (almost) everything you need to write tests against your database.

All the tests are written in T-SQL so that means that anyone working with SQL Server can write them and they are executed by a stored procedure. This means you stay working in SQL Server Management Studio with no need to context switch to another tool to write or execute your tests.

What tSQLt provides

tSQLt is a very rich testing framework and provides the following features:

• Isolation and setup functionality
• Assertions
• Expectations (for exceptions)

Anatomy of a Unit Test (The Three A’s)

In case you aren’t familiar with unit testing theory then the three A’s are a very simple way of thinking about how to write easily understood tests.

The three A’s are Arrange, Act, Assert.

Arrange: This is where any setup for running the test is done, including mocking objects if necessary.

Act: This is where the piece of functionality we are testing is executed (this is often referred to the system under test).

Assert: This is where the result from the Act stage is compared to the expectation (i.e. did the test do what we were expecting it to do)

Example tSQLt Unit Test

Time for an example.

Take the following two tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

create table dbo.[person]
(
  person_id INT Identity (1,1) Primary Key,
  first_name nvarchar(50) not null,
  last_name nvarchar(50) not null
)
 
create table dbo.[audit_log]
(
  audit_id INT Identity(1,1) Primary Key,
  audit_message nvarchar(MAX) NOT NULL
)

and the following stored procedure

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create procedure stp_create_new_person
  @first_name nvarchar(50),
  @last_name nvarchar(50)
as
begin
 
  insert into dbo.[person] ( first_name, last_name ) values ( @first_name, @last_name )
  insert into dbo.[audit_log] ( audit_message ) values ( 'Created user with first_name ' + @first_name + ' and last_name ' + @last_name )
 
end

To test that an entry into the audit_log table is made after a user has been created we can write the following stored procedure to act as our tSQLt unit test

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

create procedure [stp_create_new_person].[test Check that an entry to the audit_log table is made with correct first and last name]
as
begin
 
  --Assemble
  declare @expected nvarchar(MAX) = 'Created user with first_name bob and last_name brown'
 
  exec tSQLt.FakeTable @TableName = 'person'
  exec tSQLt.FakeTable @TableName = 'audit_log'
 
  --Act
  exec dbo.stp_create_new_person @first_name = 'bob', @last_name = 'brown'
  declare @actual nvarchar(MAX) = (SELECT top 1 audit_message from [audit_log])
 
  --Assert
  exec tSQLt.AssertEqualsString @expected = @expected, @actual = @actual, @message = 'Audit message didn''t match expected result'
 
end

Let’s dissect this piece by piece. In the Assemble stage, we set our expectation, which will be used in the Assertion section

1

declare @expected nvarchar(MAX) = 'Created user with first_name bob and last_name brown'

Next is setting up the database, FakeTable is a tSQLt function which under the hoods makes a fresh copy of the table, removing all constraints, triggers, identities etc and then renames the existing one to a temporary name (this is then renamed back after the test has finished).

This allows the test to run in isolation.

1
2

exec tSQLt.FakeTable @TableName = 'person'
exec tSQLt.FakeTable @TableName = 'audit_log'

In the Act stage the stored procedure that is being tested is executed.

1

exec dbo.stp_create_new_person @first_name = 'bob', @last_name = 'brown'

Then from the audit_log table we set the actual result, which will then be compared to the excepted value

1

declare @actual nvarchar(MAX) = (SELECT top 1 audit_message from [audit_log])

Finally in the Assert stage we use the tSQLt.AssertEqualsString stored procedure to check if the actual result matches the expected value

1

exec tSQLt.AssertEqualsString @expected = @expected, @actual = @actual, @message = 'Audit message didn''t match expected result'

And that’s all there is to writing a simple unit test.

tSQLt likes you to group all the tests that relate to one database object into it’s own schema as this makes it easier to find all related tests in the object explorer.

tSQLt provides a stored procedure that can be used to create a new schema (test class)

1

exec tSQLt.NewTestClass '[stp_create_new_person]'

tSQLt also provides a way to execute only unit test that belong to the same test class (schema), to execute all the stp_create_new_person tests

1

exec tSQLt.Run '[stp_create_new_person]'

Wrap Up

As you can see, using tSQLt makes writing unit tests very easy. This should mean that you no longer have an excuse to not write them!

In my next post I’m going to cover off the issue of source control when using SQL Server Data Tools and the SQL Server project type.

Some Useful Resources

In this post I have only just scratched the surface of what can be achieved with tSQLt, I came across a number of useful resources that helped me to apply tSQLt:

• Unit Testing T-SQL Code with tSQLt - Pluralsight Course
• David Green’s blog - he is the author of the above Pluralsight course and has written a fair bit about tSQLt
• tSQLt.org - the official website has lots of useful information
• Greg Lucas has also written a lot about tSQLt on his blog - I found his article on cross database unit testing particularly helpful