Automated T-SQL Unit Testing With Jenkins, Docker, tSQLt and The JUnit Plugin

To date I have almost exclusively focused on building and deploying DACPACs to conventional and container-ised SQL Server environments without any testing. This post extends the one on multi-branch pipelines and docker  by adding two simple tSQLt unit tests. In terms of configuring the Jenkins environment for this exercise, the only difference between this and the multi-branch pipeline blog post, is that the Jenkins JUnit plugin needs to be installed.

Before continuing, I would like to stress the point that the intention of this post is to illustrate integration between SQL Server running in a Docker container, Jenkins and tSQLt. This post is not intended in any way shape or form to cover tSQLt best practice, for a tSQLt deep dive and best practice material I would refer you to Ed Elliot over at the agile SQL club.

Containers based on the SQL Server 2017 Linux image will be used as the deployment target. As SQL Server on Linux only supports SAFE assemblies, tSQLt should not work with it “On paper”. However, tSQLt does work on Linux if the tSQLtCLR assembly is re-created as SAFE. The Jenkinsfile and SSDT project associated with this post can be found on GitHub here.

Preparing An Instance and Database For Running tSQLt

Running tSQLt on an instance requires that we configure it in order to allow CLR assemblies to be run:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'clr enabled', 1
EXEC sp_configure 'clr strict security', 0
RECONFIGURE
GO

Download tSQLt from http://www.tSQLt.org, unzip the downloaded zip file and execute the file tSQLt.class.sql against the target database that the unit tests are to be executed against. Open up the tSQLtCRL assembly and make sure that its PERMISSION_SET = SAFE.

The Jenkins Build Pipeline

def BranchToPort(String branchName) {
    def BranchPortMap = [
         [branch: 'master'   , port: 15565]
        ,[branch: 'Release'  , port: 15566]
        ,[branch: 'Feature'  , port: 15567]
        ,[branch: 'Prototype', port: 15568]
        ,[branch: 'HotFix'   , port: 15569]
    ]
    BranchPortMap.find { it['branch'] == branchName }['port']
}

def PowerShell(psCmd) {
    bat "powershell.exe -NonInteractive -ExecutionPolicy Bypass -Command \"\$ErrorActionPreference='Stop';$psCmd;EXIT \$global:LastExitCode\""
}

def StartContainer() {
    PowerShell "If (\$((docker ps -a --filter \"name=SQLLinux${env.BRANCH_NAME}\").Length) -eq 2) { docker rm -f SQLLinux${env.BRANCH_NAME} }"
    docker.image('microsoft/mssql-server-linux').run("-e ACCEPT_EULA=Y -e SA_PASSWORD=P@ssword1 --name SQLLinux${env.BRANCH_NAME} -d -i -p ${BranchToPort(env.BRANCH_NAME)}:1433")
    PowerShell "While (\$((docker logs SQLLinux${env.BRANCH_NAME} | select-string ready | select-string client).Length) -eq 0) { Start-Sleep -s 1 }"
    bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'show advanced option', '1';RECONFIGURE\""
    bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'clr enabled', 1;RECONFIGURE\""
    bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'clr strict security', 0;RECONFIGURE\""
}

def DeployDacpac() {
   def SqlPackage = "C:\\Program Files\\Microsoft SQL Server\\140\\DAC\\bin\\sqlpackage.exe"
   def SourceFile = "SelfBuildPipelineDV_tSQLt\\bin\\Release\\SelfBuildPipelineDV_tSQLt.dacpac"
   def ConnString = "server=localhost,${BranchToPort(env.BRANCH_NAME)};database=SsdtDevOpsDemo;user id=sa;password=P@ssword1"
   unstash 'theDacpac'
   bat "\"${SqlPackage}\" /Action:Publish /SourceFile:\"${SourceFile}\" /TargetConnectionString:\"${ConnString}\" /p:ExcludeObjectType=Logins"
}

node('master') {
    stage('git checkout') {
        timeout(time: 5, unit: 'SECONDS') {
            checkout scm
        }
    }

    stage('build dacpac') {
        bat "\"${tool name: 'Default', type: 'msbuild'}\" /p:Configuration=Release"
        stash includes: 'SelfBuildPipelineDV_tSQLt\\bin\\Release\\SelfBuildPipelineDV_tSQLt.dacpac', name: 'theDacpac'
    }

    stage('start container') {
        timeout(time: 20, unit: 'SECONDS') {
            StartContainer()
        }
    }

    stage('deploy dacpac') {
        try {
           timeout(time: 60, unit: 'SECONDS') {
               DeployDacpac()
           }
        }
        catch (error) {
            throw error
        }
    }

    stage('run tests') {
        bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -d SsdtDevOpsDemo -Q \"EXEC tSQLt.RunAll\""
        bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -d SsdtDevOpsDemo -y0 -Q \"SET NOCOUNT ON;EXEC tSQLt.XmlResultFormatter\" -o \"${WORKSPACE}\\SelfBuildPipelineDV_tSQLt.xml\""
        junit 'SelfBuildPipelineDV_tSQLt.xml'
    }
}

This tSQLt tutorial covers the basics of how to perform tests with tSQLt, in this particular example demoTestClass is our test class, the corresponding demoTestClass schema contains two stored procedures. By design, one stored procedure contains a unit test that passes and the other a unit test that fails:

CREATE PROCEDURE [demoTestCLass].[testTotalInvoiceAmountPass]
AS
BEGIN
    DECLARE  @ActualTotal   NUMERIC(10,2)
            ,@ExpectedTotal NUMERIC(10,2) = 12345678.90

    SELECT @ActualTotal = ISNULL(SUM(Total), 12345678.90)
    FROM   [dbo].[Invoice];

    EXEC tSQLt.AssertEquals @ExpectedTotal , @ActualTotal;
END;
GO

CREATE PROCEDURE [demoTestCLass].[testTotalInvoiceAmountFail]
AS
BEGIN
    DECLARE  @ActualTotal   NUMERIC(10,2)
            ,@ExpectedTotal NUMERIC(10,2) = 12345678.91;

    SELECT @ActualTotal = ISNULL(SUM(Total), 12345678.90)
    FROM   [dbo].[Invoice];

    EXEC tSQLt.AssertEquals @ExpectedTotal , @ActualTotal;
END;
GO

When we run the build pipeline this is what we get:

If we drill into the build, build 80 in this case, we can see that the test has failed as per the failure that has been engineered:

We can drill down further into the test results by clicking the “Test Result” link to obtain details of the actual failure:

How Does This Work Exactly ?

Despite tSQLt and JUnit both being unit test frameworks, T-SQL and Java are both subtly different, so how do we get the rendering of results that we can not only see but drill down into in detail. Simply put, the one crucial piece of information here is that the XML output produced by the line:

bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -d SsdtDevOpsDemo -y0 -Q \"SET NOCOUNT ON;EXEC tSQLt.XmlResultFormatter\" -o \"${WORKSPACE}\\SelfBuildPipelineDV_tSQLt.xml\""

produces an XML file which is identical in format to that which the call to junit can understand.