Database Migrations & Schema Versioning

Why Schema Migrations Matter

As your application evolves, the database schema must change too. Migration tools track these changes as versioned files, making schema evolution repeatable, auditable, and safe across development, staging, and production environments.

Migration File Structure

-- migrations/
--   001_create_users.up.sql
--   001_create_users.down.sql
--   002_add_orders.up.sql
--   002_add_orders.down.sql
--   003_add_user_phone.up.sql
--   003_add_user_phone.down.sql

-- 001_create_users.up.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- 001_create_users.down.sql
DROP TABLE IF EXISTS users;

-- 003_add_user_phone.up.sql
ALTER TABLE users ADD COLUMN phone TEXT;
CREATE INDEX idx_users_phone ON users (phone);

-- 003_add_user_phone.down.sql
DROP INDEX IF EXISTS idx_users_phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;

Running Migrations

# golang-migrate
migrate -path ./migrations -database "postgresql://user:pass@localhost/app_db" up
migrate -path ./migrations -database "..." down 1  # rollback one step
migrate -path ./migrations -database "..." version  # current version

# dbmate
dbmate -u "postgresql://user:pass@localhost/app_db" up
dbmate down
dbmate status

# Prisma
npx prisma migrate dev --name add_orders   # dev: creates + applies
npx prisma migrate deploy                   # production: applies only
npx prisma migrate status                   # check status

# Flyway
flyway -url=jdbc:postgresql://localhost/app_db migrate
flyway info

Zero-Downtime Migration Strategies

Production migrations must not lock tables or break running application code. Follow the expand-contract pattern: make additive changes first, deploy code that handles both states, then clean up.

-- SAFE: Add a nullable column (instant, no lock)
ALTER TABLE users ADD COLUMN phone TEXT;

-- SAFE: Add an index concurrently (no table lock)
CREATE INDEX CONCURRENTLY idx_users_phone ON users (phone);

-- DANGEROUS: Add NOT NULL without default (rewrites table, locks it)
-- ALTER TABLE users ADD COLUMN phone TEXT NOT NULL;
-- FIX: add nullable, backfill, then add constraint
ALTER TABLE users ADD COLUMN phone TEXT;
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;  -- backfill in batches
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- SAFE: Rename with a view (backward compatible)
-- Step 1: rename column
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Step 2: create a view or alias for backward compatibility
CREATE VIEW users_compat AS
  SELECT id, full_name AS name, full_name, email FROM users;

Backward-Compatible Changes & Rollbacks

-- THE EXPAND-CONTRACT PATTERN:
-- Phase 1 (expand): add new column, keep old one
ALTER TABLE orders ADD COLUMN status_v2 TEXT;

-- Phase 2: deploy code that writes to BOTH columns
-- UPDATE orders SET status_v2 = status;  -- backfill

-- Phase 3: deploy code that reads from new column only

-- Phase 4 (contract): remove old column
ALTER TABLE orders DROP COLUMN status;

-- ROLLBACK STRATEGIES:
-- 1. Down migrations: reverse the up migration
-- 2. Forward-fix: apply a new migration that fixes the issue
-- 3. Point-in-time recovery: restore from backup (last resort)

-- Track migration state
SELECT * FROM schema_migrations;  -- golang-migrate
SELECT * FROM flyway_schema_history;  -- Flyway
SELECT * FROM _prisma_migrations;  -- Prisma