InkdownInkdown
Start writing

Python

4 files·0 subfolders

Shared Workspace

Python
Alembic

Alembic

Shared from "Python" on Inkdown

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

ConceptPrismaDrizzleAlembic
Define schema
Basics
sqlalchemy - fastapi
SQLAlchemy overview
schema.prisma
schema.ts
SQLAlchemy models (models/)
Generate migrationprisma migrate devdrizzle-kit generatealembic revision --autogenerate
Apply migrationprisma migrate deploydrizzle-kit pushalembic upgrade head
Migration filesmigrations/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_allAlembic
Use caseLocal dev onlyStaging + 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

GotchaFix
New model not detectedImport it in alembic/env.py
--autogenerate misses some changesAlways review generated files before applying
Rolling back drops columnsData in those columns is gone — back up first
Running create_all alongside AlembicDon't mix them — pick one approach per environment