Continuous Integration and Continuous Deployment (CI/CD) – SQL Server Database testing using tSQLt – Part 4

In the previous posts, we have created a Continuous integration and a Conntinuous Deployment pipeline for a SQL Server database using the Azure DevOps server. Also, we have demonstrated how we can set up the cross-database dependency for a SQL Server database project in the Azure DevOps pipeline. Below are the links in case you want to read these articles.

1. SQL Server database Continuous Integration using Azure DevOps
2. SQL Server database Continuous Deployment using Azure DevOps
3. How to set SQL Server database project dependency in Azure DevOps for cross-database object access

Now, in this post, we will discuss how we can use the tSQLt framework for database unit testing in the Azure DevOps CICD (Continuous integration and continuous deployment) pipeline. Let’s start with a brief introduction of the tSQLt framework.

What is the tSQLt framework

tSQLt is an open-source database unit testing framework that can be used to implement and execute unit testing for a SQL Server database. The test cases are implemented in T-SQL itself which eliminates the need for any additional tool. Also, it is much easier for the database developers to write database unit test cases using their favorite programming language T-SQL. This framework provided the below features:

1. Test cases are executed within transactions automatically which helps us to save some cleanup work.
2. Test cases can be grouped in a schema which helps us to group similar test cases.
3. Results can be generated into XML or in plain text which helps us to publish the test output in a CICD pipeline.
4. Facilitates mocked objects like fake tables and views which helps us to simulate the behavior of real objects without actually touching them.
5. Free and open source so no need to spend any money.
6. Easy to integrate into a CICD pipeline.

Setting up the environment

In order to perform the database testing, we need an environment where we can install the tSQLt framework and deploy our database projects so that we can execute the unit test cases against it. For that, either we can use a common dev environment or we can use a local SQL Server environment on the Azure DevOps server itself. Here, we are using a local environment configured on the same machine on which the Azure DevOps server is running.

Integrating the tSQLt database Unit test in Azure DevOps CI pipeline

Before we configure our CI pipeline for unit testing, we need to install the tSQLt library on the machine where we will execute our unit test cases. Let’s install the tSQLt library first.

Install the tSQLt framework

To install the tSQLt framework, we can follow the below steps:

• First, create a new database in SQL Server named “UnitTestDB“. We will install the tSQLt framework in this new database so that we can version control it.

CREATE DATABASE UnitTestDB
GO
USE UnitTestDB
GO

• Next, we need to download the tSQLt library from here. It will download a zipped folder which might look like “tSQLt_V1.0.7597.5637“
• Unzip the downloaded folder. After unzipping it, we should get the below files.

• Open the PrepareServer.sql file and execute it. It will enable the CLR and will install a server certificate that allows the installation of the tSQLt CLR.
• Now, open the tSQLt.class.sql file and execute it in the UnitTestDB database. If it executes successfully, the below message will be displayed.

• It will install all the required tables and procedures for tSQLt library into UnitTestDB database.

• Now, we will add a new schema named “testSchema” using the below script. We will create our unit test cases In this schema.

CREATE SCHEMA testSchema
    AUTHORIZATION [dbo];

GO
EXECUTE sp_addextendedproperty @name = N'tSQLt.TestClass', @value = 1, @level0type = N'SCHEMA', @level0name = N'testSchema';

• Now, let’s create a sample unit test case into the testSchema using the below script.

CREATE PROC testSchema.[test numbers are equal]
AS
BEGIN

--ASSEMBLE
DECLARE @var1 INT = 10

--ACT
DECLARE @var2 INT = 10

--ASSERT
EXEC tsqlt.AssertEquals @var1, @var2, 'Values are not Equals.'
END;

• In order to execute all the test cases, we need to execute the below code.

EXEC tSQLt.RunAll
GO;

• It should output as below.

Version control the tSQLt framework and unit test cases using a database project

In order to version control our UnitTestDB, we need to add a new database project into our visual studio solution (which we have created in our previous tutorials). To add a new database project, follow the below steps:

• Open the visual studio solution named “Demo_Database_CICD” which we have created in part 1.
• Right-click on the solution and choose Add -> New Project.
• Choose SQL Server Database Project as a template and click on Next.
• Put a project name like UnitTestDB and click on the Create button.
• Next, right click on UnitTestDB project and choose Import -> Database.
• Use Import database wizard to import UnitTestDB.
• Next, right-click on the tSQLt folder in the database project and add a pre-deployment script named “Script.PreDeployment.EnableCLR.sql“. Add the below script to the file.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

ALTER DATABASE UnitTestDB set TRUSTWORTHY ON
GO

• In order to build this database project successfully, we need to add a reference to the master database. To add it, right-click on the References in the UnitTestDB and choose “Add Database Reference“.

• Also, as we need to publish the database projects in a local environment for unit testing, we have to add a publish.xml file in each of the database projects. To add a publish script, right-click on each of the databases and choose Publish. We need to provide the values of the variables in case we are using any cross-database references.

• Our Database solution should look like this.

Creating a Continuous Integration (CI) pipeline with tSQLt unit test task

Go to Azure DevOps UI and create a new build pipeline named “CICDDemoPipelineWithUnitTest“. You can follow the steps we have explained here in order to create a new continuous integration pipeline. Or you can follow the below steps:

1. Add a Build task

Add a build task and choose the solution/project file which represents the entire solution/project in the project field. Leave the MSBuild version as the latest and select the MSBuild architecture appropriately.

Also, in order to deploy the project into a local environment, we need to add MSBuild Arguments as below.

/t:Build;Publish /p:SqlPublishProfilePath=UnitTest.publish.xml;CmdLineInMemoryStorage=True

Please make sure that we have added the UnitTest.publish.xml file in each and every database project into our visual studio solution.

2. Add a Powershell task to execute the Unit test cases

In order to execute the test cases into our CI pipeline, we need to add a PowerShell task.

Select the Inline option to provide the target script and add below PowerShell script.

# Write your powershell commands here.

Write-Host "$(System.DefaultWorkingDirectory)"

$connectionString = "Data Source=localhost;Integrated Security=True;Initial Catalog=UnitTestDB"
$sqlCommand = 'EXEC tSQLt.RunAll; EXEC tSQLt.XmlResultFormatter'

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null

$connection.Close()
$dataSet.Tables[0].Rows[0].ItemArray[0] | Out-File "$(System.DefaultWorkingDirectory)\UnitTestDB\bin\Debug\TEST-Results.xml"

# Use the environment variables input below to pass secret variables to this script.

This task should look like this.

3. Publish test result

In order to publish the test output to the Azure DevOps pipeline, we need to add a Publish test result task.

Configure the Publish Test Results task like this.

4. Add a Copy Files task

Next, go to the pipeline Agent and search for the “Copy Files” task and add it. Put “$(system.defaultworkingdirectory)” as Source folder and “$(build.artifactstagingdirectory)” as Target folder value. Also, put “**\bin\Debug\*.dacpac” as contents value. Here, we are copying the DACPAC file from the working directory to the staging directory. This task looks like this.

5. Add a Publish Build Artifact

Finally, search for a “Publish Build Artifact” task and add it to the pipeline. Put “$(build.artifactstagingdirectory)” as Path to Publish value and rename the artifact as Database. It looks like this.

6. Save the pipeline

Now, we can save the pipeline. The build pipeline should look like this.

Now, whenever a Git push event occurs, the build pipeline will execute and publish the solution using the publish profile and then it will execute the unit test cases using the PowerShell script. Then, it will publish the test results to the pipeline and copy the artifacts. Finally, it will publish the build artifacts.

Thanks for the reading. Please share your inputs in the comment section.