We know most software applications, desktop, mobile, or web, require a database at the backend to store data. When we look at current software applications, the complexity is very high, and they have a high frequency of data transactions. So, we need testing of the data stored and retrieved from the database to make sure it has proper data integrity. Any database operation performed by the application is always one of the four, Create, Retrieve, Update and Delete.

We were required to do database unit tests in one of our recent client application implementations, as the application is mainly data-centric. The decisive reason to push for database unit testing is to ensure the application has data integrity. We were required to develop database test cases to include checks for data integrity and confirming business rules.

For database unit testing, we have the following frameworks that can be used:

• Microsoft SQL Server Data Tools (Using Visual Studio)
• tSQLt Unit Testing Framework

This blog post will give a high-level experience in implementing and demonstrating how to create test cases that use tSQLt features on AdventureWorks sample databases. We choose the tSQLt Database Unit Testing framework for Azure SQL Database to implement database unit testing. tSQLt allows us to create isolated test cases defined with the data we need; each test case runs in its own transaction.

A tSQLt unit test case is based on the AAA rule, which consists of:

Step One: tSQLt Environment Setup

The first step is to install tSQLt in your database.

• Setup includes a set of objects (tables, stored procedures, functions, and more) that you add to the database you want to test. Download “tSQLt_V1.0.5873.27393.zip” from the tSQLt site, and unzip the file.
• Run the “tSQLt.class.sql” script against the database. You can find the script in the zip file that you downloaded from the tSQLt site.
  Once the run is successful, you can find the tSQLt schema. Assigned to that schema will be tables, views, stored procedures, and user-defined functions. These are the components that do all the processing when creating and running test cases against your database. As a part of best practices, we will not be moving any objects created by “tSQLt.class.sql” and unit test cases outside the development database.

Before you create a test case, you need to create a test class where the test case will be located. A test class is a schema configured with an extended property that tells tSQLt that it is a test class. To create the test class, you use the NewTestClass stored procedure part of the tSQLt schema.

The EXEC statement creates a test class named TestPerson. Once we’ve completed this step, we can add one or more test cases to the test class, so let’s get started doing that.

Step 2: Testing [Person].[GetAddressByCity] Stored Procedure

In tSQLt, a test case is a stored procedure that’s part of a test class and uses tSQLt elements to perform the testing. We can develop and test stored procedures and functions in a database.

As part of developing the tSQLt test case, we’ll create a test case for a stored procedure [Person].[GetAddressByCity] which will return address details for a given city.

Use the CREATE PROCEDURE statement to create a test case. The procedure name must start with the word “test” and be created in an existing test class; otherwise, making the test case is much like creating any other procedure. The following T-SQL script creates a test case named “TestGetAddressByCitySuccess” in the “TestPerson” test class:

As you can see, we are using CREATE OR ALTER PROCEDURE statement to create a test case. The critical part of the test case is the main body of the procedure definition, between BEGIN and END. First, we use tSQLt’s FakeTable stored procedure, which creates a unique temporary table with the same name as the table referenced within the actual stored procedure we are testing. This will ensure that the data in an actual database and the table is not updated or deleted. Any subsequent references we make in our test case to that table will always point to the test table and not the actual table in the database. Then we populate the table with test data.

In the test case, we use two temporary tables, #Expected, used to store the expected data, and #Actual, which will store data once the stored procedure is successfully run. We are keeping both temp tables schema similar.

Finally, we use the tSQLt AssertEqualsTable stored procedure to compare the data in the #Actual table to the data in the #Expected table.

Run the test case using tSQLt.Run the stored procedure.

When we run the test case, it should evaluate to true and return the following results:

Now you have the basics, which will allow you to go a long way with unit testing using tSQLt for SQL Server. You can refer to documentation by visiting the tSQLt User Guide.