Running tSQLt in a Build Pipeline: Getting SQL Tests Out of SSMS

Shannon Lowder

Shannon Lowder

— 2 min read

Running tSQLt tests in SSMS is fine when you're developing. But tests that only run when a developer remembers to run them aren't really protecting you. The goal is tests that run automatically on every code change — before the change gets merged, before it gets deployed, before it can break a shared environment.

That means getting tSQLt into a build pipeline.

What tSQLt Outputs

tSQLt can produce results in JUnit-compatible XML format — the same format that CI servers like TeamCity, Jenkins, and TFS Build parse and display natively:

EXEC tSQLt.RunAll;
EXEC tSQLt.XmlResultFormatter;

The XML output follows the standard JUnit schema: test suite name, test counts, failure messages, time elapsed. Any CI server that understands JUnit XML can display your SQL test results without plugins or custom parsers.

Running tSQLt from sqlcmd

From the command line, run tSQLt and capture output like this:

sqlcmd -S .SQLEXPRESS -d YourDatabase ^
    -Q "EXEC tSQLt.RunAll; EXEC tSQLt.XmlResultFormatter;" ^
    -o TestResults.xml

tSQLt raises a SQL error when tests fail. sqlcmd exits non-zero on SQL error. Build scripts detect that exit code and fail the build. The chain works automatically:

sqlcmd -S .SQLEXPRESS -d YourDatabase -Q "EXEC tSQLt.RunAll;"
IF %ERRORLEVEL% NEQ 0 (
    ECHO Tests failed.
    EXIT /B 1
)

In PowerShell:

& sqlcmd -S '.SQLEXPRESS' -d 'YourDatabase' -Q 'EXEC tSQLt.RunAll;'
if ($LASTEXITCODE -ne 0) {
    Write-Error "tSQLt tests failed."
    exit 1
}

The Full Pipeline Pattern

  1. Deploy the schema changes to a dedicated CI database
  2. Run tSQLt tests via sqlcmd
  3. Capture the XML output
  4. Fail the build if any tests fail
  5. Publish the XML results for reporting in the CI dashboard

The deployment step is where SSDT comes in. An SSDT database project builds to a dacpac file; sqlpackage.exe deploys it to the CI database, computing only what changed:

sqlpackage.exe /Action:Publish ^
    /SourceFile:YourDatabase.dacpac ^
    /TargetServerName:.SQLEXPRESS ^
    /TargetDatabaseName:YourDatabase_CI

Deploy schema, run tests, report results. That's a complete CI database pipeline.

The Practical Constraint

The limiting factor is usually the test database itself. You need a SQL Server instance accessible from your build agents, with CLR enabled and TRUSTWORTHY ON on the test database. In most shops this is a dedicated CI SQL instance or a named instance on the build machine. One-time setup.

Even if you can't automate the full pipeline immediately, the discipline of running EXEC tSQLt.RunAll before every schema deployment is a meaningful improvement over nothing. Automation comes later; the tests protect you now. As always, I'm here to help.

Read more