Writing Migrations

Goal

Generate reliable, reversible Alembic migrations that evolve the database schema incrementally. Every migration must include both an upgrade and a downgrade path, and must be reviewed before being applied.

When to Use

• Adding, modifying, or removing database tables or columns
• Creating or dropping indexes and constraints
• Changing column types or default values
• Any schema change that needs to be tracked and reversible

Instructions

1. Generate the Migration

Use Alembic's autogenerate feature as a starting point, then review and edit the output.

alembic revision --autogenerate -m "add status column to tasks"

This creates a file like 20250115_143022_add_status_column_to_tasks.py. The naming convention is: timestamp prefix followed by an imperative description of the change.

2. Review the Autogenerated Code

Never trust autogenerate blindly. It may miss certain changes or produce incorrect operations. Always open the generated file and verify:

• All intended changes are present
• No unintended changes are included
• Data types and constraints are correct
• The downgrade function correctly reverses the upgrade

3. Write the Migration

A well-structured migration includes both upgrade() and downgrade() functions.

"""add status column to tasks

Revision ID: a1b2c3d4e5f6
Revises: f6e5d4c3b2a1
Create Date: 2025-01-15 14:30:22.000000
"""
from alembic import op
import sqlalchemy as sa

revision = "a1b2c3d4e5f6"
down_revision = "f6e5d4c3b2a1"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "tasks",
        sa.Column(
            "status",
            sa.String(length=20),
            nullable=False,
            server_default="pending",
        ),
    )
    op.create_index("ix_tasks_status", "tasks", ["status"])


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

4. Handle Data Migrations Separately

Schema migrations change structure. Data migrations change content. Keep them in separate migration files to maintain clarity and reversibility.

# Schema migration: add the column
def upgrade() -> None:
    op.add_column("users", sa.Column("full_name", sa.String(200), nullable=True))


# Separate data migration: populate the column
def upgrade() -> None:
    op.execute(
        "UPDATE users SET full_name = display_name WHERE full_name IS NULL"
    )

After the data migration, create a third migration to add the NOT NULL constraint if needed.

5. Test Forward and Backward

Always test both directions before merging:

# Apply the migration
alembic upgrade head

# Verify the schema change
# ...

# Roll back the migration
alembic downgrade -1

# Verify clean rollback
# ...

# Re-apply for final state
alembic upgrade head

6. Handle Common Patterns

Renaming a Column

def upgrade() -> None:
    op.alter_column("tasks", "name", new_column_name="title")

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

Adding a Non-Nullable Column to an Existing Table

def upgrade() -> None:
    # Step 1: Add as nullable with a default
    op.add_column("tasks", sa.Column("priority", sa.Integer(), nullable=True))
    # Step 2: Backfill existing rows
    op.execute("UPDATE tasks SET priority = 0 WHERE priority IS NULL")
    # Step 3: Set NOT NULL
    op.alter_column("tasks", "priority", nullable=False)

def downgrade() -> None:
    op.drop_column("tasks", "priority")

Constraints

✅ Do

• Test every migration forward (upgrade) and backward (downgrade)
• Keep each migration small and focused on a single logical change
• Review autogenerated output before applying
• Use server_default for new non-nullable columns on existing tables
• Include index creation in the same migration as the column it covers
• Run migrations in a transaction (Alembic default) so failures roll back cleanly

❌ Don't

• Modify an existing migration after it has been applied to any environment
• Put data transforms in the same file as schema changes
• Skip writing a downgrade function
• Use raw SQL in migrations when Alembic operations are available
• Create migrations that depend on application code imports
• Squash migrations without team agreement

Output Format

Produce an Alembic migration Python file with the standard header (revision, down_revision, description), an upgrade() function, and a downgrade() function. Include inline comments explaining non-obvious operations.

Dependencies

• Designing Schemas — the schema definition that migrations implement
• Git Workflow — commit migration files following the branch and commit conventions