Changing SQLAlchemy models does not safely update an existing database. Alembic stores schema changes as ordered revisions so development, test, and production can apply the same sequence.

This guide assumes familiarity with SQLAlchemy. Migrations do not replace backups or deployment testing.

Initialize and connect metadata

python -m pip install alembic
alembic init migrations

Commit alembic.ini, migrations/env.py, and migrations/versions. Keep credentials out of the repository and import model metadata in env.py:

from app.database import Base
target_metadata = Base.metadata

The official Alembic tutorial explains the complete migration environment.

Generate, inspect, and apply

alembic revision --autogenerate -m "add order status"
alembic upgrade head
alembic current

Open every generated revision. Check names, types, defaults, nullability, indexes, and both upgrade() and downgrade(). A column rename may appear as a drop plus an add, which can destroy data unless corrected manually.

Adding a required column to a populated table often needs stages: add it as nullable, backfill values, then enforce the constraint. Dropping columns or shrinking types requires a backup and knowledge of locks on the target database.

Autogenerate is an assistant, not an audit. Its official documentation lists what it detects and known limitations.

Team workflow

  • Create one revision per logical schema change.
  • Review generated SQL on a disposable database.
  • Never rewrite revisions already deployed; create a new one.
  • Run migrations once per deployment, not in every application worker.
  • Test upgrade head in CI from an empty database.

Reliable migrations make schema history visible and repeatable. The important habit is not generating revisions quickly; it is reviewing their data impact before production.

Understand the revision graph

Every file under migrations/versions has a revision identifier and usually points to a previous revision. Those links form a graph rather than a list sorted by filename. alembic heads shows the current tips, alembic current reports the revision recorded by a database, and alembic history displays paths.

Alembic stores the database position in alembic_version. Do not edit that table manually to make an error disappear. A mismatch can mean a partially applied migration, an out-of-band schema change, or an incorrect stamp. Determine which operations actually happened before changing the recorded state.

Parallel branches may produce two revisions with the same down_revision. After merging code, the repository then has multiple heads. Do not delete one migration or arbitrarily change its parent. Create and review a merge revision:

alembic heads
alembic merge heads -m "merge migration branches"

Test the resulting graph from the common ancestor. Teams with frequent schema work benefit from integrating early, before independent revisions accumulate assumptions about incompatible states.

Configure connections without committing secrets

The generated example places sqlalchemy.url in alembic.ini, while real applications often load it from secure configuration. env.py can use the same settings source as the application and pass the URL to Alembic. Avoid logging the complete value because it may include credentials.

Import every model before assigning target_metadata. If a model module is never loaded, its table may be absent from metadata and autogenerate can omit it or propose an unexpected drop. A central model-registration module makes discovery explicit, but the generated migration still requires review.

Projects with several metadata collections or databases need a documented strategy. The official autogenerate API documentation covers comparison hooks and multiple collections, but deployment ordering remains an application decision.

Write a manual revision

Not every database operation begins as a model comparison. Create an empty revision when the change is data-oriented or autogenerate cannot express it:

alembic revision -m "normalize order status"

A small structural revision might contain:

from alembic import op
import sqlalchemy as sa

def upgrade() -> None:
    op.add_column(
        "order",
        sa.Column("status", sa.String(length=20), nullable=True),
    )

def downgrade() -> None:
    op.drop_column("order", "status")

This intentionally adds a nullable column. For a populated table, a compatible deployment can add it as nullable, release code that writes both old and new representations, backfill existing rows, and enforce nullable=False later. This expand, migrate, contract approach prevents one incompatible schema change from becoming a single fragile deployment moment.

Backfills require operational planning. One large UPDATE may hold locks and create a huge transaction. On large tables, use observable batches through an appropriate controlled process and verify that no rows remain before adding a constraint. Schema revision and data migration may need different execution mechanisms.

Distinguish Python defaults from server defaults

SQLAlchemy's default= commonly describes a value supplied by application-side code. server_default= creates behavior in the database. A migration executed directly by the database will not automatically call a Python default from the model.

A temporary server default can help when adding a required column, but decide whether it belongs in the final contract. Removing it requires every writer to provide a value; keeping it defines permanent database behavior. Inspect rendered SQL for quoting, type casts, and dialect-specific functions.

Enums, timestamps, and constraint changes also differ among PostgreSQL, MySQL, and SQLite. A migration tested only on in-memory SQLite is not meaningful evidence for a PostgreSQL production database.

Inspect SQL and offline migrations

Generate SQL without applying it when a deployment process requires review:

alembic upgrade head --sql

Offline mode is useful when a database team executes approved scripts. Operations that query current data or rely on an active connection may not render correctly, so write revisions with the intended execution mode in mind.

Generated SQL is dialect-specific. Produce it for the target database, review expected locks, and test against representative volume. The deployment plan should also define how long old and new application versions can coexist with the intermediate schema.

Treat downgrade and recovery separately

downgrade() is a schema transformation, not time travel. Recreating a dropped column produces an empty column, not its former data. If new application code has already written a different representation, the previous binary may not understand it.

Before a destructive deployment, define a verified backup, stop condition, data restoration procedure, and compatibility of the previous release. In some incidents, a forward repair is safer than a destructive downgrade. That choice should be anticipated rather than made automatically because a downgrade function exists.

alembic stamp also deserves caution. It marks a revision as present without running its operations. Use it only when the schema was independently created or verified and the history must be aligned. Compare the actual schema first and document why stamping is correct.

Test migrations as production code

Two test paths provide different evidence. First, create an empty database and run alembic upgrade head to prove the full history remains executable. Second, start from the previous production revision with representative rows, apply the new migration, and validate both structure and data.

If downgrade is supported, test upgrade, downgrade, and upgrade again on a disposable database. This reveals incomplete reverse operations and unstable constraint names. Do not promise reversibility for data that cannot be reconstructed.

Application tests should start only after migrations complete. In a deployment with several replicas, select one migration job and wait for it before releasing code that requires the schema. Running Alembic in every worker's startup creates races and makes failures harder to observe.

Review and deployment checklist

Confirm that down_revision is correct, the file contains one logical change, and autogenerate did not include unrelated drops. Review nullability, defaults, indexes, foreign keys, constraint names, and dialect behavior. Measure backfills and table-locking operations.

Then test upgrade head from an empty database and from the production predecessor, check the result with alembic current, run application tests, and record backup and recovery steps. Ship the revision with the code that uses it, ordered so each deployment stage remains compatible.

Alembic makes history executable, but the surrounding discipline provides safety: compatible phases, reviewed operations, representative data, and one observable migration runner. Treating revisions as production code turns a convenient command into a dependable schema-evolution practice.