Database Migration Strategies That Do Not Break Production

Database Migration Strategies That Do Not Break Production

You run a migration, the deployment succeeds, and then your API starts returning 500 errors. A column was renamed, but the old code is still running on some servers. A NOT NULL constraint was added, but existing rows have null values. The migration was correct in isolation — it broke production because it was not deployed safely.

The Golden Rule

Every migration must be backward-compatible with the currently running code. The database is shared — the old code and new code run simultaneously during deployment.

Pattern 1: Additive Changes

Adding a column, index, or table is always safe:

-- Safe: existing code ignores new columns
ALTER TABLE users ADD COLUMN avatar_url TEXT;

Existing queries like SELECT name, email FROM users continue to work. The new column has a NULL default, so no existing rows break.

Pattern 2: Expand-Contract for Renames

Renaming a column breaks all queries that reference the old name. Instead, expand first, then contract later:

Deploy 1: Add new column

ALTER TABLE users ADD COLUMN full_name TEXT;
UPDATE users SET full_name = name;

Deploy 2: Update code to write both columns

# Write to both columns during transition
user.name = value
user.full_name = value

Deploy 3: Update code to read from new column

# Read from full_name, stop writing to name
display_name = user.full_name

Deploy 4: Drop old column

ALTER TABLE users DROP COLUMN name;

Four deploys instead of one, but zero downtime.

Pattern 3: NOT NULL Constraints

Adding NOT NULL to an existing column fails if any row has NULL:

-- FAILS if any user has null email
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Safe approach:

-- Step 1: Backfill nulls
UPDATE users SET email = 'unknown@placeholder.com' WHERE email IS NULL;

-- Step 2: Add constraint with validation
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 3: Validate in background (does not lock the table)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 4: Convert to proper NOT NULL (now safe because all rows pass)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

The NOT VALID flag adds the constraint without checking existing rows. New inserts are validated immediately. VALIDATE checks existing rows without holding a write lock.

Pattern 4: Safe Index Creation

-- BAD: Locks the table for writes during index creation
CREATE INDEX idx_users_email ON users (email);

-- GOOD: Creates index without locking
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY takes longer but does not block writes. For a table with millions of rows, a normal index creation can lock writes for minutes.

Migration Tools

For Supabase projects, use the built-in migration system:

supabase migration new add_avatar_url

This creates a timestamped SQL file:

supabase/migrations/20240115120000_add_avatar_url.sql

Migrations run in order. Each migration runs exactly once. The supabase_migrations table tracks which migrations have been applied.

Testing Migrations

Before running on production:

1. Test on a copy. Clone the production schema, apply the migration, verify.
2. Test with real data volumes. A migration that works on 100 rows might lock the table for 10 minutes on 10 million rows.
3. Test rollback. Write a down migration for every up migration.

Rollback Strategy

Every migration should have a corresponding rollback:

-- up.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- down.sql
ALTER TABLE users DROP COLUMN avatar_url;

If the migration causes issues, you can revert without writing emergency SQL under pressure.

Takeaways

Database migrations are deployments. They deserve the same care as code deployments — testing, staging, rollback plans, and gradual rollouts. The expand-contract pattern handles any schema change safely. Never run a destructive migration (DROP, RENAME, NOT NULL) in a single step.