Database & ORM Mocking

Every repository, service, and query builder in a modern JavaScript backend eventually talks to a database, and that dependency is the single hardest thing to isolate well. A live Postgres or MongoDB instance makes tests slow, order-dependent, and prone to cross-contamination under parallel CI; a naive mock of the ORM makes tests fast but liable to pass against SQL that would never execute in production. This topic area sits under advanced mocking and service isolation patterns and draws the line between two legitimate strategies — replacing the database client with a programmable double, and running a real but ephemeral engine in memory — then shows exactly when each one earns its keep. Across four focused guides we cover Prisma client mocking in Vitest, in-memory SQLite for repository tests, stubbing Redis with an ioredis mock, and isolating MongoDB with mongodb-memory-server, all with transaction rollback per test and connection isolation that survives a fully parallel pipeline.

Architectural Scope & Boundaries

The defining decision in database testing is where you cut the seam. You can cut it high, at the ORM or client object, and hand the system under test a fake that returns canned rows without any query ever being parsed. Or you can cut it low, below the driver, and let the real query engine execute against an in-memory or throwaway instance so the SQL, indexes, and constraints are genuinely exercised. Both are valid; they answer different questions. A high seam answers “does my service call the repository correctly and handle the shape it returns?” A low seam answers “does my query actually retrieve the right rows and respect the schema’s constraints?” Confusing the two is how teams end up with green suites that miss a broken WHERE clause or a violated unique index.

This topic area treats the ORM double and the in-memory engine as complementary tiers rather than competitors. Mock the client at the unit level, where you are testing branching logic, error handling, and orchestration and the database is merely a collaborator. Reach for a real in-memory engine at the integration level, where the query itself is the thing under test and a plausible fake would only give you false confidence. The boundary matters because the failure modes differ: a client mock can silently drift from the real driver’s return shape, while an in-memory engine can subtly diverge from your production dialect. Naming which risk you are accepting is half of designing the test.

The topic also draws a hard boundary around external, networked stores. When a repository fronts a service you do not own — a hosted search index, a payments ledger, a third-party analytics sink — the right tool is not a database double at all but request-level interception, which is the domain of external service simulation. Database and ORM mocking is specifically about the stores your application owns and whose schema you control: relational engines through an ORM, key-value caches through Redis, and document stores through MongoDB. Anything reached over HTTP belongs to a different seam and a different guide.

There is a third boundary that trips teams up more quietly: the difference between isolating a store and isolating a transaction. Two tests can hit the same in-memory engine and still interfere if they share a transaction scope or a connection, because uncommitted writes in one become visible to the other or the second test observes locks the first is holding. Isolation is therefore not a property of the database alone but of the triple database plus connection plus transaction. Every strategy in this topic area is really a way of guaranteeing that triple is private to a single test: the client mock gives each test its own programmable object, the in-memory engine gives each test either a fresh instance or a rolled-back transaction, and the memory server gives each worker its own process. Keeping that framing in mind prevents the classic mistake of assuming a fast in-memory engine is automatically isolated when the connection is still shared.

Finally, the seam you pick has a downstream effect on what a failing test tells you. A failure at the high seam points squarely at your code, because the double is deterministic — nothing else could have moved. A failure at the low seam is more informative but less pinpointed: it could be your query, your schema, or a genuine divergence between the ephemeral engine and production. That trade between precision of blame and fidelity of coverage is the throughline of every guide here, and it is why the topic treats the two seams as a deliberate portfolio rather than a single default.

Prerequisites

Before working through the guides below, confirm your project has the tooling and conventions each one assumes. The list is deliberately shared so every guide starts from the same baseline.

• Vitest 1.x installed and configured with a test block in vitest.config.ts.
• TypeScript 5.x with strict enabled, so mock return shapes are checked at compile time.
• An ORM or client under test — Prisma, Drizzle, a raw pg/better-sqlite3 repository, ioredis, or the MongoDB Node driver.
• A single place where the database client is constructed and exported, so a test can substitute or point it at an ephemeral instance.
• pool set to a value greater than one in Vitest (the default) so you actually exercise parallel isolation rather than accidentally serializing.
• Familiarity with the taxonomy of spies, stubs, mocks, and fakes from the parent overview, since the client double is a fake and the in-memory engine is closer to the real thing.

Step-by-Step Implementation

