Connecting the Dots of Azure SQL CICD Part 3: Testing with tSQLt

This ran through the pre-created tSQLt tests and returns the results. As you create the code for your SQL database, you will also create tests in tSQLt. And seeing you never want to install tSQLt into a production instance, being able to use Docker to bring up and use SQL Databases in seconds is key to having a good testing pipeline.

OK, so we can test locally all day and ensure we are checking in quality code. But what about the famous line “Well, it worked on my computer”? Using GitHub Actions, we can create a workflow that when someone checks in their code, we create a SQL Server instance in docker, deploy the SQL Database Project, run through the tSQLt test, and report back the results. Sounds like a lot of work but thanks to GitHub actions and SQL Actions, it’s a single file that we will provide for you right now!

Start by taking a look in the AzureSQLDevOpsDemo/.github/workflows folder. There is a file called sql_test_container.yml.  Let’s break this file down now.

We start with the Name and when to trigger the workflow. For our example, we will be using workflow_dispatch which allows us to manually trigger it.

name: SQL Server container in tSQLt testing pipeline
on:
  # push:
  #   branches:
  #     - main
  workflow_dispatch:

The next section is the build and deploy under jobs:

jobs:
  build-and-deploy:
    # Containers must run in Linux based operating systems
    runs-on: ubuntu-latest

    # service/sidecar container for azure-sql-2022
    services:
      mssql:
        image: mcr.microsoft.com/mssql/server:2022-latest
        env:
          ACCEPT_EULA: 1
          SA_PASSWORD: P@ssw0rd
        ports:
          - 1433:1433

Here we give the information for the docker container we want to create. In out example, we are creating the latest version of SQL Server 2022 that is available from the Microsoft Artifact Registry.

After the container is created, we have what steps we want to perform in the workflow. To start, we check if the container is up and running and accepting SQL connections via sqlcmd:

    steps:
      - name: 'Checkout GitHub Action'
        uses: actions/checkout@v2

      - name: 'wait for sql to be ready'
        run: |
          set +o pipefail +e
          for i in {1..60};
          do
              sqlcmd -S localhost -U sa -P P@ssw0rd -d master -Q "select getdate()"
              if [ $? -eq 0 ]
              then
                  echo "sql server ready"
                  break
              else
                  echo "not ready yet..."
                  sleep 1
              fi
          done
          set -o pipefail -e

Once we can connect, we can start deploying our SQL Database Projects:

      - name: 'Create and setup database'
        uses: azure/sql-action@v2
        with:
          connection-string: "Server=localhost;Initial Catalog=master;User ID=sa;Password=P@ssw0rd;Encrypt=False;TrustServerCertificate=False;"  # the local connection string
          path: './setups/setupDatabase.sql' # the sql script to create db and configure for clr

      - name: 'Deploy Projects'
        uses: azure/sql-action@v2
        with:
          connection-string: "Server=localhost;Initial Catalog=testingDB;User ID=sa;Password=P@ssw0rd;Encrypt=False;TrustServerCertificate=False;"  # the local connection string
          path: './TestsSQLServer/TestsSQLServer.sqlproj' # the SQLproj file
          action: 'Publish'
          arguments: '/p:IncludeCompositeObjects=True'

And the last step is to run the tSQLt tests:

      - name: 'Run tSQLt tests'
        uses: azure/sql-action@v2
        with:
          connection-string: "Server=localhost;Initial Catalog=testingDB;User ID=sa;Password=P@ssw0rd;"  # the local connection string
          path: './TestsSQLServer/myTests/demoExecAzureSQL.sql' # the tsqlt test command

We can test this ourselves using GitHub actions in our account.

Running the Workflow

Back in the project you forked, look across the top for the Actions tab. Click the Actions tab.

In the Actions page, on the left-hand side, click our workflow “SQL Server container in tSQLt testing pipeline”.

Then, click the Run Workflow button to see the dropdown, then click the green Run Workflow button.

Once the page refreshes with the running workflow, click it.

next, click on build-and-deploy to watch the details of the progress of the workflow.

And with a successful run, we can see the tSQLt test completed without error.

Summary

You can see how GitHub actions can help you test code on your projects by simulating a deployment and running regression tests via tSQLt providing more stable and error free release.

I also cannot stress to you enough that everything we did in these three blog posts are 100% free. There is no charge for using SQL Server in developer mode on Docker or via GitHub actions (2000 free CICD minutes that include GitHub actions, and our test took 1m 23s). Visual Studio Code is free and used by millions of people around the world as well.

So, what do you think? Is this something you can see incorporating into your CICD pipelines for database change management? Let us know in the comments section or feel free to reach out to us directly!

Special thanks to Drew Skwiers-Koballa and Liz Baron for their help and guidance on writing this post.