Simplifying cross-platform test driven SQL database project development

This article shows tips and tricks of using VS Code and Docker to make database project development and testing easy and fast.

Visual Studio and SQL Management Studio have been the go-to IDEs for developers maintaining database project targetting SQL Server. But these tools require Windows OS and your development team may prefer using a Unix OS. If you or your team is looking for ways to develop and test database project on using OS, this article may be for you.

In this article, I have shown how we can use VS Code tasks to automate and simplify build and publishing database to a local server. You will also see how we can use a customized database project for tSQLt tests and run them in container at build time.

Setting up database project

First thing, let’s create a new SDK style database project. I have written all steps in my other story. Please have a read through that one if not done already.

Migrating database to new SDK style SQL project and containerising it

Setting up unit tests project

Now, we will add database tests project. We use tSQLt for unit testing. This framework has been around for years and very powerful.

Create database test project

dotnet new sqlproj -n Database.Tests

Create a pre and post deployment scripts as shown below and add them in the .sqlproj file.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.12-preview" />
  <PropertyGroup>
    <Name>DatabaseTests</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <ProjectVersion>4.1</ProjectVersion>
    <ProjectGuid>{dcec296f-b411-4ac0-a60d-42cae042cf9d}</ProjectGuid>
  </PropertyGroup>
  <!-- Compilation properties -->
  <PropertyGroup>
    <GenerateCreateScript>True</GenerateCreateScript>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Master" Version="160.0.0" DacpacName="master" />
  </ItemGroup>
  <ItemGroup>
    <PostDeploy Include="Scripts/PostDeployment.sql" />
    <PreDeploy Include="Scripts/PreDeployment.sql" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="../Database.DacFx/Database.DacFx.sqlproj"/>
  </ItemGroup>
</Project>

Download the latest version of the framework from https://tsqlt.org/downloads/. Extract and keep the file tSQLt.class.sql. That’s the only thing we will need.

To run tests, we need this framework to be setup on the database. In SDK style project, we can achieve this by using a pre-deployment script.

Copy all content of the tSQLt.class.sql in pre-deployment script. This is only one time setup and you won’t have to do it again.

Append below script at the end in PreDeployment.sql to alter assembly so that tests can be run.

ALTER DATABASE [Database.DacFx] SET TRUSTWORTHY ON;
PRINT N'Altering CLR assemblies'
GO

ALTER Authorization ON DATABASE::[Database.DacFx] to SA
GO

There are different ways to run tests. We will be doing the simplest thing possible and run tests after database project is published.

Update PostDeployment.sql script as below to run tests after deployment.

PRINT 'Running tests...'

EXEC tSQLt.RunAll
GO

PRINT 'Tests completed.'

Now the database and test projects are setup. Next is containerization.

Here’s the all-in-one Dockerfile which can be used to build, test and run database in container. We will be using server stage to build and run database in container, which can be used in local testing. test stage is used to run unit tests inside a container at build time and the results can be seen in the terminal.

FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
LABEL maintainer="Iqan Shaikh"
WORKDIR /src
COPY Database.DacFx .
COPY init-db.sh .
RUN dotnet build

FROM mcr.microsoft.com/mssql/server:2022-CU13-ubuntu-22.04 AS server
LABEL maintainer="Iqan Shaikh"
WORKDIR /db
COPY --from=build /src/bin/Debug/Database.DacFx_Create.sql .
COPY --from=build --chown=mssql /src/init-db.sh .
RUN chmod +x init-db.sh
RUN ./init-db.sh Database.DacFx_Create.sql

FROM mcr.microsoft.com/dotnet/sdk:8.0 AS testbuilder
LABEL maintainer="Iqan Shaikh"
WORKDIR /src
COPY . .
WORKDIR /src/Database.Tests
RUN dotnet build

FROM server AS test
LABEL maintainer="Iqan Shaikh"
WORKDIR /db
COPY --from=testbuilder /src/Database.Tests/bin/Debug/Database.Tests_Create.sql .
COPY --from=testbuilder --chown=mssql /src/init-db.sh .
RUN chmod +x init-db.sh
RUN ./init-db.sh Database.Tests_Create.sql

