vitest-drizzle-mock

Mock drizzle-orm database instances for fast, in-memory unit testing. No database required.

Works with all drizzle drivers: PostgreSQL (node-postgres, postgres.js), MySQL (mysql2), and SQLite (better-sqlite3, libsql).

Install

pnpm add -D vitest-drizzle-mock

Quick Start

import { drizzle } from "drizzle-orm/node-postgres";
import { mockDatabase } from "vitest-drizzle-mock";
import * as schema from "./schema";

// Create a mock drizzle instance — no connection needed
const db = drizzle.mock({ schema });
const mock = mockDatabase(db);

// Register mocks by table and operation
mock.onSelect(schema.users).respond([
  { id: 1, name: "Alice", email: "[email protected]" },
]);

// Execute the query — returns the mocked data
const users = await db.select().from(schema.users);

Usage with Vitest

import { describe, it, expect, beforeEach } from "vitest";
import { eq } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { mockDatabase, MockController } from "vitest-drizzle-mock";
import * as schema from "./schema";

describe("user service", () => {
  let db: ReturnType<typeof drizzle.mock<typeof schema>>;
  let mock: MockController<typeof db>;

  beforeEach(() => {
    db = drizzle.mock({ schema });
    mock = mockDatabase(db);
  });

  it("should find a user by id", async () => {
    mock.onSelect(schema.users).respond([
      { id: 1, name: "Alice", email: "[email protected]" },
    ]);

    const result = await db
      .select()
      .from(schema.users)
      .where(eq(schema.users.id, 1));

    expect(result).toEqual([
      { id: 1, name: "Alice", email: "[email protected]" },
    ]);
  });

  it("should create a user", async () => {
    mock
      .onInsert(schema.users)
      .values({ name: "x", email: "x" })
      .respond([{ id: 2, name: "Bob", email: "[email protected]" }]);

    const result = await db
      .insert(schema.users)
      .values({ name: "Bob", email: "[email protected]" })
      .returning();

    expect(result).toEqual([
      { id: 2, name: "Bob", email: "[email protected]" },
    ]);
  });

  it("should update a user", async () => {
    mock
      .onUpdate(schema.users)
      .set({ name: "x" })
      .respond({ rowCount: 1 });

    const result = await db
      .update(schema.users)
      .set({ name: "Alice Updated" })
      .where(eq(schema.users.id, 1));

    expect(result).toEqual({ rowCount: 1 });
  });
});

API

mockDatabase(db)

Takes a drizzle mock instance and returns a MockController.

const db = drizzle.mock({ schema });
const mock = mockDatabase(db);

Matching by Table

The simplest way to register mocks. Pass a table to match by operation type, with full column-name autocomplete and all columns optional.

mock.onSelect(table)

Match any select from the table, regardless of WHERE, ORDER BY, LIMIT, etc.

mock.onSelect(schema.users).respond([{ id: 1, name: "Alice" }]);

mock.onInsert(table)

Match any insert on the table. Optionally constrain which columns must be present with .values().

// Match any insert on the users table
mock.onInsert(schema.users).respond({ rowCount: 1 });

// Match inserts that include at least name and email
mock
  .onInsert(schema.users)
  .values({ name: "x", email: "x" })
  .respond({ rowCount: 1 });

Column matching uses subset logic: if the mock specifies {name}, it matches queries that insert {name} or {name, email}. But {name, email} won't match a query that only inserts {name}.

mock.onUpdate(table)

Match any update on the table. Optionally constrain which columns must be set with .set().

// Match any update on the users table
mock.onUpdate(schema.users).respond({ rowCount: 1 });

// Match updates that set at least the name column
mock
  .onUpdate(schema.users)
  .set({ name: "x" })
  .respond({ rowCount: 1 });

mock.onDelete(table)

mock.onDelete(schema.users).respond({ rowCount: 1 });

