Azure SQL unit testing with tSQLt using Azure DevOps

Azure SQL unit testing with tSQLt using Azure DevOps

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!

  1. Hey!

    I am struggling with the release pipeline and hope you can help me out. I have a Visual studio project like you have set up and an azure sql database (xx_ci) with tsqlt. In my release pipeline I have the tasks you have defined, but when I am running it, it deletes all the tables and procedures that is not defined in my test sql project. This happens in the Azure SQL DacpacTask where I have set all parameter to the azure sql database referencing the test XXX_ci database and further set the Action to Publish, the Dacpac file is the test dacpac (…tests.dacpac) and the publish profile is set to the xx.tests.publish.xml which I adjusted according to your blog post)

    When this release pipeline is running it deletes all tsqlt tables and procedures, since this is not found in the test project. This is a side effect I was not expected. I hoped that it only would deploy the tests from the test project and keep all other things.

    Since you have done this, I hope you can guide me through this so I can make it work. I believe I have done things what you said but I am not coming through the steps in your blog since it deletes all tsqlt things and therefore is not able to run the tests.

    Hope you can read this and give me some advice

    1. Hey,

      Sorry for late reply, been quite busy before the summer hit us in Norway and then off work doing some mountaineering during the vacation time. Did you find out what was wrong? If not, I may think that the issue occurs due to some wrong reference in the test project.
      1. Open the Visual Studio solution and find the test project. Open the folder references and verify that it’s referencing the tSQLt project and your database project. In my example I have two references – ndevdb and ndevdb.framework.tsqlt.
      2. If the references are there, do you remember if the database location was set to same database during the Add database reference? If not, I guess this is the case. When adding database references and setting the database location as same database the build should generate a dacpack where your database will contain all objects from the tSQLt project and your database project.

      Did any of the above help? If not please drop me a message here or on LinkedIn and I try help out πŸ™‚

  2. Thank you very much for this! I was able to get my own deploy/test/report pipeline set up. Very informative and helpful.

  3. Hi Nicolai, Great article. Currently using this one for a mock up. I’ll blog about this later! But one issue I have is the execution of the powershell script. The build and release happens on different servers and therefore has different ip adresses. I have contineous firewall issues. I see different Ip adresses. So I was wondering how you solved that? Hennie

  4. I’ve used now the option : “Allow access to azure services” and seems to work now.

    I’ve checked the location of the Azure Devops for me and compared the ip adresses with the list provided : publicIPs_20190715.xml but can’t find any of the blocked Ip adresses.

    For now it’s ok but it seems a bit unhandy to add 50 to 100 ip adresses for connecting to Azure SQL database. Even it seems that the ip adresses changes on regular base?!

    I’m not an admin so I expect that I miss something here….But what πŸ˜‰

    1. Cool, Hennie! Read your blog and I’ve bookmarked it πŸ™‚

      I see your challenge and by default Azure SQL is protected by a firewall. Allowing Azure Services however will open up the database for all people running something in Azure I think. In Azure DevOps you could go to the Firewall tab for the database deployment task and choose the Auto Detect feature. Remember to check the option for deleting database rules when it’s done as well. It can be that the Azure DevOps service need to be a registered as an enterprise application (app registration) in AD with correct permissions to be able to perform this.

  5. Hi Nikolai,

    Thanks for this great post.

    I am using SSDT for db unit tests. Having a problem in managing connection string securely. Whatever connection string realized at DevOps pipeline execution is physically present (and copied) in to the agent where it is running. It is a security hazard, and I want to get rid of this. Since the values of the connection string is updated during execution, I can’t see the encryption of connection string it it’s own form won’t work. Any ideas would be appreciated.

    Btw, I wrote an article how the configuration looks like here: https://medium.com/@shanmugamchinnappa/securely-configuring-azure-devops-pipeline-for-sql-unit-testing-be81f4c2cc10

  6. Hi Nikolai
    Thanks so much for this, very helpful. I can’t believe this is the only place in the whole internet where someone has been able to spell it out!

    That bloomin’ master.dacpac bug arrgh, thought i was going mad

    thanks again

    1. Hi Jim,

      No problem! It’s a really annoying bug, and almost drove me mad as well. Glad my post helped you out

      Br,
      Nikolai

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Up Next:

Azure Data Studio now support MFA

Azure Data Studio now support MFA