Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 37 ยท Alembic and Migrations

Alembic Autogenerate: Let Alembic Write the Migration

Connect env.py to your models, generate migrations with --autogenerate, review what it produced and learn what autogenerate can and cannot detect. Runnable demo.

Writing op.add_column(...) by hand is fine for one change, tedious for twenty. Alembic can compare your models (the MetaData in Python) with the actual database and write the difference as a migration. That is --autogenerate, and it is the way most projects work: change the model, ask Alembic for the diff, review it, apply it.

Autogenerate is a draft writer, not an oracle. It misses some changes and misreads others, so the rule is always read the generated file before you run it.

Step 1: point env.py at your metadata

alembic init creates env.py with target_metadata = None. Autogenerate needs the real thing. Import your declarative base and assign its metadata:

Python
# migrations/env.py (excerpt)
from myapp.models import Base
target_metadata = Base.metadata

Importing Base alone is not enough: every model module must have been imported so each class is registered on the metadata. A common approach is a myapp/models/__init__.py that imports all of them.

Step 2: generate and review

The demo below builds a tiny project in your browser: a models file, Alembic set up for it, an autogenerated first revision, then a model change and a second revision. It prints the generated code so you can read what Alembic wrote.

Python Autogenerate two revisions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the second revision carefully. It contains op.create_table('post', ...), an op.create_index for the indexed title and an op.add_column for phone, with a matching downgrade() that reverses each step in the opposite order. You did not write any of it, and you can still edit it before applying.

Name your constraints. Alembic can only drop a constraint it can refer to by name. Give your MetaData a naming convention (MetaData(naming_convention={"ix": "ix_%(column_0_label)s", "uq": "uq_%(table_name)s_%(column_0_name)s", "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", "pk": "pk_%(table_name)s"})) on your declarative base before the first migration. It makes every later change deterministic.

What autogenerate detects

Detected Not detected or unreliable
Table added or removed Table or column renames (seen as drop + add: data loss!)
Column added or removed Changes to CHECK constraints and many server-side defaults
Nullable changes Enum value changes on some databases
Column type changes (with compare_type=True) Changes inside views, triggers, stored procedures
Index and unique constraint changes Anything not in your metadata
Foreign key changes Sequence changes and many engine-specific options

Two settings are worth turning on in env.py (inside context.configure(...)):

  • compare_type=True: report type changes (on by default in current versions, but be explicit).
  • compare_server_default=True: report default changes.

A rename looks like "drop old column, add new column" to autogenerate. Applied as written it destroys the data. When you rename, edit the generated migration by hand and use op.alter_column("user_account", "phone", new_column_name="mobile") instead.

Seeing the diff without writing a file: compare_metadata

For a quick check (and for tests that assert "the models and the migrated database agree") you can ask Alembic for the diff directly with compare_metadata. An empty list means no drift.

Python Detecting drift with compare_metadata

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A CI job that runs alembic upgrade head on an empty database and then fails if compare_metadata returns anything is a cheap, effective guard against forgetting to commit a migration.

A review checklist for generated migrations

  1. Does upgrade() only contain changes you intended? Unexpected drop_table often means a model module was not imported.
  2. Are renames handled by hand?
  3. Adding a NOT NULL column to a table with rows: it needs a server_default or a data step, or it fails.
  4. Does downgrade() actually reverse everything?
  5. Are the constraint and index names explicit?
  6. Run it against a copy of production data before releasing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What must env.py define for autogenerate to work?

    Autogenerate compares target_metadata with the live database, so it must point at the metadata with your models registered.
  2. 2. A column is renamed in the model. What will autogenerate write?

    Alembic cannot know a column was renamed; it sees one missing and one new column.
  3. 3. Which statement about autogenerate is correct?

    Autogenerate writes a candidate migration file; applying it is a separate, reviewed step.
  4. 4. How can a test detect a forgotten migration?

    If the migrated schema and the metadata still differ, compare_metadata lists the difference.

Summary

  • Set target_metadata = Base.metadata in env.py and make sure all models are imported.
  • alembic revision --autogenerate -m "..." writes a draft migration from the model-versus-database diff.
  • Always review: renames, defaults, constraints and data steps need human care.
  • Use a naming convention so constraints can be dropped reliably.
  • compare_metadata turns "are we in sync?" into an automated test.

Next: Production Migration Workflow: Data, Batch Mode and Branches