Automate SQL Testing using Azure DevOps and SQLCover

Overview 

If you’re a developer, you probably know what code coverage is, right? It’s basically a way of tracking what code is and isn’t covered by automated tests. We normally get this information as a percentage of lines of code covered and can generate reports that give us a visual representation of our coverage. This helps developers identify holes in their testing and encourages overall better code that’s less likely to break. But I digress, this blog isn’t about what coverage is, there’s endless googling to be done there. I want to talk about how ClearBank identified a hole in our coverage and fixed it – which might inspire you to do the same.  

The problem 

At ClearBank we’re primarily a .Net house, which in turn means most of our code is C#. We have the relevant tools and checks in our C# CI build pipelines to make sure all new code is well covered. But looking around something was missing.  

When it came to our SQL stored procedures (which we have a lot of) there wasn’t any coverage. Although we try to keep the amount of logic in stored procedures to a minimum, it’s critical that we have confidence they’re functioning as expected. So why are we not treating them with the same respect as our C# code? Doesn’t it make sense that our stored procedures have the same level of test coverage? 

The .NET tool 

We recently discovered GoEddie SQLCover. It took a little poking around in the SQLCover repo before we found an unpublished .NET tool. We knew this would be a good fit with our Azure DevOps build pipelines. But this tool wasn't ready to go out of the box, we wanted to make some small updates. So, we forked the repo and updated the tool to .Net 6, then published the updated tool to our private ClearBank NuGet feed. 

How it works 

Although there are multiple commands within the .NET tool, let’s focus on the one we are interested in. I think what will help us understand the tool is if we break down the SQLCover command we are going to use. 

SQLCover -c Get-CoverExe -e Export-OpenXml -k < DatabaseConnectionString > -d < DatabaseName > -t <Executable> -a < Arguments >

The first param we are passing in with the -c tag is Get-CoverExe this means we can pass in any executable in and it will run it while SQLCover is analysing the database. The following arguments are as follows:

  • -e The format we want the coverage to be exported 
  • -k The Connection string of the database we are running our tests against 
  • -b The database name 
  • -t The executable we are going to pass in… for example dotnet 
  • -a The arguments for example test ./path 

The tool takes the connection string and connects to the database. The process of analysing the database begins. While SQLCover is analysing the database it then runs the executable. Once the executable has completed, it stops the SQLCover process and outputs the coverage information in the desired format. In this case Open XML. 

Write tests against Stored Procedures using xUnit 

Because we like C#, we’re going to use xUnit to test our stored procedures. Below is a quick run through on how to set up your test project: 

Step 1 

Create your xUnit project and add an appsettings.json file. The file should contain the following JSON 

{
  "ConnectionStrings": {
    "DatabaseName": "Server=localhost,1433;Database=DatabaseName;Integrated Security=False;User Id=sa;Password=P@ssw0rd"
  }
}

Note: you will need to replace the DatabaseName 

Remember this password: P@ssw0rd. We’ll use it later on when spinning up our SQl Server in the CI build. 

Step 2 

Set up connection and write some tests. You might want to add data to the db before running the tests, but you can figure that bit out. Below is a simple test setup example: 

    public class StoredProcedureTests
    {
        private IDbConnection _connection;
     
        public StoredProcedureTests()
        {
            var configuration = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json")
                .Build();

            var connectionString = configuration.GetConnectionString("DatabaseName");
            _connection = new SqlConnection(connectionString);

            // more potential set up
        }

        // Imagine we already seeded some records for this test.
        [Fact]
        public async Task Test()
        {
            // Arrange
            var userFirstName = "Sally";
            var userAge = 30;

            var parameters = new DynamicParameters();
            parameters.Add("@FirstName", userFirstName);
            parameters.Add("@Age", userAge);

            // Act
            var user = await _connection.QueryAsync<User>("dbo.uspGetUserByFirstNameAndAge", parameters, commandType: CommandType.StoredProcedure);
            var result = user.FirstOrDefault();

            // Assert
            Assert.Equal(userFirstName, result.FirstName);
            Assert.Equal(userAge, result.Age);
        }
    }  

How we to set up our Azure DevOps yaml pipelines 

Step 1 

