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.

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.
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.

Run in your terminal to get started locally.
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 };
},
);
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.
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.
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.
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.
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.
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.
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.