SQLAlchemy Spanner

sqlalchemy-spanner is the SQLAlchemy dialect for Google Cloud Spanner, letting Python applications create tables, run queries, and manage schema migrations against a globally distributed relational database using the same SQLAlchemy Core and ORM patterns they already use for Postgres or MySQL. It sits on top of the google-cloud-spanner client and the Spanner DB API (PEP-249), translating SQLAlchemy’s SQL constructs into Spanner-compatible DDL and DML, while exposing Spanner-specific features — interleaved tables, commit-timestamp columns, query hints, and configurable isolation levels — as dialect extensions rather than forcing developers to drop down to raw Spanner client calls.

Because Spanner has different transactional and schema semantics than a typical RDBMS (no UNIQUE constraints, non-transactional DDL, bit-reversed auto-increment sequences to avoid write hotspotting), the dialect also retries aborted SERIALIZABLE transactions automatically and integrates with Alembic for migrations, with documented guidance for avoiding common pitfalls like primary-key-based revision tracking.

What You Get

• A registered SQLAlchemy dialect (spanner+spanner://) usable with create_engine() like any other backend
• Support for Spanner-specific schema features: interleaved tables, commit-timestamp columns, and UNIQUE-index workarounds for Spanner’s lack of UNIQUE constraints
• Automatic retry of aborted SERIALIZABLE transactions, plus REPEATABLE_READ and AUTOCOMMIT isolation-level options
• Alembic integration for schema migrations, with documented guidance on batching DDL and avoiding primary-key-based revision tables
• Query hint support via with_hint() for passing Spanner-specific execution directives like FORCE_INDEX
• Read-only transaction and stale-read execution options for reducing lock contention on read-heavy workloads

Common Use Cases

• Porting an existing SQLAlchemy-based Python app from Postgres or MySQL onto Cloud Spanner without rewriting query code
• Running Alembic-managed schema migrations against a Spanner database in a CI/CD pipeline
• Building read-heavy services that use stale reads or read-only transactions to avoid Spanner lock contention
• Modeling parent-child data relationships with Spanner interleaved tables directly from SQLAlchemy Table definitions

Under The Hood

Architecture The dialect follows SQLAlchemy’s standard extension-point pattern: a single SpannerDialect(DefaultDialect) class composes dedicated compiler and preparer classes — SpannerSQLCompiler(SQLCompiler), SpannerDDLCompiler(DDLCompiler), SpannerTypeCompiler(GenericTypeCompiler), SpannerIdentifierPreparer(IdentifierPreparer), and SpannerExecutionContext(DefaultExecutionContext) — each overriding one layer of SQLAlchemy’s SQL-generation pipeline rather than reimplementing the ORM itself. Supporting concerns are split into small dedicated modules (_opentelemetry_tracing.py, dml.py, provision.py, requirements.py), and the dialect self-registers via a setuptools entry point (sqlalchemy.dialects: spanner.spanner) so SQLAlchemy discovers it dynamically at create_engine() time — a well-understood plugin-over-a-stable-extension-point design where the main risk is SQLAlchemy’s own compiler internals changing, not internal coupling within the dialect.

Tech Stack Pure Python, packaged with a classic setup.py (no pyproject.toml), depending on sqlalchemy>=1.1.13, google-cloud-spanner>=3.55.0, and alembic, with an optional tracing extra pulling in opentelemetry-api/opentelemetry-sdk/opentelemetry-instrumentation. It wraps the lower-level google-cloud-spanner client (itself built on the Spanner DB API / PEP-249) rather than talking to Spanner’s gRPC surface directly. CI runs through nox, executing lint (lint_setup_py, lint, Black formatting) and separate unit, system, and mockserver test sessions, following the same tooling conventions used across other googleapis Python client libraries.

Code Quality The repository carries an extensive test suite split across unit, system, and mockserver-backed integration tests, and CI enforces Black formatting and linting via nox — though there is no static type checking configured, and the core dialect module uses limited type annotations. Error handling favors narrow, explicit exception handling over broad catch-alls. Class and module naming closely mirrors SQLAlchemy’s own dialect API conventions, which keeps the learning curve low for anyone already familiar with writing or reading SQLAlchemy dialects.

What Makes It Unique Because it plugs into SQLAlchemy’s own dialect interface, everyday usage requires no new API beyond passing a spanner+spanner:/// URL to create_engine() — existing SQLAlchemy Core/ORM code mostly works unchanged. Spanner-specific behavior is exposed idiomatically as SQLAlchemy extension points (Table kwargs like spanner_interleave_in, Column kwargs like spanner_allow_commit_timestamp, and execution_options(read_only=..., staleness=...)) rather than a bespoke parallel API, backed by a comprehensive samples directory. The main friction is Spanner’s own semantic differences from a typical RDBMS leaking through as documented caveats — appropriate given they reflect genuine backend differences rather than library design gaps.