tSQLt + TeamCity: Automated SQL Testing on Every Commit

Shannon Lowder

Shannon Lowder

— 2 min read

Getting tSQLt into TeamCity is where individual tests become a team safety net. Instead of one developer running tests in SSMS occasionally, every commit triggers a build that deploys the schema and runs the full suite. Broken tests block the merge. Passing tests give the whole team confidence to ship.

Prerequisites

  • A SQL Server instance accessible from the TeamCity build agent
  • CLR enabled and TRUSTWORTHY ON on your test database
  • SSDT installed on the build agent (for MSBuild)
  • Your SSDT production and test projects in source control

The Build Configuration — Five Steps

Step 1: Build the SSDT Projects

MSBuild runner, pointed at your solution:

MSBuild YourSolution.sln /p:Configuration=Release /p:Platform="Any CPU"

Produces YourDatabase.dacpac and YourDatabase.Tests.dacpac in each project's bin/Release folder.

Step 2: Deploy the Production Schema

sqlpackage.exe
  /Action:Publish
  /SourceFile:srcYourDatabaseinReleaseYourDatabase.dacpac
  /TargetServerName:%ci.sql.server%
  /TargetDatabaseName:YourDatabase_CI
  /p:BlockOnPossibleDataLoss=false

Store the server name in a TeamCity configuration parameter (ci.sql.server) so it's not hardcoded in the build definition.

Step 3: Deploy the Test Project

sqlpackage.exe
  /Action:Publish
  /SourceFile:srcYourDatabase.TestsinReleaseYourDatabase.Tests.dacpac
  /TargetServerName:%ci.sql.server%
  /TargetDatabaseName:YourDatabase_CI

Step 4: Run tSQLt and Capture XML

sqlcmd
  -S %ci.sql.server%
  -d YourDatabase_CI
  -Q "EXEC tSQLt.RunAll; EXEC tSQLt.XmlResultFormatter;"
  -o TestResults.xml

Step 5: Publish Test Results

Use TeamCity's XML Report Processing build feature pointed at TestResults.xml (JUnit format). TeamCity parses the file and displays test counts, pass/fail status, and failure messages in the build report alongside your regular unit test results.

Failing the Build Automatically

tSQLt raises a SQL error when tests fail → sqlcmd exits non-zero → TeamCity marks the build step failed. The chain works automatically. Add a Test Failure Condition in the build configuration for an explicit safety net on top of the exit code behavior.

Keeping Tests Fast

A 10-minute test suite will get turned off. tSQLt tests run on the server side — no round trips per assertion. A suite of 50 tests typically completes in under 30 seconds. The slow part is usually the SSDT deploy (schema comparison on each build). If deploys are slow, investigate whether you can use a CI database that stays alive between builds rather than recreating from scratch on each run.

The Feedback Loop

Developer commits a schema change. TeamCity picks it up. Deploys the schema, runs the suite, reports results. If the change broke something, the developer knows in under 5 minutes, with a failure message identifying exactly which test failed and what it expected versus what it got. That's the CI feedback loop that application developers have had for years — now available for your database tier. As always, I'm here to help.

Read more