Introduction to SQL Unit Testing using TSQLT

• Download source - 1.1 KB

Introduction

tSQLt (website) is a database unit testing framework for Microsoft SQL Server. It provides APIs to create and execute test cases, as well as integrates them with continuous integration servers (such as CruiseControl, TFS and Bamboo). tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

Background

Now a days, the emphasis on unit testing code is growing. Even clients sometimes will not accept or review code if there are no unit test cases written. We can either follow TDD (test driven development) to write unit tests before writing code or write unit tests after writing code. In this modern world where a lot of projects follow agile, TDD can be a blessing in disguise.

When it comes to writing unit tests, developers can use a variety to unit testing frameworks such as NUnit, JUnit, Moq or Microsoft Visual Studio’s own unit testing framework. All these frameworks and many other similar frameworks are very handy in writing unit tests for server side code. There are frameworks available for testing front end code as well, like Jasmine, Mocha and Karma to name a few. Similarly, tSQLt is a framework for unit testing code written in SQL such as stored procedures and functions.

What is the Importance of Writing Unit Tests for SQL Code?

A lot of people argue that there is no need to write unit tests for the code written in SQL because there are already test cases written for DAL (data access layer) of the solution or the server code which connects with database and fetches, updates or inserts data. The following points will shed light on the importance of writing unit tests for database code.

• When using NUnit or any other server side framework for testing database, we will have to write code for cleanup too. This is required, otherwise these test cases will cause unnecessary updates, inserts or deletions inside database. Whereas with tSQLt, tests are automatically executed within transactions thus eliminating the need of writing cleanup code.
• Using server side framework makes the test cases highly dependent on the data in database. If someone changes this data (database refresh, someone deletes rows from tables or updates them) test cases will start failing. tSQLt provides the ability to fake tables and views, and to create stored procedure spies thereby eliminating dependency on the underlying data.
• Let’s say our stored procedure returns events based on their start dates and end dates. A server side test case written for this stored procedure might be passing now but after a few days when an event expires, the result returned by it will change and the test case will fail. So, even though no one changed the underlying data, still test cases will start failing. Since tSQLt is totally data independent, this can be avoided.
• Some of the stored procedures can have very complicated logic and calculations in them. With tSQLt, we can be sure that all this is properly tested.

Now before getting started with writing UTCs, we need to take care of some prerequisites. I would recommend installing tSQLt in your local database first and writing a few sample test cases and execute them before installing it in your development database.

Prerequisites

• Download tSQLt from Sourceforge.
• Enable CLR on the server where your database is present. It should be your local SQL server in case you want to first try writing UTCs in your local database, otherwise do it on development server.

  Execute the below script to do so.

  SQL

  EXEC sp_configure 'clr enabled', 1;
  
  RECONFIGURE;

• Set your database to trustworthy for tSQLt to run. I have created a database in my local named POC_UnitTests. Execute the following script.
  SQL

  ALTER DATABASE POC_UnitTests SET TRUSTWORTHY ON;

• Install tSQLt on database by running tSQLt.class.sql script file present in the download. This will add a few tables and a lot of stored procedures in your database. These are required by tSQLt to function.

Note: Since we will be writing UTCs only on local and development servers, it is fine to enable CLR and setting trustworthy on. This is however not recommended on any other servers, especially production.

Using the Code

Writing Unit Test Cases using tSQLt

Now that we are done with all the prerequisites, we can start writing test cases.

EXEC tSQLt.NewTestClass 'testJobs';

GO

The above statement creates a class in tSQLt named ‘testJobs’. This is helpful because we can group all our test cases from one module in one class and have different classes for each module. Also, each developer can have his/her own class for writing test cases. After writing all test cases, we can just execute test cases from one particular class or execute all test cases from all classes. The following statements can be used for achieving this.

EXEC tSQLt.RunAll           --for executing all modules

EXEC tSQLt.Run 'testJobs';        --for executing only testJobs class/module

Let’s start with a simple test case to check if tSQLt framework is working properly.

CREATE PROCEDURE testJobs.[testing simple UTC]

AS

BEGIN
    DECLARE @sum INT;
    SELECT @sum = 3 + 2;
    EXEC tSQLt.AssertEquals 5, @sum;
END;

tSQLt creates a stored procedure in database for each test case within a schema (testJobs in this case) and giving it a name (testing simple UTC in this case). The above test case should succeed because we are just trying to check if the sum of 3 and 2 is 5. To execute this test case, use command:       

EXEC tSQLt.Run 'testJobs';

Test Case 1 (execute the following in your database)

EXEC tSQLt.NewTestClass 'testJobs';

GO

---------------------------------------------------------------------------------------------------

CREATE PROCEDURE testJobs.[testing simple UTC]

AS

BEGIN

    DECLARE @sum INT;

    SELECT @sum = 3 + 2;

    EXEC tSQLt.AssertEquals 5, @sum;

END;

GO

-----------------------------------------------------------------------------------------

EXEC tSQLt.Run 'testJobs';

The following message will confirm that the test case was executed successfully.

Now that we have written a simple UTC, let’s write some involving our actual tables, SPs and functions.

First I am creating a table, function and a Stored Procedure in my DB.

CREATE TABLE [dbo].[ReportSchedulerJob](

       [ReportSchedulerJobID] [bigint] IDENTITY(1,1) NOT NULL,

       [Status] [nvarchar](50) NOT NULL,

       [ExecutionStatus] [nvarchar](50) NULL,

       [StartDate] [datetime2](7) NOT NULL,

       [EndDate] [datetime2](7) NULL,

       [JobType] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_ReportSchedulerJob] PRIMARY KEY CLUSTERED

