Unit Testing Microsoft SQL Server Objects with Transact-SQL-Alfus Inigo Jaganathan

In this article, let’s see how we can write and execute an unit test case for a Microsoft Sql Server database object using Transact-SQL. Let’s start with some background, before getting into the details.

Background

We know that database is one of the important parts of a software system, where there are different types like Relational, NoSql, Graph, etc.

In most of the organizations, where there is dominance of .NET ecosystem, it’s very common to see the usage of Microsoft Sql Server as the first choice for relational database needs. So, in this article we are going to talk about using Microsoft Sql Server as the database, imagining that we are building a system using .NET.

Let’s also assume that, we are strictly following TDD while building this software system. ORM tools like Entity Framework Core - Code First can help us in easily unit testing database objects using c# code and popular frameworks like Xunit, Nunit, etc.

However, certain system requirements force engineers to use Transact-SQL based database source code, where .NET engineers like to make use of Visual Studio Database Project, which provides a cool designer, similar to SQL Server Management Studio.

In such cases, what I have seen so far is, application code is unit tested well, with good amount of code coverage, but database code Transact-SQL is just ignored. This increases the risk of lack of code confidence, which raises questions like Will the stored procedure work as expected?, Is the database table composed as expected?, etc.

So we need to do something to reduce/avoid the risk and improve code confidence. The best way to achieve it is by writing test cases, either by using several integration/e2e tests covering all scenarios or by writing unit test cases around each objects like stored procedures, functions, etc.

I prefer unit tests over integration tests due to the reason that they are simpler and cheaper, so as to keep the keep the scope of integration tests to integration itself. Please refer here for more details.

There are several handy open source tools available for this purpose, but I am going to pick a tool called tSQLt which works specifically for Microsoft Sql Server

tSQLt is a wonderful tool that comes with several in-built assert functions which helps engineers writing test cases effectively and rapidly.

Here are some of the high level steps to get it accomplished.

Setup the testing framework ( tSQLt) in the database server
Deploy all the test cases in the database
Execute all the deployed test cases

How are we going to execute the above steps?

Testing frameworks like Xunit, uses test runners, which executes all the available test cases in that project. So I thought of making use of it by writing a simple test class fixture to perform steps #1 & #2, and then a simple test case to execute step #3. Alternatively, we can also perform all these steps using simple powershell scripts or any other language of your choice.

Getting Started

A complete sample project can be found here. Please follow readme documentation for usage instructions.

Prerequisites

Latest .NET SDK for the Xunit project
Admin previlages (sa permission) on the database server to execute tSQLt setup scripts. (Build/CI environment only)

Steps

Create an Xunit project named Sample.DB.TsqlTests, preferably use latest .NET framework. Run the command dotnet new xunit -n Sample.DB.TsqlTests
Create a folder called tSQLt_Framework under the project
Dowload the framework from tsqlt.org, extract and move into folder tSQLt_Framework. The folder contains sql files for tasks like server preperation (PrepareServer.sql), tSQLt framework setup (tSQLt.class.sql), test examples (Example.sql), etc.
Add the below xml element in the .csproj file. This will ensure that all the sql files (includes tSQLt framework and tests) are copied to the bin folder after any successful build.
```
<ItemGroup>
    <None Include="**/*.sql" Exclude="**/bin/**;**/obj/**;" CopyToPublishDirectory="Always" CopyToOutputDirectory="Always" />
</ItemGroup>
```

To perform steps #1 & #2, let’s create a test fixture class named TsqltTestFactory as below to start with. tSQLt scripts contain GO statements, so there are 2 ways we can execute/deploy these sql scripts. One way is to use SqlServerConnection -> new Server(new ServerConnection(connection)) OR the other is to use SqlConnection -> new SqlConnection() after ignoring GO statements and execute in chunks. The method ExecuteScriptsInChunks(string[] sqlContent) in the below code snippet explains it.

public class TsqltTestFactory : IDisposable
{
    private SqlConnection connection;

    public TsqltTestFactory()
    {
        connection = GetSqlConnection();
    }

    private async Task ExecuteScriptsInChunks(string[] sqlContent)
    {
        var sqlBuilder = new StringBuilder();
        foreach (var line in sqlContent)
        {
            if (line.Trim().ToLower() == "go")
            {
                await ExecuteNonQueryAsync(sqlBuilder.ToString());
                sqlBuilder = new StringBuilder();
            }
            else
                sqlBuilder.AppendLine(line);
        }
    }

    public async Task<int> ExecuteNonQueryAsync(string sql)
    {
        using var cmd = new SqlCommand(sql, connection);
        return await cmd.ExecuteNonQueryAsync();
    }

    private SqlConnection GetSqlConnection()
    {
        var sqlConnection = new SqlConnection("Server=localhost;Database=Sample;User Id=sa;Password=XXXXXXX;");
        sqlConnection.Open();
        return sqlConnection;
    }

    public void Dispose()
    {
        // Dispose logic here
        // close sql connection here
    }
}

Add a method (as below) to perform server preperation task. For this, we need to execute the sql file PrepareServer.sql. This is basically an onetime operation, however doesn’t hurt if executed multiple times.

Note: This needs sa permission.

