Should I use SQLite in‑memory or a disposable PostgreSQL container for SQLAlchemy integration tests that avoid production credentials?

Goal

The goal is to run SQLAlchemy integration tests that exercise ORM behavior without exposing any production database credentials.

Constraints and uncertainty

Tests must remain isolated, avoid credential leakage in logs, and run quickly in CI. Using an in‑memory SQLite engine (sqlite:///:memory:) satisfies the credential‑free requirement but lacks PostgreSQL‑specific types, JSONB operators, and certain locking behaviors. Alternatively, spinning up a disposable PostgreSQL container with masked credentials provides higher fidelity but adds container‑management overhead and requires careful secret handling.

Which approach yields the best trade‑off between test fidelity and operational simplicity while keeping the test suite free of production credentials?

• Does relying on SQLite in‑memory risk false‑positive tests because of missing PostgreSQL‑specific features?
• What are the operational overhead and secret‑management implications of using a temporary PostgreSQL container for each test run?
• Can NullPool combined with an in‑memory SQLite engine provide sufficient isolation while keeping the test suite credential‑free?