The following steps establish the patterns that all four guides reuse. Treat them as the scaffolding; each guide then specializes one store. The sequence is deliberate: first make the seam substitutable, then choose the seam per file, then guarantee isolation, then guarantee it survives parallelism, and only then assert. Skipping straight to assertions on top of a shared, un-substitutable client is the origin of most database test pain, because there is no clean point at which to insert a double or roll back a change.

Step 1 — Centralize client construction behind one module. Isolation is only possible if there is a single seam to substitute. Export the client from one file and import it everywhere, so a test can swap the export or repoint its connection string without touching call sites.

// src/db/client.ts — the single seam every test targets
import { PrismaClient } from '@prisma/client';

export const db = new PrismaClient({
  datasourceUrl: process.env.DATABASE_URL,
});

Step 2 — Decide the seam per test file, not per project. A unit test of a service imports a mocked db; an integration test of a query imports a real db bound to an in-memory engine. Encode the choice in the file, so the seam is explicit and reviewable.

// unit: mock the whole client module
import { vi } from 'vitest';
vi.mock('../db/client', () => ({
  db: { user: { findUnique: vi.fn() } },
}));

Step 3 — Wrap every integration test in a transaction and roll it back. The cleanest per-test isolation for a real relational engine is to open a transaction in beforeEach, run the test inside it, and roll back in afterEach. Nothing commits, so tests never see each other’s writes and there is no truncation cost between them.

// integration: a rollback-per-test harness
import { beforeEach, afterEach } from 'vitest';
import { db } from '../db/client';

beforeEach(async () => {
  await db.$executeRawUnsafe('BEGIN');
});

afterEach(async () => {
  await db.$executeRawUnsafe('ROLLBACK');
});

Step 4 — Give each worker its own connection. Under parallel CI, two workers sharing one connection will interleave statements and corrupt each other’s transactions. Bind the connection or database name to the Vitest worker id so every worker is hermetic.

// src/db/test-client.ts — one database per worker
import Database from 'better-sqlite3';

const workerId = process.env.VITEST_WORKER_ID ?? '0';
export const testDb = new Database(`file:memdb-${workerId}?mode=memory&cache=shared`);

Step 5 — Assert on both the return shape and the persisted state. A client mock lets you assert what the service did with the returned rows; an in-memory engine lets you re-query and assert what actually landed. Use each for the assertion it is good at, and never assert persistence against a mock that never persisted anything.

Step 6 — Make teardown unconditional and idempotent. The reset that restores isolation must run even when the test body throws, or a single failing test will poison every test after it. Put mock resets and transaction rollbacks in afterEach, close pooled connections and stop ephemeral servers in afterAll, and never gate teardown behind a condition that a failure could skip. A teardown that only runs on the happy path is not isolation; it is a race waiting to surface as a mysterious cascade of failures on CI that never reproduces locally.

Configuration Reference

The table summarizes the isolation strategy each guide adopts and the trade-off it accepts. Use it to pick a starting point before diving into a specific guide.

Read the table as a gradient rather than four unrelated rows. The further down you go, the more of the real engine you exercise and the more setup cost you pay, so the isolation unit grows coarser — from a per-file module mock, to a per-test transaction, to a per-worker process — to keep the cost bounded. The “best for” column is the question each row answers well, and the “main risk” column is the lie each row can tell if you push it past its remit: a Prisma mock lies about persistence, a SQLite test lies about dialect, an ioredis-mock lies about uncommon commands, and a memory server lies mostly about your patience if you boot it per test. Choosing well is a matter of asking which lie you can tolerate for the behavior you are trying to prove, and pairing it with the isolation unit that keeps the suite fast.

Verification & Assertions

Verification differs by seam. When you mock the client, you verify interactions: assert that findUnique was called with the expected where clause and that the service mapped the returned row correctly. When you run a real in-memory engine, you verify state: perform the write through the code path under test, then issue an independent read and assert the persisted rows, constraint violations, and index behavior. The trap is asserting persistence against a mock — a mock that returns a hard-coded row proves nothing about whether your INSERT would have succeeded. Match the assertion style to the seam or the test proves the wrong thing.

A useful discipline is to always read back through a different code path than the one that wrote. If the write went through the repository’s create method, verify it with a raw query rather than the repository’s own findById, so a bug that corrupts both symmetrically cannot hide. At the high seam the equivalent discipline is to assert on the exact argument object, not a loose subset, because a mock will happily accept a call that omitted a field the real query requires. In both cases the goal is the same: make the assertion sensitive to the failure you are actually worried about, rather than to a tautology that the double satisfies by construction.

