SQL Database Projects: Build and Validate

Your database deserves source control too

Simple explanation

Think of a construction blueprint.

Application developers keep their code in Git — every change tracked, every version recoverable. But database teams often make changes directly on the live server with no record of what changed or why. That is like building a skyscraper without blueprints.

SQL Database Projects are your database blueprint in code. Every table, view, procedure, and index is defined in SQL files stored in Git. When you change something, you build the project (like compiling code) to check for errors BEFORE deploying to the real database.

SDK-style SQL Database Projects

The new SDK-style format uses a streamlined .sqlproj file:

<Project Sdk="Microsoft.Build.Sql/0.2.0-preview">
  <PropertyGroup>
    <Name>VaultBankDB</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>
  </PropertyGroup>
</Project>

Project structure

VaultBankDB/
├── VaultBankDB.sqlproj          # Project file
├── Tables/
│   ├── Customers.sql            # CREATE TABLE Customers (...)
│   ├── Orders.sql               # CREATE TABLE Orders (...)
│   └── Transactions.sql
├── Views/
│   └── vw_ActiveCustomers.sql
├── StoredProcedures/
│   ├── usp_GetCustomer.sql
│   └── usp_ProcessOrder.sql
├── Security/
│   ├── Roles.sql
│   └── Permissions.sql
├── Data/                        # Reference/static data
│   ├── Countries.sql            # INSERT INTO Countries VALUES (...)
│   └── TransactionTypes.sql
└── Tests/
    ├── test_GetCustomer.sql
    └── test_ProcessOrder.sql

Building and validating

# Build the project (validates schema correctness)
dotnet build VaultBankDB.sqlproj

# Build produces a .dacpac file (compiled database model)

A successful build means: all references resolve, data types match across foreign keys, no syntax errors, and no circular dependencies.

Reference data in source control

Reference data (lookup tables, configuration values) should be in source control alongside the schema:

-- Data/TransactionTypes.sql
MERGE INTO dbo.TransactionTypes AS target
USING (VALUES
    (1, 'Deposit', 'Credit'),
    (2, 'Withdrawal', 'Debit'),
    (3, 'Transfer', 'Both'),
    (4, 'Fee', 'Debit')
) AS source (TypeId, TypeName, Direction)
ON target.TypeId = source.TypeId
WHEN MATCHED THEN UPDATE SET TypeName = source.TypeName, Direction = source.Direction
WHEN NOT MATCHED THEN INSERT (TypeId, TypeName, Direction) VALUES (source.TypeId, source.TypeName, source.Direction);

Use MERGE for idempotent data scripts — they can be run repeatedly without duplicating data.

Testing strategies

Testing pyramid for SQL Database Projects
Test Type	What It Tests	Tools	When to Run
Schema validation	All objects compile, references resolve	dotnet build / MSBuild	Every commit (CI)
Unit tests	Individual procedures/functions return expected results	tSQLt framework, .NET test projects	Every commit (CI)
Integration tests	End-to-end workflows across multiple objects	Deploy to test DB, run scenarios, validate	Before merge to main
Schema comparison	Project matches target database (no drift)	SqlPackage /a:DeployReport, Schema Compare	Before deployment

-- tSQLt unit test example
EXEC tSQLt.NewTestClass 'CustomerTests';
GO

CREATE PROCEDURE CustomerTests.[test that usp_GetCustomer returns correct customer]
AS
BEGIN
    -- Arrange: insert test data
    EXEC tSQLt.FakeTable 'dbo.Customers';
    INSERT INTO dbo.Customers (CustomerId, FullName, Email)
    VALUES (1, 'Test User', 'test@example.com');

    -- Act: call the procedure
    CREATE TABLE #Result (CustomerId INT, FullName NVARCHAR(200), Email NVARCHAR(320));
    INSERT INTO #Result EXEC dbo.usp_GetCustomer @CustomerId = 1;

    -- Assert: check results
    CREATE TABLE #Expected (CustomerId INT, FullName NVARCHAR(200), Email NVARCHAR(320));
    INSERT INTO #Expected VALUES (1, 'Test User', 'test@example.com');
    EXEC tSQLt.AssertEqualsTable '#Expected', '#Result';
END;

Question

What is a .dacpac file?

Click or press Enter to reveal answer

Answer

A .dacpac (Data-tier Application Package) is the compiled output of a SQL Database Project build. It contains the complete database model (schema, procedures, permissions) in a portable format. It is used for deployment — SqlPackage compares the .dacpac to a target database and generates the ALTER scripts needed.

Click to flip back

Question

What is the advantage of SDK-style SQL projects over legacy SSDT?

Click or press Enter to reveal answer

Answer

SDK-style projects use a simplified .sqlproj format, support cross-platform builds (dotnet build on Windows, Mac, Linux), use NuGet for dependencies, and integrate naturally with modern CI/CD pipelines. Legacy SSDT requires Visual Studio on Windows.

Click to flip back

Question

Why use MERGE for reference data scripts?

Click or press Enter to reveal answer

Answer

MERGE is idempotent — it inserts missing rows, updates changed rows, and optionally deletes removed rows. Running the script multiple times produces the same result. This makes reference data scripts safe for repeated deployment in CI/CD pipelines.

Click to flip back

Knowledge Check

Dev at PixelForge runs 'dotnet build' on the SQL Database Project and it fails with 'unresolved reference to dbo.Categories.' The Categories table exists in a separate shared database project. What should Dev do?

Next up: CI/CD Pipelines for SQL Databases — automate deployments with branching, pull requests, and pipeline controls.