database-migration-patterns

Database Migration Patterns

Evolve database schemas safely with versioned, reversible, tested migrations.

Alembic Setup

# Initialize
alembic init alembic

# Create migration
alembic revision --autogenerate -m "add skills table"

# Run migrations
alembic upgrade head

# Rollback one step
alembic downgrade -1

# Show current state
alembic current
alembic history

Configuration

# alembic/env.py
from app.models import Base
from app.config import settings

target_metadata = Base.metadata

def run_migrations_online():
    connectable = create_async_engine(settings.database_url)
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)

def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()

Migration Structure

"""add skills table

Revision ID: abc123
Revises: def456
Create Date: 2026-03-20 10:00:00
"""
from alembic import op
import sqlalchemy as sa

revision = "abc123"
down_revision = "def456"

def upgrade():
    op.create_table(
        "skills",
        sa.Column("id", sa.String(64), primary_key=True),
        sa.Column("name", sa.String(64), nullable=False, unique=True),
        sa.Column("description", sa.Text, nullable=False),
        sa.Column("category", sa.String(32), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )
    op.create_index("ix_skills_category", "skills", ["category"])

def downgrade():
    op.drop_index("ix_skills_category")
    op.drop_table("skills")

Zero-Downtime Migration Strategies

Adding a Column

# Safe: add nullable column first
def upgrade():
    op.add_column("skills", sa.Column("tier", sa.String(20), nullable=True))

# Later migration: backfill then add constraint
def upgrade():
    op.execute("UPDATE skills SET tier = 'community' WHERE tier IS NULL")
    op.alter_column("skills", "tier", nullable=False, server_default="community")