// interaction assertion against a mocked client
import { describe, it, expect, vi } from 'vitest';
import { db } from '../db/client';
import { getUser } from './user-service';

it('queries by id', async () => {
  vi.mocked(db.user.findUnique).mockResolvedValue({ id: 'u1', name: 'Ada' } as any);
  await getUser('u1');
  expect(db.user.findUnique).toHaveBeenCalledWith({ where: { id: 'u1' } });
});

Edge Cases & Failure Modes

The recurring failure is shared mutable state leaking across tests. With a mock, that means a mockResolvedValue from one test bleeding into the next because vi.clearAllMocks() was never called; with a real engine, it means a committed row surviving because a test committed instead of rolling back. Both produce the same symptom — a test that passes alone but fails in a suite, or vice versa — and both are cured by making the reset unconditional in afterEach. A subtler failure is dialect divergence: an in-memory SQLite test passes because SQLite is permissive about a foreign key that Postgres would reject, so the constraint you were testing was never actually enforced. Where the query depends on production-specific behavior, prefer a throwaway instance of the real engine over a compatible stand-in, and reserve SQLite for schema-portable queries. Deciding when the integration test is even worth its cost is its own judgment call, covered in when to skip integration tests in favor of unit tests.

Three more failure modes recur often enough to name. The first is the auto-increment surprise: a test that hard-codes an expected primary key of 1 breaks the moment another test runs first, because sequences do not reset on rollback in every engine. Assert on values you inserted, never on generated ids. The second is time coupling: a row written with now() and later compared to a fixed timestamp is flaky by construction; freeze the clock so createdAt is deterministic. The third is async teardown races, where an afterEach that forgets to await its rollback lets the next test’s BEGIN interleave with the previous rollback, corrupting both — every teardown that touches the database must be awaited. Each of these passes in isolation and fails only under the ordering and parallelism of real CI, which is precisely why they are so corrosive to trust in the suite.

Performance & CI Impact

The performance profile of each strategy shapes how aggressively you can parallelize. A client mock costs essentially nothing — no process, no I/O — so mocked unit tests scale linearly with workers and dominate the fast base of the suite. An in-memory SQLite database is nearly as cheap because it never touches disk; the transaction-rollback harness adds microseconds per test rather than the tens of milliseconds a truncate-and-reseed cycle would. ioredis-mock is similarly in-process and free of network cost. The expensive outlier is mongodb-memory-server, which spins up a real mongod binary; amortize that by starting one instance per worker in global setup and reusing it, rather than per test. The same connection-pooling and leak discipline that governs HTTP doubles applies here — an unclosed database handle leaks across the run exactly like an unclosed fetch mock, a hazard covered in mocking fetch and axios in Vitest without memory leaks. Close every client in afterAll, bind instances to the worker, and the database tier stays fast even at high parallelism.

The CI impact compounds with the isolation choice you make. A per-test fresh database multiplies setup cost by the number of tests, which is fine for microsecond-cheap SQLite but catastrophic for a mongod boot; a per-test rollback keeps setup constant regardless of test count, which is why it is the default recommendation for any engine whose startup is not free. The second-order effect is on failure diagnosis under parallelism: when a mocked suite goes red it is almost always the code, so triage is fast, whereas a red real-engine suite may reflect a flaky binary download or a port collision, so it needs richer logging to stay debuggable. Budget for that by capturing the ephemeral engine’s own logs in CI artifacts and by pinning binary versions, so the extra fidelity of the low seam does not cost you extra hours chasing infrastructure noise. Tuned this way, a database suite of thousands of tests can still complete in the time an untuned handful of container-backed tests would take, and the pipeline stays a gate people believe rather than a slow lane they learn to ignore.

In This Topic Area

• Mocking Prisma Client in Vitest — replace the Prisma client with a typed, deeply-mocked double so service logic is tested without a database.
• Using an In-Memory SQLite for Repository Tests — run real SQL against an ephemeral engine with transaction rollback per test.
• Stubbing Redis with ioredis-mock — fake a Redis instance in process to test cache logic, TTL, and eviction branches.
• Isolating MongoDB with mongodb-memory-server — give each worker a throwaway MongoDB so aggregations and indexes run against the real engine.

Related

• Back to Advanced Mocking & Service Isolation Patterns
• External service simulation — the right seam when the store is a networked service you do not own.
• Mocking fetch and axios in Vitest without memory leaks — the same teardown discipline applied to the network tier.
• When to skip integration tests in favor of unit tests — decide whether a real-engine test earns its cost.