Working as a Data Engineer I often see that unit tests are written for the applications that are developed, but often forgotten about when doing database development. We would assume that it’s the de facto standard in 2019 if the target is to be fully DevOps as a development team.
There are many great third party tools out there that simplifies setting up the processes, most of them requires licenses. Therefore I will go back to the basics so that you cannot blame the license cost for not implementing database unit testing.
This post will focus on the core concepts around setting up an Azure SQL database using tSQLt as unit test framework. I assume that you are familiar with Azure DevOps, SQL Server Data Tools and dacpac deployments.
Please make sure that you have the following created before continuing
- Azure SQL Server with one database where it is post-fixed with CI. For example ndevdb_ci will be used throughout this post.
- Azure DevOps project connected to your favorite source control system.
Setting up the database project
As we are using SQL Server Data Tools for modelling and building the database, the Visual Studio IDE is probably a familiar sight. Create a new SQL Server Database project with the naming of your choice.
Initially we want to create a folder structure with project files that break down our solution into different parts. Make sure that all the projects are targeting Microsoft Azure SQL Database V12 if not developing on another version of SQL Server. For the purpose of this post I have created the following:
ndevdb is the project that contains our database model.
ndevdb.framework.tsql is the project that contain the tSQLt framework that is used when writing unit tests. The reason for having a separate project for the unit test framework is because we only want one place to update the definition when new versions are published.
ndevdb.tests is a composite project that will have a reference to both the tSQLt and ndevdb databases. This is the project that contains our database unit tests, and will be deployed to the CI database.
Note! The unit test project is also referring to the master database so that Azure DevOps release pipeline is able to fetch the master.dacpac file when deploying.
Note! As of 2019-02-26 SSDT contains a bug that has a hard reference to the master.dacpac file. Make sure to unload both of the projects containing the master database reference and update the include path to t he following: $(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\AzureV12\SqlSchemas\master.dacpac
Importing the tSQLt definition
The first step is to get the latest release of tSQLt from the official website. Inside the downloaded zip file we will find tSQLt.class file that is containing the script to install the framework on a targeted database. As we will install tSQLt on a Azure SQL database, running the SetClrEnabled is not necessary and can be ignored.
Open the tSQLt.class file in your favorite database management/query tool and execute it. When the script is done executing the installation the following message will be given.
Navigate back to the Visual Studio solution and initiate a Schema Compare where the source is ndevdb_ci and the target is ndevdb.framework.tsqlt.
When the schema compare is done we will see the list of all the tSQLt objects that was installed. Click the update button to import everything to the database project and the solution should look something like this.
Note! I have yet not discovered an easy way to import the tSQLt objects without installing it on a database first, so if you know a better way please let me know in the comments below!
Create your first unit test
With some “television magic” I have created the initial structure of the ndevdb database with a table named Customer and a procedure to insert a customer.
The Customer table has the following definition
And the InsertCustomer procedure has the following definition
Currently the procedure for inserting a customer has no functionality because we always want to write our unit tests to verify that the acceptance criteria for the method is met. This means that our unit test always will start failing when we do the initial deployment to our ndevdb_ci database. Let’s populate the tests project with a unit test.
First we need to define a test-class. This is normally done using the NewTestClass method in the tSQLt framework, but to get the it compiled in SSDT we have to define it manually. Create a new SQL script that is part of the build process and name it testCustomer. Add the following content.
When the test-class is defined we are now able to write and run unit tests for that schema. Our first test will be to verify that the middle name of the customer is saved when the parameter @MiddleName has a value. Create a new stored procedure with the name [test Given customer is created When Customer has a middle name Then save the @MiddleName]. You may notice I follow GivenWhenThen style for representing my unit tests.
I haven’t written much about structuring the project, but you may have something looking like the below image. For now ignore the file run_tests.sql as it’s a topic for another post, and is often run as a post deployment step on publish.
Let’s prepare the solution for deployment using a publishing profile that also will be included in the release pipeline later. Create the publish profile with the name ndevdb.tests.publish.xml and make sure that you configure it to drop things in target but not in source.
For now make sure that the Target database connection is targeting ndevdb_ci and click the publish button. If the deployment went well make sure that the target database connection is removed and re-save the publish profile.
Navigate back to your favorite database management/query tool and execute the EXEC tSQLt.RunAll against the ndevdb_ci database. You will notice that the test case we have written is failing, but – “hey! we want the test result as a report in Azure DevOps and not as a text output?”
Configure release pipeline to execute unit tests and publish test results
Here I assume that you have most of the knowledge about setting up a build pipeline in Azure DevOps as it’s required to get the release build. These few next steps will enable us to display the test results within Azure DevOps.
Create a release pipeline with name ndev-db-release where the artifact source is set to the output of your build pipeline. When that is done create a new stage with the name Unit Tests that will have two responsibilities
- Deploy our database based on the output of the ndevdb.tests project
- Run and retrieve the test results so they can be published to the deployment stage.
In basics this require a few tasks; Azure SQL Database Deployment, Azure Powershell and Publish Test Results.
The configuration of Azure SQL Database Deployment task is straightforward, just make sure to pick the test project (ndevdb.tests) and corresponding publish profile (ndevdb.tests.publish).
For the Azure Powershell task we will for demonstration purpose use an inline script that will connect to the database and run the necessary stored procedures. The steps for connecting are taken from the answer given on StackOverflow and modified to suit the needs for this post.
Note! I’m running the tSQLt.RunAll command as part of the PowerShell step, but this can be a database post deployment step as well.
Publishing the test results doesn’t require any configuration if the inline script is used as is. Just add the task to be executed after the Powershell script has run.
If everything has been configured correct we should be able to save the changes and create a new release. Create a new release and wait for the Unit Tests stage to complete. When the release process is done navigate inside the release under the unit test stage and click the Test tab. Neat right? Now we only need to implement the logic that satisfies the needs of the unit test.. but that’s another story 😉
Leave your comments or feedback below, I would love to get your inputs!