Nov 18, 20269 min read

Node.js Integration Tests with Postgres: Real Databases in Vitest

How to run Node.js and TypeScript integration tests against a real Postgres instead of mocks, keep tests isolated when files run in parallel, apply migrations once, and run the same suite in CI.

Illustration for Node.js Integration Tests with Postgres: Real Databases in Vitest

An integration test for a Node.js backend runs your query code against a real Postgres, not a mock. That is the only way to find out that a CHECK constraint rejects the value the handler allows, that an index is missing for the ORDER BY, or that the migration and the code disagree about a column name.

A container starts a database in seconds. The work is in how tests share it: applying migrations once per run instead of once per file, keeping parallel test files from reading each other's rows, cleaning up between tests, and making the same setup work on a laptop and in CI.

This guide starts with Encore, where encore test creates the test database for you and tests call endpoints directly, and then builds the same setup by hand with Vitest, Testcontainers and a migrated template database.

Postgres test setups at a glance

Encore with encore testVitest with Testcontainers and templatesShared database with mocks for the rest
DatabaseSeparate test database per SQLDatabaseContainer started in globalSetupOne long-lived dev database
MigrationsFrom the service's migrations directoryApplied once to a template databaseApplied by hand
Parallel test filesWrite tests that only read their own rowsA fresh database per file from the templateTests collide
What you testEndpoint functions, called directlyYour query functions or HTTP layerWhatever the mocks allow
Setup codevite.config.ts alias and a test scriptglobalSetup, a database helper, DockerLittle, until it flakes

How do you write Postgres integration tests with Encore?

Encore

Encore is a backend framework with built-in infrastructure. A database is declared in the service that owns it, together with its migrations, and encore test sets up a separate test database for each one before it starts Vitest. The test databases skip fsync and use an in-memory filesystem, so a test that writes to Postgres runs close to the speed of a unit test.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create shop --example=ts/hello-world$ cd shop && npm install -D vitest$ mkdir -p orders/migrations

The service under test

The schema enforces the rule the tests care about, a positive total:

CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id TEXT NOT NULL, total_cents INTEGER NOT NULL CHECK (total_cents > 0), status TEXT NOT NULL DEFAULT 'pending', created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX orders_customer_idx ON orders (customer_id, created_at DESC);

The endpoints are plain async functions, which is what lets a test call them without an HTTP client:

// orders/orders.ts import { api, APIError } from "encore.dev/api"; import { getAuthData } from "~encore/auth"; import { SQLDatabase } from "encore.dev/storage/sqldb"; const db = new SQLDatabase("orders", { migrations: "./migrations" }); export interface Order { id: number; customerId: string; totalCents: number; status: string; } export const create = api( { expose: true, auth: true, method: "POST", path: "/orders" }, async ({ totalCents }: { totalCents: number }): Promise<Order> => { const { userID } = getAuthData()!; if (totalCents <= 0) throw APIError.invalidArgument("totalCents must be positive"); const row = await db.queryRow<Order>` INSERT INTO orders (customer_id, total_cents) VALUES (${userID}, ${totalCents}) RETURNING id, customer_id AS "customerId", total_cents AS "totalCents", status`; return row!; }, ); export const get = api( { expose: true, auth: true, method: "GET", path: "/orders/:id" }, async ({ id }: { id: number }): Promise<Order> => { const { userID } = getAuthData()!; const row = await db.queryRow<Order>` SELECT id, customer_id AS "customerId", total_cents AS "totalCents", status FROM orders WHERE id = ${id} AND customer_id = ${userID}`; if (!row) throw APIError.notFound("order not found"); return row; }, ); export const listForCustomer = api( { expose: true, auth: true, method: "GET", path: "/orders" }, async (): Promise<{ orders: Order[] }> => { const { userID } = getAuthData()!; const orders: Order[] = []; for await (const row of db.query<Order>` SELECT id, customer_id AS "customerId", total_cents AS "totalCents", status FROM orders WHERE customer_id = ${userID} ORDER BY created_at DESC`) { orders.push(row); } return { orders }; }, );

The tests

Add "test": "vitest" to the scripts in package.json, and a vite.config.ts that resolves the generated ~encore imports:

