Alembic Migration Workflow: Full Setup Guide, Commands, and Fixes for Common Errors

Every Alembic disaster I have debugged came from the same root cause: the migration tool and the application disagreeing about which database they were talking to, or which state it was in. Not exotic bugs. Configuration drift. So my Alembic migration workflow is built backwards from that failure mode — one source of truth for the database URL, models imported before Alembic ever looks at metadata, and an append-only history that nobody edits after the fact.

I first built this workflow on AutoblogX, a FastAPI backend I wrote for automated content publishing, and I have since carried the same env.py pattern into every Python API I run, including a vendor-security scoring service and an accounting assistant backend. The setup below is copied from those projects, not from the docs.

What Alembic Actually Does for You

Alembic is the migration tool built by the SQLAlchemy author. Think of it as version control for your schema: every change to your tables becomes a small Python script in a versions/ folder, applied in order, tracked in a tiny alembic_version table inside the database itself.

Without it, schema changes are hand-run SQL — unrepeatable across local, staging, and production, and unreviewable in a pull request. With it, a schema change is a file your teammates can read, your CI can apply, and you can roll back.

If you come from Laravel, this is php artisan migrate for Python — the same discipline I lean on in my Laravel deployment pipeline, applied to SQLAlchemy.

The Setup I Use on Real Projects

Install and initialize:

pip install alembic
alembic init alembic

That creates alembic.ini, an alembic/ directory with env.py, and the empty versions/ folder.

Then fix env.py before writing a single migration. The generated file works for demos and fails quietly on real apps. Two changes matter, and the order of the first one is the part most guides skip:

from alembic import context
from logging.config import fileConfig

config = context.config
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# 1. Import ALL models BEFORE touching target_metadata.
#    This registers every table with Base.metadata — autogenerate
#    can only diff what has been imported.
import app.models  # noqa: F401

from app.database import Base
target_metadata = Base.metadata

# 2. Take the database URL from the application's own settings,
#    never from a hardcoded value in alembic.ini.
from app.config import settings
config.set_main_option("sqlalchemy.url", settings.DATABASE_URL_SYNC)

Both of my current FastAPI backends carry exactly this block. The first change kills the "autogenerate produced an empty migration" mystery. The second guarantees migrations always run against the same database the app reads — the single most valuable line in the whole workflow. On AutoblogX I loaded the URL from .env with python-dotenv; the newer projects read a typed settings object. Same principle either way: one source of truth.

One subtlety in that second line: if your app connects with an async driver like asyncpg, keep a separate synchronous URL (postgresql://... via psycopg2) just for Alembic. That is why the setting is called DATABASE_URL_SYNC and not just DATABASE_URL — Alembic's default engine setup is synchronous, and pointing it at an async DSN fails with a confusing driver error.

Writing and Applying Migrations

Create a migration and apply it:

alembic revision -m "create_users_table"
alembic upgrade head

Inside the generated script, define the change with SQLAlchemy operations:

def upgrade():
    op.create_table(
        "users",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("username", sa.String(50), nullable=False),
        sa.Column("email", sa.String(255), nullable=False),
    )

def downgrade():
    op.drop_table("users")

Write the downgrade() every time, even when you doubt you will use it. The one time you need it is the one time you cannot write it calmly.

On AutoblogX, when I added the social publishing feature, I put social_accounts, social_pages, and social_posts in a single revision because they ship or roll back as one unit. That is my rule for grouping: one migration per feature, not one per table. Rollbacks follow feature boundaries, so migrations should too.

The commands I actually use day to day:

The Errors You Will Hit, and the Real Fixes

"Can't locate revision identified by ..." — the database's alembic_version points at a revision file that no longer exists, usually after a branch switch or a deleted file. Check versions/ for the missing ID. If it is genuinely gone, inspect the schema, decide which surviving revision it actually matches, and alembic stamp that revision. Stamp is the escape hatch: it rewrites Alembic's belief about the database without touching the schema.

Autogenerate produces an empty migration — your models were never imported, so Base.metadata is empty. Move the import app.models line above target_metadata in env.py. This is the exact bug the setup section prevents.

Wrong database URL — the migration ran, but against the wrong database. If you load from .env, confirm the loader actually runs inside env.py (it does not inherit your shell). Better, adopt the settings-object pattern above so this class of error becomes impossible.

Multiple heads after a merge — two branches each added a migration on top of the same parent. alembic heads shows two IDs; alembic merge heads -m "merge branches" creates an empty migration joining them. Run it the moment both branches land. The longer a fork lives, the worse the untangling.

Autogenerate Is a Draft, Not a Decision

--autogenerate diffs your models against the live schema, and it misses things by design: a renamed column looks like drop-plus-add (data gone), server-side defaults pass silently, and type nuances vary by backend. I treat the generated file as a draft that must be read line by line before upgrade — and on team projects, migration review carries the same weight in code review as application logic. A wrong migration is the rare bug that corrupts its way through every environment in sequence, the same way an unnoticed N+1 quietly taxes every request — except this one rewrites data.

Three more rules that have saved me real hours:

• Never edit an applied migration. History is append-only. Fix mistakes with a new revision.
• Print the database URL, in red, in any wrapper script that runs migrations. Ugly, unmissable, and it has stopped more production accidents than any elegant safeguard I have used.
• Practice the recoveries once on a throwaway database — a deliberate broken head, a deliberate stamp. Ten minutes of practice converts a future outage into a shrug.

Shipping Migrations to Production

My production sequence is boring on purpose: backup first, run alembic upgrade head from the same environment configuration the app uses, verify with alembic current, then restart workers so no process holds the old schema in memory. On queue-heavy backends that last step matters more than people expect — a worker mid-job against a changed table fails in ugly ways, which is the same reason I supervise queue workers explicitly on my Laravel servers. And if you are standing up the database layer itself, I covered that end of the stack in my EC2 PostgreSQL and Redis setup notes.

Test the migration on staging with a copy of production data, not an empty schema. Empty databases hide every interesting failure: lock times on large tables, constraint violations from legacy rows, defaults that backfill slowly.

Quick FAQ

Can I use Alembic without SQLAlchemy? No — it is built on SQLAlchemy Core. If you use a different ORM, use that ORM's migration tool.

upgrade vs stamp? upgrade runs migrations and changes the schema. stamp only updates Alembic's bookkeeping. Use stamp for repair, never as a shortcut.

One migration per table or per feature? Per feature. Rollbacks follow feature boundaries.

If you have a FastAPI or Python backend whose migration story is currently "someone runs SQL and hopes," that is exactly the kind of pipeline hardening I take on — my backend services are here, and the workflow above is what your project ends up with.