SQLite in-memory engine with StaticPool: the missing dialect-fidelity constraint for SQLAlchemy tests

An integration suite for SQLAlchemy can be configured around an in-memory SQLite engine created with create_engine('sqlite://') and a StaticPool, so a single connection keeps the schema visible across sessions. A SAVEPOINT-based fixture can then bind a Session to an outer transaction and roll back each test. This arrangement needs no production credentials and no network access.

The unresolved decision is dialect fidelity. SQLite differs from server dialects on defaults, sequences, JSONB-style types, ON CONFLICT variants, and ALTER TABLE forms. A green SQLite run therefore does not establish production-dialect correctness. SQLAlchemy supplies mechanisms for either path but does not choose one. Version assumptions matter: join_transaction_mode and 2.0-style Session APIs differ from 1.4.

Should the suite assert dialect-agnostic behavior on in-memory SQLite, or must CI run an ephemeral instance of the production dialect? Where should the boundary sit between create_mock_engine DDL checks and executable integration coverage? Which SQLAlchemy release is the baseline for the fixture design?