Database
Advanced 16 min read Database

Migrations & Seeding

Schema evolution done right: versioned, ordered, reviewable migrations with rollback paths, applied exactly once everywhere. Plus idempotent seeding so every environment — from CI to production — converges on the same structure and data.

migrationschema evolutionseedingFlywayAlembicgooseexpand-contractCI/CD
Prerequisites: Database Design & Normalization PostgreSQL Deep Dive
Introduction

What is this?

Migrations are versioned, ordered schema changes applied in sequence, so every environment converges on the same structure. Each migration has a unique ID and records itself in a tracking table, guaranteeing a change applies exactly once. Seeding loads initial or development data. Together they make schema evolution a first-class, reviewable and reversible part of CI/CD.

Motivation

Why do you need it?

  • Without versioned migrations, schemas drift: production differs from staging and nobody knows why.
  • Migrations give every change a code review, a rollback path and an audit trail.
  • They apply identically to empty CI databases and long-lived production — no manual ALTER TABLE.
  • Seeding standardizes realistic dev/QA data so bugs surface before deploy.
  • Downtime-free patterns (expand-contract, batched backfill) change schema while traffic keeps flowing.
Think about it

Mental model

Imagine

A recipe listing every step and ingredient in order

Think of

A migration sequence

Skip a step and the cake fails; run a step twice and you spoil it. Versioned migrations number the steps so every kitchen applies them exactly once, in the same order.

Mechanics

How it works

  • Each change is authored as an ordered, uniquely named up/down file pair, often protected by a checksum.
  • A tracking table (schema_migrations) records the version of the last applied change.
  • On deploy, the tool compares applied versions with available files and runs the missing ones in order.
  • Each migration typically runs inside a transaction so a failure rolls back cleanly.
  • The tracking row is inserted only after success, so a failed run retries cleanly.
  • Seeding then populates reference data or a test dataset idempotently.

Applying a schema migration through CI/CD.

Author 004_add_teams.up.sql
CI applies in order
Migration lock table
DDL executes
Rollback file ready
Schema updated
Reference

Key commands

goose create add_teams sql

Generate a new, timestamped up/down SQL migration pair (goose).

goose up

Apply all pending migrations in order.

OK   004_add_teams.sql (19.3ms)
goose down

Roll back the most recent migration using its down file.

goose status

Show each migration's version and applied state.

Applied At       Migration\n2026-05-01 004_add_teams.sql\nPending          003_create_orders.sql
alembic revision -m "add teams"

Create a new revision with SQLAlchemy/Alembic.

alembic upgrade head

Apply all pending Alembic revisions up to the latest.

Hands-on

Examples

A versioned up/down migration pair

A versioned up/down migration pair
-- 004_add_teams.up.sql
CREATE TABLE teams (
  id         BIGSERIAL PRIMARY KEY,
  name       TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
ALTER TABLE users ADD COLUMN team_id BIGINT REFERENCES teams(id);
 
CREATE INDEX idx_users_team ON users (team_id);
 
-- 004_add_teams.down.sql
DROP INDEX IF EXISTS idx_users_team;
ALTER TABLE users DROP COLUMN IF EXISTS team_id;
DROP TABLE IF EXISTS teams;

The up file adds the teams table, a foreign key column and an index; the down file reverses each step in the reverse order. The name 004 encodes the version and the tool records it in schema_migrations so it runs once.

Real world

In production

  • CI runs migrations against a clean scratch database to validate every change before deploy.
  • Expand-contract deployments add columns with NULL defaults first, backfill, then drop old columns.
  • Release trains batch migrations with the application version so schema and code deploy together.
  • Seed scripts rebuild demo environments and load reference tables (currencies, regions, roles) idempotently.
Guidelines

Best practices

  • Treat applied migrations as immutable history; write a new migration instead of editing an old one.
  • Add columns as nullable or with defaults first so old application code keeps working.
  • Backfill large tables in batches with a WHERE range, not one giant UPDATE.
  • Split risky migrations (ADD NOT NULL + backfill) across deploy windows and verify with row counts.
  • Run one migration process at a time and rely on its locking to prevent double-apply.
Watch out

Common mistakes

  • Editing an already-applied migration to 'fix' production, breaking checksums and replayability.
  • Running a long ALTER during peak traffic, locking the table and stalling requests.
  • Forgetting a down migration, leaving the rollback path incomplete.
  • Shipping a migration that rebuilds a huge table without a zero-downtime strategy.
Root cause analysis

Common issues

Error
goose: Lock held by another migration process
Root cause

Two migration runs started concurrently.

Fix

Serialize deploys with a single runner; the lock clears when the other process finishes.

Error
checksum mismatch for migration 004
Root cause

An applied migration file was edited after it ran.

Fix

Never edit applied files; create a corrective migration instead.

Error
ERROR: column "team_id" of relation "users" already exists
Root cause

The migration ran before but was not recorded, or was edited and re-run.

Fix

Verify goose status, repair the tracking table to match reality, then re-run.

When things break

Debugging tips

  • Run goose status / alembic history on every environment to compare applied vs pending versions.
  • Test migrations against a copy of production data — restore a dump to staging first.
  • Watch the migration lock table: a stuck lock means another run is holding it.
  • Wrap backfills in transactions and count rows per batch to estimate total time.
Interviews

Interview checkpoint

Practice

Hands-on lab

Versioned Schema Evolution
40 min · advanced
In progress
Objectives
  • Create a versioned migration up/down pair
  • Apply and verify migrations on a clean database
  • Roll back with the down migration
  • Seed reference data idempotently

Versioned Schema Evolution

40 min · advanced

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/3
1

Where does a migration tool record which versions have been applied?

2

Editing a migration that already ran in production is safe as long as you fix the tracking table.

3

A backfill UPDATE of 50 million rows is planned. What is the safest approach?

Quick reference

Cheat sheet

Migration workflow
createGenerate an up/down pair
statusApplied vs pending versions
upApply pending migrations
downRoll back one migration
redoRoll back then re-apply
Zero-downtime pattern
Add column nullableNo immediate rewrite
Backfill in batchesBound lock and I/O
Add NOT NULLOnly after backfill
Drop old columnAfter app rollout
Seed idempotentlyINSERT ... ON CONFLICT
Go deeper

Official documentation

Alembic — SQLAlchemy migrations
docs
Flyway documentation
docs
Prisma Migrate
docs
goose migrations (GitHub)
docs