Writing Reversible Geometry Migrations

Problem Statement

A deploy goes wrong at 2 a.m. and the runbook says alembic downgrade -1. Whether that command helps depends entirely on decisions made when the migration was written — days earlier, by someone who was thinking about the upgrade. This page covers what makes a spatial downgrade work, which changes cannot be undone at all, and how to structure a revision so the irreversible part is isolated. It builds on the Alembic setup for PostGIS schemas.

Why the Naive Approach Fails

Alembic’s generated downgrade inverts each operation and keeps the original order. For a single add_column that is correct. For anything with dependencies it is not:

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


def downgrade() -> None:
    # generated: same order, inverted operations — fails
    op.drop_column("listings", "geom")            # ← the constraint still needs it
    op.drop_index("idx_listings_geom", "listings")
    op.drop_constraint("listings_geom_valid", "listings")
A downgrade is a mirror, not a translation The upgrade adds a column, then an index, then a constraint. The correct downgrade drops the constraint, then the index, then the column — the exact reverse. The generated downgrade keeps the original order and fails on the first dependency. Undo the last thing first upgrade 1 add_column 2 create_index 3 create_check_constraint each step depends on the one before it downgrade 3′ drop_constraint 2′ drop_index 1′ drop_column the numbers count down — if they do not, the downgrade has a dependency bug

Production-Ready Implementation

The corrected revision, with the reverse order and an explicit note about what is lost:

python
"""add validated geometry to listings

Revision ID: c7d21a9f4e08
Reversible: yes, but the geometry data is destroyed on downgrade.
"""
from alembic import op
import sqlalchemy as sa
import geoalchemy2                       # noqa: F401
from geoalchemy2 import Geometry

revision = "c7d21a9f4e08"
down_revision = "8c41f0a2e7b3"


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


def downgrade() -> None:
    # exact mirror of upgrade: 3, 2, 1
    op.drop_constraint("listings_geom_valid", "listings", type_="check")
    op.drop_index("idx_listings_geom", table_name="listings")
    op.drop_column("listings", "geom")

When the downgrade would lose data

A downgrade that drops a populated column destroys data. Sometimes that is fine — the column was added in this deploy and holds nothing anyone needs. Sometimes it is not, and the honest response is to refuse:

python
def downgrade() -> None:
    conn = op.get_bind()
    populated = conn.execute(sa.text(
        "SELECT count(*) FROM listings WHERE geom IS NOT NULL"
    )).scalar()

    if populated:
        raise RuntimeError(
            f"downgrade would destroy geometry for {populated} listings. "
            "Export it first, or run this migration with ALLOW_DATA_LOSS=1."
        )
    op.drop_column("listings", "geom")

An escape hatch via an environment variable keeps the refusal from becoming an obstacle during a genuine emergency, while making the loss a decision somebody consciously took rather than a side effect of a runbook line.

Structuring for reversibility

The most effective technique is not writing clever downgrades but splitting revisions so the irreversible step stands alone:

One irreversible revision, isolated on purpose Three revisions in sequence. The expand revision adds the new column and index and is fully reversible. The migrate revision backfills data and is reversible because the old column still holds the truth. The contract revision drops the old column and is the only irreversible one, deployed weeks later. Three revisions, one point of no return expand add geom + index nothing reads it yet fully reversible migrate backfill from lat/lon reads switch over reversible — lat/lon intact contract drop lat/lon columns weeks later, on purpose not reversible During the window between migrate and contract, any deploy can be rolled back with a single command. That window is the entire value of the split — it costs some disk and buys a safe rollback for as long as you keep it open.

Configuration and Tuning Knobs

down_revision chains determine what downgrade -1 actually does. A revision that bundles four unrelated changes can only be rolled back as a unit; four small revisions can be rolled back individually. Prefer small revisions for anything touching geometry.

op.execute with raw SQL bypasses Alembic’s operation objects and therefore its ability to generate anything. That is fine — most interesting spatial DDL ends up as raw SQL — but it means the downgrade must also be raw SQL, written by hand, and it will not appear in --sql offline output unless you write both directions.

Data migrations inside a schema revision make reversibility harder to reason about. Where a backfill is large, keep it out of Alembic entirely and run it as an operational job with its own watermark and resumability; the revision then only creates the column, and stays trivially reversible.

What each revision type can undo Reversibility by revision type: adding a column is fully reversible, adding an index or constraint is fully reversible, a backfill is reversible only while the source column survives, and dropping a column is not reversible at all. Reversibility, by what the revision does add column fully reversible, no data loss add index or constraint fully reversible backfill a column reversible while the source column exists drop a column not reversible — the data is gone Every revision belongs to one of these four rows, and the docstring should say which.

Verification Steps

The test that matters runs against data, not an empty schema:

python
# tests/test_migrations.py
import subprocess

def test_migration_round_trip(seeded_database):
    """upgrade → downgrade → upgrade must all succeed with rows present."""
    subprocess.run(["alembic", "upgrade", "head"], check=True)
    subprocess.run(["alembic", "downgrade", "-1"], check=True)
    subprocess.run(["alembic", "upgrade", "head"], check=True)

The seeded_database fixture is the important part. An empty database hides every dependency-order bug, because there is nothing for a constraint to reference and no data for a NOT NULL to reject.

Documenting reversibility in the revision itself

The person running alembic downgrade at 2 a.m. is not going to read the code. Put the answer in the docstring, in a fixed format the whole team uses:

python
"""add coverage geometry to districts

Revision ID: e2f8c1d40a97
Reversible: yes — the downgrade drops a column populated only by this migration.
Data loss on downgrade: none (the source lat/lon columns are untouched).
Expected duration: 40 s upgrade, 2 s downgrade.
"""

Three lines, written while the change is fresh, that answer the only three questions anyone asks during an incident. A revision whose reversibility line says “no” is doing its job — it tells the operator to reach for a restore rather than burning ten minutes discovering that the downgrade cannot work.

The same header makes review better. A pull request containing a migration marked irreversible gets the attention it deserves, and one marked reversible with a two-second downgrade can be approved quickly.

Gotchas Checklist

  • Test downgrades against seeded data. An empty schema will pass a broken downgrade every time.
  • op.drop_constraint needs type_. Without it Alembic guesses, and guesses wrong on check constraints often enough to matter.
  • Dropping a geometry column leaves geometry_columns correct automatically. That view is derived in modern PostGIS; older code that manually called DropGeometryColumn is obsolete and will error.
  • A downgrade that recreates a column silently loses its data. If the runbook expects a rollback to restore state, the migration must not have been the thing holding that state.
  • Re-upgrading after a downgrade must work too. It is the step people forget, and it is the one that runs during the second attempt at a failed deploy.

The rollback that is not a downgrade

Some situations are past the point where alembic downgrade helps: a migration that half-applied because the connection dropped, a constraint validated against data that has since changed, a contract step run a week early. For those, the recovery is a restore or a hand-written repair, and the useful preparation is knowing which category a revision belongs to before it runs.

That is the practical value of the reversibility header: it partitions every migration into “roll back with one command” and “call somebody”. Both are acceptable answers. Not knowing which one applies, at the moment it matters, is not.