Integration Tests Against Real PostgreSQL with Testcontainers: How We Run One Container per Run and a Database per Test File

Key takeaways

• Testcontainers is a library that starts real Docker containers from test code and removes them afterwards, so integration tests run against the same PostgreSQL major version as production.

• We start one PostgreSQL container per test run in Vitest's globalSetup and pass connection details to test workers with provide() and inject().

• We run migrations once into a template database and clone it for each test file with CREATE DATABASE ... TEMPLATE, which copies the migrated schema instead of re-running every migration.

• Per-test transaction rollback breaks when the code under test opens its own transaction or uses a second pooled connection, so we reserve it for simple repository tests.

• We mount the test database on tmpfs and turn off fsync, because durability settings protect data that is deleted after the run anyway.

Why test against real PostgreSQL instead of mocks or SQLite?

We test against real PostgreSQL because most data-layer bugs are database behaviour, and only the real engine reproduces them. A mocked repository returns whatever the test told it to return; it cannot raise a unique-constraint violation, apply a migration, or evaluate an ON CONFLICT clause.

SQLite and in-memory fakes such as pg-mem implement a different or partial SQL dialect. jsonb operators, ON CONFLICT ... DO UPDATE, row-level security policies, extensions such as pgvector, and exact error codes either behave differently or do not exist.

Testcontainers, an open-source library with implementations for Java, Go, .NET, Python and Node.js, closes that gap without a shared test database. In Node.js, the testcontainers package handles generic containers, and modules such as @testcontainers/postgresql and @testcontainers/redis add service-specific defaults.

Each container gets a random host port, so parallel runs on one machine never collide over port 5432. A sidecar container called Ryuk, the Testcontainers resource reaper, removes containers when the test process exits, even after a crash. We pin the image tag to the major version production runs, for example postgres:17-alpine, or pgvector/pgvector:pg17 when a schema needs the vector extension.

How do we start one PostgreSQL container for the whole Vitest run?

We start the container in a Vitest globalSetup file, which runs once in the main process before any test worker starts. Starting a container per test file multiplies startup cost by the number of files, and most of that cost is PostgreSQL initialising, not Docker.

Our setup file creates a PostgreSqlContainer from the postgres:17-alpine image with a database named app_template, mounts the data directory on tmpfs, and passes fsync=off, synchronous_commit=off and full_page_writes=off as server flags. It then applies every Drizzle ORM migration once to app_template with migrate() from drizzle-orm/node-postgres/migrator, closes that pool, and locks the template with ALTER DATABASE app_template WITH ALLOW_CONNECTIONS false.

Vitest workers do not reliably see process.env changes made in globalSetup. In Vitest 3 and later, the setup function receives a TestProject whose provide() method serialises the host, mapped port, user and password, and test files read them with inject('pg'). A ProvidedContext type declaration makes inject() fully typed, and the function returned from setup stops the container after the last test file.

We register that file only in a separate integration config, such as vitest.integration.config.ts, so the fast unit suite never needs Docker.

How do we isolate tests: transaction rollback, TRUNCATE, or a cloned database?

We give each test file its own database cloned from the migrated template, because clone isolation holds no matter what the code under test does with transactions or connections. The three common strategies compare like this:

• Transaction per test, rolled back: fast and parallel-safe, but it breaks when the code under test calls BEGIN and COMMIT itself or queries through a second pooled connection.

• TRUNCATE ... RESTART IDENTITY CASCADE between tests: simple, but every file shares one database, so files cannot run in parallel and the table list drifts from the schema.

• A database per test file, created with CREATE DATABASE ... TEMPLATE app_template: parallel-safe and independent of the code under test; it only fails while the template still has an open connection.

Transaction rollback is the trap teams hit first. PostgreSQL has no nested transactions: a second BEGIN inside an open transaction only raises a warning, and the next COMMIT commits the outer transaction, so the test's final ROLLBACK has nothing left to undo. A service that uses a connection pool also sends queries on other connections, which cannot see the test's uncommitted rows.

Our useTestDatabase() helper generates a unique name, test_ followed by a UUID without dashes, creates that database from app_template in beforeAll, hands a pg Pool to the test file, and in afterAll closes the pool and runs DROP DATABASE ... WITH (FORCE), which needs PostgreSQL 13 or later. pg.Pool connects lazily, so creating the pool before the database exists is safe.

Tests inside one file share the database, so each test creates its own data, such as unique email addresses, instead of resetting tables. A test that creates two users with the same email and expects PostgreSQL error code 23505, unique_violation, fails the moment the unique index goes missing from the migrations. A mocked repository can never produce that error.

How do we keep Testcontainers fast enough to run on every change?

We keep Testcontainers fast by paying for container startup and migrations once per run, and by switching off durability work that a throwaway database does not need.

tmpfs for the data directory: withTmpFs() mounts PostgreSQL's data directory in memory. The path must match the image's PGDATA, which is /var/lib/postgresql/data in the official postgres:17 images, so we check it again before moving to a newer major version.

fsync=off, synchronous_commit=off and full_page_writes=off: these PostgreSQL server settings skip the disk flushes that protect committed data from a crash. They are unsafe in production and pointless in a container that is deleted after the run.

Template cloning instead of migrating per file: CREATE DATABASE ... TEMPLATE copies the migrated database at the storage level, so the fiftieth migration does not slow down every test file.

For local watch mode, withReuse() lets Testcontainers reattach to a running container with the same configuration. We use it only outside CI, because a reused template database must be unlocked and re-migrated whenever migrations change, and CI should always start from a clean container.

How do we run Testcontainers in GitHub Actions?

We run Testcontainers on GitHub-hosted ubuntu-latest runners, which ship with Docker Engine, so the same test command works in CI with no extra setup. The workflow checks out the code, installs Node.js 24 with actions/setup-node, runs npm ci, and runs vitest with the integration config.

The alternative is a services: block in the workflow, which starts a PostgreSQL container next to the job. We prefer Testcontainers because the database version, server flags and migration step live in test code that runs identically on a laptop and in CI, instead of being duplicated in workflow YAML.

GitHub's macOS runners do not include Docker, so integration jobs belong on Linux runners. The first run of a job pays for pulling the image, so a small, pinned tag such as postgres:17-alpine keeps that cost down.

FAQ

Q: Is Testcontainers slower than mocking the database?

A: Yes, per test, because every query makes a real round trip to PostgreSQL. Starting one container per run, migrating once, and cloning a template per file limits the fixed cost to one container startup per run and one clone per test file.

Q: Do we still need unit tests with mocks?

A: Yes. Pure business logic is faster and clearer to test without a database. We use Testcontainers for code whose correctness depends on SQL, constraints, migrations or transactions, and plain unit tests for everything else.

Q: Can Testcontainers run without Docker Desktop?

A: Yes. Testcontainers works with any Docker-API-compatible runtime, including Docker Engine on Linux, Colima, OrbStack, and Podman through its Docker-compatible socket. Rootless or remote setups may need the DOCKER_HOST environment variable pointed at the right socket.

Q: Why does CREATE DATABASE ... TEMPLATE fail with "source database is being accessed by other users"?

A: PostgreSQL refuses to clone a database that has open connections. Close every pool that touched the template before cloning, and set ALLOW_CONNECTIONS false on the template so a stray connection cannot reopen it.

Q: Does this setup work with NestJS or Prisma?

A: Yes. In NestJS, we replace the database provider in Test.createTestingModule() with overrideProvider() and a pool that points at the cloned database. With Prisma, we run prisma migrate deploy against the template database in globalSetup and give PrismaClient the cloned database's connection URL.