Alembic Migration Patterns and Pitfalls

The Feature

A developer adds a new field to the Application model, generates a migration, reviews it, and applies it without taking the application offline.

The Decision

Alembic’s --autogenerate flag detects differences between the SQLAlchemy models and the database schema and generates the necessary ALTER TABLE statements. Autogenerate handles column additions, column removals, index creation, and constraint changes. It does not handle column renames (it generates a drop and add instead), data migrations, or complex constraint changes. Every autogenerated migration must be reviewed before applying.

The Implementation

Adding a Nullable Column (Safe)

# Add a notes field to Application for organizer comments
# In backend/app/models/application.py, add:
notes: Mapped[str | None] = mapped_column(Text)

# Generate the migration
docker compose exec backend uv run alembic revision --autogenerate -m "add notes to applications"

# Generated migration (reviewed and approved)
def upgrade() -> None:
    op.add_column("applications", sa.Column("notes", sa.Text(), nullable=True))

def downgrade() -> None:
    op.drop_column("applications", "notes")

Adding a nullable column is the safest schema change. It does not require a table rewrite, does not lock the table for an extended period, and does not affect existing queries. New code can use the column. Old code that does not know about it continues to work.

Adding a Non-Nullable Column (Requires Two Steps)

# TRAP: Adding a non-nullable column in one migration
def upgrade() -> None:
    op.add_column(
        "applications",
        sa.Column("review_priority", sa.Integer(), nullable=False)
    )
    # FAILS if the table has existing rows because
    # the new column has no default and cannot be NULL

# SAFE: Two-step migration
# Migration 1: Add column as nullable with a default
def upgrade() -> None:
    op.add_column(
        "applications",
        sa.Column("review_priority", sa.Integer(), nullable=True)
    )
    # Backfill existing rows
    op.execute("UPDATE applications SET review_priority = 0 WHERE review_priority IS NULL")

# Migration 2: After backfill is confirmed, make it non-nullable
def upgrade() -> None:
    op.alter_column(
        "applications",
        "review_priority",
        nullable=False,
        server_default="0",
    )

Renaming a Column

# TRAP: Autogenerate drops and re-adds the column (loses data)
def upgrade() -> None:
    op.drop_column("vendors", "name")
    op.add_column("vendors", sa.Column("business_name", sa.String(200)))

# SAFE: Manual rename preserves data
def upgrade() -> None:
    op.alter_column("vendors", "name", new_column_name="business_name")

def downgrade() -> None:
    op.alter_column("vendors", "business_name", new_column_name="name")

Alembic’s autogenerate cannot detect renames. It sees a missing column and a new column. Always review autogenerated migrations for drop/add pairs that should be renames.

Running Migrations in CI

# .github/workflows/deploy.yml (relevant section)
- name: Run migrations
  run: |
    docker compose exec -T backend uv run alembic upgrade head
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

Migrations run before the new application code deploys. If the migration fails, the deployment stops. The old application code continues running against the old schema. This requires that migrations are backward-compatible: the old code must be able to run against the new schema without errors. Adding a nullable column is backward-compatible. Dropping a column is not.

The Trap

# TRAP: Running migrations manually in production
psql -h db.supabase.co -U postgres -c "ALTER TABLE applications ADD COLUMN notes TEXT"

# This works, but now the Alembic migration history does not know about it.
# The next autogenerate sees the column in both the model and the database
# and generates nothing. But if you deploy to a new environment,
# the migration that adds this column does not exist.

# SAFE: Always use Alembic
docker compose exec backend uv run alembic revision --autogenerate -m "add notes to applications"
docker compose exec backend uv run alembic upgrade head

Manual schema changes create drift. Alembic is the single source of truth for schema state. If a change is not in a migration file, it does not exist.

The Cost

Alembic migrations add approximately 30 seconds to each deployment (running alembic upgrade head against the production database). This is negligible. The cost of not using migrations, schema drift between environments, data loss from manual changes, and the inability to reproduce the database state on a new server, is measured in days of debugging.