Best practices for mocking PostGIS connections

7 min read

Mocking a PostGIS connection is not the same problem as mocking a generic SQL endpoint, and treating it that way is the single most common source of flaky spatial test suites. The moment you patch psycopg2.connect or swap in an in-memory SQLite double, every spatial function — ST_Intersects, ST_Buffer, ST_DWithin, the entire topology layer — disappears, because those operators live in compiled GEOS, PROJ, and GDAL libraries that the mock never loaded. This page is a focused continuation of Mocking Geospatial Data for Tests: where that page surveys the full taxonomy of spatial doubles, here we go deep on one data class — the database connection itself — and show how to isolate it without throwing away the spatial semantics the test exists to verify. The patterns below target GIS QA engineers, data engineers, and platform teams wiring PostGIS into pytest-driven CI.

Root-cause framing: why “just mock the connection” fails

A connection mock substitutes the transport layer (the socket, the cursor, the result rows) with Python objects you control. That works for a CRUD API because the database is a passive store. PostGIS is not passive — it is a stateful spatial engine, and four distinct couplings break when you sever the real connection.

1. C-extension function resolution. ST_* functions are dispatched to libgeos and libproj at query-plan time. A mock returns canned rows but never resolves the function, so either the assertion tests nothing or the query raises UndefinedFunction. You cannot mock a ST_Intersects result and trust it, because the production answer depends on GEOS’s exact predicate evaluation, not your fixture’s guess.
2. GiST index planner behaviour. Spatial queries are won or lost in the bounding-box pre-filter that the PostgreSQL planner runs against a GiST or SP-GiST index. Mocks bypass the planner entirely, so a query that does a sequential scan in production looks identical to one that uses an index — masking the exact performance regressions a GIS test pyramid wants its integration tier to catch.
3. WKB/WKT serialization drift. Geometry crosses the wire as Well-Known Binary. When a mock hands a Shapely object straight back instead of round-tripping it through PostGIS’s binary encoder, you lose the precision-reduction and axis-ordering behaviour the real engine applies. Tests then pass on coordinates production would alter.
4. Session and pool state leakage. search_path, postgis.gdal_enabled_drivers, and temporary tables persist on a pooled connection. A mock never resets them, so state bleeds across cases and across pytest-xdist workers, producing the classic “passes alone, fails in the suite” signature.

The correct mental model is to mock around PostGIS — the network boundary, the test data provisioning, the credentials — while keeping a real, ephemeral spatial engine in the loop wherever a spatial predicate is asserted. This mirrors the guidance on when to use unit vs integration tests in GIS: pure mocks belong only where no spatial operator is exercised.

Parameter and configuration reference

The table below lists the knobs that make the difference between a deterministic spatial test database and a leaky one. Versions are pinned because GEOS topology behaviour changes across releases.

For coordinate comparisons, do not test geometric equality with ==. Compare within a tolerance $\tau$ defined per CRS unit, so that two coordinates are equal when

where $\tau \approx 10^{-7}$ for degrees (roughly 1.1 cm at the equator) and $\tau \approx 10^{-3}$ for projected metres. Pick $\tau$ once, centrally, the same way you would configure spatial tolerance thresholds for any geometric assertion.

Step-by-step implementation

1. Stand up an ephemeral PostGIS with testcontainers (testcontainers 4.x)

Replace the socket mock with a real, throwaway engine. A session-scoped fixture keeps spin-up cost off the per-test path.

import pytest
from sqlalchemy import create_engine, text
from testcontainers.postgres import PostgresContainer


@pytest.fixture(scope="session")
def postgis_engine():
    # Pin Postgres + PostGIS so GEOS/PROJ versions stay reproducible.
    with PostgresContainer("postgis/postgis:16-3.4") as pg:
        engine = create_engine(pg.get_connection_url(), future=True)
        with engine.begin() as conn:
            conn.execute(text("CREATE EXTENSION IF NOT EXISTS postgis;"))
            conn.execute(text("SET postgis.enable_outdb_rasters = false;"))
        yield engine
        engine.dispose()

2. Provision deterministic fixtures, not scraped data (SQLAlchemy 2.0)

Load a fixed, version-controlled geometry set so every run sees identical input. Use SRID-tagged WKT inserts and a real GiST index so the planner behaves as it will in production.

import pytest
from sqlalchemy import text


@pytest.fixture
def parcels(postgis_engine):
    with postgis_engine.begin() as conn:
        conn.execute(text("DROP TABLE IF EXISTS parcels;"))
        conn.execute(text(
            "CREATE TABLE parcels (id int primary key, geom geometry(Polygon, 4326));"
        ))
        conn.execute(text(
            "INSERT INTO parcels (id, geom) VALUES "
            "(1, ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))', 4326)),"
            "(2, ST_GeomFromText('POLYGON((2 2,2 3,3 3,3 2,2 2))', 4326));"
        ))
        conn.execute(text("CREATE INDEX parcels_gix ON parcels USING GIST (geom);"))
        conn.execute(text("ANALYZE parcels;"))  # force a real plan, not a default estimate
    return postgis_engine

