Best practice for integration tests that need PostgreSQL?
Options
- Mock the whole ORM and assert on the calls made
- Run real Postgres in a container, roll back per test (Correct option)
- Use SQLite instead, since all SQL is standard
- Point the tests at the shared production database
Answer
Use a real Postgres (e.g. in a container via testcontainers or docker compose) and isolate each test with a transaction rollback or fresh schema
Why
Integration tests check that your code really works with the real dependency. Mocking the ORM tests nothing real; SQLite behaves differently from Postgres (types, constraints, JSON, locking).
Start a throwaway Postgres in Docker for the tests. Keep tests independent by rolling back each test's transaction or recreating the schema.
Never touch production data in tests.
Remember
Integration tests: real database in a container, isolated per test.