CodePark/ questions / Databases, Testcontainers & test data
START PRACTICINGPRACTICE
Backend question · Backend testing › Databases, Testcontainers & test data

Why is in-memory SQLite a poor substitute for Postgres when testing a Postgres-backed app?

Because you end up testing a different database.

SQLite has its own SQL dialect and a loose type system. For example, it happily stores a string in an integer column. It has no native jsonb, arrays, enums or timestamptz.

Many Postgres features you rely on behave differently in SQLite or don't exist:

  • jsonb operators
  • ILIKE
  • SELECT ... FOR UPDATE
  • isolation levels
  • Postgres-specific constraint behaviour

Raw queries and migrations often can't run on both.

The result: tests pass on SQLite and fail in production. Or you bend production code so it works on both.

Docker starts a real Postgres in seconds. So test against the same major version you deploy. Use Testcontainers locally and in CI, or a CI service container.

  • SQLite has a different dialect, types and constraint behaviour.
  • Postgres features like jsonb, arrays, enums and FOR UPDATE are missing or behave differently.
  • Migrations and raw SQL may not run on both databases.
  • A real Postgres in Docker is cheap, so match the production version.