database-migration

Database Migration

Safe patterns for evolving database schemas in production.

Migration Principles

1. Backward compatible - New code works with old schema
2. Reversible - Can rollback if needed
3. Tested - Verify on staging before production
4. Incremental - Small changes, not big-bang
5. Zero downtime - No service interruption

Safe Migration Pattern

Phase 1: Add New (Compatible)

-- Add new column (nullable initially)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255) NULL;

-- Deploy new code that writes to both old and new
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);

Phase 2: Migrate Data

-- Backfill existing data
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL;

Phase 3: Make Required

-- Make column required
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Phase 4: Remove Old (After New Code Deployed)

-- Remove old columns
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

Common Migrations

Adding Index

-- Create index concurrently (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Renaming Column

-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Phase 2: Copy data
UPDATE users SET email_address = email;

-- Phase 3: Drop old column (after deploy)
ALTER TABLE users DROP COLUMN email;

Changing Column Type

-- Phase 1: Add new column with new type
ALTER TABLE products ADD COLUMN price_cents INTEGER;

-- Phase 2: Migrate data
UPDATE products SET price_cents = CAST(price * 100 AS INTEGER);

-- Phase 3: Drop old column
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;

Adding Foreign Key

-- Add column first
ALTER TABLE orders ADD COLUMN user_id INTEGER NULL;

-- Populate data
UPDATE orders SET user_id = (
    SELECT id FROM users WHERE users.email = orders.user_email
);

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);