SQL Migrations

Migration Fundamentals

A migration is a versioned, incremental change to a database schema. Migrations run in order and track which have been applied via a metadata table (e.g., schema_migrations, _prisma_migrations).

Up/Down: The up migration applies the change. The down migration reverses it. Not all changes are reversible (dropping a column with data destroys it).

Versioned vs Repeatable: Versioned migrations run once in order (001, 002, ...). Repeatable migrations (Flyway R__ prefix) re-run whenever their checksum changes -- useful for views, functions, and stored procedures.

Idempotent migrations: Use IF NOT EXISTS / IF EXISTS guards so a migration can be re-run safely without erroring on already-applied state:

CREATE TABLE IF NOT EXISTS users (...);
ALTER TABLE users ADD COLUMN IF NOT EXISTS role TEXT;
DROP INDEX IF EXISTS idx_users_email;

Tool-Specific Patterns