Geospatial Schema Standardization & Mapping Automation

Versioning PostGIS DDL with Alembic Migrations

Alembic is excellent at versioning ordinary relational schemas and, out of the box, actively dangerous against PostGIS. Its autogeneration compares your SQLAlchemy models to the live database, and everything PostGIS adds that the models do not describe — the spatial_ref_sys table, the extension’s own functions, the implicit index behind a geometry column — looks to it like drift to be removed. A first autogenerated revision in a PostGIS database commonly proposes dropping the geometry column type it cannot interpret and every spatial index it did not create. This procedure configures the environment so autogeneration is trustworthy, implementing the tooling half of spatial database schema migrations inside Geospatial Schema Architecture & Standards Mapping.

The steps map to configure (Steps 1–2, models and filters), execute (Step 3, index handling), validate (Step 4, review), and log (Step 5, golden-schema verification).

Prerequisites checklist

Step 1: Declare geometry columns so autogeneration can see them

python
# models.py — geoalchemy2 >=0.14, sqlalchemy >=2.0 — Python 3.10+
from geoalchemy2 import Geometry
from sqlalchemy import Column, Integer, Text
from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass


class Parcel(Base):
    __tablename__ = "parcels"

    id = Column(Integer, primary_key=True)
    stable_id = Column(Text, nullable=False, unique=True)
    land_use_code = Column(Text, nullable=False)
    # An explicit geometry type and SRID: this becomes geometry(Polygon,25832) in DDL.
    # spatial_index=True lets GeoAlchemy2 emit the GiST index — but see Step 3.
    geom = Column(Geometry(geometry_type="POLYGON", srid=25832, spatial_index=True),
                  nullable=False)

Declaring geometry_type and srid is not cosmetic. A bare Geometry() produces a column that accepts any geometry in any CRS, which defeats the type system exactly where it is most valuable — and it also gives autogeneration nothing to compare, so a later SRID correction produces no migration at all.

Step 2: Filter PostGIS-managed objects out of the comparison

python
# migrations/env.py — alembic >=1.13 — Python 3.10+
from alembic import context
from geoalchemy2 import alembic_helpers

from models import Base

POSTGIS_OWNED_TABLES = {"spatial_ref_sys", "geography_columns", "geometry_columns",
                        "raster_columns", "raster_overviews"}


def include_object(obj, name, type_, reflected, compare_to):
    """Keep PostGIS's own objects out of autogenerate entirely."""
    if type_ == "table" and name in POSTGIS_OWNED_TABLES:
        return False
    # GeoAlchemy2 creates the index behind a geometry column; do not let Alembic
    # propose dropping and recreating it on every run.
    if type_ == "index" and name.startswith("idx_") and name.endswith("_geom"):
        return False
    return True


context.configure(
    connection=connection,
    target_metadata=Base.metadata,
    include_object=include_object,
    # GeoAlchemy2 supplies the render/compare hooks so geometry types round-trip.
    process_revision_directives=alembic_helpers.writer,
    render_item=alembic_helpers.render_item,
    include_schemas=False,
    compare_type=True,
    compare_server_default=True,
)

Without include_object, every autogenerated revision contains op.drop_table('spatial_ref_sys') — and a developer who runs it once loses the projection definitions the whole database depends on. Without GeoAlchemy2’s render_item, geometry columns render as opaque NullType and the migration writes a column no PostGIS function will accept.

Step 3: Keep spatial index creation explicit and concurrent

spatial_index=True on the model is convenient for a fresh database and wrong for a live one: it emits CREATE INDEX, which takes an exclusive lock for the duration of the build. On a large parcel table that is minutes of failed map requests.

python
# migrations/versions/0043_add_geom_index.py — alembic >=1.13
from alembic import op

revision = "0043"
down_revision = "0042"


def upgrade() -> None:
    # CONCURRENTLY cannot run inside a transaction block.
    with op.get_context().autocommit_block():
        op.execute("CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_parcels_geom "
                   "ON parcels USING gist (geom)")


def downgrade() -> None:
    with op.get_context().autocommit_block():
        op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_parcels_geom")

Two details make this safe. autocommit_block() is required because PostgreSQL refuses CONCURRENTLY inside a transaction, and Alembic wraps migrations in one by default. IF NOT EXISTS makes the migration re-runnable after a failed concurrent build, which leaves an invalid index behind that must be dropped and rebuilt — a state you will meet eventually on a busy table.