3. Enforce per-test pool hygiene (SQLAlchemy 2.0 event API)

Register a checkout listener once so every connection handed out by the pool is reset. This is the cheapest defence against pytest-xdist cross-worker contamination.

from sqlalchemy import event, text


def attach_pool_reset(engine):
    @event.listens_for(engine, "checkout")
    def _reset(dbapi_conn, conn_record, conn_proxy):
        cur = dbapi_conn.cursor()
        cur.execute("RESET ALL; SET search_path = public, postgis;")
        cur.close()

4. Round-trip geometry through PostGIS, then assert with tolerance (Shapely 2.x)

The point of keeping a real engine is that WKB encoding happens for real. Read it back through Shapely and compare within $\tau$ rather than exactly.

from shapely import from_wkb, equals_exact
from shapely.geometry import Point
from sqlalchemy import text

TOL_DEG = 1e-7


def test_point_roundtrip_is_stable(parcels):
    p = Point(0.1234567, 0.7654321)
    with parcels.connect() as conn:
        wkb = conn.execute(
            text("SELECT ST_AsBinary(ST_GeomFromText(:wkt, 4326))"),
            {"wkt": p.wkt},
        ).scalar()
    back = from_wkb(wkb)
    # tolerance-based equality, never == on coordinates
    assert equals_exact(p, back, tolerance=TOL_DEG)

Verification pattern

To confirm the real engine — not a mock — is answering, assert that the spatial predicate and the index are both genuinely in play. This is the one-liner an engineer can run to prove the setup is honest:

from sqlalchemy import text


def test_spatial_join_uses_real_predicate(parcels):
    with parcels.connect() as conn:
        hit = conn.execute(text(
            "SELECT count(*) FROM parcels "
            "WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(0.5, 0.5), 4326), 0.0)"
        )).scalar()
        plan = conn.execute(text(
            "EXPLAIN SELECT * FROM parcels "
            "WHERE geom && ST_SetSRID(ST_MakePoint(0.5, 0.5), 4326)"
        )).scalars().all()
    assert hit == 1                                  # GEOS evaluated the predicate
    assert any("Index Scan" in row for row in plan)  # GiST index was actually used

If hit is wrong or the plan shows a Seq Scan, the engine — not your assertion — has changed, which is exactly the regression a mock would have hidden. Pair this with the structuring guidance in how to structure pytest-geo for large shapefiles when the fixture set grows beyond a handful of rows.

Failure modes and edge cases

1. Anti-meridian polygons. A geometry spanning ±180° longitude can serialize to WKB that GEOS interprets as a near-global polygon. Test the wrap explicitly and assert area stays bounded; a mock would never surface the encoding flip.
2. Empty and NULL geometries. ST_GeomFromText('POLYGON EMPTY') round-trips to a valid-but-empty geometry, while a NULL column is different again. Assert both paths — ST_IsEmpty true vs. geom IS NULL — because Shapely’s is_empty and SQL NULL are not interchangeable.
3. Mixed Z/M coordinates. Inserting 3D geometry into a geometry(Polygon, 4326) (2D) column silently drops Z in some PostGIS builds. Pin the column type with the dimension you intend and assert ST_NDims.
4. CRS unit mismatch in tolerance. A $\tau$ of 1e-7 is centimetres in degrees but nanometres in metres. Using the wrong unit makes assertions either uselessly loose or impossibly strict — keep CRS-aware thresholds, the way automating CRS validation in CI pipelines treats CRS as a first-class contract.
5. Pool state survives a rolled-back transaction. ROLLBACK undoes data but not session GUCs or SET LOCAL-free search_path changes; without the step 3 reset listener, one test’s SET search_path poisons the next. Treat session state and transactional state as separate concerns under the security boundaries in spatial QA scoping rules.

Conclusion

Mock the network and the credentials around PostGIS, but never mock the spatial engine itself: keep a pinned, ephemeral container in the loop, provision deterministic SRID-tagged fixtures, reset the pool on every checkout, and assert geometry with an explicit per-CRS tolerance. That combination removes flakiness without sacrificing the GEOS, planner, and WKB behaviour your tests are meant to guard. For the wider catalogue of spatial doubles and where connection mocking fits among them, return to Mocking Geospatial Data for Tests.

Related

• When to Use Unit vs Integration Tests in GIS
• How to Structure pytest-geo for Large Shapefiles
• Setting Up Spatial Tolerance Thresholds in Assertions
• Parent guide: Mocking Geospatial Data for Tests