SQL Migrations

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

Prisma

npm install prisma @prisma/client
npx prisma init

Commands:

npx prisma migrate dev --name add_users_table   # create + apply migration (dev)
npx prisma migrate deploy                       # apply pending migrations (production)
npx prisma migrate reset                        # drop + recreate + seed (destructive)
npx prisma migrate status                       # check migration status
npx prisma generate                             # regenerate client
npx prisma db push                              # push schema without migration file (prototyping)
npx prisma db seed                              # run seed script
npx prisma studio                               # open database GUI

Drizzle

npm install drizzle-orm drizzle-kit

Commands:

npx drizzle-kit generate    # generate migration from schema diff
npx drizzle-kit migrate     # apply migrations
npx drizzle-kit push        # push schema directly (prototyping)
npx drizzle-kit studio      # open Drizzle Studio
npx drizzle-kit drop        # drop a migration

Knex

npm install knex pg
npx knex init                              # create knexfile.js
npx knex migrate:make add_users_table      # create migration
npx knex migrate:latest                    # run pending
npx knex migrate:rollback                  # undo last batch
npx knex migrate:rollback --all            # undo everything
npx knex seed:make seed_users              # create seed file
npx knex seed:run                          # run seeds

TypeORM

npx typeorm migration:create src/migrations/AddUsersTable
npx typeorm migration:generate -d src/data-source.ts src/migrations/AddUsersTable
npx typeorm migration:run -d src/data-source.ts
npx typeorm migration:revert -d src/data-source.ts

Alembic (Python / SQLAlchemy)

alembic init alembic                       # initialize
alembic revision --autogenerate -m "add users table"
alembic upgrade head                       # apply all
alembic downgrade -1                       # undo last
alembic history                            # list migrations
alembic current                            # show current revision

Flyway (Java / JVM)

flyway migrate                             # apply pending
flyway info                                # show status
flyway validate                            # verify applied match local
flyway repair                              # fix metadata table
flyway clean                               # drop all objects (destructive)
# Naming: V1__Create_users.sql, V2__Add_email_index.sql
# Repeatable: R__Create_views.sql (re-runs when checksum changes)

golang-migrate

migrate create -ext sql -dir db/migrations -seq add_users_table
migrate -path db/migrations -database "$DB_URL" up
migrate -path db/migrations -database "$DB_URL" down 1
migrate -path db/migrations -database "$DB_URL" force 3   # fix dirty state

Writing Safe Migrations (Zero-Downtime)

Adding a Column

Never add a NOT NULL column without a default to a table that has existing rows. The safe sequence:

1. Add column as nullable: ALTER TABLE users ADD COLUMN role TEXT;
2. Deploy code that writes the new column.
3. Backfill existing rows: UPDATE users SET role = 'user' WHERE role IS NULL;
4. Add the constraint: ALTER TABLE users ALTER COLUMN role SET NOT NULL;

Removing a Column

Never drop a column that code still reads. The safe sequence:

1. Stop reading the column in application code. Deploy.
2. Stop writing the column. Deploy.
3. Drop the column: ALTER TABLE users DROP COLUMN legacy_field;

Renaming a Column or Table

Renaming breaks existing queries instantly. The safe sequence:

1. Add the new column. Deploy code that writes to both old and new.
2. Backfill new column from old column.
3. Switch reads to new column. Deploy.
4. Stop writing old column. Deploy.
5. Drop old column.

For tables, the same expand-migrate-contract pattern applies. Alternatively, create a view with the old name during transition.

Index Creation

On Postgres, CREATE INDEX locks the table for writes. Use CONCURRENTLY:

-- Safe: does not block writes (Postgres only, cannot run inside a transaction)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Drop safely too
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;

In migration tools that wrap each file in a transaction, you must disable the transaction for that specific migration or run the index creation separately.

Adding Constraints

-- Add foreign key without locking (Postgres)
ALTER TABLE posts ADD CONSTRAINT fk_posts_author
  FOREIGN KEY (author_id) REFERENCES users(id) NOT VALID;
ALTER TABLE posts VALIDATE CONSTRAINT fk_posts_author;

-- Add check constraint without locking (Postgres 12+)
ALTER TABLE users ADD CONSTRAINT chk_role
  CHECK (role IN ('admin', 'user', 'moderator')) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_role;

Data Migrations vs Schema Migrations

Keep them separate. Schema migrations change structure (DDL). Data migrations change content (DML). Mixing them causes problems:

• Schema migrations should be fast and reversible. Data migrations on large tables are slow.
• Schema rollbacks cannot un-delete data.
• Data migrations may need batching; schema migrations do not.

-- Schema migration: 005_add_status_column.sql
ALTER TABLE orders ADD COLUMN status TEXT;

-- Data migration: 006_backfill_status.sql (separate file)
UPDATE orders SET status = 'completed' WHERE completed_at IS NOT NULL;
UPDATE orders SET status = 'pending' WHERE completed_at IS NULL;

Large Table Migrations

