skip to content

Autogenerated Revisions

alembic revision --autogenerate compares target_metadata with the live schema and drafts a script to review. Interviewers ask why a rename comes out as a drop and an add.

on this pageshow

explore

questions

6

In Alembic, what must env.py and alembic.ini provide before `alembic revision --autogenerate` can draft a migration from SQLAlchemy models?

level: juniorimportance: must knowfreq 70%

answer

  1. two sides of one comparison
  2. the generic template's None placeholder
  3. Base.metadata, every model imported
  4. a live database at the head revision

basics

~20 s

env.py must set target_metadata to the models' MetaData, usually Base.metadata with every model module imported, and Alembic needs a database URL. Autogenerate diffs that metadata against the live schema and writes a candidate script to review.

solid answer

~40 s

`alembic init migrations` writes `alembic.ini` and `migrations/env.py` from the `generic` template, where `target_metadata = None`. For autogenerate you import the declarative `Base`, through a module that imports every model, and set `target_metadata = Base.metadata`; left at `None`, the command stops with "Can't proceed with --autogenerate option". The URL comes from `sqlalchemy.url` in `alembic.ini`, or env.py sets it from an environment variable so no password sits in the repository. `alembic revision --autogenerate -m "initial"` then connects, reflects the live schema, compares it with the metadata and writes a file under `versions/` with `upgrade()` and `downgrade()`. It refuses with "Target database is not up to date." when the database is behind the newest revision, and what it writes is a draft you read and edit before anyone applies it.

code

python · 11 lines
python
# migrations/env.py (excerpt; the rest is the generic template)
import os

from alembic import context

from myapp.models import Base  # the package must import every model module

config = context.config
config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])

target_metadata = Base.metadata

go deeper

for a junior

Recall the two edits: target_metadata = Base.metadata with every model imported, and a database URL. Know that the output is a draft in versions/ that you read before applying.

for a middle

Explain that autogenerate reflects the live database and diffs it against MetaData, why an unimported model looks dropped, and which error appears when the database is behind the newest revision.

for a senior

Show you keep credentials out of alembic.ini, read the URL from the environment, and know that offline --sql mode cannot autogenerate because nothing is reflected.

for a principal

Frame env.py as shared infrastructure: one models package that imports everything, one way to supply the URL, so every developer's draft is computed against the same metadata.

