Tech & AI, Explained

migrations

9 min read

Migrations

Status: 🟩 COMPLETE Last updated: 2026-06-19 Plain-English tagline: Versioned, replayable changes to your database schema. Like Git commits but for the DB. Lets you bring any environment (local, staging, prod) to the same schema state by applying the same migrations.

In plain English

Your database schema changes over the lifetime of a project: you add tables, columns, indexes, constraints, drop old fields, restructure. Each change has to:

  1. Be applied to your local development DB
  2. Be applied to staging for testing
  3. Be applied to production when ready
  4. Be applied to your teammates’ local DBs
  5. Be applied to any future environment you spin up

You can’t just edit the production DB directly. You need a system that records each change in order, lets you apply them in sequence, and tracks which environment has which changes applied. That system is migrations.

A migration is a small script (usually SQL or a code function) that makes one specific change:

-- migrations/2026_06_15_add_user_avatar.sql
ALTER TABLE users ADD COLUMN avatar_url text;

You commit the migration to Git. When deploying, your tool runs all migrations not yet applied to that database. Schema versions stay in sync.

Tools that manage migrations: Supabase CLI, Prisma Migrate, Drizzle Kit, db-migrate, Knex, Atlas, and many others. The mechanics differ; the concept is the same.

Migrations are how you treat your database schema like code: version-controlled, code-reviewed, reproducible, rollback-able.

Why it matters

  • Reproducibility. Anyone can spin up a new environment and get the exact schema.
  • Team coordination. Schema changes flow through Git like code changes — no out-of-band “oh BTW I added a column.”
  • Auditability. History of every schema change is in version control.
  • Safe deployment. Migrations run as part of deploy. Schema drift between environments becomes impossible (if migrations are run consistently).
  • Rollback potential. Most migration tools support down migrations to undo changes (with caveats).

Without migrations: production drifts, environments differ, “works on my machine” multiplies.

How a migration tool works

The mechanism is consistent across tools:

  1. A migrations folder in your repo: supabase/migrations/ or prisma/migrations/ or similar.
  2. Each migration is a file with a timestamp prefix: 20260615123000_add_user_avatar.sql. Timestamps ensure order.
  3. A tracking table in your database (schema_migrations or _prisma_migrations) records which migrations have been applied to THIS database.
  4. Running migrations applies any in the folder not yet in the tracking table, in timestamp order, and records each.

The tool handles:

  • Generating new migration files
  • Running pending migrations
  • Tracking history
  • (Sometimes) generating SQL from schema definitions
  • (Sometimes) down migrations to roll back

A concrete example with the Supabase CLI

# Initialize Supabase in your project (once)
supabase init
 
# Link to your remote Supabase project
supabase link --project-ref xyzabc
 
# Create a new migration
supabase migration new add_user_avatar
# Creates: supabase/migrations/20260619120000_add_user_avatar.sql

Edit the file:

-- supabase/migrations/20260619120000_add_user_avatar.sql
ALTER TABLE users ADD COLUMN avatar_url text;
CREATE INDEX users_avatar_url_idx ON users(avatar_url) WHERE avatar_url IS NOT NULL;

Apply to your local Supabase (running via supabase start):

supabase migration up

Push to your linked remote Supabase project:

supabase db push

Commit the migration file to Git. Now teammates pulling will get the same change and can apply it locally.

Two main migration patterns

1. Hand-written SQL migrations (Supabase, Flyway, raw SQL)

You write the SQL directly. The tool just orchestrates running it in order.

Pros: maximum control, no abstraction layer, you see exactly what runs. Cons: you write more, easier to make mistakes.

2. Generated migrations from schema diffs (Prisma, Drizzle, EF Core)

You declare the desired schema in code. The tool diffs against the current schema and generates the migration SQL.

// prisma/schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  avatarUrl String?  // ← added this
}
npx prisma migrate dev --name add_user_avatar
# Tool generates: prisma/migrations/.../migration.sql
# Containing: ALTER TABLE users ADD COLUMN avatar_url text;

Pros: less typing, harder to make typos. Cons: the abstraction sometimes generates surprising or suboptimal SQL; you should review.

For Supabase specifically, hand-written SQL is the conventional approach. Prisma and Drizzle integrate with Supabase too if you prefer their generators.

Down migrations (rollback)

Some tools support “down” or reverse migrations:

-- Up migration
ALTER TABLE users ADD COLUMN avatar_url text;
 
-- Down migration (rollback)
ALTER TABLE users DROP COLUMN avatar_url;

In practice: down migrations are useful in dev, dangerous in prod. Dropping a column that contains data = data loss. Most teams treat production migrations as forward-only and use database backups / point-in-time recovery as the real rollback mechanism.

Supabase migrations are forward-only by default.

Zero-downtime migrations

Some schema changes can lock tables for long enough to cause outages on a live system. Patterns to avoid this:

Adding a column

Almost always safe. Modern Postgres handles ALTER TABLE ADD COLUMN very fast (even with defaults, since Postgres 11).

Adding NOT NULL on an existing column

  • Old way: locks the table while validating every row.
  • Modern way: ALTER TABLE ... ADD CONSTRAINT ... NOT VALID; then VALIDATE CONSTRAINT later (no lock).

Renaming a column

A rename breaks the running app code that still references the old name. Pattern:

  1. Add new column
  2. Make app write to both
  3. Backfill old to new
  4. Switch app to read from new
  5. Stop app writing to old
  6. Drop old

Each step is independently safe.

Changing a column’s type

Often requires rewriting all rows. Pattern: add new column → backfill → switch → drop old.

Adding an index

CREATE INDEX locks the table. CREATE INDEX CONCURRENTLY doesn’t, but takes longer.

Dropping a column

Just ALTER TABLE DROP COLUMN. But your app must already not reference it.

What migrations don’t do

  • Data migrations are sometimes separate. Schema migrations change structure; data migrations change data. Big data migrations may need to run in batches outside the migration framework.

  • Migrations don’t auto-rollback. If a migration fails partway, you may need to fix and re-run.

  • Migrations don’t validate semantics. Tool generates SQL; doesn’t ensure the SQL is what you wanted. Review every migration.

  • Migrations don’t handle environment-specific things. Production has real data; local does not. Watch for migrations that assume specific data.

Migration workflow in a team

Typical flow:

Developer makes change to schema:
1. Create migration (CLI or by hand)
2. Run migration locally; test
3. Commit migration to Git
4. Open PR

Code review:
5. Reviewer reads migration
6. Reviewer checks: is this safe for production? Any locking? Data loss risk?
7. Approve / request changes

Merge:
8. Migration is in main
9. CI deploys to staging; runs migration
10. Test on staging
11. Deploy to production; runs migration

Production:
12. Migration runs against production DB
13. App deploys with code that depends on new schema
14. Monitor

Order of steps 13-14 is critical. The schema must be ready before the app code that uses it. The opposite order causes errors.

For zero-downtime: make schema additions backward-compatible. Old code works with new schema; new code works with new schema; switch app code; later, clean up old schema.

Common gotchas

  • Editing a migration after it’s been committed. Don’t. The migration may have been applied somewhere. Editing changes history without changing what already ran. Create a new migration to fix.

  • Migration order matters. Two developers create migrations on parallel branches. When merged, timestamps interleave. Usually fine, but watch for dependencies.

  • Running migrations against the wrong database. Always verify the connection before applying.

  • Migrations that lock tables for minutes. A bad migration on a huge table = downtime. Test on prod-size data. Use online patterns.

  • Forgetting to commit the migration file. App expects the new column; DB doesn’t have it. Deploy fails.

  • Skipping migrations and editing the DB directly. Tracking goes out of sync. Future migrations behave unpredictably. Don’t.

  • Migration history table tampered with. Manually editing the migrations table to “skip” a failed migration → schema drift. Fix the migration and run properly.

  • Squashing migrations carelessly. Some tools support squashing many migrations into one. Useful but risky if downstream environments have already applied the originals.

  • Data loss without backup. Migration that drops a column = data gone. Back up first (or test on a copy).

  • Migrations that depend on data state. A migration assuming “all users have an email” fails if some don’t. Make migrations idempotent and resilient.

  • Long-running data backfills inside a migration. Should be separate scripts, not blocking migrations.

  • Production migrations running with low privileges. Service account may not have permission to ALTER TABLE. Use a migration-specific role.

  • Forgetting CASCADE / RESTRICT semantics. ON DELETE CASCADE in a migration → silently deletes data. Be intentional.

  • Adding NOT NULL on existing data. Existing rows might not have values. Either backfill, or default, or NOT VALID + VALIDATE pattern.

  • Renames that break running app code. Mid-deploy, old code is still running. Use the 6-step pattern for renames.

  • Changing primary key types. Usually requires recreating the table. Very disruptive.

  • Adding an index that locks. Use CREATE INDEX CONCURRENTLY on production.

  • Migrations against materialized views. Refresh after schema changes.

  • Trigger / function changes. ALTER FUNCTION sometimes needs CREATE OR REPLACE; handle dependencies.

  • Different DB versions across environments. Migration written for Postgres 16 might fail on Postgres 14. Match versions.

  • No automated migration runs in deploy. Forgot to run them → app breaks. Wire into CI/CD.

  • Manually running migrations in production console. Now no automation knows. Drift.

  • Migration files with non-deterministic content. Including timestamps or random data in a migration file → different SQL between environments.

  • Treating migrations as the only history. Migrations evolve over time. The current schema isn’t easily seen in any one file. Some tools generate “schema.sql” snapshots; use them.

See also

Sources

Graph View

Backlinks