SQL Unit Testing Using tSQLt: Part 2

Unit Testing Experts!

In the last post, we went through what unit testing is, at least when discussing it in the context of SQL Server. In addition to that, we discussed what benefits using the open-source framework tSQLt can bring. Of course, you have studied that post diligently and now we’re all unit testing experts, right? … hello?

In this post, I would like to demonstrate how to create a very basic unit test using a tSQLt to verify a simple stored procedure.

The Setup

I’m not going to cover how to get tSQLt installed within this post (its quite straightforward), there are plenty of guides on how to do that already. These should have you more than covered:

tSQLt Download - Get the framework download directly from tSQLt.org
SQL Server Central - An install guide from the fine folks over at SQL Server Central
Redgate Simple Talk - Another install guide from the good people at Redgate Simple Talk

A Demo

1 - The Three Stages

Not to be mistaken with The Three Stooges, these stages are typically the three core steps that the unit test is composed of:

Assemble - In this stage, we will ‘assemble’ our tables and data within those tables
Act - In this stage, we will execute or run our intended procedure
Assert - The assertion stage is where we validate our output or changes

We don’t need to drill down much deeper into the stages at the moment, I just wanted to make you aware of them for relevance as we will be referencing them shortly.

2 - Our Procedure to be tested

Using the StackOverflow2010 database (I was feeling retro), we have a very simple stored procedure to use for our demo.

The procedure, simply takes in one parameter for DisplayName. It will then return general information along with the comment count for that user:

 1CREATE OR ALTER   PROCEDURE [dbo].[GetUserDetail]
 2	@DisplayName NVARCHAR(100)
 3AS
 4BEGIN
 5    SELECT
 6        u.DisplayName,
 7        u.Location,
 8        u.Reputation,
 9        COUNT(c.Id) AS CommentCount
10    FROM dbo.Users u
11    INNER JOIN dbo.Comments c ON u.Id = c.UserId
12    WHERE u.DisplayName = @DisplayName
13    GROUP BY u.DisplayName, u.Location, u.Reputation
14END
15GO

3 - Our Unit Test: Assemble Stage

We shall begin by creating our test, as we would declare a standard stored procedure:

1CREATE OR ALTER PROCEDURE [tests].[test GetUserCommentCount]
2AS
3BEGIN
4
5DECLARE @UserName NVARCHAR(100)	= 'Jon Skeet'

As you can see, we are using a custom schema for this called [tests] which will enable us to group our tests together later.

Additionally, its important that our unit test name begins with the word ’test’. So in our example, the unit test will be called [tests].[test GetUserCommentCount]. All tSQLt unit tests must adhere to this naming convention.

Finally, we are declaring our input parameter that is to be used - @DisplayName. As you can see, we are setting this as ‘Jon Skeet’ directly within the code. (Something which i had to learn when starting with tSQLt was the fact that it’s not about the data specifically, its about ensuring a repeatable process that validates the functionality. )

Next up, we begin our Assemble stage. Were going to start setting up our tables and putting some test data into them:

 1EXEC tSQLt.FakeTable 'dbo.Users'
 2
 3INSERT INTO dbo.Users
 4(Id,DisplayName, Location, Reputation)
 5VALUES
 6	(22656,	'Jon Skeet'	,	'Reading, United Kingdom'	,	1047863),
 7	(22661,	'Virne'		,	'Finland'					,	1076),
 8	(22668,	'Dickon Reed',	'Cambridge, United Kingdom'	,	2237)
 9
10
11EXEC tSQLt.FakeTable 'dbo.Comments'
12
13INSERT INTO dbo.Comments
14(Id, UserId)
15VALUES
16	(37313	, 22656),
17	(37360	, 22656),
18	(37585	, 22656),
19	(436275	, 22661),
20	(197827	, 22668)

The first item we need to discuss here is the use of the tSQLt Fake Table Function. This allows us to generate a unit test that has zero dependencies outside of context its running in. The Fake table function will allow us to write a test in isolation from any constrainsts which may exist on the table. Any work we do against the table from here on will be a ‘faked’ version of the original table giving us more freedom than normal. Nice!

We then insert some static, dummy data into our shiny new faked table which will enable the functionality of the tested stored procedure to be validated. We essentially need to supply the procedure in question with suitable data for it to be tested.

For example, our sample data should render 3 regarding the comment count of this user:

In the last part of the Assemble stage, we’re now going to create two tables that will be used in the final comparison. An ’expected’ and ‘actual’ table will hold their respective datasets:

 1CREATE TABLE dbo.Expected
 2(DisplayName NVARCHAR(100), Location NVARCHAR(200),Reputation INT, CommentCount INT)
 3
 4
 5CREATE TABLE dbo.Actual
 6(DisplayName NVARCHAR(100), Location NVARCHAR(200),Reputation INT, CommentCount INT)
 7
 8
 9INSERT INTO dbo.Expected
10(DisplayName,Location, Reputation, CommentCount)
11VALUES
12	('Jon Skeet',	'Reading, United Kingdom',	1047863,	3)

As you can see above, within our ’expected’ table, we are saying that we expect a 3 for the comment count, for this particular user.

Finally, we will add one record to the Expected table. We are adding the information we expect to retrieve back should we use the @DisplayName of ‘Jon Skeet’. We are inserting these values based on the functionality of the tested stored procedure as it stands ’now’. This allows us to capture the functionality, not specifically the data. This is a strong mechanism to employ when trying to negate future bugs.

4 - Our Unit Test: Act Stage

Moving onto the Act stage

1INSERT INTO dbo.Actual
2
3EXEC [dbo].[GetUserDetail] 
4	@DisplayName	= 'Jon Skeet'