## What autogenerate compares **Autogenerate** is the `--autogenerate` option of Alembic's `alembic revision` command. It compares two descriptions of a schema: - the **proposed** schema: a SQLAlchemy `MetaData` object holding a `Table` for every mapped class, handed to Alembic as `target_metadata`; - the **current** schema: what Alembic reflects from the live database through a SQLAlchemy connection, using the `Inspector` (tables, columns, indexes, unique and foreign-key constraints). Every difference it recognises becomes an operation such as `op.create_table()` or `op.add_column()` in a new revision file. The comparison is with the database, not with earlier revision files, which is why the database has to be reachable and up to date. ## Setting up the environment 1. Run `alembic init migrations`. With no `-t` option it uses the `generic` template and writes `alembic.ini`, `migrations/env.py`, `migrations/script.py.mako` and an empty `migrations/versions/` directory. Other templates exist (`async`, `multidb`, `pyproject`, `pyproject_async`), each with its own env.py. 2. Open `env.py`. The generic template carries `target_metadata = None` under a comment that says "add your model's MetaData object here for 'autogenerate' support". 3. Replace it: import your `DeclarativeBase` subclass and set `target_metadata = Base.metadata`. A declarative class adds its `Table` to `Base.metadata` only when its `class` statement runs, so the import must reach every module that defines a model. The usual shape is a models package whose `__init__.py` imports each module. 4. `target_metadata` may also be a list of `MetaData` objects when an application has several; Alembic consults them in order and raises an error if two hold a table with the same name and schema. ## Pointing Alembic at the database The generic `env.py` builds its engine with `engine_from_config()` from the `sqlalchemy.` keys in `alembic.ini`, so the simplest setup writes `sqlalchemy.url = postgresql://...` there. That file is committed, so a real password does not belong in it. The common pattern reads the URL from the environment in `env.py` with `config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])`. That value passes through Python's `ConfigParser`, so a literal `%` in a password must be written as `%%`. The template also builds the engine with `poolclass=pool.NullPool`: a migration command is a short-lived process, so each connection is closed when it is released. ## What gets compared The comparison has a scope, and knowing it explains most surprises: - only the connection's **default schema** is scanned unless `include_schemas=True` is passed to `context.configure()`; - within it, **every table** is reflected, including tables no model describes, which is why a stray table shows up as a drop; - per table, Alembic reads columns, indexes, unique constraints and foreign keys; CHECK constraints and server defaults are compared only when the matching options are switched on; - the `alembic_version` table that Alembic itself maintains is ignored. Filtering hooks such as `include_name` narrow this scope further when a database holds tables the application does not own. ## Running the command `alembic revision --autogenerate -m "initial"` loads `env.py`, connects, reflects the schema, compares, and writes `versions/<rev>_initial.py`. The console names each difference it found, for example `Detected added table 'customer'`. The file holds `revision`, `down_revision`, an `upgrade()` with the detected operations between `### commands auto generated by Alembic - please adjust! ###` markers, and a `downgrade()` that reverses them. Nothing has touched the database yet; applying the revision is a separate step. ## The first errors people meet | Message | Cause | Fix | |---|---|---| | `Can't proceed with --autogenerate option; environment script ... does not provide a MetaData object or sequence of objects to the context.` | `target_metadata` is still `None` | set it to `Base.metadata` | | `Target database is not up to date.` | the database is behind the newest revision file | apply the existing revisions first, then autogenerate | | `Using --sql with --autogenerate does not make any sense` | `--sql` (offline mode) has no connection to reflect | drop `--sql` | | a draft full of `op.drop_table()` | model modules were never imported, so `Base.metadata` lacks their tables | import every model module before reading `Base.metadata` | ## What you get, and what it is not The output is a **candidate migration**. Alembic's own documentation says it is always necessary to review and correct it by hand: autogenerate cannot see a rename, misses CHECK constraints and server defaults under the default settings, and knows nothing about the data in the tables. The draft is a fast first version of a script you still own, read and edit before it is committed.

  • Can Alembic's autogenerate run in offline mode with --sql?
    No. Autogenerate reflects the live database to find differences, and offline mode has no connection to reflect. Alembic stops with "Using --sql with --autogenerate does not make any sense". Offline mode is for printing the SQL of revisions that already exist.
  • Your application has two declarative bases with separate MetaData objects. How does env.py hand both to autogenerate?
    Set `target_metadata` to a list, `[Base1.metadata, Base2.metadata]`. Alembic consults them in order. Each table key, its name plus schema, must be unique across the list; two `MetaData` objects holding the same table raise an error.
  • Why does Alembic's generic env.py create its engine with poolclass=pool.NullPool?
    A migration command is a short-lived process that opens a connection, does its work and exits. `NullPool` closes each connection when it is released instead of keeping it in a pool, which suits a one-shot command and leaves no idle connections behind.

  • Alembic discovers DeclarativeBase subclasses itself, so target_metadata can stay None.
  • Autogenerate compares the models with the previous revision files, not the database.
  • Importing Base alone is enough; model modules load themselves when Alembic runs.
  • The generated script is finished code that can be applied without reading it.
  • The production password can sit in alembic.ini because it is only a config file.
open as a page

In Alembic, after models rename a table, add a column, change a column's type and add a named CHECK constraint, what does autogenerate draft?

level: middleimportance: must knowfreq 75%

basics

~20 s

Alembic drafts the new column and the type change correctly, turns the table rename into op.create_table() plus op.drop_table(), which would lose the rows, and leaves out the CHECK constraint, because CHECK detection is off by default.

open as a page

For Alembic autogenerate, why should a SQLAlchemy MetaData carry a naming_convention before the first revision is drafted?

level: middleimportance: should knowfreq 50%

basics

~20 s

Without a convention, unique, foreign-key and CHECK constraints get whatever name the database invents, or none, so autogenerate drafts them as None and later drops cannot target them. A naming_convention makes names predictable; adopted late, it churns existing ones.

open as a page

On SQLite, why does an Alembic autogenerated op.alter_column() fail at upgrade, and what does render_as_batch=True change in the draft?

level: middleimportance: should knowfreq 35%

basics

~10 s

SQLite's ALTER TABLE cannot change a column's type, nullability or constraints, so a plain op.alter_column() fails. render_as_batch=True makes autogenerate wrap table changes in op.batch_alter_table(), which on SQLite rebuilds the table and copies the rows.

open as a page

Alembic autogenerate drafts op.drop_table() for a table your SQLAlchemy models still define, and for tables another system owns: what causes each, and how do you fix it?

level: seniorimportance: should knowfreq 40%

basics

~20 s

The model's module was never imported, so its table is missing from Base.metadata; other tables are reflected but appear in no model. Import every model module in env.py, and exclude foreign tables with an include_name hook.

open as a page

As a lead, what rules do you set for Alembic autogenerate on a SQLAlchemy team: who reviews drafts, and what goes into the shared env.py?

level: principalimportance: should knowfreq 30%

basics

~20 s

Treat every autogenerated revision as a draft the author edits and a schema-aware reviewer approves, with a checklist for autogenerate's blind spots. Standardise env.py once: naming convention, compare options, table filters, batch rendering and revision hooks.

open as a page