# Testing

> Fast tests on in-memory SQLite with a fresh client per test.

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

Use this guide to set up tests for code that uses better-drizzle. There is no test mode and no mock client: you test it the way it runs, `better(db)` on top of a real Drizzle database. For most suites that database is SQLite in memory, which is fast enough to build from scratch for every test.

The examples use `bun:test`. Vitest has the same `describe`, `test`, `expect`, `beforeEach`, and `describe.skipIf`, so the code carries over as is. Under Vitest on Node, use better-sqlite3 instead of `bun:sqlite`.

**SQLite only:**

## In-memory SQLite

Put the database setup in one helper that every test file imports. It opens a new `:memory:` database, applies the schema, and returns a client:

**Bun**

```ts title="tests/db.ts"
import { Database } from 'bun:sqlite';
import { better } from 'better-drizzle';
import { drizzle } from 'drizzle-orm/bun-sqlite';
import { relations } from '../src/db/relations';

const ddl = `
CREATE TABLE users (
	id INTEGER PRIMARY KEY NOT NULL,
	email TEXT NOT NULL UNIQUE,
	name TEXT NOT NULL,
	active INTEGER NOT NULL
);
`;

export const createTestDb = () => {
	const sqlite = new Database(':memory:');
	sqlite.exec('PRAGMA foreign_keys = ON;');
	sqlite.exec(ddl);

	const db = drizzle({ client: sqlite, relations });
	const client = better(db);

	return { client, db, close: () => sqlite.close() };
};
```

**Node (better-sqlite3)**

```ts title="tests/db.ts"
import Database from 'better-sqlite3';
import { better } from 'better-drizzle';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { relations } from '../src/db/relations';

const ddl = `
CREATE TABLE users (
	id INTEGER PRIMARY KEY NOT NULL,
	email TEXT NOT NULL UNIQUE,
	name TEXT NOT NULL,
	active INTEGER NOT NULL
);
`;

export const createTestDb = () => {
	const sqlite = new Database(':memory:');
	sqlite.exec('PRAGMA foreign_keys = ON;');
	sqlite.exec(ddl);

	const db = drizzle({ client: sqlite, relations });
	const client = better(db);

	return { client, db, close: () => sqlite.close() };
};
```

SQLite leaves foreign keys off by default. Turn them on so tests fail on the same constraint violations as production.

## Applying the schema

Any way that produces the right tables works. Pick one and keep it in the helper:

* **Raw DDL** in the test helper. It is the fastest option, and you see exactly what the tests run against. The downside is that it can drift from your real migrations.
* **Your drizzle-kit migrations.** Run the generated SQL files with Drizzle's migrator, so tests use the same schema history as production (see below).
* **`drizzle-kit push`** against a database that lives longer than one test, such as a PostgreSQL test database. It cannot target a `:memory:` database, because that database only exists inside the test process.

**PostgreSQL**

```ts
import { migrate } from 'drizzle-orm/node-postgres/migrator';

await migrate(db, { migrationsFolder: 'drizzle' });
```

**MySQL**

```ts
import { drizzle } from 'drizzle-orm/mysql2';
import { migrate } from 'drizzle-orm/mysql2/migrator';

const db = drizzle(process.env.DATABASE_URL ?? '');
await migrate(db, { migrationsFolder: 'drizzle' });
```

**SQLite**

```ts
import { Database } from 'bun:sqlite';
import { drizzle } from 'drizzle-orm/bun-sqlite';
import { migrate } from 'drizzle-orm/bun-sqlite/migrator';

const db = drizzle({ client: new Database(':memory:') });
migrate(db, { migrationsFolder: 'drizzle' });
```

On Node, import from `drizzle-orm/better-sqlite3/migrator`.

## A fresh client per test

Create the database in `beforeEach` and close it in `afterEach`. Tests then share nothing, which means no ordering bugs and no cleanup code. The code under test takes the client as a parameter:

```ts title="src/users.ts"
import type { BetterDrizzleClient } from 'better-drizzle';
import type { relations } from './db/relations';

export const deactivateUser = (
	client: BetterDrizzleClient<typeof relations>,
	id: number,
) => client.users.update({ where: { id }, data: { active: false } });
```

```ts title="tests/users.test.ts"
import { afterEach, beforeEach, expect, test } from 'bun:test';
import { deactivateUser } from '../src/users';
import { createTestDb } from './db';

let ctx: ReturnType<typeof createTestDb>;

beforeEach(async () => {
	ctx = createTestDb();

	await ctx.client.users.createMany({
		data: [
			{ id: 1, email: 'alice@example.com', name: 'Alice', active: true },
			{ id: 2, email: 'bob@example.com', name: 'Bob', active: true },
		],
	});
});

afterEach(() => ctx.close());

test('deactivateUser flips the flag', async () => {
	await deactivateUser(ctx.client, 1);

	const user = await ctx.client.users.findUnique({ where: { id: 1 } });
	expect(user?.active).toBe(false);
});
```

To test code this way, it has to take the client as a parameter or from a factory. A module that imports a process-wide client directly cannot be pointed at a test database.

`better(...)` runs every plugin's `setup()` when it builds the client. With a fresh client per test, that happens once per test. On an in-memory database this costs very little.

## Seeding

Seed with `createMany`. It goes through the same path as your application writes, including [plugins](/docs/plugins/overview) such as timestamps:

```ts
const { count } = await ctx.client.users.createMany({
	data: [
		{ id: 1, email: 'alice@example.com', name: 'Alice', active: true },
		{ id: 2, email: 'bob@example.com', name: 'Bob', active: true },
	],
});

expect(count).toBe(2);
```

If a plugin should not touch fixture rows, seed through `ctx.client.users.$withoutPlugins().createMany(...)`, or insert with the raw Drizzle `db`.

To assert on hooks, plugins, and errors, see [test assertions](/docs/guides/testing-assertions). For a shared PostgreSQL database, see [testing on PostgreSQL](/docs/guides/testing-postgresql).

## Awaiting reads

Read methods (`findMany`, `findFirst`, `findOne`, `findUnique`, `count`, `exists`, `paginate`, `cursor`) return lazy thenables, like Drizzle's query builders: the query runs when you `await` it. Bun's `expect(...).resolves` / `.rejects` only accept native promises, so await the read first, or wrap it with `Promise.resolve(...)`. Helpers that already return promises, such as `.throw()` and `.explain()`, work with them directly:

```ts
expect(await ctx.client.users.findMany({ take: 2 })).toHaveLength(2);

// row locks are rejected on SQLite
await expect(
	Promise.resolve(ctx.client.users.findMany({ lock: 'update' })),
).rejects.toMatchObject({ code: BetterDrizzleErrorCode.LockNotSupported });
```

## Edge cases

**Why not mock the client**

Mocking `client.users.findMany` removes the part that tends to break:

* `where`, `select`, and `include` compile to SQL. A mock accepts any shape, including ones the compiler would reject.
* Relation loading, pagination metadata, and `count` in `createMany` come from real queries.
* Unique, foreign key, and `NOT NULL` violations only happen in a database.
* Transactions, savepoints, and `afterCommit` ordering need a real connection.

An in-memory SQLite test runs in milliseconds and covers all of this. Where you want a seam, keep it at your own service boundary, for example a `UsersService` interface. Do not mock the client. Keep pure business logic out of data-access functions so it can be tested with no database at all.