/// <reference types="vitest" /> import { defineConfig } from "vite"; import path from "path"; export default defineConfig({ resolve: { alias: { "~encore": path.resolve(__dirname, "./encore.gen"), }, }, });

The tests import the endpoints and call them. Each test creates its own customer ID, so the tests stay independent even though they share the run's test database:

// orders/orders.test.ts import { randomUUID } from "node:crypto"; import { describe, expect, test, vi } from "vitest"; import { create, get, listForCustomer } from "./orders"; const authState = { userID: "" }; vi.mock("~encore/auth", () => ({ getAuthData: () => ({ userID: authState.userID }), })); // Each test uses its own customer ID, so tests never depend on rows // written by another test in the same database. const newCustomer = () => { authState.userID = `cus_${randomUUID()}`; return authState.userID; }; describe("orders", () => { test("create then get returns the stored order", async () => { newCustomer(); const created = await create({ totalCents: 4200 }); const fetched = await get({ id: created.id }); expect(fetched).toEqual({ ...created, status: "pending" }); }); test("rejects a non-positive total", async () => { newCustomer(); await expect(create({ totalCents: 0 })).rejects.toMatchObject({ code: "invalid_argument", }); }); test("lists only the customer's own orders, newest first", async () => { const alice = newCustomer(); const first = await create({ totalCents: 100 }); const second = await create({ totalCents: 200 }); newCustomer(); await create({ totalCents: 300 }); authState.userID = alice; const { orders } = await listForCustomer(); expect(orders.map((o) => o.id)).toEqual([second.id, first.id]); }); test("get on a missing order is not_found", async () => { newCustomer(); await expect(get({ id: 999_999_999 })).rejects.toMatchObject({ code: "not_found" }); }); });

Run them with encore test. In CI or any shell without a terminal, run CI=true encore test so Vitest exits instead of starting watch mode. The Encore Platform's deploy pipeline has a build and test phase, so the same suite runs before each deploy to your AWS or GCP account.

We type-checked this app and its tests and compiled it with encore check; the database step needs Docker, which our build machine lacks, so the same schema and assertions ran in the Vitest setup below against Postgres 17.

A coding agent that changes a query gets the same feedback a developer does: encore test runs the endpoint against a real migrated Postgres, so a column rename that breaks the query fails locally instead of in review.

Best fit: TypeScript backends that want database tests without writing container setup, and teams that want those tests to gate deploys.

How do you write Postgres integration tests with Vitest and Testcontainers?

Without a framework, the setup has three parts: a Vitest globalSetup that starts Postgres and migrates a template database once per run, a helper that gives each test file its own database copied from the template, and the tests themselves.

The global setup uses Testcontainers for Node.js to start Postgres, or a TEST_DATABASE_URL if one is set, so CI can use a service container instead:

// test/global-setup.ts import { readdir, readFile } from "node:fs/promises"; import path from "node:path"; import pg from "pg"; import { PostgreSqlContainer, type StartedPostgreSqlContainer } from "@testcontainers/postgresql"; import type { TestProject } from "vitest/node"; declare module "vitest" { export interface ProvidedContext { adminUrl: string; } } let container: StartedPostgreSqlContainer | undefined; // Runs once per `vitest` invocation: start Postgres, migrate a template database. export default async function setup(project: TestProject) { let adminUrl = process.env.TEST_DATABASE_URL; if (!adminUrl) { container = await new PostgreSqlContainer("postgres:17-alpine").start(); adminUrl = container.getConnectionUri(); } const admin = new pg.Client({ connectionString: adminUrl }); await admin.connect(); await admin.query("DROP DATABASE IF EXISTS app_template"); await admin.query("CREATE DATABASE app_template"); await admin.end(); const tpl = new pg.Client({ connectionString: withDb(adminUrl, "app_template") }); await tpl.connect(); const dir = path.resolve(import.meta.dirname, "migrations"); for (const file of (await readdir(dir)).sort()) { await tpl.query(await readFile(path.join(dir, file), "utf8")); } await tpl.end(); project.provide("adminUrl", adminUrl); return async () => { await container?.stop(); }; } export function withDb(url: string, db: string) { const u = new URL(url); u.pathname = `/${db}`; return u.toString(); }

