Use Testcontainers for PostgreSQL

SQLite is not a smaller PostgreSQL. It has different types, different case sensitivity rules, different JSON handling, and a different SQL dialect. If production runs PostgreSQL, a green SQLite test can be a false positive.

Testcontainers solves this. It starts a real PostgreSQL in Docker from your test code, hands you the connection string, and throws the container away when you’re done.

Start a container from a test

Install the PostgreSQL module:

npm install -D @testcontainers/postgresql

Then start a container, connect the real repository to it, run the migrations, and stop it in finally:

import { PostgreSqlContainer } from '@testcontainers/postgresql'

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()
}

Testcontainers picks a random free port, so two test runs on the same machine don’t collide. getConnectionUri() gives you the full connection string for that instance.

The first run pulls the image and takes a while. After that, starting a container takes a few seconds.

Pin the image

Notice the explicit tag, postgres:17-alpine. Never use latest here. A new major version would change CI underneath you, and you’d spend a morning debugging a failure that has nothing to do with your code. Update the tag on purpose, in its own commit.

Started is not ready

A container can be running while PostgreSQL inside it is still starting up. The PostgreSqlContainer module waits for the database to accept connections before start() resolves. If you use a generic container instead, add a wait strategy that represents a usable database, not just a process.

Use the same migrations and the same driver configuration as production. Otherwise you’re back to testing something production doesn’t run.

How many containers?

One container per test gives the strongest isolation and costs the most time. One per suite is usually the better trade, as long as every test gets its own database or schema inside it, and cleanup is verified. Never share fixed rows between tests that run at the same time.

When a test fails, keep the query and the container logs. They tell you whether the product is wrong or Docker wasn’t available on the runner. Those two failures need very different fixes.

Try this: write a repository test that starts PostgreSQL, inserts a book, reads it back through a new query, and stops the container even when the assertion fails. Then add one PostgreSQL-specific case, like a case-insensitive unique index on ISBN, that the in-memory fake can’t prove.