Autogenerating Spatial Migrations With Alembic

Problem Statement

Run alembic revision --autogenerate against a fresh PostGIS database and the first revision proposes dropping spatial_ref_sys, geography_columns and every GiST index in the schema. Run it against a mature one and it proposes the same index drop-and-recreate on every invocation, forever. Both are configuration problems with specific fixes, and this page walks through each in the order you hit them while setting up Alembic for a spatial schema.

Why the Naive Approach Fails

Autogenerate compares two things: the SQLAlchemy metadata your models define, and the schema it reflects from the database. Anything present in the database and absent from the metadata is a candidate for deletion. That rule is exactly right for an ordinary schema and wrong for a spatial one, because PostGIS puts objects in your database that no model will ever declare.

What an unfiltered first revision would delete Three groups of objects proposed for deletion by an unconfigured autogenerate run: the spatial reference table, the PostGIS metadata views, and the implicit GiST indexes. Each is annotated with the consequence of actually running the drop. Three groups, three different disasters op.drop_table('spatial_ref_sys') every ST_Transform and SRID lookup in the database stops working op.drop_table('geometry_columns') a PostGIS view — the DROP fails, leaving the migration half-applied op.drop_index('idx_listings_geom') succeeds silently — every spatial query falls back to a sequential scan

The third is the dangerous one, because it works. Nothing errors, no test fails, and the application gets slower in a way nobody attributes to a migration.

Production-Ready Implementation

The include_object hook does the filtering, and being specific about why each rule exists keeps it maintainable:

python
# alembic/env.py
POSTGIS_OWNED = {
    "spatial_ref_sys", "geometry_columns", "geography_columns",
    "raster_columns", "raster_overviews", "topology", "layer",
}


def include_object(obj, name, type_, reflected, compare_to):
    """Decide whether autogenerate may consider an object.

    Returning False means 'pretend this does not exist', which for a reflected
    object means 'do not propose dropping it'.
    """
    # 1. Objects the PostGIS extension owns.
    if type_ == "table" and name in POSTGIS_OWNED:
        return False

    # 2. Tables in the topology or tiger schemas, if those extensions are installed.
    if type_ == "table" and getattr(obj, "schema", None) in {"topology", "tiger"}:
        return False

    # 3. Reflected GiST indexes with no metadata counterpart: GeoAlchemy2 created
    #    them implicitly, so proposing a drop would silently de-index the table.
    if type_ == "index" and reflected and compare_to is None:
        if getattr(obj, "dialect_options", {}).get("postgresql", {}).get("using") == "gist":
            return False

    return True

Rule three uses the access method rather than a name pattern, which is more robust than matching idx_%_geom — it survives a renamed index and does not accidentally protect a B-tree that genuinely should be dropped.

Making the model declare what the database has

Filtering hides the asymmetry; declaring the index removes it. This is the better fix where you control the model:

python
from geoalchemy2 import Geometry
from sqlalchemy import Index
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Listing(Base):
    __tablename__ = "listings"

    id: Mapped[int] = mapped_column(primary_key=True)
    # spatial_index=False: we declare the index explicitly below, so the
    # metadata and the database agree and autogenerate has nothing to say.
    geom: Mapped[str] = mapped_column(
        Geometry("POINT", srid=4326, spatial_index=False), nullable=False)

    __table_args__ = (
        Index("idx_listings_geom", "geom", postgresql_using="gist"),
    )

With the index declared, autogenerate compares like with like: the index is in both the metadata and the database, so no diff is produced and no filter rule is needed for it. The filter stays for the PostGIS-owned tables, which will never be in the metadata.

Filter it, or declare it Filtering the reflected index stops the destructive diff but also blinds autogenerate to a genuinely missing index. Declaring the index in the model keeps the comparison symmetric, so a hand-dropped index shows up as drift the next time autogenerate runs. Both stop the bad diff — only one keeps detecting drift filter the reflected index no destructive diff an index dropped by hand is invisible needed anyway for spatial_ref_sys the minimum that works declare it in __table_args__ no destructive diff a missing index becomes visible drift the index name lives in version control the better answer where you own the model