private async Task PrepareSqlServer()
{
    var prepareServerSqlFile = Path.Combine(Directory.GetCurrentDirectory(), "tSQLt_Framework", "PrepareServer.sql");
    var prepareServerSql = File.ReadAllLines(prepareServerSqlFile);
    await ExecuteScriptsInChunks(prepareServerSql);
}

Add another (as below) method to perform configuration of tSQLt framework. For this, we need to execute the sql file tSQLt.class.sql.

private async Task ConfigureTsqltFramework()
{
    var tSQLtClassSqlFile = Path.Combine(Directory.GetCurrentDirectory(), "tSQLt_Framework", "tSQLt.class.sql");
    var tSQLtClassSql = File.ReadAllLines(tSQLtClassSqlFile);
    await ExecuteScriptsInChunks(tSQLtClassSql);
}

To deploy all the tSQLt test cases that we create, let’s add the below method in the test class fixture.

private async Task DeployAllTsqltTests()
{
    var tsqltTestFiles = Directory.GetFiles(Directory.GetCurrentDirectory(), "*Tests.sql", SearchOption.AllDirectories);

    foreach (var tsqltTestFile in tsqltTestFiles)
    {
        var tsqltTestFileSql = File.ReadAllLines(tsqltTestFile);
        await ExecuteScriptsInChunks(tsqltTestFileSql);
    }
}

To execute all the above methods in order and before even executing the tests, let’s call these methods from the constructor of the fixture class as below.

public TsqltTestFactory()
{
    connection = GetSqlConnection();
    Task.WaitAll(PrepareSqlServer());
    Task.WaitAll(ConfigureTsqltFramework());
    Task.WaitAll(DeployAllTsqltTests());
}

At this point we are done with execution of steps #1 & #2, which are configuring the database server and deployment of tests.

Finally, let’s create a simple test (as below), to run all the tests on the database. Make sure of the usage of the created fixture. In addition, you can capture the results from the database using a ITestOutputHelper. For more details, you can look into my sample project

public class TsqlUnitTests : IClassFixture<TsqltTestFactory>
{
    private readonly TsqltTestFactory factory;

    public TsqlUnitTests(TsqltTestFactory factory)
    {
        this.factory = factory;
    }

    [Fact]
    public async Task ExecuteAllTsqltTests()
    {
        await factory.ExecuteNonQueryAsync("EXEC tSQLt.RunAll;");
    }
}

Awesome, we have created a tSQLt runner using Xunit.
Let’s write our first test case here. For that, create a file called Table1Tests.sql under the test project.

Create the test (as below), which verifies the composition of the table dbo.Table1. To learn more about writing test cases, refer here. I will be publishing another article on how to write test cases for different types of database objects.

-- create a test class
EXEC tSQLt.NewTestClass 'Table1Tests';
GO
-- create a test function
CREATE PROCEDURE Table1Tests.[test if Table1 table is composed of]
AS
BEGIN
  -- Arrange
  -- create expected table
  CREATE TABLE Expected
  (
      COLUMN_NAME VARCHAR(20), 
      COLUMN_DEFAULT VARCHAR(20), 
      IS_NULLABLE VARCHAR(20), 
      DATA_TYPE VARCHAR(20), 
      CHARACTER_MAXIMUM_LENGTH VARCHAR(20)
  )

  -- create actual table
  SELECT * INTO #Actual FROM Expected 

  -- load expected table with expected table composition values
  INSERT INTO Expected VALUES ('Id',null,'NO','int',null)
  INSERT INTO Expected VALUES ('Text',null,'NO','varchar','50')
  INSERT INTO Expected VALUES ('Description',null,'YES','varchar','500')

  -- Act
  -- load actual table with `Table1` composition
  INSERT INTO #Actual
    SELECT COLUMN_NAME, 
      COLUMN_DEFAULT, 
      IS_NULLABLE, 
      DATA_TYPE, 
      CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH 
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table1'

  --Assert
  EXEC tSQLt.AssertEqualsTable 'Expected', '#Actual';
END;

Awesome, we have created our first tSQLt test case.
If we run this test, it will definitely fail because we don’t have a table called Table1 created.

So, what’s next? Let’s try to make the test pass.

For this, we need to create a database project (.sqlproj) and try to test it using tSQLt runner we built.
We can use Visual Studio Database Project template OR by using a SDK style project.
For creation and deployment of the database project (.sqlproj) using SDK style, please follow the steps provided in my another article How to create a dotnet SDK style SqlServer database project?
There we go, we should have the database project ready and deployed successfully.
Finally, let’s verify if Table1 composition is as expected, by running the test case by executing ExecuteAllTsqltTests(). If the test passes, we should see the result as below. Use ITestOutputHelper to stream information from SqlConnection as done in the sample project.

In the next article, I shall try to go a bit deep into writing tests for different database objects like table, stored procedures, constraints, etc.

Sample Project

A complete sample project can be found here, which includes..

SDK Style database project with a sample database object (table)
Compiling and publishing the project as .dacpac
Creation of Microsoft Sql Server in docker
Deployment the project (from published artifact .dacpac) into the Microsoft Sql Server
Testing database objects using tSQLt

Love helping the community!

Unit Testing Microsoft SQL Server Objects with Transact-SQL

Ensuring Robustness and Reliability in Database Development