Alembic for Spatial Schemas

Alembic and PostGIS work together well once three specific misunderstandings are cleared up, and badly until then. Autogenerate proposes dropping spatial_ref_sys. It proposes dropping every GiST index GeoAlchemy2 created. And the transaction it wraps around each revision makes the one statement you most want in a spatial migration — CREATE INDEX CONCURRENTLY — impossible. None of these is subtle once seen; all of them are baffling the first time. This page, part of the SQLAlchemy and GeoAlchemy integration workflows, fixes each in turn and then covers what a good spatial migration looks like once the tooling stops arguing.

Why autogenerate proposes destructive changes Two columns. The database contains the application tables, spatial_ref_sys, the PostGIS views and the GiST indexes GeoAlchemy2 created. The metadata contains only the application tables and their declared columns. Everything present in the first column and absent from the second is proposed for deletion unless a filter stops it. Autogenerate deletes whatever the model does not mention in the database listings, regions — your tables spatial_ref_sys — PostGIS needs it geography_columns — a PostGIS view idx_listings_geom — GiST, implicit in the metadata listings, regions — declared nothing else — so the three red rows above become DROP statements in the generated revision Running that revision unreviewed removes the spatial reference system table and every spatial index. Read the diff.

Prerequisites and Infrastructure Validation

The versions matter here more than usual, because GeoAlchemy2’s Alembic integration has changed materially:

bash
python -c "import alembic, geoalchemy2, sqlalchemy; \
print(alembic.__version__, geoalchemy2.__version__, sqlalchemy.__version__)"
# 1.13.2 0.15.2 2.0.31

GeoAlchemy2 0.12 and later ship an alembic_helpers module that handles the geometry column type in autogenerate output; earlier versions require rendering the type by hand in every migration. SQLAlchemy 2.0 changes the declarative style but not the migration mechanics.

Confirm the extension is present and note which objects it owns, since those are exactly what needs filtering:

sql
SELECT c.relname, c.relkind
FROM pg_class c
JOIN pg_depend d ON d.objid = c.oid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'postgis'
ORDER BY c.relkind, c.relname;

Core Execution Workflow

Step 1 — Filter what autogenerate is allowed to see

Everything begins with include_object in env.py. Without it, the first generated revision is destructive:

python
# alembic/env.py
from alembic import context
from sqlalchemy import engine_from_config, pool

from myapp.models import Base

target_metadata = Base.metadata

# Objects PostGIS owns, which the model will never declare.
POSTGIS_TABLES = {"spatial_ref_sys", "geometry_columns", "geography_columns",
                  "raster_columns", "raster_overviews", "topology", "layer"}


def include_object(obj, name, type_, reflected, compare_to):
    """Keep autogenerate away from anything the PostGIS extension owns."""
    if type_ == "table" and name in POSTGIS_TABLES:
        return False
    # GeoAlchemy2 creates the GiST index implicitly; it is not in the metadata,
    # so a reflected spatial index would otherwise be proposed for deletion.
    if type_ == "index" and reflected and compare_to is None:
        if name.startswith("idx_") and name.endswith("_geom"):
            return False
    return True


def run_migrations_online() -> None:
    connectable = engine_from_config(
        context.config.get_section(context.config.config_ini_section),
        prefix="sqlalchemy.", poolclass=pool.NullPool)

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_object=include_object,
            compare_type=True,
        )
        with context.begin_transaction():
            context.run_migrations()

The index rule above is deliberately conservative — it matches only the naming convention GeoAlchemy2 uses. A broader rule that ignores all reflected indexes would also hide genuine drift, which is worse than the problem it solves.

Step 2 — Make the generated migration importable

Autogenerate emits geoalchemy2.types.Geometry(...) into the revision, and the revision template does not import it, so the migration fails at import time with a NameError. Fix it once in the template rather than in every file:

python
# alembic/script.py.mako — add alongside the existing imports
import sqlalchemy as sa
import geoalchemy2          # noqa: F401  (needed by autogenerated column types)
${imports if imports else ""}

Step 3 — Read the diff before running it

What to trust in an autogenerated spatial revision Three bands. Ordinary column additions and renames are usually correct as generated. Index and constraint operations need the concurrency and validation treatment added by hand. Anything involving SRID, geometry type changes or partitioning should be rewritten entirely. Three tiers of trust in the generated diff usually correct as written add_column · drop_column · alter_column (nullable) · create_table needs the production treatment added create_index → CONCURRENTLY · create_check_constraint → NOT VALID then VALIDATE rewrite by hand SRID change · geometry type change · partitioning · anything that rewrites the table

