How do you write reliable integration tests that use a database?

Summary

Run the real database engine (via Testcontainers or a CI service container), apply migrations, isolate each test (transaction rollback, truncation, or unique data), and seed data with factories rather than shared fixtures.

Longer Answer

SQLite as a stand-in for PostgreSQL misses differences in types, locking, JSON and constraints. Keep tests independent so they can run in parallel and in any order.

Key Points

• Use the real engine
• Apply migrations in tests
• Isolate per test (rollback/truncate)
• Factories over shared fixtures

Common mistakes

• Using SQLite to test Postgres-specific behavior
• Tests depending on execution order