ADR 0012: SQLite in tests, PostgreSQL in production

Status:

Accepted

Context

Spinning up real PostgreSQL for every unit and repository test is slow and infra-heavy. The persistence layer is abstracted enough to swap the backend, so tests can run against an in-memory database.

Decision

Repository and integration tests run the real repositories against an in-memory sqlite+aiosqlite:///:memory: engine. A small type shim compiles the PostgreSQL-specific column types to SQLite equivalents (UUID -> CHAR(36), JSONB -> JSON) so production model definitions are used untouched (tests/unit_tests/repository/conftest.py). TestConfigs is selected when ENV=test; the Database wrapper coerces sync URLs to async drivers and skips pool kwargs for SQLite. The few PostgreSQL-only paths (for example ON CONFLICT DO UPDATE) fall back to a mocked session.

Consequences

• Fast, isolated, no-infra tests for the whole service and repository layer.

• A constraint on contributors: repository queries must stay portable - no PostgreSQL-only SQL in the common path.

See also

• Contributing - “Testing” documents the SQLite-vs-PostgreSQL split.

• ADR 0011: DI container - db Singleton, repos/services Factories - the provider override that points db at SQLite.