(

       [ReportSchedulerJobID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]


GO


CREATE FUNCTION GetStatusMessage()

  RETURNS NVARCHAR(MAX)

AS

BEGIN

  DECLARE @NumParticles INT;

  SELECT @NumParticles = COUNT(1) FROM dbo.ReportSchedulerJob;

  --RETURN 'The Accelerator is prepared with ' + 
  --CAST(@NumParticles AS NVARCHAR(MAX)) + ' particles.';

  RETURN 'testing function with row count '+ CAST(@NumParticles AS NVARCHAR(MAX));

END;


CREATE Procedure GetJobsFromDB(@jobType nvarchar(25))

AS

BEGIN

       select top 1 [Status],[ExecutionStatus],[StartDate],_
       [EndDate],[JobType] from ReportSchedulerJob where JobType = @jobType

       RETURN;

END

Now we can write test cases based on these DB objects.

Test Case 2

CREATE PROCEDURE testJobs.[testing a function with a fake table]

AS

BEGIN

  --Assemble: Fake the ReportSchedulerJob table to make sure it is empty 
  --and that constraints will not be a problem

  EXEC tSQLt.FakeTable 'dbo.ReportSchedulerJob';

  --Put 2 test records into the table

  INSERT INTO [dbo].[ReportSchedulerJob] ([Status],[ExecutionStatus],[StartDate],[EndDate],[JobType])

         VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual');

  INSERT INTO [dbo].[ReportSchedulerJob] ([Status],[ExecutionStatus],[StartDate],[EndDate],[JobType])

         VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual');


  --Act: Call the GetStatusMessage Function

  DECLARE @StatusMessage NVARCHAR(MAX);

  SELECT @StatusMessage = dbo.GetStatusMessage();


  --Assert: Make sure the status message is correct

  EXEC tSQLt.AssertEqualsString 'testing function with row count 2', @StatusMessage;

END;

In the above test case, we have faked table ReportSchedulerJob which is already there in our DB. When we fake a table, tSQLt creates a temporary table with the same name in our UTC schema (testJobs). The table is blank and has no constraints by default (having no constraints makes the inserts easier). We inserted 2 rows (dummy records) in the table after faking it. Then we executed our function which basically returns a count of records in the table. As we can see, it returned count as 2 even though the actual table has no records (since we did not insert anything in the actual table). We successfully tested a function without looking at the data present inside the table referenced by it. So, faking a table makes our UTC independent of the data present inside it.

Test Case 3

CREATE PROCEDURE [testJobs].[testing a SP which gets Jobs From DB table]

AS

BEGIN

  -- Create Actual and Expected table to hold the actual results of GetJobsFromDB SP

  -- and the results that we expect

  CREATE TABLE [testJobs].[Actual] ([Status] nvarchar(50) null, _
  [ExStatus] nvarchar(50) null, StartDate DateTime2, EndDate DateTime2, JobType nvarchar(50));

  CREATE TABLE [testJobs].[Expected] ([Status] nvarchar(50) null, _
  [ExStatus] nvarchar(50) null, StartDate DateTime2, EndDate DateTime2, JobType nvarchar(50));


  --Fake Table - Isolate this test from the real data

  EXEC tSQLt.FakeTable 'dbo.ReportSchedulerJob'


  INSERT [dbo].[ReportSchedulerJob] ([Status],[ExecutionStatus],[StartDate],[EndDate],[JobType])

  VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual')


  --Execution

  INSERT [testJobs].[Actual]([Status],[ExStatus],[StartDate],[EndDate],[JobType])

  EXEC dbo.GetJobsFromDB 'manual';


  -- Create the expected output

  INSERT [testJobs].[Expected] ([Status],[ExStatus],[StartDate],[EndDate],[JobType])

  VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual');


  --Check that Expected and Actual tables contain the same results

   DECLARE @expectedRowCount int; SET @expectedRowCount = 1;

   DECLARE @actualRowCount int; SET @actualRowCount = (SELECT COUNT(*) FROM [testJobs].[Actual])


    --! Assert

   EXEC tSQLt.AssertEquals @expectedRowCount, @actualRowCount, 'The number of rows returned is incorrect';


   EXEC tSQLt.AssertEqualsTable '[testJobs].[Expected]', _
   '[testJobs].[Actual]', 'The column order in the result set is incorrect';

END;

In the above test case, we have tested a Stored Procedure by faking a table referenced by it. By doing so, our test case will not be dependent on the actual data present inside the table.

We can now execute all the 3 UTCs written by running the following command:

EXEC tSQLt.Run 'testJobs'

The below message will be seen after the execution:

Points of Interest

So, we just saw how easily we can test our functions and stored procedures using tSQLt. It uses transactions to run the test cases and it cleans all the unnecessary DB objects which were written just for the sake of the test case after executing the test case. This means we do not have to worry about deleting data from faketables or deleting the faketables or deleting tables we created inside test case like Actual and Expected. There are a lot of other tSQLt features available which make writing UTCs very simple. I will try to write another article to go through all such interesting features. You can go through tSQLt user guide to read more about tSQLt. If you run into troubled waters while writing UTCs or have any other questions, then feel free to contact me.

I am attaching a script which has the 3 UTCs we just created.

History

First version of this post.