Spin up a Docker container and create a SQL Server. 

      - pwsh: |
          docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=P@ssw0rd" `
              -p 1433:1433 --name sql `
              -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

          # Wait a little while and see if the container doesn't automatically shut down
          # If the sql server container isn't running at this point, then you may need to update the container image version. See https://hub.docker.com/_/microsoft-mssql-server
          Start-Sleep -s 3
          docker ps
        displayName: Start Docker SQL Server

Important: The "SA_PASSWORD=P@ssw0rd" is the same as what's in our app settings. 

Step 2 

It's important the test stage runs after the DACPAC build has run. We use DACPAC to deploy changes to our databases and we can deploy it the same way we do for production services. 

      - task: DownloadBuildArtifacts@0
        displayName: Download Database artifacts
        inputs:
          buildType: current
          artifactName: ${{ parameters.dbArtifactName }}

      - script: |
          apt-get update
          apt-get install unzip

          cd ~
          wget "https://go.microsoft.com/fwlink/?linkid=2165213" -O sqlpackage.zip
          unzip sqlpackage

          dotnet sqlpackage.dll /Action:Publish /SourceFile:"$(System.ArtifactsDirectory)/dacpacFileName.dacpac" /TargetConnectionString:"Server=localhost,1433;Database=databaseName;Integrated Security=False;User Id=sa;Password=P@ssw0rd" "/p:CreateNewDatabase=true"
        displayName: Deploy dacpac via downloaded SqlPackage

Step 3 

Let run our tests and get some coverage! 

      - script: dotnet tool install --global SQLCoverCore --ignore-failed-sources
        displayName: Install SQLCover

      - script: SQLCover -c Get-CoverExe -e Export-OpenXml -k "Server=localhost,1433;Database=DatabaseName;Integrated Security=False;User Id=sa;Password=P@ssw0rd" -d DatabaseName -t "dotnet" -a "test ./xunitTestProject -l:trx;LogFileName=TestResults.xml"
        displayName: Start SQLCover and Run Tests

We went through the arguments we’re passing in the previous example. The main things to note are the following:

  • -k The connection string matches what in the test projects appsettings.json 
  • -t The dotnet command which is the executable. 
  • -a The arguments we are passing in with the executable. In this case, 'test' is followed by the path to the test project. 

Once the tool has run it will output the file 'Coverage.opencover.xml'. This contains all the coverage info. But for us to easily integrate it with Azure DevOps, we use the Report Generator tool to convert the OpenCover format to Cobertura. 

      - script: dotnet tool install --global dotnet-reportgenerator-globaltool --ignore-failed-sources
        displayName: Install ReportGenerator

      - script: |
          cd Coverage
          reportgenerator -reports:./Coverage.opencover.xml -targetdir:. -reporttypes:Cobertura
        displayName: Create SQL coverage report

Step 4 

We can now easily publish the tests and coverage in Azure Devops like so. 

      - task: PublishTestResults@2
        displayName: Varify and publish test results
        inputs:
          testResultsFormat: VSTest
          testResultsFiles: "**/TestResults.xml"
          failTaskOnFailedTests: true

      - task: PublishCodeCoverageResults@1
        displayName: Publish SQL coverage
        inputs:
          codeCoverageTool: Cobertura
          summaryFileLocation: $(Build.SourcesDirectory)/Coverage/Cobertura.xml
          pathToSources: $(Build.SourcesDirectory)/Coverage

This will display test and coverage reports for pipeline runs. These images were taken from one of our pipelines.

You can see these reports by selecting the below tabs:

Tests

Coverage

Extras

As a bonus, we now run the same coverage checks we use in our C# pipelines. For example, failing builds if coverage drops.

    jobs:
      - template: sql/dotnet-test-with-sqlcover.yaml@templates
        parameters:
          databaseName: databaseName
          dbArtifactName: dbArtifactName
          dacpacFile: FileName.dacpac
          testsDirPath: PathToTestDirectory
          additionalArguments: "/p:CreateNewDatabase=true /v:environment=cibuild"
          additionalBuildSteps:
          - task: BuildQualityChecks@8
            displayName: Check SQL coverage
            inputs:
              checkWarnings: false
              checkCoverage: true
              coverageFailOption: build
              coverageType: lines
              baseBranchRef: refs/heads/master
              allowCoverageVariance: true
              coverageVariance: 1
              coverageDeltaType: percentage

Conclusion 

If you have any questions, please get in touch with Ed Courage directly.

Ed Courage

Software Developer, ClearBank