Artwork:Susan Haejin Lee

Chrissy LeMaire //Automation Engineer, dbatools

The ReadME Project amplifies the voices of the open source community: the maintainers, developers, and teams whose contributions move the world forward every day.

Building the future of the command line

There are so many different ways to useGitHub 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 notunexpectedly 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 therunners 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 calledrunners. 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:

• Testing your SQL Server-based project

• 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 astSQLt. 

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 minemain.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 assqlcmd andbcp? 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 everypush to GitHub by usingon: [push], but there are currently 30 different events that can initiate a workflow. Check out GitHub's documentation onevents 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. PerGitHub's documentation:

Have a private repository and need more minutes? You can implementact , which allows you toexecute 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.

Hi, I'm Chrissy! I'm an author, SQL Server expert, and the creator and maintainer of dbatools, a toolset that enables enterprises to manage SQL Server at scale. If you want to learn more about dbatools, you can read my book 📖, Learn dbatools in a Month of Lunches. Originally from Louisiana, I currently work and live in Europe.