In the previous article, have shown how to build and run database in container. Here we will see, how to run tests. It’s very simple.

To run unit tests, run below command in terminal.

docker build -t tests --target test .

This will build source and test projects, publish database to intermediate container and run all tests. Once successful, you should see output similar to below

Using VS Code tasks to automate workflow

In VS Code, we can define custom tasks for automating repetitive commands. This file is located under .vscode directory. For database project, repetitive tasks are build dacpac, publish to local server and run tests.

The tasks added for this article are:

• clean database — clean bin and obj folders
• build database — build database project and produces dacpac
• publish database — uses dacpac produced from build to deploy on localhost
• Run database in docker container — builds docker image with ready to use database
• Run database unit tests in docker container — runs database unit tests using docker

To run a task, use Ctrl+Shift+P or Cmd+Shift+P and select task to run.

Below is contents of .vscode/tasks.json used to configure mentioned tasks.

{
  "version": "2.0.0",
  "tasks": [
    {
      "label": "clean database",
      "command": "dotnet",
      "args": [
        "clean",
        "/property:GenerateFullPaths=true",
        "/consoleloggerparameters:NoSummary"
      ],
      "type": "process",
      "problemMatcher": "$msCompile",
      "options": {
        "cwd": "${workspaceFolder}/Database.DacFx"
      }
    },
    {
      "label": "build database",
      "command": "dotnet",
      "args": [
        "build",
        "/property:GenerateFullPaths=true",
        "/consoleloggerparameters:NoSummary"
      ],
      "type": "process",
      "dependsOn": "clean database",
      "group": {
        "kind": "build"
      },
      "problemMatcher": "$msCompile",
      "options": {
        "cwd": "${workspaceFolder}/Database.DacFx"
      }
    },
    {
      "label": "publish database",
      "command": "SqlPackage",
      "args": [
        "/Action:Publish",
        "/SourceFile:bin/Debug/Database.DacFx.dacpac",
        "/TargetServerName:localhost",
        "/TargetDatabaseName:Database.DacFx",
        "/TargetTrustServerCertificate:True"
      ],
      "type": "process",
      "dependsOn": "build database",
      "problemMatcher": "$msCompile",
      "options": {
        "cwd": "${workspaceFolder}/Database.DacFx"
      }
    },
    {
      "label": "build  docker image database",
      "type": "docker-build",
      "dockerBuild": {
        "context": "${workspaceFolder}",
        "dockerfile": "${workspaceFolder}/Dockerfile",
        "tag": "db:local",
        "target": "server"
      }
    },
    {
      "label": "Run database in docker container",
      "type": "docker-run",
      "dependsOn": "build  docker image database",
      "dockerRun": {
        "env": {
          "ACCEPT_EULA": "Y",
          "SA_PASSWORD": "SuperSecretPassword#1"
        },
        "ports": [
          {
            "hostPort": 1434, // This is to avoid port conflict with the local SQL Server instance
            "containerPort": 1433
          }
        ],
        "image": "db:local",
        "containerName": "db",
        "remove": true
      }
    },
    {
      "label": "Run database unit tests in docker container",
      "type": "docker-build",
      "dockerBuild": {
        "context": "${workspaceFolder}",
        "dockerfile": "${workspaceFolder}/Dockerfile",
        "tag": "db-tests:local",
        "target": "test"
      }
    }
  ]
}

You can extend this task.json file to add other commands that you may use in your development work and make it easier to execute. This can also help reduce how-to documents (which can be outdate if not maintained).

Using tips and methods mentioned in this article, we can do test-driven database development on any OS using just Docker and VS Code.

Thanks for reading the article. Hope it helped.

Feel free to have a look at the repository which contains source code used in this article. Any questions or issues, feel free to ask.

GitHub - iqan/sql-project-with-dacfx: Contains source code for database project and documentation…

Have a good day :)

References

• DacFx — https://github.com/microsoft/DacFx
• VSCode Tasks — https://code.visualstudio.com/docs/editor/tasks
• Dockerfile — https://docs.docker.com/reference/dockerfile
• tSQLt — https://tsqlt.org