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/000migrationhistory.sql](templates/000migrationhistory.sql) and [templates/001createitemstable.sql](templates/001createitemstable.sql).

Rules

1. File naming: {NNN}_{description}.sql — zero-padded, snake_case.
2. Never edit an applied migration — add a new one instead.
3. Seed data must be idempotent — IF NOT EXISTS or MERGE.
4. No rollback scripts — roll forward only. Point-in-time restore is available (7-day retention on serverless).
5. 000 always runs first — creates the tracking table.

Why not azure/sql-action

The GitHub action azure/sql-action@v2.3 accepts only one file. Multi-file pushes fail with Multiple files found matching pattern *.sql. Use the sqlcmd bash step in [scripts/run-migrations.sh](scripts/run-migrations.sh) instead.

The workflow step

The deploy workflows call [scripts/install-sqlcmd.sh](scripts/install-sqlcmd.sh) and [scripts/run-migrations.sh](scripts/run-migrations.sh). Both encode hard-won fixes:

| Fix | Why | |-----|-----| | gpg --batch --yes --dearmor | Without --batch, gpg tries to open /dev/tty and fails in headless CI | | Pipe through sudo tee | Don't use sudo gpg -o /path — permission issues | | Install mssql-tools18 explicitly | Not pre-installed on ubuntu-24.04 (which ubuntu-latest now points to) | | sqlcmd -C flag | Required with mssql-tools18 to trust Azure SQL TLS certificate | | trap ... EXIT for firewall cleanup | Guarantees the runner's temporary firewall rule is removed even if a migration fails |

Local development

To run migrations against a local SQL Server or LocalDB:

for f in $(ls infra/sql/migrations/*.sql | sort); do
  echo "Running: $f"
  sqlcmd -S localhost -d MyDb -E -i "$f"     # -E uses integrated auth
done

For Azure SQL from your dev box, add your IP to the firewall first:

MY_IP=$(curl -s https://api.ipify.org)
az sql server firewall-rule create --server "$SQL_SERVER_NAME" --resource-group "$RG" \
  --name "dev-$(whoami)" --start-ip-address "$MY_IP" --end-ip-address "$MY_IP"

Composes with

• [scaffolding-azure-bicep-infrastructure](../scaffolding-azure-bicep-infrastructure/SKILL.md) — generates the SQL Bicep + initial migration files
• [configuring-azure-oidc-for-github-actions](../configuring-azure-oidc-for-github-actions/SKILL.md) — for the SQL_ADMIN_PASSWORD_* GitHub secrets the workflow uses

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: alexpizarro
• Source: alexpizarro/azure-lean-stack-skills
• License: MIT
• Homepage: https://github.com/alexpizarro/azure-lean-stack-skills

Install and usage instructions live in the source repository linked above.