In Alembic, what must env.py and alembic.ini provide before `alembic revision --autogenerate` can draft a migration from SQLAlchemy models?
answer
- two sides of one comparison
- the generic template's None placeholder
- Base.metadata, every model imported
- a live database at the head revision
basics
~20 senv.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# 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.metadatago deeper
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.
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.
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.
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.