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

Direct answer

Run both, but do not expect SQLite to prove production correctness. Use the in-memory SQLite engine with StaticPool and SAVEPOINT rollback for dialect-agnostic ORM behavior, and run an ephemeral production-dialect instance in CI for anything that touches server-specific SQL, types, or DDL. The baseline fixture should target SQLAlchemy 2.0 or later; the 1.4 transaction model does not support the same join_transaction_mode pattern.

What the SQLite fixture actually proves

Confirmed behavior: create_engine('sqlite://') with StaticPool keeps one connection alive, so schema created in one Session remains visible to the next. Session.begin_nested() issues SAVEPOINTs that roll back cleanly. This gives fast, isolated tests without credentials.

Likely limitation: SQLite silently coerces server-specific types. A PostgreSQL UUID or JSONB column becomes TEXT. ON CONFLICT, sequences, and certain ALTER TABLE forms either fail or take a different path. A green SQLite run therefore validates your ORM mapping and generic query construction, not dialect fidelity.

Where to draw the boundary

• create_mock_engine: Assert DDL shape only. Use it when the test needs to confirm that CREATE TABLE, indexes, or constraints are emitted correctly. No data is inserted, so runtime behavior is out of scope.
• SQLite + StaticPool: Use for business logic, relationship loading, cascade behavior, and generic CRUD that avoids server-specific syntax. Keep these tests fast and local.
• Ephemeral production dialect: Use for raw SQL, server-specific types, upsert/ON CONFLICT logic, sequence handling, and migration DDL. A Docker or testcontainers PostgreSQL/MySQL instance in a separate CI stage is the standard pattern.

Version baseline

Design the fixture for SQLAlchemy 2.0+. In 2.0, Session is unified and join_transaction_mode exists, so the SAVEPOINT pattern works with standard sessionmaker. Under 1.4 you must use legacy Session APIs and explicit transaction management; attempting the 2.0 pattern raises AttributeError. Verify your baseline before copying fixture code:

python -c "import sqlalchemy; print(sqlalchemy.__version__)"

Practical verification

1. Confirm the SQLite engine is truly shared by creating a table in one session and querying it in another.
2. Add a dialect-specific column (for example, postgresql.UUID) and inspect the SQLite DDL to confirm it degrades to TEXT.
3. Run the same test against an ephemeral PostgreSQL container to observe the difference.
4. Use create_mock_engine to capture DDL and compare it with the real engine's output for the same model.

One detail that changes the recommendation

Which production dialect and major version is the CI target? If the application is PostgreSQL 14+, the integration stage must cover ON CONFLICT, JSONB, and UUID; if it is MySQL 8, the focus shifts to ON DUPLICATE KEY, charset/collation defaults, and ALTER TABLE restrictions. That choice determines which dialect-specific tests are mandatory.