Python

Alembic — Quick Reference

What Is Alembic?

Alembic is Prisma Migrate for Python/SQLAlchemy. You change your models → Alembic generates the SQL → you apply it to the DB.

The core problem it solves: your Python model changed, but the real database has no idea. Alembic bridges that gap with versioned, reproducible migration files.

Direct Mapping — Prisma / Drizzle vs Alembic

Concept	Prisma	Drizzle	Alembic
Define schema	`schema.prisma`	`schema.ts`	SQLAlchemy models (`models/`)
Generate migration	`prisma migrate dev`	`drizzle-kit generate`	`alembic revision --autogenerate`
Apply migration	`prisma migrate deploy`	`drizzle-kit push`	`alembic upgrade head`
Migration files	`migrations/`	`migrations/`	`alembic/versions/`
Roll back	❌ not built-in	❌ not built-in	✅ `alembic downgrade -1`

Alembic's rollback is a genuine advantage over Prisma/Drizzle. Every migration has an upgrade() and downgrade() — you can go backwards cleanly.

Key Difference vs Prisma

In Prisma, the .prisma file is the single source of truth — it generates both the client and migrations.

In SQLAlchemy + Alembic, they are two separate tools:

Plain text

SQLAlchemy models  →  your Python source of truth (defines classes)
Alembic            →  watches those models, generates SQL migrations

You have to manually wire them together in alembic/env.py.

Setup

Bash

pip install alembic
alembic init alembic   # creates alembic/ folder + alembic.ini

alembic.ini

Ini

sqlalchemy.url = postgresql+asyncpg://user:password@localhost/mydb

alembic/env.py — The Wiring File

Python

from app.database import Base
from app.models import user, post, tag  # ← import ALL models here
                                         # Alembic can only see what's imported

target_metadata = Base.metadata

⚠️ #1 Gotcha: If you add a new model file and forget to import it here, Alembic will not detect it — and won't generate a migration for it.

The Workflow (3 Steps Every Time)

Step 1 — Change your model

Python

# Added two new columns to User
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255))
    bio: Mapped[str | None] = mapped_column(Text, nullable=True)         # ← new
    avatar_url: Mapped[str | None] = mapped_column(String(500), nullable=True)  # ← new

Step 2 — Generate the migration

Bash

alembic revision --autogenerate -m "add bio and avatar to users"

Alembic compares your current models vs what the DB looks like right now and generates a migration file automatically:

Python

# alembic/versions/abc123_add_bio_and_avatar_to_users.py

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

def downgrade():
    op.drop_column("users", "avatar_url")
    op.drop_column("users", "bio")

Step 3 — Apply it

Bash

alembic upgrade head   # "head" = latest migration

Common Commands

Bash

# Apply all pending migrations
alembic upgrade head

# Roll back the last migration
alembic downgrade -1

# Roll back to a specific version
alembic downgrade abc123

# See full migration history
alembic history

# See where your DB currently is
alembic current

# Generate a blank migration (for manual SQL)
alembic revision -m "some manual change"

Base.metadata.create_all vs Alembic

	`create_all`	Alembic
Use case	Local dev only	Staging + Production
Tracks changes	❌ No	✅ Yes, versioned
Safe on existing data	❌ Won't alter existing tables	✅ Generates precise ALTER statements
Rollback	❌ No	✅ Yes, via `downgrade()`

Rule of thumb: use create_all to spin up a fresh local DB fast. Use Alembic for anything that has real data.

What a Migration File Looks Like

Python

# alembic/versions/abc123_add_role_to_users.py

revision = "abc123"
down_revision = "xyz789"   # ← points to the previous migration (forms a chain)
branch_labels = None
depends_on = None

def upgrade():
    op.add_column("users", sa.Column("role", sa.String(50), nullable=True))

def downgrade():
    op.drop_column("users", "role")
    # ⚠️ downgrade drops the column — all data in that column is permanently lost

Always write a proper downgrade(). It's what makes rollbacks possible.

Gotchas

Gotcha	Fix
New model not detected	Import it in `alembic/env.py`
`--autogenerate` misses some changes	Always review generated files before applying
Rolling back drops columns	Data in those columns is gone — back up first
Running `create_all` alongside Alembic	Don't mix them — pick one approach per environment

Concept

Prisma

Drizzle

Alembic

Define schema

schema.prisma

schema.ts

SQLAlchemy models (models/)

Generate migration

prisma migrate dev

drizzle-kit generate

alembic revision --autogenerate

Apply migration

prisma migrate deploy

drizzle-kit push

alembic upgrade head

Migration files

migrations/

alembic/versions/

Roll back

❌ not built-in

✅ alembic downgrade -1

from app.database import Base from app.models import user, post, tag # ← import ALL models here # Alembic can only see what's imported target_metadata = Base.metadata

# Added two new columns to User class User(Base): __tablename__ = "users" id: Mapped[int] = mapped_column(primary_key=True) email: Mapped[str] = mapped_column(String(255)) bio: Mapped[str | None] = mapped_column(Text, nullable=True) # ← new avatar_url: Mapped[str | None] = mapped_column(String(500), nullable=True) # ← new

# alembic/versions/abc123_add_bio_and_avatar_to_users.py def upgrade(): op.add_column("users", sa.Column("bio", sa.Text(), nullable=True)) op.add_column("users", sa.Column("avatar_url", sa.String(500), nullable=True)) def downgrade(): op.drop_column("users", "avatar_url") op.drop_column("users", "bio")

# Apply all pending migrations alembic upgrade head # Roll back the last migration alembic downgrade -1 # Roll back to a specific version alembic downgrade abc123 # See full migration history alembic history # See where your DB currently is alembic current # Generate a blank migration (for manual SQL) alembic revision -m "some manual change"

create_all

Alembic

Use case

Local dev only

Staging + Production

Tracks changes

❌ No

✅ Yes, versioned

Safe on existing data

❌ Won't alter existing tables

✅ Generates precise ALTER statements

Rollback

❌ No

✅ Yes, via downgrade()

# alembic/versions/abc123_add_role_to_users.py revision = "abc123" down_revision = "xyz789" # ← points to the previous migration (forms a chain) branch_labels = None depends_on = None def upgrade(): op.add_column("users", sa.Column("role", sa.String(50), nullable=True)) def downgrade(): op.drop_column("users", "role") # ⚠️ downgrade drops the column — all data in that column is permanently lost

Gotcha

Fix

New model not detected

Import it in alembic/env.py

--autogenerate misses some changes

Always review generated files before applying

Rolling back drops columns

Data in those columns is gone — back up first

Running create_all alongside Alembic

Don't mix them — pick one approach per environment