Dark navy graphic with orange accents reading Testcontainers for Postgres

Testcontainers for Postgres: Make Your Tests Actually Fail

Byalexrusin

I have a set of isolation tests that check one account cannot read another account’s data. They pass. So I opened the migration file, commented out the FORCE ROW LEVEL SECURITY statement, and ran them again. Still green. The tests were not badly written. They were pointed at my dev database, which Docker Compose migrated days ago. Testcontainers for Postgres fixes that by giving every test run a database it owns.

🚀 Complete JavaScript Guide (Beginner + Advanced)

🚀 NodeJS – The Complete Guide (MVC, REST APIs, GraphQL, Deno)

A test that cannot fail

Editing a migration file does not change a database that has already been migrated. The guarantee was still switched on down there, and the test had no way to see what I had just done. Then I brought the database down with docker compose down and ran the suite again. Nothing failed. It could not even run.

Both halves say the same thing. That suite was not checking the isolation rule. It was checking that a connection string still worked. Claude Code wrote those tests against the dev database because that is the connection string in the project, which is a completely reasonable thing to have done. It is also not maintainable. State accumulates. Two people run the suite at once. The database ends up older than the code it is supposed to test.

Mocks and SQLite do not test your database

Mocking the data access layer works when the subject is your code. A service, an adapter, a client. It is useless when the subject is your database. Constraints, policies, indexes, cascade rules, transaction behavior. None of that lives in your source. It lives in the engine.

In-memory databases have the same hole. If you are on MySQL you can point tests at SQLite, and that works for a demo project. On a real one it falls apart fast, because the constraints, policies, and indexes you care about do not transfer between engines. Some of them do not exist in SQLite at all. The fix is not a better mock or a cleaner in-memory database. It is a real database.

Wiring Testcontainers for Postgres into the run

Your test suite starts a real Postgres in a Docker container, gets the connection string, and destroys the container when the run ends. That is the whole idea. The database is disposable and it belongs to this run. Not a shared server, not a service configured separately in CI, not a Compose file someone forgot to update. Three things follow. It is real, so real behavior shows up. It is fresh, so runs stop being order dependent. It is yours, so two branches in CI mean two containers instead of a collision.

I asked Claude Code for a Vitest global setup file that starts a Postgres container, sets DATABASE_URL to the container connection string, runs prisma migrate deploy against it, and stops the container in teardown. The instruction that mattered most was a negative one: do not add retry or wait-for-ready logic. The library already waits for the container to accept connections before handing you the object. Without that line you get a polling loop bolted on top, not because the agent is careless, but because “wait for the database” is an overwhelmingly common pattern in its training data.

// tests/global-setup.ts
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
import { execSync } from 'node:child_process';

let container: StartedPostgreSqlContainer;

export async function setup() {
  container = await new PostgreSqlContainer('postgres:16').start();
  process.env.DATABASE_URL = container.getConnectionUri();

  execSync('npx prisma migrate deploy', { stdio: 'inherit' });
}

export async function teardown() {
  await container.stop();
}

migrate deploy, not migrate dev

migrate dev is interactive and will try to generate migrations. Here you want to apply exactly what is committed and nothing else. The file also has to be registered in vitest.config.ts, which is what actually makes it run.

// vitest.config.ts
export default defineConfig({
  test: {
    globalSetup: './tests/global-setup.ts',
    setupFiles: ['./tests/setup.ts'],
  },
});

My first run failed, and it was a good failure. The isolation tests need two accounts with data in them, and that data comes from the seed script I normally run by hand. Fresh container, no seed, nothing to isolate. After the seed ran, the suite went green against a container that did not exist ninety seconds earlier. Then I commented out FORCE ROW LEVEL SECURITY again and the suite went red: account B read account A’s record, and findUnique returned a person where the test expected null. That test finally did its job.

Truncate between tests, and move seeding into them

The container is fresh per run, not per test. The moment I added a second file that creates a person for account A using the same key the seeder uses, I got a collision. Tests share a database, so they are coupled, and one only passes if the other has not run yet. There are three ways out, and this is a real decision rather than a best practice. Truncate every table between tests: simple, fast enough, and you have to remember to include new tables. Wrap each test in a transaction and roll it back: fastest, but it breaks anything that manages its own transaction, which is exactly what my withAccount wrapper does. A container per test file: total isolation, seconds per file. I went with truncation.

// tests/setup.ts
import { beforeEach } from 'vitest';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const rows = await prisma.$queryRaw<{ table_name: string }[]>`
  SELECT table_name FROM information_schema.tables
  WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
`;

const tables = rows
  .map((r) => r.table_name)
  .filter((name) => name !== '_prisma_migrations');

beforeEach(async () => {
  await prisma.$executeRawUnsafe(
    `TRUNCATE TABLE ${tables.map((t) => `"${t}"`).join(', ')} RESTART IDENTITY CASCADE`
  );
});

Two details carry weight here. Skipping _prisma_migrations keeps Prisma from believing the schema was never applied. RESTART IDENTITY resets the increment counters so ids start at one in every test, which stops your assertions drifting with run order. And the part I did not plan for: truncation wipes the seed data too, so the first test empties everything the rest were relying on. Seeding had to move out of global setup and into the tests that need it. After that the suite was order independent.

The honest costs, and CI

You need Docker wherever tests run. Locally that is fine, and GitHub Actions is fine. On a locked-down build box it may be a conversation with whoever owns that box. Startup is not free either: eleven seconds cold on my machine, about four warm with the image cached. That is per run, not per test, but if your suite finishes in under a second today you will feel it. This also does not replace unit tests. Convert everything and you get a slow suite that mostly still tests your own functions, which never needed a database. Pure logic stays mocked. Failures also get noisier, because now there are two suspects, your code or the container. Most of the time it is your code, though the first few times it will not feel that way.

The CI side is smaller than people expect. No services block for Postgres, which would only hand you a second database nothing connects to, and no DATABASE_URL secret, because the connection string comes from global setup.

# .github/workflows/test.yml
name: test
on: push

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 20
      - run: npm install
      - run: npm run test

Nothing about the database appears in that file, because the database is a detail of the test suite now, which is where it belongs. Same Postgres version, same migrations, same seed on the runner and on my laptop, because it is the same setup code either way.

Key takeaways

  • A test pointed at your dev database cannot see a schema change you have not applied, so it will pass whether the rule holds or not.
  • Mocks and in-memory databases cannot test constraints, policies, indexes, or transaction behavior, because those live in the engine rather than in your code.
  • A container created in your test setup, migrated from scratch and destroyed at the end, makes database guarantees fail loudly when you break them.
  • A fresh container per run still leaves tests coupled, so pick truncation, transaction rollback, or a container per file, and move seeding into the tests that need the data.
  • Break the rule on purpose and watch the test go red, because a test you have never seen fail is a test you are guessing about.

Conclusion

Nothing about those isolation tests got better written. They got pointed at something true. That is the entire move, and it applies to every guarantee that lives in your schema rather than your source: unique constraints, cascade deletes, check constraints, RLS policies. If Postgres enforces it, only Postgres can test it, and Testcontainers for Postgres is how you get one that belongs to the run.

Share this article

Similar Posts