Alembic

Alembic manages database schema migrations for SQLAlchemy-based projects. Each change is a versioned script with an upgrade and a downgrade path, chained so a database can be brought from any revision to any other.

What it really provides is that schema change becomes a reviewable artefact. Without it, the production schema is the accumulated result of every manual ALTER TABLE anyone ran, with no record of when or why. Reproducing that database means reconstructing an undocumented history, and discovering that staging and production have quietly diverged is a familiar and unpleasant experience.

Using it from the beginning is much easier than adopting it later. Retrofitting migrations onto a hand-modified database means reverse-engineering the current schema into an initial migration and hoping nothing was missed.

Autogenerate is a draft

Alembic can diff models against the database and generate a migration. This is genuinely useful and should be treated as a first draft rather than output.

It reliably misses things: column type changes it cannot detect, server defaults, constraint and index modifications depending on configuration, and anything in a database object it does not manage. More importantly, it cannot know about data. A migration adding a non-nullable column to a populated table will generate cleanly and fail on execution, because autogenerate reasons about schema and the failure is about rows.

Every generated migration needs reading before it is committed. This is the discipline the tool requires and the one most often skipped.

What the tool does not solve

Data migration is separate from schema migration and much harder. Splitting a column into two means writing the transformation, deciding what to do with rows that do not fit the new shape, and having a plan for failure partway through. Alembic will run the code; it will not tell you the code is right.

Downgrades are usually a fiction. Alembic generates them and they are frequently untested and often lossy — dropping a column loses the data in it, and no downgrade recovers that. Treating downgrade as a rollback plan for production is a mistake; a restore from backup is the actual plan.

Sequencing under zero downtime is its own problem. During a deployment, old and new application code run simultaneously, so the schema must be compatible with both. That usually means splitting one logical change into several migrations released across deployments — add the column, backfill, start writing to it, stop reading the old one, drop it. The tool supports this and does not suggest it.

See also: SQLModel, PostgreSQL, FastAPI, and Docker Compose for running migrations at deploy time.