For tables with millions of rows, a single ALTER TABLE or UPDATE can lock the table or run for hours.

Batched updates: Process rows in chunks to avoid long locks and transaction log bloat:

-- Backfill in batches of 10,000
DO $$
DECLARE batch_size INT := 10000;
BEGIN
  LOOP
    UPDATE orders SET status = 'pending'
    WHERE id IN (SELECT id FROM orders WHERE status IS NULL LIMIT batch_size);
    EXIT WHEN NOT FOUND;
    COMMIT;
  END LOOP;
END $$;

Online schema change tools (MySQL): pt-online-schema-change (Percona) and gh-ost (GitHub) create a shadow table, copy data, replay binlog changes, then swap. Use these for any DDL on large MySQL tables in production.

# pt-online-schema-change
pt-online-schema-change --alter "ADD COLUMN status VARCHAR(50)" \
  --execute D=mydb,t=orders

# gh-ost
gh-ost --alter "ADD COLUMN status VARCHAR(50)" \
  --database=mydb --table=orders --execute

Squashing / Consolidating Migrations

When migration count grows unwieldy (100+ files), squash them:

1. Dump the current schema: pg_dump --schema-only > baseline.sql
2. Delete all existing migration files.
3. Create a single baseline migration from the dump.
4. Mark it as applied in the migrations table without running it.
5. All future migrations build from this baseline.

Prisma: npx prisma migrate diff can generate a diff between two states. Drizzle and Knex do not have built-in squash -- do it manually.

Rollback Strategies and Limitations

Always write down migrations but understand their limits:

• Dropping a column is irreversible (data is gone). The down migration can recreate the column but not the data.
• Data migrations cannot be meaningfully reversed if the old value was overwritten.
• Rollbacks in production are risky. Prefer forward-fixing: deploy a new migration that undoes the change.

-- Down migration: 003_add_role.down.sql
ALTER TABLE users DROP COLUMN IF EXISTS role;

Migration Locking and Concurrent Deploys

Most migration tools use advisory locks to prevent two processes from running migrations simultaneously. If your deployment runs multiple instances:

• Prisma and Flyway acquire locks automatically.
• Knex, golang-migrate: only one instance should run migrations (use a deploy step, not application startup).
• If a migration crashes mid-run and leaves a lock, you may need to manually clear the lock or use migrate force (golang-migrate) / flyway repair.

Run migrations in a dedicated CI/CD step, not at application boot.

Testing Migrations

• Test against a production-like dataset, not an empty database. Schema changes that work on empty tables may lock or fail on tables with millions of rows.
• Run up then down then up again to verify reversibility.
• Use a copy of production data (anonymized) in staging.
• Check migration speed: if a migration takes > 1 second on staging, it will take longer in production. Plan accordingly.

Seeding Data

Seeds populate the database with initial or test data. Keep seeds idempotent.

-- Idempotent seed
INSERT INTO roles (name) VALUES ('admin'), ('user'), ('moderator')
ON CONFLICT (name) DO NOTHING;

// Prisma seed (prisma/seed.ts, configured in package.json "prisma.seed")
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
async function main() {
  await prisma.role.upsert({
    where: { name: "admin" },
    update: {},
    create: { name: "admin" },
  });
}
main().finally(() => prisma.$disconnect());

Environment-Specific Migrations

• Dev: Use migrate reset / db push freely. Speed matters more than safety.
• Staging: Mirror production. Run the exact same migration files. Test with realistic data volumes.
• Production: Never use reset, push, or clean. Only migrate deploy / migrate up. Always back up before migrating.

Use environment variables to control connection strings. Never hardcode credentials in migration files.

Migration CI/CD Integration

# GitHub Actions example
- name: Run migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

# Validate migrations in PR checks
- name: Check migration status
  run: |
    npx prisma migrate status
    npx prisma migrate diff --from-migrations ./prisma/migrations --to-schema-datamodel ./prisma/schema.prisma --exit-code

CI pipeline checklist:

1. Run migrations against a test database before merging.
2. Verify no pending migrations exist after running.
3. Run the application test suite after migrations.
4. In production deploys, run migrations before deploying new application code (if the migration is backward-compatible) or after (if old code must stop using removed columns first).

Raw SQL Migration Structure

migrations/
├── 001_create_users.up.sql
├── 001_create_users.down.sql
├── 002_create_posts.up.sql
├── 002_create_posts.down.sql
└── 003_add_email_index.up.sql

-- Example up migration
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255),
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Example down migration
DROP TABLE IF EXISTS users;

Quick Reference

1. One change per migration -- easier to review, rollback, and debug
2. Always write down migrations for reversibility
3. Never edit an applied migration -- create a new one
4. Test migrations against production-like data
5. Wrap multi-statement migrations in transactions (except CONCURRENTLY)
6. Separate schema migrations from data migrations
7. Backfill data in a separate migration from the schema change
8. Run migrations in CI/CD, not at application boot
9. Back up the database before running production migrations