Generating Migrations — Autogenerate and Manual Scripts

Alembic’s most powerful feature is autogenerate — comparing your SQLAlchemy models to the current database schema and generating a migration script that bridges the gap. It detects added tables, dropped tables, added columns, dropped columns, type changes, and index changes. It does not detect column renames (sees a drop + add), changes to stored procedures, or triggers. Understanding the limits of autogenerate prevents the most dangerous Alembic mistake: applying a migration that silently drops a column because Alembic misread a rename as a delete-and-create.

Generating Autogenerated Migrations #

# Generate a migration by comparing models to the current DB schema
alembic revision --autogenerate -m "add_tags_table"

# The generated file appears in alembic/versions/
# Example filename: abc123def456_add_tags_table.py

# Review BEFORE applying:
cat alembic/versions/abc123def456_add_tags_table.py

# Apply after review:
alembic upgrade head

# Example generated migration: alembic/versions/abc123_add_tags_table.py
"""add tags table

Revision ID: abc123def456
Revises: xyz789abc000
Create Date: 2025-08-06 14:30:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = "abc123def456"
down_revision = "xyz789abc000"   # points to the previous migration
branch_labels = None
depends_on = None

def upgrade() -> None:
    op.create_table(
        "tags",
        sa.Column("id",   sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=100), nullable=False),
        sa.Column("slug", sa.String(length=100), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("name"),
        sa.UniqueConstraint("slug"),
    )
    op.create_index("ix_tags_id", "tags", ["id"], unique=False)

def downgrade() -> None:
    op.drop_index("ix_tags_id", table_name="tags")
    op.drop_table("tags")

Writing Manual Migrations #

# Generate an empty migration for manual SQL operations
alembic revision -m "add_full_text_search_index"

# alembic/versions/def456_add_full_text_search_index.py
"""add full text search index

Revision ID: def456ghi789
Revises: abc123def456
Create Date: 2025-08-06 15:00:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = "def456ghi789"
down_revision = "abc123def456"

def upgrade() -> None:
    # ── Add a generated tsvector column ──────────────────────────────────────
    op.execute("""
        ALTER TABLE posts
        ADD COLUMN search_vector TSVECTOR
        GENERATED ALWAYS AS (
            setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
            setweight(to_tsvector('english', COALESCE(body,  '')), 'B')
        ) STORED
    """)

    # ── Create GIN index concurrently (non-blocking on production) ───────────
    # Note: CONCURRENTLY cannot run inside a transaction — use execute_if
    op.execute(
        "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_search "
        "ON posts USING GIN (search_vector)"
    )

def downgrade() -> None:
    op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_posts_search")
    op.drop_column("posts", "search_vector")

What Autogenerate Can and Cannot Detect #

Common Mistakes #

Mistake 1 — Applying migration without reviewing it #

❌ Wrong — blindly applying autogenerated migration:

alembic revision --autogenerate -m "changes" && alembic upgrade head
# May drop columns or tables if Alembic misread the intent!

✅ Correct — review the generated file before applying:

alembic revision --autogenerate -m "changes"
# READ the generated file carefully
alembic upgrade head   # ✓ only after review

Mistake 2 — Using CONCURRENTLY inside a transaction #

❌ Wrong — CREATE INDEX CONCURRENTLY cannot run inside a transaction:

def upgrade():
    op.create_index("idx_posts_status", "posts", ["status"])   # wraps in transaction
    # For CONCURRENTLY, use op.execute() directly

✅ Correct — use op.execute() and mark as non-transactional if needed:

def upgrade():
    op.execute("CREATE INDEX CONCURRENTLY idx_posts_status ON posts(status)")   # ✓

Mistake 3 — Empty downgrade() function #

❌ Wrong — cannot roll back:

def downgrade() -> None:
    pass   # can never roll back this migration!

✅ Correct — always implement downgrade():

def downgrade() -> None:
    op.drop_column("posts", "search_vector")   # ✓ reversible

Quick Reference #