tSQLt – Automate Unit Test Execution with Azure DevOps Pipelines

In the previous data recipe, tSQLt for Data Warehousing, test your Stored Procedure, we created our first Unit Test to validate our SQL Server Stored Procedures. In this data recipe, we will dive into the process of Database Unit Testing automation with Azure DevOps.

Recipe Overview

Why?

Before we dive into “how to automate”, I would like to explain why bothering about Unit Test Automation. Unit Tests in general are a good practice to identify potential issues with new source code upfront. Therefore, executing Unit Tests is a good idea. By automating Unit Tests, developers can be sure that Unit Tests will be executed for every single deployment that happens.

In the upcoming step-by-step guide, Unit Test automation will be implemented in a YAML pipeline. The pipelines I’m using are very simplified in comparison with many other pipelines I have seen in the past.

Step-by-step recipe

Introduction

For this data recipe, I’m assuming that you already have a working CI pipeline in place. If not, you can follow the following data recipe to get you up to speed: Azure DevOps YAML Pipelines – Building Database Projects

The final dish we want to serve at the end of this data recipe is a pipeline that executes our created Unit Tests and hopefully succeeds after the pipeline execution is completed.

Before we dive into the practicals, let’s take a look at the following screenshot. As you can identify, the pipeline has been executed 2 times. During the first execution, the 2nd stage of the pipeline failed. Why? When 1 of the Unit Tests fails, I want my pipeline to fail in the end and notify me.

If we look closer at why the pipeline failed, we can identify the error message that explains: “There are one or more test failures detected in result files.”

If we navigate to the Tests pane, which is located in the top left corner next to the Summary pane, we can figure out which of my Unit Tests failed.

As you can see in the screenshot above, my Unit Test to validate if my Dim Product synonym exists failed. This tells me, that I probably didn’t create the synonym in my SQL Database Project.

You can change the filtering on the right-hand side of this overview and also show the passed Unit Tests.

After making the change in the SQL Database Project and pushing the changes to the remote, the Unit Tests succeed.

Now that we know what we want to achieve, we can take a closer look at how we are going to implement the execution of unit tests in our pipeline.

Adding Unit Tests to a pipeline

In Azure DevOps, I’m starting from my existing CD-Unit-Testing pipeline which currently only contains the Deploy stage. (If you don’t have any CD pipeline in place yet, the following repo can help you get started: Azure DevOps Pipeline Ideas). In the coming steps, we’re adding the UnitTesting stage together.

After deploying the unit tests during the deploy stage, we can execute the unit tests via our CD pipeline. To get this done, we’re going to use PowerShell with dbatools (dbatools – the community’s sql powershell module).

For the ease of this recipe, we will be using Inline PowerShell code. To get started, we open our existing CD pipeline, navigate to the Assistant and search for PowerShell.

After choosing the PowerShell task that is highlighted in the screenshot above, we can add our PowerShell commands to execute the Unit Tests.

Install-Module dbatools -Force -Scope CurrentUser
                
$connstring = "Data Source=TCP:localhost,1433;User ID=<USER>;Password=<PASSWORD>;Connect Timeout=30;"
$server = Connect-DbaInstance -ConnectionString $connstring
$result = Invoke-DbaQuery -SqlInstance $server -Database "BiteSizeDataStaging" -Query "exec tSQLt.XmlResultFormatter" | Select-Object ItemArray -ExpandProperty ItemArray
$result | Set-Content "tSQLtResult.xml"

As you can see in the code sample above, I’m using SQL Authentication, this is not required and in all fairness, there are better solutions to authenticate securely.

As a final step, we need to add a “Publish Test Results” task, which allows us to get the results of the unit tests back to Azure DevOps.

Once selected the task, we need to provide the configuration, which doesn’t require too much information. We need to provide the Test result format, where we choose JUnit. To continue, we need to provide the path to the location where the test results were saved in the previous step. In my case, I’m looking for my tSQLtResult.xml file. And finally, we want the Pipeline to fail when there are failures in the publishing test results.

That’s all we need to do to automate our Unit Testing process. Below, I’m showing how my CD-Unit-Testing.yaml looks like (be aware I took out my Trigger and my pool configuration).

- stage: UnitTesting
    jobs:
      - job: UnitTestingProcess
        displayName: Unit Testing Process
        steps:
          - checkout: none
          - task: PowerShell@2
            inputs:
              targetType: 'inline'
              script: |
                Install-Module dbatools -Force -Scope CurrentUser
                
$connstring = "Data Source=TCP:localhost,1433;User ID=<USER>;Password=<PASSWORD>;Connect Timeout=30;"
$server = Connect-DbaInstance -ConnectionString $connstring
$result = Invoke-DbaQuery -SqlInstance $server -Database "BiteSizeDataStaging" -Query "exec tSQLt.XmlResultFormatter" | Select-Object ItemArray -ExpandProperty ItemArray
$result | Set-Content "tSQLtResult.xml"
          - task: PublishTestResults@2
            inputs:
              testResultsFormat: 'JUnit'
              testResultsFiles: '**/tSQLtResult.xml'
              failTaskOnFailedTests: true

Bon Appétit!