This is the ninth part in the series: SQL Server and Continuous Integration. In this post I’ll add some unit tests and pull them into the build process in GitLab. Topics covered so far in this series are:

• Git
• Visual Studio
• ReadyRoll
  • Installation
  • Starting a Project
  • Development
• GitLab
  • Basics
  • Runners
• tSQLt
• SQL Cover

To have confidence in our database changes, we need to run tests to make sure the database still operates as expected. I’ll be using the opensource tSQLt unit testing framework for T-SQL. I’ll only be covering the automation of tSQLt tests, not the actual writing of tests. The user guide is a good starting point for getting to grips with tSQLt.

It’s possible to add a secondary ReadyRoll project in which you can develop your tSQLt tests, see here. This allows you to execute your tests in Visual Studio with a nice visual output. I prefer to develop (and run my tests) in SSMS, switching to VS every time I want to run some tests isn’t ideal.

I use a PowerShell script in the build to execute my tests. In the future, I can have the script run coverage reports (which I’ll cover in my next post in the series) , integration tests, ostress, Pester tests and much more.

tSQLt

Before we can write any tests, we need to install the tSQLt framework in our development database.

• Enable CLR on each SQL instance you will be running tests on
• Download the framework from tSQLt.org
• Run the tSQLt.class.sql script against your development database

Now we’re ready to test the function below:

CREATE FUNCTION dbo.ConvertCurrency 
(
    @rate DECIMAL(10,4), 
    @amount DECIMAL(10,4)
)
RETURNS DECIMAL(10,4)
AS
BEGIN
    DECLARE @Result DECIMAL(10,4);
 
    SET @Result = (SELECT @amount / @rate);
 
    RETURN @Result;
END;

First, I create a test class.

EXEC tSQLt.NewTestClass 'ConvCurrency';

Then, this simple test:

CREATE PROCEDURE ConvCurrency.[test CC converts correctly]
AS
BEGIN
    DECLARE @actual DECIMAL(10,4), 
            @rate DECIMAL(10,4) = 1.2,
            @amount DECIMAL(10,4) = 2.00;
 
    SELECT @actual = dbo.ConvertCurrency(@rate, @amount);
 
    DECLARE @expected DECIMAL(10,4) = 2.4;  
 
    EXEC tSQLt.AssertEquals @expected, @actual;
END;

When I want to run the test, I execute tSQLt.RunAll in SSMS and receive these results:

[ConvCurrency].[test CC converts correctly] failed: (Failure) Expected: <2.4000> but was: <1.6667>
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                             |Dur(ms)|Result |
+--+-------------------------------------------+-------+-------+
|1 |[ConvCurrency].[test CC converts correctly]|     50|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

Correcting the function (to multiply instead of divide) and rerunning the test shows that we are now in a good state.

+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                             |Dur(ms)|Result |
+--+-------------------------------------------+-------+-------+
|1 |[ConvCurrency].[test CC converts correctly]|      7|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

By default, ReadyRoll will ignore tSQLt objects, including our tests. We don’t want ReadyRoll to script out the tSQLt objects, but we do want it to script our tests. To set our filter we need to unload the project in VS and edit the project file. Add the following to the section named ReadyRoll Script Generation Section:

<SyncOptionIgnoretSQLt>False</SyncOptionIgnoretSQLt>
<ExcludeObjectsFromImport>
  Assembly=\[tSQLtCLR\];
  Schema=\[tSQLt\];
  StoredProcedure=\[tSQLt\]\.\[(.*?)\];
  Table=\[tSQLt\]\.\[(.*?)\];
  UserDefinedDataType=\[tSQLt\]\.\[(.*?)\];
  View=\[tSQLt\]\.\[(.*?)\];
  UserDefinedFunction=\[tSQLt\]\.\[(.*?)\];
</ExcludeObjectsFromImport>

ReadyRoll

We can then refresh the ReadyRoll project to sync our new function, schema (test class) and test.

Our tests will now be included in our Git repo, but we don’t want to deploy them to production. We need to add a SQLCMD variable to the deployment package, like we did in the post Starting a ReadyRoll Project. We will then be able to decide if we want to deploy our tests or not.

Add a SQLCMD variable named $(DeployUnitTestObjects) with a default value of False. Add the following line to the top of each unit test file in the ReadyRoll project:

-- <Migration Condition="'$(DeployUnitTestObjects)' = 'True'" />

When we run the deployment package into production, we can now specify to not deploy the unit tests.

Automation

To get these tests to run automatically on each build, we need to add the tSQLt.class.sql script to our solution. Copy the script to the root of the solution. Then right click the solution, select “Add Existing Item” and select the script.

Now we can create the PowerShell script to run the tests. The script below should be created in the root of the solution and added as an existing item.

param(
    [Parameter(Mandatory=$true)][string]$OutputFolder,
    [Parameter(Mandatory=$true)][string]$DatabaseServer,
    [Parameter(Mandatory=$true)][string]$DatabaseName
  )
$Query = @"
SELECT value_in_use  
FROM sys.configurations 
WHERE name = 'clr enabled';
"@ 
$CLREnabled = Invoke-Sqlcmd `
    -Query $Query `
    -ServerInstance $DatabaseServer `
    -Database $DatabaseName 
    
if($CLREnabled.value_in_use -eq 1){
    Invoke-Sqlcmd `
       -InputFile "$PSScriptRoot\tSQLt.class.sql" `
       -ServerInstance $DatabaseServer `
       -Database $DatabaseName 
    Sqlcmd `
        -Q "EXEC tSQLt.RUNALL;" `
        -S $DatabaseServer `
        -d $DatabaseName `
        -o "$OutputFolder\$DatabaseName tSQLt Report.txt"
} 
else {     
    Write-Error("CLR is not enabled on $DatabaseServer.")
}

GitLab

We need to tell GitLab to run our test harness. We give instructions to GitLab via the .gitlab-ci.yml file (see the last post in this series for details).

build:
  script: '"C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe" ^
  /p:TargetServer=LocalHost ^
  /p:TargetDatabase=RR_Test ^
  /p:Configuration=Release ^
  /p:GenerateSqlPackage=True ^
  /p:DBDeployOnBuild=True'
test:
  script: powershell .\TestHarness.ps1 %CI_PROJECT_DIR% 'LocalHost' 'RR_Test'
  artifacts:
    paths: 
    - RR_Test
    untracked: true

The build section deploys our database project to the default instance on the runner. The last part tells GitLab to save any artifacts(files) that are generated during the test phase of the build. Our test harness is writing the results of our tSQLt tests out to the file system, so they will be stored against the build as artifacts.

Once we commit and push to GitLab you will see the new test step in the pipeline. If we go into the test section of the build, we can browse or download the build artifacts.

We finally have a basic CI pipeline, with automated deployment to a test environment, test execution and result capture. In the next post, I will add some more tests and a coverage report to show which parts of the database we are (and aren’t) testing.

The post Automating tSQLt Unit Tests appeared first on The Database Avenger.