managing-azure-sql-migrations

Managing Azure SQL Migrations

Idempotent, roll-forward SQL migrations run on every deploy via sqlcmd. The migration system uses a __MigrationHistory table for tracking, guard-clause migrations to make every run safe, and a workflow step that handles the operational quirks of running sqlcmd on ubuntu-24.04 in GitHub Actions.

When to invoke

• Adding a new migration file
• Bootstrapping the migration system on a new project
• Fixing a CI failure related to SQL migrations

File naming

infra/sql/migrations/
├── 000_migration_history.sql       # tracking table — always runs first
├── 001_create_items_table.sql      # initial schema (pre-tracking guard ok)
├── 002_add_user_id_to_items.sql    # guarded by __MigrationHistory
├── 003_seed_demo_data.sql          # also guarded
└── ...

Pattern: {NNN}_{snake_case_description}.sql. Zero-padded, alphabetical order = execution order.

Guard clause template

Every migration from 002 onward MUST use this pattern:

IF NOT EXISTS (
    SELECT 1 FROM dbo.__MigrationHistory WHERE MigrationId = 'NNN_describe_change'
)
BEGIN
    -- DDL here (CREATE TABLE, ALTER TABLE, INSERT, MERGE, etc.)

    INSERT INTO dbo.__MigrationHistory (MigrationId) VALUES ('NNN_describe_change');
    PRINT 'Migration NNN_describe_change applied.';
END
ELSE
BEGIN
    PRINT 'Migration NNN_describe_change already applied — skipping.';
END

The exception is 001_create_items_table.sql, which uses IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Items') because Items may have been deployed before tracking existed.

The tracking table

000_migration_history.sql creates __MigrationHistory. Idempotent — safe to re-run:

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = '__MigrationHistory')
BEGIN
    CREATE TABLE dbo.__MigrationHistory (
        MigrationId NVARCHAR(200) NOT NULL PRIMARY KEY,
        AppliedAt   DATETIME2     NOT NULL DEFAULT GETUTCDATE()
    );
END

See templates/000_migration_history.sql and templates/001_create_items_table.sql.