Skip to content

migration-safety.md

Latest commit

 

History

History
374 lines (261 loc) · 9.96 KB

migration-safety.md

File metadata and controls

374 lines (261 loc) · 9.96 KB
Raw

Migration Safety for Blue-Green Deployments

Overview

During blue-green deployments, both old and new application versions run simultaneously. This means database migrations must be compatible with both versions of the application code. Unsafe migrations can cause:

  • Application crashes in the old version
  • Data corruption
  • Deployment rollback failures
  • Service downtime

This document outlines safe migration patterns enforced by our CI pipeline.

Automated Safety Checks

All migrations are automatically checked by scripts/check-migration-safety.sh in CI. The checker enforces these rules:

❌ Blocking Errors

These patterns will fail CI and block PR merges:

  1. NOT NULL columns without DEFAULT
  2. Column renames
  3. Table drops without deprecation
  4. Column type changes

⚠️ Warnings

These patterns generate warnings but don't block CI:

  1. Column drops
  2. Constraints without NOT VALID
  3. Indexes without CONCURRENTLY
  4. Foreign keys without NOT VALID
  5. Enum modifications

Safe Migration Patterns

✅ Adding Nullable Columns

Safe: Old app ignores new columns, new app uses them.

-- ✅ SAFE: Nullable column
ALTER TABLE transactions
ADD COLUMN IF NOT EXISTS memo TEXT;

✅ Adding Columns with Defaults

Safe: Old app ignores column, new app gets default value for existing rows.

-- ✅ SAFE: Column with default
ALTER TABLE transactions
ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'pending';

❌ Adding NOT NULL Columns Without Defaults

Unsafe: Old app tries to insert rows without the new column, violating NOT NULL constraint.

-- ❌ UNSAFE: NOT NULL without default
ALTER TABLE transactions
ADD COLUMN status VARCHAR(20) NOT NULL;

Fix: Add a default value:

-- ✅ SAFE: NOT NULL with default
ALTER TABLE transactions
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

✅ Renaming Columns (Multi-Step Pattern)

Never use RENAME COLUMN - it breaks old app versions immediately.

Instead, use the add+migrate+drop pattern:

Step 1: Add new column (Migration 1)

-- Add new column with same data type
ALTER TABLE users
ADD COLUMN IF NOT EXISTS username VARCHAR(255);

-- Backfill existing data
UPDATE users SET username = user_name WHERE username IS NULL;

Step 2: Deploy app that writes to both columns

Update application code to write to both user_name and username.

Step 3: Deploy app that reads from new column

Update application code to read from username instead of user_name.

Step 4: Drop old column (Migration 2, after full deployment)

-- Safe to drop after all instances use new column
ALTER TABLE users
DROP COLUMN IF EXISTS user_name;

✅ Dropping Tables (Deprecation Pattern)

Never drop tables immediately - old app versions may still query them.

Step 1: Stop writing to table

Update application code to stop writing to the table.

Step 2: Wait for full deployment

Ensure all old app instances are replaced.

Step 3: Drop table (separate migration)

-- Safe after deprecation period
DROP TABLE IF EXISTS old_table_name;

✅ Changing Column Types (Multi-Step Pattern)

Never use ALTER COLUMN TYPE - it can break old app versions and lock tables.

Step 1: Add new column with new type

ALTER TABLE transactions
ADD COLUMN amount_cents BIGINT;

-- Backfill data
UPDATE transactions 
SET amount_cents = (amount * 100)::BIGINT 
WHERE amount_cents IS NULL;

Step 2: Deploy app using new column

Update application code to use amount_cents.

Step 3: Drop old column

ALTER TABLE transactions
DROP COLUMN IF EXISTS amount;

✅ Security-Sensitive Column Migrations (Precondition Guards)

A migration that reinterprets or renames a column holding credentials, keys, or other secrets (e.g. hashing a plaintext API key in place, or converting a signing secret to an encrypted BYTEA) is often written against an assumed precondition — "this table is empty," "no tenant has provisioned yet." That assumption is easy to state in a comment and easy to leave unenforced. If it's wrong, the migration doesn't fail: it succeeds, silently, having turned every existing row's credential into something the application can no longer read back correctly (a hash that no plaintext will ever match again, or bytes that aren't valid ciphertext).

Never leave this precondition as a comment alone. Enforce it:

-- ✅ SAFE: precondition enforced, not just documented
DO $$
DECLARE
    row_count INTEGER;
BEGIN
    SELECT count(*) INTO row_count FROM tenants;
    IF row_count > 0 THEN
        RAISE EXCEPTION 'tenants has % existing row(s); this migration assumes it is empty', row_count;
    END IF;
