Alembic Setup — Initialising and Configuring env.py

Alembic is the database migration tool built specifically for SQLAlchemy. It tracks your schema’s history as a chain of versioned migration scripts, each with an upgrade() and downgrade() function. When the schema changes — a new column, a dropped index, a renamed table — you generate a migration script, commit it to version control, and run it against every environment. Alembic stores the current migration version in an alembic_version table and applies only the unapplied migrations, making the process idempotent and safe to run multiple times. Correct setup — especially pointing Alembic at your models — is the foundation for everything else in this chapter.

Installation and Initialisation #

pip install alembic

# Initialise the migration environment (run once per project)
alembic init alembic

# Resulting directory structure:
# alembic/
#   env.py             ← edit this to connect to your DB and import models
#   script.py.mako     ← template for generated migration files
#   versions/          ← generated migration scripts live here
# alembic.ini          ← main config file (database URL configured here or in env.py)

Configuring env.py #

# alembic/env.py — the critical configuration file
import os
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

# ── Import EVERYTHING that inherits from Base ─────────────────────────────────
# Alembic's autogenerate compares Base.metadata to the current DB schema.
# If a model is not imported here, Alembic cannot see it → migration misses it.
from app.database import Base       # the DeclarativeBase
from app.models.user import User    # import every model
from app.models.post import Post
from app.models.tag  import Tag
from app.models.comment import Comment
# Or: from app.models import *  (if models/__init__.py imports all models)

# ── Read database URL from environment (not from alembic.ini) ────────────────
from app.config import get_settings
settings = get_settings()

config = context.config
config.set_main_option("sqlalchemy.url", str(settings.database_url))

# ── Standard Alembic boilerplate ──────────────────────────────────────────────
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata   # tells autogenerate what to compare against

def run_migrations_offline() -> None:
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url              = url,
        target_metadata  = target_metadata,
        literal_binds    = True,
        dialect_opts     = {"paramstyle": "named"},
        compare_type     = True,   # detect column type changes
        compare_server_default = True,  # detect server_default changes
    )
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix      = "sqlalchemy.",
        poolclass   = pool.NullPool,   # no pool for migration runs
    )
    with connectable.connect() as connection:
        context.configure(
            connection       = connection,
            target_metadata  = target_metadata,
            compare_type     = True,
            compare_server_default = True,
        )
        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Verifying the Setup #

# Check current migration state
alembic current
# If database has no alembic_version table: "(head)" after running first migration
# If no migrations applied yet: outputs nothing or "No current revisions"

# Show all migrations and their status
alembic history --verbose

# Verify autogenerate can see your models (dry run — no file written)
alembic revision --autogenerate -m "test" --sql   # shows SQL without writing a file

# Check for any differences between models and DB (useful in CI)
# Returns non-zero exit code if migrations are needed
alembic check   # Alembic 1.9+

Common Mistakes #

Mistake 1 — Not importing all models in env.py #

❌ Wrong — model not imported, Alembic does not see it:

from app.database import Base
from app.models.user import User   # only User imported
# Post, Tag, Comment not imported → their tables missing from autogenerate!

✅ Correct — import every model:

from app.database import Base
from app.models.user    import User
from app.models.post    import Post
from app.models.tag     import Tag
from app.models.comment import Comment   # ✓ all models imported

Mistake 2 — Hardcoding database URL in alembic.ini #

❌ Wrong — credentials in version control:

# alembic.ini
sqlalchemy.url = postgresql://user:secret@prod-db/mydb   # EXPOSED!

✅ Correct — override in env.py from environment variable.

Mistake 3 — Forgetting compare_type=True (type changes not detected) #

❌ Wrong — column type changes silently ignored:

context.configure(connection=connection, target_metadata=target_metadata)
# compare_type defaults to False — VARCHAR→TEXT changes not detected!

✅ Correct:

context.configure(connection=connection, target_metadata=target_metadata,
                  compare_type=True)   # ✓ detects column type changes

Quick Reference #