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 discussed what benefits using the open-source framework tSQLt can bring. Of course, you have skimmed studied that post diligently and now we’re all unit testing experts, right?.
In this post, I would like to demonstrate how to create a very basic unit test using a simple tSQLt test procedure.
The Setup
I’m not going to cover how to get tSQLt installed within this post, 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 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 (although some would argue that’s pretty apt), there are typically three core stages 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 reference as we will be referencing them shortly.
2 – Our Procedure To Be Tested
Using the StackOverflow2013 database, we have a very simple stored procedure to use for our demo. This procedure simply takes in a Display Name and returns some information about that user, within the StackOverflow database. The stored procedure utilises two tables within the database, which are the dbo.Users and dbo.Comments tables:
3 – Our Unit Test: Assembly Stage
We will begin by creating our test, as we would declare a normal stored procedure:
CREATE OR ALTER PROCEDURE [tests].[test sp_GetCommentCountByUser 1] AS BEGIN DECLARE @DisplayName NVARCHAR (400) = N'Jon Skeet';
As you can see, we are using a custom schema for this called [tests] which will enable us to group them together later.
Additionally, it’s important that our unit test name begins with the word ‘test’. So in our example, the unit test will be called [test sp_GetCommentCountByUser 1]. 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:
/* 1 - Assemble Stage */ EXEC tSQLt.FakeTable 'dbo.Comments' INSERT INTO dbo.Comments (Id, UserId) VALUES (37313,22656), (37585,22656), (37591,22656), (140484,26837), (140499,26837), (87543,9623); EXEC tSQLt.FakeTable 'dbo.Users' INSERT INTO dbo.Users (Id, DisplayName, Reputation) VALUES (22656,N'Jon Skeet', 1047863), (26837,N'Brent Ozar', 12145), (9623, N'David Smart', 91);
The first item we need to discuss here is the use of the tSQLt Fake Table Function (tSQLt.FakeTable). This allows us to generate a unit test that has no dependencies outside of its context. The Fake table function will allow us to write a test in isolation from any constraints 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.
In the last part of our assembly 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.
CREATE TABLE dbo.Expected
(DisplayName NVARCHAR(100),Reputation INT,CommentCount INT);
CREATE TABLE dbo.Actual
(DisplayName NVARCHAR(100),Reputation INT,CommentCount INT);
INSERT INTO dbo.Expected
(DisplayName,Reputation,CommentCount)
VALUES
('Jon Skeet',1047863,3)
Finally, we will add one record to the Expected table. As you can see, 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 its stands ‘now’. This allows us to capture the functionality and future-proof any bugs.
4 – Our Unit Test: Act Stage
Moving onto the Act stage…
/* 2 - Act Stage */ INSERT INTO dbo.Actual EXEC [dbo].[sp_GetCommentCountByUser] @DisplayName = @DisplayName
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.
/* 3 - Assert Stage */ EXEC tSQLt.AssertEqualsTable @Expected = 'dbo.Expected', @Actual = 'dbo.Actual', @FailMsg = 'Our test failed!' END
As you can see, the AssertEqualsTable stored procedure accepts a few input parameters, some are mandatory, some are not –
- @Expected – As you have probably guessed, we’re going to supply our expected dataset here
- @Actual – Here we will supply our Actual dataset
- @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 complete tSQLt unit test:
CREATE OR ALTER PROCEDURE [tests].[test sp_GetCommentCountByUser 1]
AS
BEGIN
DECLARE @DisplayName NVARCHAR (400) = N'Jon Skeet';
/* 1 - Assemble Stage */
EXEC tSQLt.FakeTable 'dbo.Comments'
INSERT INTO dbo.Comments
(Id, UserId)
VALUES
(37313, 22656),
(37585, 22656),
(37591, 22656),
(140484,26837),
(140499,26837),
(87543, 9623);
EXEC tSQLt.FakeTable 'dbo.Users'
INSERT INTO dbo.Users
(Id, DisplayName, Reputation)
VALUES
(22656, N'Jon Skeet', 1047863),
(26837, N'Brent Ozar', 12145),
(9623, N'David Smart', 91);
CREATE TABLE dbo.Expected
(DisplayName NVARCHAR(100),Reputation INT,CommentCount INT);
CREATE TABLE dbo.Actual
(DisplayName NVARCHAR(100),Reputation INT,CommentCount INT);
INSERT INTO dbo.Expected
(DisplayName,Reputation,CommentCount)
VALUES
('Jon Skeet', 1047863, 3)
/* 2 - Act Stage */
INSERT INTO dbo.Actual
EXEC [dbo].[sp_GetCommentCountByUser]
@DisplayName = @DisplayName
/* 3 - Assert Stage */
EXEC tSQLt.AssertEqualsTable @Expected = 'dbo.Expected', @Actual = 'dbo.Actual', @FailMsg = 'Our test failed!'
END
7 – Calling the unit test
Now we have the Stored Procedure for our unit test done, we need to deploy (run) it like any normal procedure. In order to call the stored procedure, we can use the following –
USE StackOverflow2013; GO EXEC tSQLt.Run @TestName = '[tests].[test sp_GetCommentCountByUser 1]'
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 it’s running everything within a transaction)
Once run, we should ‘hopefully’ get the following output:
As we can see, our test passed! This indicates that the Expected and Actual were exact matches. We now be confidence that our stored procedure (sp_GetCommentCountByUser) is functioning as expected.
8 – When Things Go Wrong
So, 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.
Currently, within the procedure we are performing the following SELECT:
SELECT
U.DisplayName,
U.Reputation,
C.CommentCount AS [CommentCount]
FROM dbo.Users AS U
Let us assume dear reader, that a developer makes a change and feels like doing everybody a good deed by adding a default 100 to everyone’s Reputation which gets retrieved:
SELECT
U.DisplayName,
U.Reputation + 100,
C.CommentCount AS [CommentCount]
FROM dbo.Users AS U
[This has obviously been in error, no developer is this friendly 🙂 ]
So what happens when we run our unit test now?
Well, we have a big obvious failure, but for once that’s 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 we can see, our @FailMsg text from earlier can be seen. Additionally, we get useful information about why the test has failed. tSQLt will highlight to us the records which have mismatched. We can clearly see the difference in Reputation. We expected 1047863 to be returned, but due to our ‘bug’, we actually returned 1047963 because of the functional change made within our procedure. Cool!
Closing Thoughts
In this post, we went through a basic demo to highlight the basics of using tSQLt to verify the core functionality of our stored procedure.
During the demo, we highlighted the differences between the three main stages of our unit test (Assembly, Act, and Assert).
We then ran a test that generated a pass. We also then introduced a ‘bug’ into our underlying procedure and were able to see how tSQLt reacts.
In the next post in this series, we will dive a bit deeper into some of the powerful features tSQLt provides for more complex scenarios than the one we have looked at today. In addition to that, we will look at some of the third-party tools utilizing the power of tSQLt such as Redgate SQL Test
Until the next time!