Autogenerate Output Before and After Configuring the Environment The left panel lists what an unconfigured Alembic autogenerate proposes against a PostGIS database: drop the spatial_ref_sys table, alter the geometry column to an unrecognized null type, and drop the GiST spatial index — none of which the developer intended. The right panel lists the output after registering GeoAlchemy2's render hooks and filtering PostGIS-owned objects: a single add-column operation, which is the actual change. A caption notes that the dangerous operations are silently valid SQL, so nothing fails until the data is gone. Unconfigured — proposed operations drop_table('spatial_ref_sys') alter_column('parcels', 'geom', type_=NullType) drop_index('idx_parcels_geom') add_column('parcels', 'land_use_code') three of the four are destructive and all four are valid SQL Configured — proposed operations add_column('parcels', 'land_use_code') spatial_ref_sys excluded by include_object geometry type rendered by GeoAlchemy2 GiST index owned by an explicit migration the diff now contains only the change that was intended

Step 4: Review every autogenerated revision by hand

Autogeneration is a first draft. Read each revision for four things before committing it:

  • Proposed drops of anything spatial. A drop_column on a geometry column, or a drop_index on a GiST index, is almost never what was meant.
  • A missing downgrade. Alembic writes pass when it cannot infer the reverse. A migration whose reverse is pass is not reversible, and the policy requires that it be.
  • Type changes that rewrite the table. alter_column ... type_ on a large table is an offline operation whatever the diff says.
  • Server defaults. compare_server_default=True catches real drift and also produces noisy false positives on expressions; confirm each one rather than accepting it.

Step 5: Verify the result against a golden schema

bash
# PostgreSQL client >= 16 — regenerate the golden schema after a reviewed change
pg_dump --schema-only --no-owner --no-privileges \
        --exclude-schema=tiger --exclude-schema=topology \
        "$DATABASE_URL" > schema/cadastre.golden.sql
python
# tests/test_migrations_postgis.py — pytest >=7, alembic >=1.13
def test_upgrade_head_matches_golden(disposable_db, alembic_config):
    command.upgrade(alembic_config, "head")
    assert dump_schema(disposable_db) == read_text("schema/cadastre.golden.sql")


def test_round_trip(disposable_db, alembic_config):
    command.upgrade(alembic_config, "head")
    command.downgrade(alembic_config, "base")
    command.upgrade(alembic_config, "head")


def test_no_geometry_column_lost_its_typmod(disposable_db):
    rows = disposable_db.execute(
        "SELECT f_table_name FROM geometry_columns WHERE type = 'GEOMETRY' OR srid = 0"
    ).fetchall()
    assert rows == [], f"untyped or SRID-less geometry columns: {rows}"

Excluding the tiger and topology schemas from the dump keeps the golden file about your schema rather than about the PostGIS installation, which otherwise makes every extension upgrade look like application drift.

Verification

A correctly configured environment produces an empty autogenerate diff against an up-to-date database — that is the check to run before starting any change:

bash
alembic revision --autogenerate -m "should-be-empty"
# then inspect: the generated file's upgrade() body must be exactly "pass"
grep -A2 "def upgrade" migrations/versions/*should_be_empty*.py

If it is not empty on a database that is already at head, something in the model or the filters disagrees with reality, and every subsequent migration will carry that disagreement forward.

Two habits keep that check meaningful over time. Run it against a database built only by migrations — never against a development database that has been hand-edited, because the hand edits are exactly what the check exists to surface. And run it in CI rather than locally, so the answer does not depend on which PostGIS version a particular developer installed: an extension upgrade changes what the catalogue reports, and a diff that is empty on one machine and noisy on another is a diff nobody will trust for long.

Troubleshooting

Symptom Likely cause Fix
Autogenerate proposes dropping spatial_ref_sys include_object filter missing Add the PostGIS-owned table filter from Step 2
Geometry column renders as NullType GeoAlchemy2’s render_item not registered Pass render_item=alembic_helpers.render_item in context.configure
CREATE INDEX CONCURRENTLY cannot run inside a transaction block Alembic’s default transaction wrapper Wrap the statement in op.get_context().autocommit_block()
Every run proposes dropping and recreating the same index GeoAlchemy2 created it implicitly and Alembic does not recognize the name Exclude the implicit index in include_object and own it in an explicit migration
Golden diff is noisy on every deploy Dump includes PostGIS-managed schemas or ownership Add --no-owner --no-privileges and exclude tiger and topology
A migration works locally and times out in production Local table is small; the operation is offline-class Reclassify it in the policy and run it in a window — see the expand-and-contract sequence