# Testing on PostgreSQL

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

URL: https://better-drizzle.com/docs/guides/testing-postgresql

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:

```ts title="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;
	}
};
```

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

**SQLite only:**

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

**PostgreSQL only:**

## PostgreSQL-only features

Some features cannot run on SQLite: [JSONB filters](/docs/querying/jsonb), [array filters](/docs/querying/arrays), and [row locks](/docs/advanced/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:

```ts title="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`.