Here, we are executing our stored procedure and inserting the output into our newly created ‘actual’ table. Simple huh?

5 - Our Unit Test: Assert Stage

Now we’re ready to do some comparison within the Assert stage. There are several forms of assertion using the tSQLt framework. Today we will be using the tSQLt.AssertEqualsTable feature as we’re going to be comparing …erm, tables:

1EXEC tSQLt.AssertEqualsTable
2	@Expected	= 'dbo.Expected',
3	@Actual		= 'dbo.Actual',
4	@FailMsg	= 'Our test failed!'

As shown above, the AssertEqualsTable stored procedure accepts a few input parameters, some are mandatory, some are not:

@Expected - As you will have probably guessed, we’re going to supply our expected dataset here
@Actual - Here we will supply our Actual dataset (ie. the one rendered by the stored procedure)
@FailMsg - This is an optional parameter that allows us to supply some text to be returned, should our test fail

6 - Our Unit Test: All Together Now

Now we have our completed tSQLt unit test, each of the three stages have been highlighted with a comment. Behold it in all its glory:

 1CREATE OR ALTER PROCEDURE [tests].[test GetUserCommentCount]
 2AS
 3BEGIN
 4
 5DECLARE 
 6	@UserName NVARCHAR(100)	= 'Jon Skeet'
 7
 8
 9/* ASSEMBLE STAGE */
10
11EXEC tSQLt.FakeTable 'dbo.Users'
12
13INSERT INTO dbo.Users
14(Id,DisplayName, Location, Reputation)
15VALUES
16	(22656,	'Jon Skeet'		,	'Reading, United Kingdom'	,	1047863),
17	(22661,	'Virne'			,	'Finland'					,	1076),
18	(22668,	'Dickon Reed'	,	'Cambridge, United Kingdom'	,	2237)
19
20
21EXEC tSQLt.FakeTable 'dbo.Comments'
22
23INSERT INTO dbo.Comments
24(Id, UserId)
25VALUES
26	(37313	, 22656),
27	(37360	, 22656),
28	(37585	, 22656),
29	(436275	, 22661),
30	(197827	, 22668)
31
32
33CREATE TABLE dbo.Expected
34(DisplayName NVARCHAR(100), Location NVARCHAR(200),Reputation INT, CommentCount INT)
35
36CREATE TABLE dbo.Actual
37(DisplayName NVARCHAR(100), Location NVARCHAR(200),Reputation INT, CommentCount INT)
38
39INSERT INTO dbo.Expected
40(DisplayName,Location, Reputation, CommentCount)
41VALUES
42	('Jon Skeet',	'Reading, United Kingdom',	1047863,	3)
43
44
45/* ACT STAGE */
46
47INSERT INTO dbo.Actual
48
49EXEC [dbo].[GetUserDetail] 
50	@DisplayName	= 'Jon Skeet'
51
52
53/* ASSERT STAGE */
54
55EXEC tSQLt.AssertEqualsTable
56	@Expected	= 'dbo.Expected',
57	@Actual		= 'dbo.Actual',
58	@FailMsg	= 'Our test failed!'
59
60
61END

7 - Calling the Unit Test

Now we have the stored procedure for our unit test done, we need to deploy and run it like any normal stored procedured. In order to call the stored procedure, we can use the following:

1USE [StackOverflow2010];
2GO
3
4EXEC tSQLt.Run @TestName = '[tests].[test GetUserCommentCount]'

Using the tSQLt.Run procedure, we can initiate the test run. Using this mechanism will allow tSQLt to run the test in isolation (behind the scenes its running everything within a transaction).

Once run, we should ‘hopefully’ get the following output:

As we can see above, our test passed! This indicates that the Expected and Actual were exact matches. We can now be confident that our stored procedure (dbo.GetUserDetail) is functioning as anticipated.

8 - When Things Go Wrong

So far, we have shown what things look like when the test passes and everything is right with the world. What about when things go wrong? To achieve a failure, let us assume one of our developers makes a change to the stored procedure in question, by accident.

Currently, within the stored procedure GetUserDetail, we are performing the following SELECT:

1SELECT
2    u.DisplayName,
3    u.Location,
4    u.Reputation,
5    COUNT(c.Id) AS CommentCount
6FROM dbo.Users u

Let us assume, that a developer makes a change and accidently does everyone a good deed by adding an extra 100 points to everyone’s reputation:

1SELECT
2    u.DisplayName,
3    u.Location,
4    u.Reputation + 100,
5    COUNT(c.Id) AS CommentCount
6FROM dbo.Users u

Obviously an error, no developer is this friendly 😙

So, what happens when our unit test runs next time?

Well, we have test failure, but for once thats a good thing! Our unit test has detected that now, the Expected and Actual outputs no longer match (ie. The functionality of the underlying procedure is not as expected and so the outputs are out of alignment).

As shown above, we get some useful information in regards to ‘why’ the test failed. tSQLt will highlight the records which have been mismatched. We can clearly see the difference in the Reputation value. We expected 1047863 to be returned, but due to our ‘bug’, we actually returned 1047963 because of the the functional change made within our procedure. Cool!

Closing Thoughts

In this post, we went through a simple demo to highlight the fundementals of using tSQLt to verify the functionality of our stored procedure. During the demo, we highlighted the differences between the three main stages of our unit test (Assemble, Act and Assert).

We then ran a test that generated a pass. We also then introduced a ‘bug’ into our underlying procedure so that we could see how tSQLt reacts.

Unit testing using tSQLt is a vast topic. In the next post in this series, we will dive a bit deeper into some of the powerful features offered by tSQLt for more complex scenarios than the one we looked at today. In addition to that, we will look at some of the third party tooling available to accelerate the scale at which tSQLt can be used.

Until next time!