END
$$;

ALTER TABLE tenants RENAME COLUMN api_key TO api_key_hash;

See migrations/20260824000003_hash_tenant_secrets.sql for the full pattern, including the down migration. scripts/check-migration-safety.sh flags any ALTER COLUMN ... TYPE on a column whose name looks security-sensitive (secret, password, token, api_key, credential, private_key) — as a blocking error if no RAISE EXCEPTION + count(*) guard is present in the same file, or as a notice (informational only) if one is. This is deliberately narrower than a full data-flow analysis; it exists to force a deliberate, informed decision instead of a silent one, not to replace review.

If the table genuinely can have pre-existing rows in some environment, the guard is telling you the truth: this migration needs a real data-migration step (rotate/re-encrypt existing values through the new code path) instead of an empty-table assumption — do not just delete the guard to make CI green.

✅ Adding Indexes Safely

Use CONCURRENTLY to avoid locking tables:

-- ✅ SAFE: Non-blocking index creation
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_status 
ON transactions(status);

Without CONCURRENTLY, index creation locks the table and blocks writes.

✅ Adding Constraints Safely

Use NOT VALID to avoid full table scans and locks:

-- Step 1: Add constraint without validation
ALTER TABLE transactions
ADD CONSTRAINT check_amount_positive 
CHECK (amount > 0) NOT VALID;

-- Step 2: Validate in separate transaction (can be done later)
ALTER TABLE transactions
VALIDATE CONSTRAINT check_amount_positive;

✅ Adding Foreign Keys Safely

Similar to constraints, use NOT VALID:

-- Step 1: Add FK without validation
ALTER TABLE transactions
ADD CONSTRAINT fk_settlement 
FOREIGN KEY (settlement_id) REFERENCES settlements(id) NOT VALID;

-- Step 2: Validate separately
ALTER TABLE transactions
VALIDATE CONSTRAINT fk_settlement;

✅ Modifying Enums

Adding enum values is generally safe, but old app versions won't recognize them:

-- ✅ SAFE: Adding enum value
ALTER TYPE transaction_status ADD VALUE IF NOT EXISTS 'disputed';

Important: Ensure old app code handles unknown enum values gracefully (e.g., treats them as a default state).

Migration Checklist

Before submitting a PR with migrations:

  • Run ./scripts/check-migration-safety.sh locally
  • All blocking errors are resolved
  • Warnings are reviewed and acceptable
  • Multi-step migrations are documented in PR description
  • Deployment order is clear (if multiple migrations)
  • Rollback plan is documented

Testing Migrations

Local Testing

# Check migration safety
./scripts/check-migration-safety.sh

# Test migration forward
sqlx migrate run

# Test migration backward (if .down.sql exists)
sqlx migrate revert

CI Testing

The CI pipeline automatically:

  1. Runs migration safety checks
  2. Applies migrations to test database
  3. Runs application tests against migrated schema

Common Scenarios

Scenario: Adding a Required Field

Wrong approach:

ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;

Right approach:

-- Migration 1: Add nullable column
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- Migration 2 (after app deployment): Make it required
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Scenario: Splitting a Column

Example: Split full_name into first_name and last_name

-- Migration 1: Add new columns
ALTER TABLE users 
ADD COLUMN first_name VARCHAR(255),
ADD COLUMN last_name VARCHAR(255);

-- Backfill data
UPDATE users 
SET 
  first_name = split_part(full_name, ' ', 1),
  last_name = split_part(full_name, ' ', 2)
WHERE first_name IS NULL;

-- Deploy app that uses new columns

-- Migration 2: Drop old column
ALTER TABLE users DROP COLUMN full_name;

Scenario: Making a Column Required

-- Migration 1: Add column as nullable with default
ALTER TABLE transactions 
ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Backfill existing rows
UPDATE transactions SET status = 'pending' WHERE status IS NULL;

-- Migration 2 (after backfill): Make it NOT NULL
ALTER TABLE transactions 
ALTER COLUMN status SET NOT NULL;

Rollback Considerations

Always provide .down.sql migrations for rollback:

-- 20260429000000_add_user_email.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- 20260429000000_add_user_email.down.sql
ALTER TABLE users DROP COLUMN IF EXISTS email;

Important: Down migrations should also follow safety rules. Dropping columns immediately may break the app version being rolled back to.

Resources

Questions?

If you're unsure whether a migration is safe:

  1. Run ./scripts/check-migration-safety.sh
  2. Review this document
  3. Ask in #engineering-database channel
  4. Consider breaking the migration into multiple steps

Remember: It's better to deploy in multiple steps than to cause downtime.