SQL Server unit testing with tSQLt, Docker, and GitHub Actions!

The real game changer is automation!

Published in

CodeX

7 min readOct 21, 2022

Photo by Caspar Camille Rubin on Unsplash

In the previous article, the third of this series, we described how to write and run a unit test for a trigger! This article describes how to automate the testing for one or more SQL Server objects using tSQLt, Docker, and GitHub Actions!

Technologies and frameworks used here

tSQLt is a unit testing framework for SQL Server. It provides the APIs to create and execute test cases, as well as integrates them with continuous integration servers. The power of the tSQLt framework has been described in my previous article The tSQLt framework and the execution of a test.

Docker is one of the most popular systems for running applications in isolable, minimal and easily deployable environments called containers. Since SQL Server 2017, the SQL Server Engine can run in a Docker container, a typical usage of running SQL Server in a Docker container concerns the automation of software tests.

GitHub Actions is a continuous integration and continuous delivery (CI/CD) platform that allows you to automate your build, test, and deployment pipeline. You can create workflows that build and test every pull request to your repository, or deploy merged pull requests to production. GitHub provides Linux, Windows, and macOS virtual machines to run your workflows, or you can host your own self-hosted runners in your own data center or cloud infrastructure such as Microsoft Azure.

Case history

The AdventureWorks2017 database contains the Production.Product table that stores products managed and sold by the fake company Adventure Works LTD.

The trigger we have wrote is to prevent the insertion of new products with values less than 10 as a “safety stock”. The Company wishes to always have a warehouse stock of no less than 10 units for each product. The safety stock level is a very important value for the automatic procedures: it allows to re-order materials. The creation of new purchase orders and production orders are based on the safety stock level. To make our trigger simple, it will only respond to the OnInsert event, for INSERT commands.

The TSQL scripts for creating the trigger and related unit tests are available on the GitHub sql-server-demos-ci-cd repository, the stored procedure Production.usp_Raiserror_SafetyStockLevel handles errors centrally.

Unattended unit tests execution with tSQLt, Docker and GitHub Actions

The implementation of the trigger and related unit tests has been done, the next challenge is to automate the execution of the tests at each commit on the main branch of the repository. To achieve the goal, it is necessary to identify a Continuous Integration/Continuous Delivery platform capable of supporting the use of Docker containers.

GitHub Actions will be our CI/CD platform, it supports the use of Docker containers and it is intimately integrated into GitHub, the source control that manages our source code. The use of GitHub Actions is not the only possibility but for this project it is certainly the most suitable.

Let’s go ahead with the creation of a workflow: an automated and configurable process that will execute one or more jobs. Workflows are defined with a YAML file stored in the same repository which holds the source code. The workflows will be triggered when an event occurs in the repository (for example a commit).

A workflow can also be activated manually or according to a defined schedule. The YAML file that implements the test automation workflow is available here, the fundamental steps are:

Definition of activation events
Creating a Docker container from a SQL Server image on Linux
AdventureWorks2017 database recovery
Installation of the tSQLt framework
Creating the database objects to be tested (SUT)
Creation and execution unit tests

Definition of activation events

The definition of the activation events is typically done at the beginning of the YAML script with a code snippet similar to the one shown below. The workflow is activated when push or pull request events occur on the “master” branch. The “workflow_dispatch” specification allows you to run the workflow manually from the actions tab.

# Controls when the workflow will run
on:
  # Triggers the workflow on push or pull request events but only for the "master" branch
  push:
    branches: [ "master" ]
  pull_request:
    branches: [ "master" ]
    
  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:

Creating a Docker container from a SQL Server image on Linux

Creating a Docker container from a SQL Server image on Linux can be done by requesting the sqlserver service accompanied by the path to the Docker image you want to use. Official images provided by Microsoft for SQL Server on Linux are available here. We will not use an official image downloaded from the Microsoft registry. We will use a Docker image of SQL Server with the AdventureWorks database installed, this image is published by chriseaton, you can find it at this link. The following YAML code snippet sets up the SQL Server service.

jobs:
  windows-auth-tsqlt:
    name: Installting tSQLt with SQL Auth
    # The type of runner that the job will run on
    runs-on: ubuntu-latest
    
    services:
      sqlserver:
        image: chriseaton/adventureworks:latest
        ports:
          - 1433:1433
        env:
          ACCEPT_EULA: Y
          SA_PASSWORD: 3uuiCaKxfbForrK

