Setting up Alembic

Alembic needs a one-time setup in your project:

# Install alembic
pip install alembic

# Initialize in your project directory
alembic init alembic

This creates:

alembic/
├── env.py           # Configuration: how Alembic connects to the database
├── script.py.mako   # Template for new migration files
└── versions/        # Migration scripts live here
alembic.ini          # Alembic config file (connection string goes here)

The critical file is env.py. You need to configure two things: the database URL and the SQLAlchemy metadata (so Alembic knows your models).

# alembic/env.py (key parts)
from myapp.models import Base  # import your models' Base

target_metadata = Base.metadata  # Alembic compares this to the database

And in alembic.ini:

sqlalchemy.url = postgresql://user:pass@localhost:5432/mydb

Creating migrations

The core workflow has three commands.

Autogenerate a migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. Ask AI for more

alembic revision --autogenerate -m "add posts table"

Alembic compares your SQLAlchemy models (the target_metadata) to the current database schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. Ask AI for more and generates a migration script that reconciles the difference. If your models have a Post class but the database has no posts table, the migration creates it.

This generates a file like alembic/versions/a1b2c3d4_add_posts_table.py:

"""add posts table

Revision ID: a1b2c3d4
Revises: 9f8e7d6c
Create Date: 2026-03-15 14:30:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = "a1b2c3d4"
down_revision = "9f8e7d6c"

def upgrade():
    op.create_table(
        "posts",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("title", sa.String(200), nullable=False),
        sa.Column("content", sa.Text(), nullable=False),
        sa.Column("user_id", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"]),
    )
    op.create_index("ix_posts_user_id", "posts", ["user_id"])

def downgrade():
    op.drop_index("ix_posts_user_id", table_name="posts")
    op.drop_table("posts")

Apply migrations

# Apply all pending migrations
alembic upgrade head

# Apply one migration at a time
alembic upgrade +1

Revert migrations

# Revert the last migration
alembic downgrade -1

# Revert to a specific revision
alembic downgrade a1b2c3d4

# Revert all migrations (back to empty database)
alembic downgrade base

Reading migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. Ask AI for more files

Every migration has two functions. Understanding what each does, and what can go wrong, is the core skill.

upgrade(): the forward path

This function transforms the database from the previous version to the next. Common operations:

def upgrade():
    # Create a table
    op.create_table("posts", ...)

    # Add a column to an existing table
    op.add_column("users", sa.Column("bio", sa.Text(), nullable=True))

    # Create an index
    op.create_index("ix_users_email", "users", ["email"], unique=True)

    # Rename a column
    op.alter_column("users", "name", new_column_name="full_name")

    # Change a column type
    op.alter_column("users", "age",
        type_=sa.String(3),
        existing_type=sa.Integer(),
        postgresql_using="age::varchar"
    )

downgrade(): the rollbackWhat is rollback?Undoing a database migration or deployment to restore the previous state when something goes wrong. Ask AI for more path

This function reverses the upgrade(). It must undo every change in reverse order.

def downgrade():
    # Reverse order of upgrade()
    op.alter_column("users", "age",
        type_=sa.Integer(),
        existing_type=sa.String(3),
        postgresql_using="age::integer"
    )
    op.alter_column("users", "full_name", new_column_name="name")
    op.drop_index("ix_users_email", table_name="users")
    op.drop_column("users", "bio")
    op.drop_table("posts")

Dangerous migrations AI generates

Not all schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. Ask AI for more changes are safe. Some lose data, some lock tables, some silently corrupt values. AI generates all of them without warning.

Dropping columns

def upgrade():
    op.drop_column("users", "phone_number")

This permanently deletes every user's phone number. If you realize the column was needed, downgrade() can recreate the column, but the data is gone forever. The safe approach is a two-step migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. Ask AI for more: first, stop writing to the column; later, after confirming nothing reads it, drop it.

Changing column types

def upgrade():
    op.alter_column("products", "price",
        type_=sa.String(20),
        existing_type=sa.Numeric(10, 2)
    )

This converts a precise decimal price (19.99) to a string ("19.99"). The data survives, but you lose the ability to do math in SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more (SUM(price) no longer works). Going back is worse: String to Numeric fails if any value is not a valid number. AI generates type changes without considering whether the conversion is reversible.

Renaming columns without data migration

def upgrade():
    # AI generates this as "rename"
    op.drop_column("users", "name")
    op.add_column("users", sa.Column("full_name", sa.String(100)))

This is not a rename, it drops the name column (losing all data) and creates a new empty full_name column. The correct approach uses op.alter_column() with new_column_name:

def upgrade():
    op.alter_column("users", "name", new_column_name="full_name")

Adding non-nullable columns without defaults

def upgrade():
    op.add_column("users",
        sa.Column("avatar_url", sa.String(500), nullable=False)
    )

If the users table has existing rows, this migration fails. PostgreSQL cannot add a non-nullable column to a table with data unless you provide a default value. The fix:

def upgrade():
    # Step 1: add as nullable
    op.add_column("users",
        sa.Column("avatar_url", sa.String(500), nullable=True)
    )
    # Step 2: fill existing rows
    op.execute("UPDATE users SET avatar_url = '/default-avatar.png'")
    # Step 3: set non-nullable
    op.alter_column("users", "avatar_url", nullable=False)

MigrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. Ask AI for more workflow best practices

When you get AI to generate a migration, treat the output as a first draft. Read the upgrade() for destructive operations, verify the downgrade() reverses everything, and test both directions locally before applying to production.