database-migration

Database Migration

Migration File Conventions

Every schema change must be captured in a versioned migration file. Follow these conventions:

• Timestamped naming: Use YYYYMMDDHHMMSS_descriptive_name (e.g., 20240315143022_add_status_to_orders.sql).
• Sequential ordering: Migrations run in order. Never insert a migration before one that has already been applied.
• One concern per file: Each migration handles a single logical change. Do not combine unrelated schema changes.
• Descriptive names: The filename should describe the change, not the ticket number. Use verbs like add, remove, create, drop, rename, alter.
• Idempotent when possible: Use IF NOT EXISTS / IF EXISTS guards to make re-runs safe.

migrations/
  20240301100000_create_users_table.sql
  20240305120000_add_email_index_to_users.sql
  20240310090000_create_orders_table.sql
  20240315143022_add_status_to_orders.sql

Forward and Rollback Pairs

Every migration must have a corresponding rollback. If you cannot write a safe rollback, document why and flag it for review.

-- Migration: 20240315143022_add_status_to_orders.sql

-- UP
ALTER TABLE orders ADD COLUMN status VARCHAR(50) DEFAULT 'pending';
CREATE INDEX idx_orders_status ON orders (status);

-- DOWN
DROP INDEX IF EXISTS idx_orders_status;
ALTER TABLE orders DROP COLUMN IF EXISTS status;

Rollback Rules

Scenario	Rollback Strategy
Add column	Drop column
Add index	Drop index
Create table	Drop table
Add constraint	Drop constraint
Rename column	Rename back
Drop column	Cannot auto-rollback — must restore from backup or use prior migration to re-add
Data backfill	Reverse backfill or accept data state

Zero-Downtime Schema Changes

When your application cannot tolerate downtime during deploys, follow the expand-and-contract pattern. Never make breaking schema changes in a single step.

Step-by-Step: Renaming a Column