Testing on a Shared PostgreSQL

Isolate tests on a shared database with rolled-back transactions and run PostgreSQL-only suites.

Use these patterns when tests run against a shared database such as PostgreSQL, where rebuilding the schema for every test is too slow, or when a feature only exists on PostgreSQL.

Isolating tests with a rolled-back transaction

When rebuilding the database for each test is too slow, as with a shared PostgreSQL database, run each test inside a transaction and roll it back at the end. tx.rollback() throws a BetterDrizzleTransactionRollbackError, which transaction(...) passes on to its caller. The helper swallows that error and rethrows anything else:

tests/rollback.ts

import {
	type BetterDrizzleClient,
	BetterDrizzleTransactionRollbackError,
} from 'better-drizzle';
import type { relations } from '../src/db/relations';

type Client = BetterDrizzleClient<typeof relations>;
type Tx = Parameters<Parameters<Client['transaction']>[0]>[0];

export const rollbackAfter = async (
	client: Client,
	run: (tx: Tx) => Promise<void>,
) => {
	try {
		await client.transaction(async (tx) => {
			await run(tx);
			tx.rollback();
		});
	} catch (error) {
		if (!(error instanceof BetterDrizzleTransactionRollbackError))
			throw error;
	}
};

test('creates an order', async () => {
	await rollbackAfter(client, async (tx) => {
		await tx.orders.create({ data: { userId: 1, total: 40 } });

		expect(await tx.orders.count({ where: { userId: 1 } })).toBe(1);
	});
});

Things to know

Code under test must use the tx it receives. A query on the root client runs outside the transaction: on PostgreSQL it does not see the test's rows and is not rolled back.
Transactions opened by the code under test become savepoints inside the test transaction, so they roll back too.
afterCommit callbacks never run, because nothing commits. afterRollback callbacks and the afterTransactionRollback hook do run.
An explicit rollback() does not fire onTransactionError.
Tests that share one database must run one after another. Do not mark them concurrent.

For SQLite in memory, a fresh database per test is usually simpler than this pattern.

PostgreSQL-only features

Some features cannot run on SQLite: JSONB filters, array filters, and row locks. On SQLite they fail fast (JSONB_QUERY_UNSUPPORTED, ARRAY_QUERY_UNSUPPORTED, LOCK_NOT_SUPPORTED) instead of running a different query.

Put these tests in separate files that run only when DATABASE_URL is set. Local runs without a database skip them, and CI runs them against a real server:

tests/posts.pg.test.ts

import { afterAll, beforeAll, describe, expect, test } from 'bun:test';
import { better } from 'better-drizzle';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Client } from 'pg';
import { relations } from '../src/db/relations';
import { rollbackAfter } from './rollback';

const DATABASE_URL = process.env.DATABASE_URL;

describe.skipIf(!DATABASE_URL)('posts (PostgreSQL)', () => {
	let pg: Client;
	let client: ReturnType<typeof better<typeof relations>>;

	beforeAll(async () => {
		pg = new Client({ connectionString: DATABASE_URL });
		await pg.connect();
		client = better(drizzle({ client: pg, relations }));
	});

	afterAll(async () => {
		await pg?.end();
	});

	test('filters by array membership', async () => {
		await rollbackAfter(client, async (tx) => {
			await tx.posts.createMany({
				data: [
					{ id: 1, title: 'ORMs', tags: ['typescript', 'orm'] },
					{ id: 2, title: 'SQL', tags: ['sql'] },
				],
			});

			const rows = await tx.posts.findMany({
				where: { tags: { has: 'orm' } },
			});

			expect(rows.map(({ id }) => id)).toEqual([1]);
		});
	});
});

Apply the schema once before the suite runs, with drizzle-kit push or your migrations, or with DDL in beforeAll.

Testing on PostgreSQL

Isolating tests with a rolled-back transaction

Things to know

PostgreSQL-only features

On this page