mock.onFindFirst(table) / mock.onFindMany(table)

Match relational queries.

mock
  .onFindFirst(schema.users)
  .respond({ id: 1, name: "Alice" });

mock
  .onFindMany(schema.users)
  .respond([{ id: 1, name: "Alice" }]);

findFirst and findMany are distinct — a findFirst mock won't match a findMany query.

.containingSql(expr)

Combine table matching with a SQL fragment check. Useful for distinguishing queries by specific WHERE conditions.

mock
  .onFindFirst(schema.users)
  .containingSql(eq(schema.users.id, 1))
  .respond({ id: 1, name: "Alice" });

mock
  .onFindFirst(schema.users)
  .containingSql(eq(schema.users.id, 2))
  .respond({ id: 2, name: "Bob" });

The fragment is checked with positional param matching, so different param values are distinguished. Only the specified fragment needs to be present — other conditions in the query are ignored.

Response Methods

.respond(data)

Return static data when the query matches.

mock.onSelect(schema.users).respond([]);

.respondOnce(data)

Queue one-time responses. Returns the builder for chaining.

mock
  .onSelect(schema.users)
  .respondOnce([{ id: 1, name: "First call" }])
  .respondOnce([{ id: 2, name: "Second call" }])
  .respond([]); // all subsequent calls

Responses are consumed in FIFO order. After the queue is exhausted, the persistent .respond() fallback kicks in (or throws "no mock registered" if none).

.respondWith(fn)

Return dynamic data based on the SQL and params.

mock
  .onSelect(schema.users)
  .respondWith((_sql, params) => {
    const id = params[0] as number;
    return [{ id, name: `User ${id}` }];
  });

.throw(error)

Simulate a database error.

mock
  .onSelect(schema.users)
  .throw(new Error("connection refused"));

.once()

Make the mock expire after a single match. Subsequent calls fall through to the next matching mock (or throw if none).

mock.onSelect(schema.users).respond([{ name: "Persistent" }]);
mock.onSelect(schema.users).once().respond([{ name: "Once" }]);

await db.select().from(schema.users); // → [{ name: "Once" }]
await db.select().from(schema.users); // → [{ name: "Persistent" }]

Mock Handles

.respond(), .respondWith(), and .throw() return a handle compatible with Vitest's spy matchers.

const findUsers = mock
  .onSelect(schema.users)
  .respond([{ id: 1, name: "Alice" }]);

const updateUser = mock
  .onUpdate(schema.users)
  .set({ name: "x" })
  .respond({ rowCount: 1 });

await db.select().from(schema.users);

expect(findUsers).toHaveBeenCalled();
expect(findUsers).toHaveBeenCalledTimes(1);
expect(updateUser).not.toHaveBeenCalled();

For .respondOnce() chains, use .handle() to get the spy handle:

const handle = mock
  .onInsert(schema.users)
  .respondOnce({ rowCount: 1 })
  .respondOnce({ rowCount: 2 })
  .handle();

expect(handle).toHaveBeenCalledTimes(2);

The handle exposes .mock.calls with [sql, params] tuples for deeper inspection:

const handle = mock.onSelect(schema.users).respond([]);

await db.select().from(schema.users).where(eq(schema.users.id, 42));

expect(handle.mock.calls[0][1]).toEqual([42]); // params

Call Recording

Every executed query is recorded in mock.calls, regardless of which mock matched.

mock.onSelect(schema.users).respond([]);
await db.select().from(schema.users);

expect(mock.calls).toHaveLength(1);
expect(mock.calls[0].sql).toContain('"users"');
expect(mock.calls[0].params).toEqual([]);

Reset Methods

mock.reset();      // Clear all mocks AND recorded calls
mock.resetMocks();  // Clear mocks only (calls are preserved)
mock.resetCalls();  // Clear recorded calls only (mocks are preserved)

Transactions

Transactions work out of the box. Mocks registered on the parent db are shared with the transaction context.

