Leveling Up Your Project Testing with tSQLt Unit Tests for SQL Queries

I am writing this blog post to share a cool unit testing tool that I learned about at the VS Live conference in Vegas. While many developers build tests for their code to make sure it is functioning as intended, fewer developers test their database queries. Testing your code and your database queries are equally vital to make sure your queries are working as intended and producing the expected results. In this blog article, I’ll demonstrate how to use tSQLt, a unit testing framework for SQL Server, to create tests for your SQL Server database queries.

Step 1: Install tSQLt

Go to the official website tSQLt to download and set up tSQLt. Once the installation zip file has been downloaded, unzip it to a spot on your hard drive, and then run the PrepareServer.sql file.

Next, Run the tSQLt.class.sql script to install the tSQLt framework in your SQL Server database.

Step 2: Create a test database

For your unit tests, you will need to set up a test database. Only the objects and information required for testing will be in this database, which will be distinct from your production database. Run the following query in SQL Server Management Studio to create the test database.

CREATE DATABASE [tSQLtTestDB];
GO

Step 3: Create a test schema

Once you’ve created your test database, you will need to create a schema specifically for your tests.

USE [tSQLtTestDB];
GO

CREATE SCHEMA [Tests];
GO

Step 4: Write your first test

We will need to create a test class first, According to tSQLt “tSQLt.NewTestClass creates a new test class. A test class is simply a schema where the user can create a test case procedure and any other related objects.”

EXEC tSQLt.NewTestClass 'TestDatabaseQueries';
GO

In this example database query, we will create a temporary table #Fruit and inserting two rows of data - one for apples and one for oranges. The test case from below verifies if the number of apples and oranges in the table matches the expected values.

USE [tSQLtTestDB];
GO

CREATE PROCEDURE TestDatabaseQueries.[test if fruit returns expected result]
AS
BEGIN
    CREATE TABLE #Fruit (FruitName VARCHAR(50), Quantity INT)
    INSERT INTO #Fruit VALUES ('Apple', 10), ('Orange', 5)

    DECLARE @AppleCount INT
    DECLARE @OrangeCount INT
    SELECT @AppleCount = SUM(Quantity) FROM #Fruit WHERE FruitName = 'Apple'
    SELECT @OrangeCount = SUM(Quantity) FROM #Fruit WHERE FruitName = 'Orange'

    -- this case will return false
    EXEC tSQLt.AssertEquals @expected = 22, @actual = @AppleCount
    -- this case will return true
    EXEC tSQLt.AssertEquals @expected = 5, @actual = @OrangeCount

    DROP TABLE #Fruit
END
GO

In this test, we’re using the tSQLt.AssertEquals procedure to check that if the query returns the expected number of AppleCount or OrangeCount.

Step 5: Run your tests

Once you’ve written your test, you can run it using the tSQLt.Run procedure. You can run all of your tests at once by running the following command:

EXEC tSQLt.RunAll;

Or, you can run an individual test by specifying the test name:

EXEC tSQLt.Run 'TestDatabaseQueries.[test if fruit returns expected result]';

Here is the result:

Finally, testing your database queries is crucial, just like testing your code. You can be sure that your SQL Server database queries are functioning as expected and delivering accurate results by creating and running tests for them using tSQLt.