Migrations with Alembic

Alembic manages schema versions for SQLAlchemy. alembic revision --autogenerate -m "..." compares models to the database and drafts a migration — always review it (autogenerate misses some changes like renames and may drop things unexpectedly). Apply with alembic upgrade head as a deploy step before starting new app versions, and keep migrations backward compatible for zero-downtime deploys.

# alembic/versions/20261010_add_orders_status.py
from alembic import op
import sqlalchemy as sa

revision = "20261010_add_orders_status"
down_revision = "20260901_create_orders"


def upgrade() -> None:
    op.add_column("orders", sa.Column("status", sa.String(20), nullable=True))
    op.execute("UPDATE orders SET status = 'paid' WHERE status IS NULL")
    op.alter_column("orders", "status", nullable=False, server_default="pending")
    with op.get_context().autocommit_block():
        op.create_index("ix_orders_status", "orders", ["status"], postgresql_concurrently=True)


def downgrade() -> None:
    op.drop_index("ix_orders_status", table_name="orders")
    op.drop_column("orders", "status")

alembic revision --autogenerate -m "add orders status"
alembic upgrade head
alembic current

Common pitfalls

• Applying autogenerated migrations without review.
• Renames detected as drop + add (data loss).
• Large backfills in one transaction locking tables.

Key points

• Autogenerate, then review
• Run migrations as a deploy step
• Expand/contract for zero downtime

Further reading: Alembic tutorial