In order to reference the newly created Docker container it is important to save its identifier in an environment variable. The following snippet of YAML code sets the ENV_CONTAINER_ID variable with the ID of the container created.

- name: Set environment variable ENV_CONTAINER_ID
  run: echo "ENV_CONTAINER_ID=$(docker ps --all --filter status=running --no-trunc --format "{{.ID}}")" >> $GITHUB_ENV

AdventureWorks2017 database recovery

The AdventureWorks2017 database recovery can be performed using the following docker exec command.

- name: Restore AdventureWorks2017
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -Q "RESTORE DATABASE [AdventureWorks2017] FROM DISK = '/adventureworks.bak' WITH MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks.mdf', MOVE 'AdventureWorks2017_log' TO '/var/opt/mssql/data/AdventureWorks_log.ldf'"

Installation of the tSQLt framework

The installation of the latest version of tSQLt framework in the AdventureWorks2017 database is done using the GitHub Actions tSQLt Installer published by lowlydba, you can find more details here and on the GitHub Actions marketplace.

The snippet of YAML code used for the installation of the tSQLt framework in the AdventureWorks2017 database is the following.

steps:
  - uses: actions/checkout@v2  - name: Install tSQLt with SQL auth on AdventureWorks2017
    uses: lowlydba/tsqlt-installer@v1
    with:
      sql-instance: localhost
      database: AdventureWorks2017
      version: latest
      user: sa
      password: 3uuiCaKxfbForrK

Creating the database objects to be tested (SUT)

The test environment is ready, we have a SQL Server instance on Linux inside a Docker container; the AdventureWorks2017 database has been restored and it is ready for use. Let’s go ahead with the creation of the trigger and the stored procedure (that manages errors), they represent our System Under Test (SUT).

The TR_Product_SafetyStockLevel trigger creation script and the usp_Raiserror_SafetyStockLevel stored procedure creation script are saved in the source directory of the sql-server-demos-ci-cd repository.

Triggers and stored procedures are created in the AdventureWorks2017 database attached to the SQL Server instance, the YAML code snippet that performs this operation is the following.

- name: Create sp usp_Raiserror_SafetyStockLevel
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./source/usp-raiserror-safetystocklevel.sql- name: Create TR_Product_SafetyStockLevel
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./source/tr_product_safetystocklevel.sql

Creation and execution of test units

The last phase of this workflow is represented by the creation and execution of the unit tests. The test class and the unit tests creation scripts are contained in the unit-test directory of the sql-server-demos-ci-cd repository.

Let’s go ahead with the creation of the test class dedicated to the TR_Product_SafetyStockLevel trigger, we called it UnitTestTRProductSafetyStockLevel. The following docker exec command, that uses sqlcmd, executes the TSQL commands contained in the test-class-trproductsafetystocklevel.sql script.

- name: Create test class UnitTestTRProductSafetyStockLevel
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-class-trproductsafetystocklevel.sql

Let’s go ahead with the creation and execution of the unit tests. Each .sql file of the “test case” family contains the TSQL commands for creating and running the related unit test. Each store procedure tests one and only one test case. For the TR_Product_SafetyStockLevel trigger we have provided four test cases. The following snippet of YAML code creates and runs the test units.

- name: Create and run test case try to insert one wrong row
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-one-wrong-row.sql- name: Create and run test case try to insert one right row
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-one-right-row.sql- name: Create and run test case try to insert multiple rows
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-multiple-rows.sql- name: Create and run test case try to insert multiple rows ordered
  run: docker exec -i $ENV_CONTAINER_ID /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "3uuiCaKxfbForrK" -d AdventureWorks2017 -b < ./unit-test/test-case-try-to-insert-multiple-rows-ordered.sql

The YAML script for our workflow is complete, you can find it here, we just have to check it by running it manually from the actions tab.

The workflow will be completed successfully if all operations performed are successful.

Summary

Unit tests developed for a SQL Server solution are not just meant to verify that requirements have been met once, prior to release; the real game changer is represented by the possibility of repeating the checks during the development of new code and during the bug fixing process. The repeatability of the tests provides the ability to automate them, an essential condition for integrating automatic tests within a Continuous Integration platform. In this article we described how to automate the testing of SQL Server objects using tSQLt, Docker and GitHub Actions!

Thanks for reading!