Gating drift in CI

Once the diff is clean, keep it clean:

yaml
# .github/workflows/ci.yml (fragment)
- name: schema drift check
  run: |
    alembic upgrade head
    alembic check     # non-zero exit if autogenerate would produce a revision

alembic check is the payoff for all the configuration above. It turns “somebody added an index in psql and forgot the migration” from a mystery discovered months later into a failed pull request.

Configuration and Tuning Knobs

compare_type=True catches column type changes and is worth enabling once GeoAlchemy2’s comparator is in play. On older versions it produces a spurious alter for every geometry column on every run; if you see that, upgrade GeoAlchemy2 before reaching for a workaround.

compare_server_default=True is usually more noise than value on a spatial schema, because generated columns and PostGIS defaults render differently between the model and the reflection.

version_table_schema matters if your application uses a dedicated schema. Leaving Alembic’s version table in public alongside PostGIS objects works but muddles ownership; putting it in the application schema keeps a pg_dump --schema clean.

render_as_batch is a SQLite concern and should stay off for PostgreSQL — batch mode rewrites tables, which is catastrophic on a large spatial table.

Diff noise before and after configuration Operations in a generated revision against an unchanged database: 41 before any configuration, 12 after filtering PostGIS-owned tables, 1 after declaring spatial indexes in the model, and zero once the template imports geoalchemy2. Operations in a revision generated against an unchanged database no configuration 41 operations, most of them DROP + include_object filter 12 — the implicit spatial indexes + indexes declared 1 — a naming mismatch + names pinned 0 — alembic check passes Zero is the target. Any non-zero number means autogenerate will keep proposing the same change forever.

Verification Steps

bash
# 1. a fresh autogenerate against an up-to-date database must be empty
alembic revision --autogenerate -m "should be empty"
grep -c "op\." alembic/versions/*_should_be_empty.py    # expect 0 in up/downgrade
rm alembic/versions/*_should_be_empty.py

# 2. and the built-in equivalent, suitable for CI
alembic check
sql
-- 3. the spatial objects that must survive every migration
SELECT to_regclass('spatial_ref_sys') IS NOT NULL AS srs_present,
       (SELECT count(*) FROM pg_index x
        JOIN pg_class ic ON ic.oid = x.indexrelid
        JOIN pg_am am ON am.oid = ic.relam
        WHERE am.amname = 'gist') AS gist_indexes;

Reviewing a generated revision in practice

Reading a generated migration is a skill with a short checklist. Open the file and check four things before anything else.

Does it drop anything? Every op.drop_* in an autogenerated revision deserves an explanation, and on a spatial schema most of them are filter failures rather than intended changes.

Does it touch an index? If so, decide whether the operation should be concurrent, and rewrite it if the table is large.

Does it add a NOT NULL column or constraint? Autogenerate has no idea whether existing rows satisfy it, and the migration will fail on the first row that does not — or worse, succeed on staging and fail in production.

Does it change a type? A geometry type or SRID change is a table rewrite; treat the generated line as a note to write the real migration by hand.

Gotchas Checklist

  • include_object is called for every object, including columns. Return True by default, or the filter will silently exclude things you never considered.
  • The reflected flag distinguishes the two sides. A rule that ignores the flag can hide a model-declared object as well as a database one, which makes autogenerate stop noticing real changes.
  • Autogenerate never sees data. It cannot know that a new NOT NULL column needs a backfill, and it will happily generate a migration that fails on the first existing row.
  • Generated revisions are drafts. Read every one; the ones that look boring are the ones that drop indexes.
  • alembic check needs an up-to-date database. In CI, run upgrade head against a fresh PostGIS container first, or the check reports drift that is really just an unapplied migration.