The middle band is where most of the work sits. Autogenerate emits op.create_index(..., postgresql_using="gist"), which is correct SQL and wrong operationally on a large table: it locks. Replacing it with a concurrent build is a two-line change and the subject of its own guide.

Step 4 — Write a downgrade that would actually work

Alembic generates a downgrade automatically and it is right often enough that people stop reading it. For spatial migrations it is frequently wrong in one specific way: the order of operations. Dropping a geometry column before dropping its index works; dropping a constraint after dropping the column it references does not. The rule is that a downgrade is the reverse sequence, not merely the inverse operations:

python
def upgrade() -> None:
    op.add_column("listings",
                  sa.Column("geom", Geometry("POINT", srid=4326), nullable=True))
    op.create_index("idx_listings_geom", "listings", ["geom"],
                    postgresql_using="gist")
    op.create_check_constraint("listings_geom_srid", "listings",
                               "geom IS NULL OR ST_SRID(geom) = 4326")


def downgrade() -> None:
    # exact reverse order — constraint, then index, then column
    op.drop_constraint("listings_geom_srid", "listings", type_="check")
    op.drop_index("idx_listings_geom", table_name="listings")
    op.drop_column("listings", "geom")

Performance Considerations

Migrations are usually judged on correctness, but on a spatial schema they have a performance dimension that shows up as downtime. Three operations dominate:

Index creation is the big one. A GiST build on tens of millions of geometries is tens of minutes, and inside a transaction-wrapped Alembic revision it holds a lock for all of it. This is the single most common cause of a “quick migration” turning into an incident.

Constraint validation scans the table. ST_IsValid in a check constraint makes that scan expensive — it is an overlay-class operation per row. Adding the constraint NOT VALID and validating separately splits one long lock into a short one plus a long weak one.

Column type changes rewrite the heap. ALTER COLUMN geom TYPE geometry(Point, 3857) USING ST_Transform(...) looks like a schema tweak and is a full table rewrite with a full index rebuild, under an exclusive lock throughout. The add-a-column-and-backfill pattern exists precisely to avoid it.

None of these is a reason to avoid Alembic. They are reasons to treat a spatial revision as a deployment with an operational plan rather than as a file that runs on startup.

Structuring Spatial Migrations

Beyond the tooling, spatial revisions benefit from a few structural habits that have nothing to do with Alembic itself and everything to do with what geometry changes cost.

One concern per revision. A revision that adds a column, backfills it, indexes it and constrains it is four operations with four different lock profiles and four different durations. Split into four revisions, each can be deployed, verified and if necessary rolled back on its own — and the long one can be scheduled separately from the fast ones.

Name the revision after the change, not the ticket. add_geom_to_listings tells an operator at 2 a.m. what the migration does; JIRA_4172 does not. The ticket reference belongs in the docstring where it is useful and not in the filename where it is noise.

Put the operational facts in the docstring. Expected duration, lock strength, reversibility, and whether the revision is safe to run during traffic. Three lines that take a minute to write and save an argument during every deploy:

python
"""add coverage geometry to districts

Revision ID: 4c9d20e1f8a6
Lock: brief ACCESS EXCLUSIVE for the ADD COLUMN; index built CONCURRENTLY.
Duration: ~3 s plus a 40-minute background index build.
Reversible: yes, no data loss (the column is populated by a later revision).
"""

Keep data migrations out of schema revisions. A backfill that takes an hour does not belong in the deployment path. The schema revision creates the column; an operational job — resumable, batched, monitored, following the backfill patterns — populates it. The two are then independently retryable, which is the property that matters when either one fails.

Test against a database with rows. An empty schema hides every interesting failure: constraint violations, ordering bugs in downgrades, NOT NULL additions that cannot succeed. A CI fixture that loads a few thousand representative geometries — including an invalid one, an empty one and one in the wrong SRID — catches migration bugs that no amount of code review will.

Python Integration Patterns

Two helpers earn their place in almost every spatial Alembic project.

The first wraps the lock-timeout-and-retry pattern so individual revisions do not each reimplement it:

python
# migrations/helpers.py
import time
from alembic import op
import sqlalchemy as sa
from psycopg import errors


def ddl_with_retry(statement: str, *, attempts: int = 10,
                   lock_timeout: str = "3s") -> None:
    """Run a DDL statement, backing off while another transaction holds the table."""
    for attempt in range(1, attempts + 1):
        try:
            op.execute(sa.text(f"SET LOCAL lock_timeout = '{lock_timeout}'"))
            op.execute(sa.text(statement))
            return
        except errors.LockNotAvailable:
            wait = min(2 ** attempt, 30)
            print(f"lock busy; retrying in {wait}s ({attempt}/{attempts})")
            time.sleep(wait)
    raise RuntimeError(f"could not acquire the lock for: {statement[:60]}…")

