skip to content

As the lead of a SQLAlchemy 2.1 service on PostgreSQL, would you run its repository tests on in-memory SQLite or on PostgreSQL, and why?

level: principalimportance: should knowfreq 28%

answer

  1. speed against fidelity
  2. what SQLite accepts silently
  3. one dialect compiles differently
  4. make the real database cheap

basics

~20 s

Default to PostgreSQL for every test that touches the database, isolated with the outer-transaction recipe. In-memory SQLite starts faster but compiles and enforces differently, so it can pass code PostgreSQL rejects; keep it, if at all, for dialect-free logic.

solid answer

~40 s

In-memory SQLite with `StaticPool` buys fast startup and no service to run, but SQLAlchemy compiles for the dialect it is given, and SQLite accepts things PostgreSQL rejects: it stores an over-long value in a `String(5)` column, ignores foreign keys unless `PRAGMA foreign_keys=ON` runs on each connection, drops `with_for_update()` from the SQL, needs driver workarounds for SAVEPOINT, and batches INSERTs differently, so statement budgets move. My default is PostgreSQL for repository tests: schema created once per run, each test inside a rolled-back outer transaction, which keeps most tests fast. SQLite can stay for pure logic that never relies on dialect behaviour. The cost is a database service in CI and locally; the alternative's cost is production finding the bugs the suite could not.

code

python · 37 lines
python
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.dialects import postgresql, sqlite
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.pool import StaticPool


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customer"
    id: Mapped[int] = mapped_column(primary_key=True)
    code: Mapped[str] = mapped_column(String(5))


class Order(Base):
    __tablename__ = "order_"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id"))


engine = create_engine(
    "sqlite://", poolclass=StaticPool, connect_args={"check_same_thread": False}
)
Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    session.add(Customer(code="far longer than five characters"))  # PostgreSQL rejects
    session.add(Order(customer_id=999))  # no such customer: PostgreSQL rejects

with Session(engine) as session:
    print(session.scalars(select(Customer.code)).all())

stmt = select(Customer).with_for_update()
print(stmt.compile(dialect=sqlite.dialect()))
print(stmt.compile(dialect=postgresql.dialect()))

go deeper

for a junior

Know that SQLAlchemy compiles statements per dialect, so SQLite and PostgreSQL can disagree about the same model and query.

for a middle

List concrete differences: column lengths, foreign keys, FOR UPDATE, savepoint handling, insert batching, and what each one means for a passing test.

for a senior

Show how you would make the real database fast enough: schema once, the outer-transaction recipe, a shared engine, a database per worker.

for a principal

Own the policy: which tiers of test run where, what exceptions need justification, and how the harness enforces it rather than relying on reviewers.

## The decision A team building on SQLAlchemy and PostgreSQL has to pick where its data-layer tests run. The choice is open: both options work, and each one has a cost the team carries every day. It is usually settled once, in the shared test harness, and then copied into every new test. ## What in-memory SQLite buys - **Speed and zero setup.** `create_engine("sqlite://", poolclass=StaticPool, connect_args={"check_same_thread": False})` gives a fresh database in milliseconds, with no service to start. - **Simple parallelism.** Each worker process gets its own private database. - **Nothing to install.** A newcomer runs the suite on a fresh clone with no database service, which lowers the cost of contributing. - **Portable models.** SQLAlchemy's declarative models and `select()` statements usually compile for SQLite unchanged. ## What it silently skips SQLAlchemy compiles each statement for the dialect it is running against, and SQLite is permissive: | Behaviour | PostgreSQL | In-memory SQLite through SQLAlchemy | |---|---|---| | `String(5)` receiving an over-long value | rejected | stored in full | | `ForeignKey` pointing at a missing row | rejected | accepted unless `PRAGMA foreign_keys=ON` runs on every connection | | `select(...).with_for_update()` | `... FOR UPDATE` | rendered without any locking clause | | the outer-transaction test recipe | works as written | needs `connect_args={"autocommit": False}` or BEGIN event hooks | | a flush that needs primary keys back | INSERTs can be batched | one INSERT per row, so statement counts differ | | concurrent Sessions | separate connections and transactions | `StaticPool` shares one connection and one transaction | Every row is a class of bug that passes locally and fails in production: a length overflow, an orphaned foreign key, a race the lock was meant to stop, a statement budget that does not hold. ## Making PostgreSQL cheap enough The usual objection is speed, and most of it can be engineered away: 1. **Create the schema once per run**, with `Base.metadata.create_all()` or by running the migrations, never per test. 2. **Isolate with the outer-transaction recipe**: one connection, `connection.begin()`, `Session(bind=connection, join_transaction_mode="create_savepoint")`, rolled back at teardown. No truncation, no re-seeding. 3. **Reuse one engine** for the whole run so connections come from the pool. 4. **Give each parallel worker its own database** or schema, so workers never see each other's rows. 5. **Run the same PostgreSQL major version as production**, in CI and on developers' machines. 6. **Keep the slow tests few and labelled**: the handful that commit for real, or open a second connection, run in their own group so the fast majority stays fast. Measured this way, the per-test cost on PostgreSQL is a few `SAVEPOINT` statements and a `ROLLBACK`, which often narrows the gap to SQLite enough that speed stops being the deciding argument. ## A tiered policy - **Pure logic** (validation, mapping between objects, query-building helpers checked with `compile()`) needs no database at all. - **Repository and service tests** that execute SQL run on PostgreSQL inside the recipe. - **Behaviour that needs a real commit or a second connection** (locking, isolation levels, migrations) gets a few slower tests that commit for real and clean up. - **SQLite** is allowed only where the team accepts that it proves nothing dialect-specific, and never for statement budgets. ## The costs the team carries - With PostgreSQL: a database service in CI and locally, a slower first run, and someone owning the harness. - With SQLite: driver workarounds in the harness, two dialects to keep compatible, and a class of defects that only production catches. - Mixed: a rule people must follow about which tests may use which engine, which drifts unless the harness enforces it. ## Signals that the current choice is wrong - A defect reached production that a test on the production database would have caught, such as a length overflow or a missing foreign key row. - The harness carries SQLite-only workarounds (`autocommit`, `PRAGMA` hooks, skipped tests) that nobody remembers adding. - Statement budgets pass locally and fail, or are never checked, against PostgreSQL. - Tests are skipped on SQLite because a construct does not compile there, so the most PostgreSQL-specific code is the least tested. - The suite is slow on PostgreSQL because every test rebuilds the schema, not because of the database itself. A defensible lead position is to treat the production database as the default and make every exception explicit, rather than start from SQLite and add PostgreSQL after the first production incident.

  • How would you keep a PostgreSQL-backed suite fast as it grows?
    Build the schema once per run, wrap each test in a rolled-back outer transaction so there is no truncation, share one engine, and give each parallel worker its own database. The per-test cost is then a few SAVEPOINTs and a ROLLBACK.
  • Where is in-memory SQLite still a reasonable choice?
    For logic that does not depend on dialect behaviour: object mapping, validation, or checking that a query builder produces the intended `select()`, which `compile()` can check without any database. Even there, label the tests so nobody reads a pass as evidence about PostgreSQL.

  • Assuming SQLAlchemy makes SQLite and PostgreSQL behave identically
  • Trusting statement budgets measured on SQLite for a PostgreSQL service
  • Testing row locking with with_for_update() on SQLite
  • Rebuilding the schema for every test and then blaming PostgreSQL for slow tests
  • Choosing SQLite for speed without listing what it cannot catch