InkdownInkdown
Start writing

Study

59 files·8 subfolders

Shared Workspace

Study
core

Alembic

Shared from "Study" 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
programming-language-concepts.md
zero-language-explanation.md
DB
01-introduction.md
02-relational-databases.md
03-database-design.md
04-indexing.md
05-transactions-acid.md
06-nosql-databases.md
07-query-optimization.md
08-replication-ha.md
09-sharding-partitioning.md
10-caching-strategies.md
11-cap-theorem.md
12-connection-pooling.md
13-backup-recovery.md
14-monitoring.md
15-database-selection.md
README.md
JS
Event loop
Merlin Backend
01-Orchestration.md
02-DeepResearch.md
03-Search.md
04-Scraping.md
05-Streaming.md
06-MultiProviderLLM.md
07-MemoryAndContext.md
08-ErrorHandling.md
09-RateLimiting.md
10-TaskQueue.md
11-SecurityAndAuth.md
Orchestration-2nd-draft
OpenAI Agents Python
00_OVERVIEW.md
01_AGENT_SYSTEM.md
02_RUNNER_SYSTEM.md
03_TOOL_SYSTEM.md
04_ITEMS_SYSTEM.md
05_GUARDRAILS.md
06_HANDOFFS.md
07_MEMORY_SESSIONS.md
08_MODEL_PROVIDERS.md
09_SANDBOX_SYSTEM.md
10_TRACING.md
11_RUN_STATE.md
12_CONTEXT.md
13_LIFECYCLE_HOOKS.md
14_CONFIGURATION.md
15_ERROR_HANDLING.md
16_STREAMING.md
17_EXTENSIONS.md
18_MCP_INTEGRATION.md
19_BEST_PRACTICES.md
20_ARCHITECTURE_PATTERNS.md
opencode-study
context-handling
core
Python
Alembic
Basics
sqlalchemy - fastapi
SQLAlchemy overview
tweets
system_design_for_agentic_apps.md
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