Use Testcontainers for PostgreSQL

Testcontainers starts real service containers from test code and returns connection details for the randomly allocated instance.

Start PostgreSQL once per appropriate test scope, wait for readiness, apply migrations, and always stop the container in teardown. Pin the image version used in CI. This is slower than a fake but catches driver and database-specific behavior.

Use PostgreSQL when production behavior depends on PostgreSQL: parameter binding, JSON types, case sensitivity, transactions, indexes, or SQL syntax. SQLite is not a smaller PostgreSQL. A green SQLite test can be a false positive if the production query or constraint behaves differently.

let container

try {
  container = await new PostgreSqlContainer('postgres:17-alpine').start()
  const repository = await connectBooksRepository(container.getConnectionUri())
  await migrate(repository)

  await repository.insert({ title: 'Dune', author: 'Frank Herbert' })
  assert.equal((await repository.findByTitle('Dune')).author, 'Frank Herbert')
} finally {
  await container?.stop()
}

Pin a version deliberately so a new image does not change CI underneath you. The container being started is not always the same as the service being ready; use the module’s readiness behavior or an explicit strategy that represents a usable database. Apply the same migrations and driver configuration as production.

One container per test gives strong isolation but is expensive. One per suite is often a better boundary if every case gets a unique database or schema and cleanup is verified. Never share fixed rows between concurrently running cases. When a failure occurs, retain the query and container logs that distinguish a product defect from an unavailable container runtime.

Write a small repository test that starts PostgreSQL, inserts a book, reads it through a new query, and stops the container even after failure. Then add one PostgreSQL-specific constraint case that the in-memory fake cannot prove.