DB Migration Guide

db/migrations/ ├── 000001_create_users.up.sql ├── 000001_create_users.down.sql ├── 000002_add_email_index.up.sql ├── 000002_add_email_index.down.sql ├── 000003_add_orders_table.up.sql └── 000003_add_orders_table.down.sql # golang-migrate example migrate -path db/migrations -database "postgres://..." up # run all pending migrate -path db/migrations -database "postgres://..." up 1 # run 1 migration migrate -path db/migrations -database "postgres://..." down # rollback all migrate -path db/migrations -database "postgres://..." version # current version # Flyway example V001__create_users.sql -- versioned R__refresh_materialized_view.sql -- repeatable U001__undo_create_users.sql -- undo (paid feature)

-- Scenario: Rename column "username" to "display_name" -- NEVER do this (breaks live app immediately): -- ALTER TABLE users RENAME COLUMN username TO display_name; -- STEP 1 (Expand): Add new column, keep old ALTER TABLE users ADD COLUMN display_name VARCHAR(100); -- STEP 2: Backfill new column UPDATE users SET display_name = username WHERE display_name IS NULL; -- STEP 3: Add NOT NULL constraint gradually ALTER TABLE users ALTER COLUMN display_name SET DEFAULT ''; -- wait for app to write both columns... -- STEP 4: Deploy new app code using display_name -- (dual-write: write both columns during transition) -- STEP 5 (Contract): Remove old column after full deploy ALTER TABLE users DROP COLUMN username; -- Zero-downtime index creation (PostgreSQL) CREATE INDEX CONCURRENTLY idx_users_email ON users(email); -- CONCURRENTLY builds without locking writes (takes longer but safe)