Database DevOps, Testing and Source Control

54.1 Database Development Approaches

State-based (declarative): define the desired schema. The tool generates the diff (SSDT/DACPAC, Redgate SQL Source Control).

Migration-based (imperative): ordered change scripts (Flyway, DbUp, Liquibase, EF Core migrations, Redgate Flyway).

54.2 Tools

SQL Database Projects (SSDT in Visual Studio, SDK-style projects with Microsoft.Build.Sql, VS Code extension)

SqlPackage: /Action:Publish | Extract | Script | DeployReport | Export | Import

Git branching strategies for databases. Store reference and seed data scripts.

SqlPackage /Action:Publish /SourceFile:ShopDB.dacpac /TargetConnectionString:"Server=...;Database=ShopDB;..."

54.3 Unit Testing with tSQLt

EXEC tSQLt.NewTestClass 'OrderTests';
GO
CREATE PROCEDURE OrderTests.[test usp_CreateOrder inserts one order]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Orders';
    EXEC tSQLt.FakeTable 'dbo.OrderItems';
    DECLARE @i dbo.OrderItemType; INSERT @i VALUES (1, 2, 10.00);
    EXEC dbo.usp_CreateOrder @CustomerID = 1, @Items = @i;
    DECLARE @cnt INT = (SELECT COUNT(*) FROM dbo.Orders);
    EXEC tSQLt.AssertEquals 1, @cnt;
END
GO
EXEC tSQLt.Run 'OrderTests';

54.4 CI/CD Pipelines

Build the DACPAC → run static code analysis (SQL rules) → deploy to an ephemeral container → run tSQLt tests → generate a deploy report → approval → deploy to production.

Platforms: Azure DevOps, GitHub Actions (azure/sql-action), Jenkins, Octopus.

Handle data-loss warnings, pre/post-deployment scripts, and refactor logs.

Zero-downtime migrations: the expand/contract pattern.

54.5 Code Quality

Linting: SQLFluff, TSQLLint, SQL Code Guard. Formatting: SQL Prompt, Poor Man's T-SQL Formatter.

Code reviews with checklists (Module 56).