The second asserts a precondition, which is how a revision refuses to run against a database that is not in the state it expects:

python
def require(condition_sql: str, message: str) -> None:
    """Fail the migration early and clearly if a precondition does not hold."""
    if not op.get_bind().execute(sa.text(condition_sql)).scalar():
        raise RuntimeError(f"precondition failed: {message}")


# in a revision
require("SELECT to_regclass('public.listings') IS NOT NULL",
        "the listings table must exist before adding its geometry column")
require("SELECT count(*) = 0 FROM listings WHERE NOT ST_IsValid(geom)",
        "repair invalid geometry before adding the validity constraint")

A precondition that fails produces a clear message and a database unchanged. Without it, the same situation produces a partially applied migration and a confusing error three statements later.

Multiple environments, one migration history

Spatial projects tend to accumulate databases: a developer copy, a CI container, staging with a sanitised extract, production. Alembic keeps one linear history across all of them, and the failure mode is a revision that works on the small ones and not on the large one.

Two habits prevent most of that divergence, and both are cheap to adopt. Seed the CI database with data shaped like production — including the awkward geometries, not just valid ones — so constraint and validation failures surface in the pipeline. And record the expected duration in the revision docstring based on the production row count rather than the developer copy, so nobody is surprised by an index build that took two seconds locally and forty minutes for real.

Common Failure Modes and Fixes

NameError: name 'geoalchemy2' is not defined — the template import from step 2 is missing. It recurs whenever the template is regenerated by alembic init.

Autogenerate proposes dropping spatial_ref_sys — the include_object filter is missing or env.py was replaced. Running that revision will break every spatial function that resolves an SRID.

Every autogenerate run produces a no-op index drop and recreate — the index name in the database differs from the one the model implies, usually because GeoAlchemy2’s naming changed between versions. Pin the name explicitly in the column definition and the churn stops.

CREATE INDEX CONCURRENTLY cannot run inside a transaction block — expected; see the dedicated guide for the two ways out.

A downgrade fails on a foreign key — the reverse-order rule was not followed. Test downgrades in CI against a schema with data, not an empty one; an empty schema hides ordering bugs completely.

Working With an Existing Database

Not every project starts with an empty database. Adopting Alembic on a PostGIS schema that already exists needs one extra step, and getting it wrong is the most common way to lose a production schema.

The step is stamping. alembic stamp head records the current revision as applied without running anything, which tells Alembic “the database already looks like this”. The sequence for an existing database is therefore:

bash
# 1. generate a baseline revision describing the schema as it stands
alembic revision --autogenerate -m "baseline"

# 2. READ IT. With the filters configured, it should contain only your tables.
#    Without them, it contains drop statements for spatial_ref_sys and every index.

# 3. mark it applied without executing it
alembic stamp head

Step two is not a formality. Running an unreviewed baseline against the database it was generated from is usually harmless, and running it against a sibling database — staging, a colleague’s copy — is where the drops actually execute. The filters from the autogenerate guide must be in place before the baseline is generated, not after.

After stamping, the useful check is that alembic check reports nothing: the model, the database and the migration history now agree, which is the state every subsequent migration starts from.

Lock duration by migration operation Lock duration for four spatial migration operations: adding a nullable column is milliseconds, adding a constraint NOT VALID is milliseconds, validating it is minutes under a weak lock, and altering a column type is an hour under an exclusive lock. How long each spatial operation holds a lock ADD COLUMN (null) milliseconds · ACCESS EXCLUSIVE ADD CONSTRAINT NOT VALID milliseconds · ACCESS EXCLUSIVE VALIDATE CONSTRAINT minutes · SHARE UPDATE EXCLUSIVE, writes continue ALTER COLUMN TYPE an hour · ACCESS EXCLUSIVE, nothing runs The first three are deployable during traffic. The fourth needs the add-column-and-backfill pattern instead.

Verification

The most valuable Alembic test is not that a migration runs, but that it is reversible and that autogenerate is quiet afterwards:

bash
# apply, reverse, re-apply — all three must succeed
alembic upgrade head && alembic downgrade -1 && alembic upgrade head

# and the model must now match the database exactly
alembic check      # exits non-zero if autogenerate would produce anything

alembic check in CI is the single highest-value guard for a spatial schema. It catches the case where somebody added a GiST index by hand in psql and never told the model — which is how a staging database and a production one start diverging.