Migrations: Schema Evolution

Migrations are your schema's source of truth

A migration is a versioned, reproducible change to the schema. 20260503_add_user_avatar.sql in your repo. Run it once on dev, once on staging, once on prod — same change, same order, every environment.

The mandatory rules

Every schema change is a migration. No "I just ran an ALTER directly on prod." That breaks the reproducibility chain forever.
Migrations are immutable once shipped. If you need to fix a migration that already ran in production, write a new migration that fixes it. Never edit an applied migration.
Test migrations forward and back. Most tools support a down() direction. Use it on dev; trust forward-only on prod.
Make destructive migrations multi-step. Renaming a column = (1) add new column, (2) backfill, (3) start writing to both, (4) cut over reads, (5) drop old column. Across deploys.

The tool zoo

Alembic (Python/SQLAlchemy), Flyway (JVM, language-agnostic), goose (Go), Prisma Migrate (Node/TypeScript), supabase migration / db push (Supabase), graphile-migrate (Postgres-native). Pick one per repo, never two.

Code

Alembic migration·python

# alembic/versions/20260503_add_user_avatar.py
def upgrade():
    op.add_column('users', sa.Column('avatar_url', sa.Text(), nullable=True))
    op.execute("UPDATE users SET avatar_url = 'https://...' WHERE id = 1")

def downgrade():
    op.drop_column('users', 'avatar_url')

Plain SQL migration (Flyway / supabase)·sql

-- migrations/20260503120000_add_user_avatar.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
UPDATE users SET avatar_url = '/avatars/default.png' WHERE avatar_url IS NULL;
ALTER TABLE users ALTER COLUMN avatar_url SET NOT NULL;

Supabase CLI flow·bash

supabase migration new add_user_avatar
# edit the generated migration file
supabase db push --include-all

Migrations are your schema's source of truth

The mandatory rules

The tool zoo

Code

External links

Exercise

Progress

Comments 0