There are so many different ways to use GitHub Actions it can be hard to decide where to start. You can automate tasks like documentation updates and compatibility checks, schedule tweets to announce new builds, or run a whole range of different tests—just to name a few use cases.
Testing is a great place to start, especially for DBAs (Database Administors), because testing can help us sleep better at night, knowing that all committed code changes will work as expected and not unexpectedly delete data.
In this Guide you will learn:
How to use GitHub Actions to test an SQL Server deployment on a Linux container. In the code section of this article, you will use a Linux container to setup and run an SQL database in order to test new code that is pushed to your repository.
Actions make testing SQL Server accessible and easy, especially if you're using PowerShell, which is built into each of the runners along with its popular testing framework, Pester.
GitHub runners are especially SQL Server-friendly and come with a number of built-in SQL Server tools, as seen in the table below.
Testing against SQL Server
SQL Server professionals may see some similarities between Actions tasks and tasks accomplished by SQL triggers or SQL Server Agent and Windows Task Scheduler. Actions run in Windows, Ubuntu, and macOS virtual machines are called runners. And all of this happens entirely in one place: your GitHub repository (repo).
You can use runners to install SQL Server containers with each commit. 🤯 This is useful for tasks like:
Updating your SQL database deployments using dacpacs
Updating your database with data from sources such as screen scrapes or remote repositories
While testing can be performed in nearly every language, this article will focus on PowerShell. PowerShell can also be used to launch alternative testing frameworks such as tSQLt.
The code
In the code below, the following steps will be performed each time a change is pushed to the repository
To get started:
Download and run the SQL Server on a Linux container. TIP: Set the SA Password to: Admin123, and expose the default SQL Server port, 1433.
Install dbatools from the PowerShell gallery
Connect to the container, including these steps: Listing the current databases; Creating a new database; executing a query that creates a new table; and confirming that a new table exists using dbatools.
Then, copy and paste the block below and save it as a .yml file (I named mine main.yml) in the directory .github\workflows within the root directory of your repository.
name: Actions Workflow
on: [push]
jobs:
run-sql:
runs-on: ubuntu-latest
steps:
- name: 🐋 Startup Container
shell: bash
run: |
# In this scenario with a fresh container used for testing,
# it is acceptable to use a plain-text password
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Admin123" \
-p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
- name: 🚀 Install required PowerShell module
uses: potatoqualitee/psmodulecache@v4.5
with:
modules-to-cache: dbatools
- name: 🛢️ Get all databases, create new database, execute query
shell: pwsh
run: |
# Create a PowerShell credential object that can login to the container
$password = ConvertTo-SecureString Admin123 -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential("sa", $password)
# View available databases
Get-DbaDatabase -SqlInstance localhost -SqlCredential $cred
# Create new objects and perform queries
New-DbaDatabase -SqlInstance localhost -SqlCredential $cred -Name testdb
$sql = "CREATE table test (id int, artist nvarchar(50))"
Invoke-DbaQuery -SqlInstance localhost -SqlCredential $cred -Database testdb -Query $sql
Get-DbaDbTable -SqlInstance localhost -SqlCredential $cred -Database testdb
Push the file to your repository then click on the Actions tab on GitHub to watch the workflow run.
Prefer to use native SQL Server tools such as sqlcmd and bcp? Linux and Windows runners include mssql tools by default!
name: Actions Workflow
on: [push]
jobs:
run-sql:
# Use an explicit version of ubuntu, as it will be referenced below
runs-on: ubuntu-18.04
steps:
- name: 🐋 Startup Container
run: |
# In this scenario with a fresh container used for testing,
# it is acceptable to use a plain-text password
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Admin123" \
-p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
- name: 🛢️ Query the server to see the server version
run: |
# Give the container a moment to startup
sleep 5
# Execute SQL a query
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Admin123 -q "SELECT @@VERSION"
The two examples provided run the workflows with every push to GitHub by using on: [push], but there are currently 30 different events that can initiate a workflow. Check out GitHub's documentation on events that trigger workflows for more information.
Usage limitations
While the usage allotment is generous, there is a limit to the number of free minutes you can use per month. Per GitHub's documentation:
Have a private repository and need more minutes? You can implement act , which allows you to execute runners locally.
Next steps
There’s plenty more you can do with this powerful and flexible tool. To dive right into using GitHub Actions for your SQL Server projects, you can:
Create a SQL Server-centric PowerShell module, complete with a build process and integration tests against a live SQL Server container. Once the module is built and tested, publish it to the PowerShell Gallery.
Gather data using an API or screenscraper, import the results into a SQL Server container then export the results to a DACPAC and save it as an artifact to your workflow runs.
Write T-SQL queries and test to ensure the syntax is valid using tSQLt.
If you have any questions, I'm @cl on Twitter, feel free to reach out there or in the comments below.