CREATE DATABASE ... TEMPLATE copies the migrated schema at the file level, which is much faster than rerunning migrations for every file. Postgres refuses to copy a template while other sessions are connected to it, so the setup closes its connection to app_template before any test starts:

// test/db.ts import { randomUUID } from "node:crypto"; import pg from "pg"; import { afterAll, inject } from "vitest"; import { withDb } from "./global-setup"; // Each test file gets a fresh copy of the migrated template, so files can // run in parallel without seeing each other's rows. export async function freshDatabase(): Promise<pg.Pool> { const adminUrl = inject("adminUrl"); const name = `test_${randomUUID().replaceAll("-", "")}`; const admin = new pg.Client({ connectionString: adminUrl }); await admin.connect(); await admin.query(`CREATE DATABASE ${name} TEMPLATE app_template`); await admin.end(); const pool = new pg.Pool({ connectionString: withDb(adminUrl, name) }); afterAll(async () => { await pool.end(); const c = new pg.Client({ connectionString: adminUrl }); await c.connect(); await c.query(`DROP DATABASE IF EXISTS ${name}`); await c.end(); }); return pool; }

Each test file asks for its own database, and beforeEach truncates so tests inside the file start empty:

// test/orders.test.ts import { beforeAll, beforeEach, expect, test } from "vitest"; import type { Pool } from "pg"; import { createOrder } from "../src/orders"; import { freshDatabase } from "./db"; let pool: Pool; beforeAll(async () => { pool = await freshDatabase(); }); beforeEach(async () => { await pool.query("TRUNCATE orders RESTART IDENTITY"); }); test("stores the order with a pending status", async () => { const order = await createOrder(pool, "cus_1", 4200); expect(order).toEqual({ id: 1, customerId: "cus_1", totalCents: 4200, status: "pending" }); }); test("the CHECK constraint rejects a zero total", async () => { await expect(createOrder(pool, "cus_1", 0)).rejects.toMatchObject({ code: "23514" }); });

Point Vitest at the global setup:

// vitest.config.ts import { defineConfig } from "vitest/config"; export default defineConfig({ test: { globalSetup: ["./test/global-setup.ts"], hookTimeout: 60_000, }, });

We ran this setup against a local Postgres 17 through TEST_DATABASE_URL with two copies of the test file in parallel: both files got their own database, the IDs restarted at 1 in each, and the zero total failed with SQLSTATE 23514, the check violation.

The container lifecycle, the migration runner, and the database for each deployed environment are yours to keep consistent.

Best fit: Node.js services on Express, Fastify or NestJS with their own migration tooling, and any project that needs full control over the test database lifecycle.

How to choose

Test against a real Postgres in both setups, and keep tests independent of each other's rows. Build the Testcontainers and template setup when you already run your own migrations and infrastructure, since it gives you per-file isolation for a few dozen lines. Pick Encore when you want encore test to create the test databases from the code that declares them, with the same suite running before each deploy to your own AWS or GCP account.

Frequently asked questions

Should Node.js integration tests use a real Postgres database?

Yes. For code whose behavior depends on SQL. A mock or an in-memory substitute does not enforce your CHECK constraints, unique indexes, transactions or Postgres-specific syntax, so the tests pass while the real query fails. A real Postgres in a container starts in a few seconds and catches those failures.

How do you isolate Postgres integration tests that run in parallel?

Give each test file its own database. Migrate a template database once per run, then create each file's database with CREATE DATABASE ... TEMPLATE, which copies the migrated schema without rerunning migrations. Within a file, truncate the tables in beforeEach or write every test so it only reads rows it created.

How do you use Testcontainers with Vitest?

Start the PostgreSqlContainer in a Vitest globalSetup file, pass the connection string to the tests with project.provide, read it with inject, and stop the container in the function globalSetup returns. Testcontainers needs a Docker-compatible runtime on the machine or CI runner.

How does Encore run database tests in TypeScript?

encore test starts the test runner configured in package.json, such as Vitest, after setting up a separate test database for each SQLDatabase in the app. The test databases skip fsync and use an in-memory filesystem for speed, and tests call the endpoint functions directly.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders