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

Brian Spendolini

Image header picture

In part 3 of our Azure SQL database change management (CICD) series we cover testing your code using tSQLt and GitHub Actions.

Prerequisites Check

Refer to part 1 and part 2 for the prerequisites.

Fork the GitHub Project

We will be using tSQLt in this post, so I have created a GitHub project made up of several SQL Database Projects for Visual Studio Code. In these database projects are the tSQLt samples installed and ready to go. This post will focus more on the process of setting up a testing pipeline but if you are curious, you can read about the tSQLt samples/quickstart here, or just read up on how amazing tSQLt is here.

Start by going to:

https://github.com/JetterMcTedder/AzureSQLDevOpsDemo

and in the upper right corner of the page, click on Fork. Then, click + Create a new fork.

Image fork1

The project will now be in your account as a new repository. You can now clone it locally.

Get the clone URL on the repository homepage. Just click the green <> Code button.

Image fork2

YOUR_ACCOUNT_NAME_HERE is a placeholder for well, your account name. Mine is JetterMcTedder. Don’t use my account name because you will not be able to use GitHub actions later in the post, which is honestly the whole point of this post!

Next, using a terminal/Powershell prompt/etc, run the following git command:

git clone https://github.com/YOUR_ACCOUNT_NAME_HERE/AzureSQLDevOpsDemo

This will create the AzureSQLDevOpsDemo folder with the SQL Database Projects inside on your local machine.

The TestsSQLServer Project

Inside the AzureSQLDevOpsDemo/TestsSQLServer directory is the TestsSQLServer.sqlproj file. This is the starting point for our pipeline testing journey.

Open this file in Visual Studio Code by 1) being on the SQL Database Projects extension and 2) clicking on the open folder icon or the Open Existing button.

Image code project2

Find the project in the AzureSQLDevOpsDemo/TestsSQLServer directory

Image code project3b

and then you should see it in SQL Database Projects

Image code project4b

Next, right click the TestsSQLServer project and select Publish.

Image code project5

This will publish not only the tSQLt needed items to our local SQL Server instance in Docker, but also the reference to the Code SQL Database Project which has all the sample code supplied by the tSQLt quickstart (tables and procedures).

Image code project6

You can refer back to Part 2 on setting up Docker for a local SQL Server 2022 instance.

Once the project is deployed to the local instance

Image code project7

you can browse the tables it has created in the local instance

Image code project8

Testing the Test

Open a new Query Window for our local SQL connection by right clicking on the localhost server name in the SQL Server connections tab.

Image code project9

In the project is a script called runDemoTests_VSC-ADS.sql in the myTests folder. In that script is the following command:

EXEC tSQLt.Run 'testFinancialApp';
 
You can open the script or just copy the command here and run it in the New Query window.
Image code project10 2
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.

Automated Testing with GitHub Actions

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.

Image action1

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

Image action2

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

Image action3

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

Image action4

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

Image action5

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

Image action6

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.

0 comments

Comments are closed. Login to edit/delete your existing comments

Feedback usabilla icon