mock.onSelect(schema.users).respond([{ id: 1, name: "Alice" }]);
mock.onUpdate(schema.users).set({ name: "x" }).respond({ rowCount: 1 });

const result = await db.transaction(async (tx) => {
  const user = await tx.select().from(schema.users);
  await tx.update(schema.users).set({ name: "Updated" });
  return user;
});

Rollbacks are also supported:

await db.transaction(async (tx) => {
  tx.rollback();
});

Advanced Matching

For cases where table-based matching isn't specific enough, there are additional matching strategies.

mock.on(queryBuilder) — Exact SQL Matching

Match by exact SQL string generated from a drizzle query builder. Parameters are ignored by default.

mock
  .on(db.select().from(schema.users).where(eq(schema.users.id, 1)))
  .respond([{ id: 1, name: "Alice" }]);

.withExactParams()

Require parameters to match exactly.

mock
  .on(db.select().from(schema.users).where(eq(schema.users.id, 1)))
  .withExactParams()
  .respond([{ id: 1, name: "Alice" }]);

// Will NOT match — param is 2, not 1
await db.select().from(schema.users).where(eq(schema.users.id, 2));

.partial()

Match any query whose SQL starts with the registered SQL. Catches queries with additional WHERE, LIMIT, ORDER BY, etc.

mock
  .on(db.select().from(schema.users))
  .partial()
  .respond([]);

mock.on(callback) — Structural Matching with Callback

Match queries structurally using the drizzle query builder inside a callback. Useful when you need the full db API or when drizzle generates complex SQL that differs from what a simple builder produces.

import { anything } from "vitest-drizzle-mock";

mock
  .on(db => db.update(schema.users).set({ name: anything() }))
  .respond({ rowCount: 1 });

The anything() helper is a typed wildcard — it satisfies any column type without needing as any. The partial() helper makes all columns optional for insert values:

import { partial } from "vitest-drizzle-mock";

mock
  .on(db => db.insert(schema.users).values(partial({ name: "x" })))
  .respond({ rowCount: 1 });

mock.onSql(pattern) / mock.onSqlContaining(substring)

Match by regex or substring against the generated SQL.

mock.onSql(/from "users"/).respond([{ id: 1, name: "Alice" }]);
mock.onSqlContaining('"users"').respond([{ id: 1, name: "Alice" }]);

Match Resolution

When multiple mocks match a query, the most specific one wins:

| Priority | Matcher | Description | |----------|---------|-------------| | 1 (highest) | .on(query).withExactParams() | Exact SQL + exact params | | 2 | .on(query) | Exact SQL, any params | | 3 | .on(query).partial().withExactParams() | SQL prefix + exact params | | 4 | .on(query).partial() | SQL prefix, any params | | 5 | .containingSql() | Structural + SQL fragment | | 6 | .onInsert().values() / .onUpdate().set() | Table + operation + columns | | 7 | .onInsert() / .onSelect() / etc. | Table + operation only | | 8 (lowest) | .onSql() / .onSqlContaining() | Regex or substring |

Within the same priority level, the last registered mock wins.

Supported Drivers

| Driver | Import | Tested | |--------|--------|--------| | node-postgres | drizzle-orm/node-postgres | Yes | | postgres.js | drizzle-orm/postgres-js | Yes | | mysql2 | drizzle-orm/mysql2 | Yes | | better-sqlite3 | drizzle-orm/better-sqlite3 | Yes | | libsql | drizzle-orm/libsql | Yes |

All drivers use drizzle's built-in .mock() constructor — no real database connection is ever created.

How It Works

mockDatabase() intercepts the prepareQuery method on the drizzle session and wraps dialect build methods to capture query configs. When a query is executed, it is matched against registered mocks using either SQL string comparison or structural config comparison. If no mock matches, an error is thrown with the SQL and a list of registered mocks to help you debug.

License

MIT