tSQLt and SSDT Database Projects: Tests Alongside Your Schema

Shannon Lowder

Shannon Lowder

— 2 min read

Last year I covered running tSQLt in a build pipeline via sqlcmd. That works, but it has a coupling problem: the tests live in the database, not in source control. If the database is recreated from scratch, the tests are gone. If someone deletes a test procedure directly, it's gone without a trace. The tests aren't versioned alongside the code they're testing.

SQL Server Data Tools database projects fix this — and tSQLt integrates cleanly with the SSDT model.

What an SSDT Database Project Is

An SSDT database project is a Visual Studio project that represents your entire database schema as .sql files — one file per object. When you build the project, it produces a dacpac. When you deploy the dacpac, sqlpackage computes the difference between the current database state and the project definition and applies only what changed.

The result: your schema is in source control, versioned, and deployable via a deterministic artifact rather than "here's the folder of scripts, run them in order."

Adding tSQLt Tests to the Project

tSQLt tests are stored procedures in test class schemas — in an SSDT project, they're .sql files in a test folder. The project structure ends up like this:

YourDatabase/
  dbo/
    Tables/
      Orders.sql
      Customers.sql
    Stored Procedures/
      GetCustomerOrderSummary.sql
      UpdateOrderStatus.sql
  Tests/
    OrderTests/
      test GetPendingOrders returns only pending orders.sql
      test UpdateOrderStatus sends notification on shipped.sql
    CustomerTests/
      test GetCustomerOrderSummary aggregates correctly.sql

One file per test procedure. The test class schemas are also defined in the project — a one-line schema creation file each.

Separate Production and Test Projects

The cleaner architecture uses two SSDT projects: your production database project with only production objects, and a second project — YourDatabase.Tests — that references the main project and adds the tSQLt framework, test schemas, and test procedures.

-- YourDatabase project: production objects only
-- YourDatabase.Tests project: tSQLt framework + test procs
--   References YourDatabase (gets all production objects)
--   Never deployed to production

When you deploy to production, only the production dacpac goes. When CI runs tests, it deploys both. This keeps tSQLt and all test objects out of production entirely.

The Build and Test Pipeline

With both projects in source control, the pipeline becomes:

  1. MSBuild compiles both projects → YourDatabase.dacpac and YourDatabase.Tests.dacpac
  2. sqlpackage deploys YourDatabase.dacpac to the CI database
  3. sqlpackage deploys YourDatabase.Tests.dacpac on top (adds tSQLt + test procedures)
  4. sqlcmd runs EXEC tSQLt.RunAll and captures JUnit XML
  5. Build passes or fails based on tSQLt output

Every step is automated. Every test is in source control. Every schema change rebuilds and re-tests from scratch.

What This Looks Like in a Code Review

When a developer changes a stored procedure, the pull request includes both the changed procedure file and the changed test file. A reviewer can see exactly which test was added or modified alongside the production change. A procedure change with no corresponding test change is a visible signal that coverage wasn't maintained.

That's the discipline that makes a test suite trustworthy over time. Not "we have tests" — that's just a fact. The value is "every schema change goes through a process that maintains the tests alongside the code." As always, I'm here to help.

Read more