Testing Code That Uses Prisma

Prisma Intermediate/Advanced

Chapter 5 · Testing Code That Uses Prisma

Code that talks to a database is awkward to test: tests need data, they can interfere with each other, and a real database is slower than pure functions. There are two main approaches — replace Prisma Client with a mock, or run the tests against a real test database. They catch different kinds of bug, and most projects end up using both.

Examples use Vitest, which works smoothly with the ES modules Prisma 7 uses. Jest works too; Prisma's own documentation uses Jest with jest-mock-extended.

The Code Under Test

// src/posts.ts
import type { PrismaClient } from "../generated/prisma/client";

export function slugify(title: string) {
  return title.toLowerCase().trim().replace(/[^a-z0-9]+/g, "-").replace(/^-|-$/g, "");
}

export async function createPost(
  db: PrismaClient,
  input: { title: string; content: string; authorId: number },
) {
  if (input.title.trim().length < 3) throw new Error("Title too short");
  return db.post.create({
    data: { ...input, slug: slugify(input.title), published: false },
  });
}

Notice createPost receives the client as a parameter (db) rather than importing it. This is dependency injection: the app passes the real client, a test passes a fake one. It's the simplest way to make database code testable.

Separate pure logic first

slugify doesn't touch the database at all, so it needs neither a mock nor a test database — just ordinary tests. The more logic you move into plain functions like this, the less database testing you need.

Approach 1: Mocking Prisma Client

npm install -D vitest vitest-mock-extended

// test/posts.unit.test.ts
import { describe, it, expect, beforeEach } from "vitest";
import { mockDeep, mockReset, type DeepMockProxy } from "vitest-mock-extended";
import type { PrismaClient } from "../generated/prisma/client";
import { createPost } from "../src/posts";

const db: DeepMockProxy<PrismaClient> = mockDeep<PrismaClient>();
beforeEach(() => mockReset(db));   // no leftovers between tests

describe("createPost", () => {
  it("saves a draft with a slug built from the title", async () => {
    db.post.create.mockResolvedValue({ id: 1, slug: "hello-world" } as any);

    await createPost(db, { title: "Hello, World!", content: "...", authorId: 3 });

    expect(db.post.create).toHaveBeenCalledWith({
      data: expect.objectContaining({ slug: "hello-world", published: false, authorId: 3 }),
    });
  });

  it("rejects a short title without touching the database", async () => {
    await expect(createPost(db, { title: "Hi", content: "", authorId: 3 }))
      .rejects.toThrow("Title too short");
    expect(db.post.create).not.toHaveBeenCalled();
  });
});

mockDeep builds a fake client where every model and method exists and is typed. You decide what each method returns with mockResolvedValue, and check how it was called with toHaveBeenCalledWith. No database is involved, so these tests run in milliseconds.

If your code imports a shared client

Code written as in Prisma Fundamentals imports prisma from lib/prisma directly. Vitest can swap that module for a mock:

import { vi } from "vitest";
import { mockDeep } from "vitest-mock-extended";
import type { PrismaClient } from "../generated/prisma/client";

vi.mock("../lib/prisma", () => ({ prisma: mockDeep<PrismaClient>() }));

import { prisma } from "../lib/prisma";   // now the mock

It works, but passing the client in is usually easier to read and doesn't depend on module-mocking rules.

What mocks can't tell you

A mock only knows what you told it

The mocked create returns whatever you configured. It won't reject a duplicate slug, won't enforce a foreign key, won't apply a default, and won't notice a typo in a where clause that the real database would treat very differently. A test can pass against the mock while the real query fails. Mock tests check your logic; they don't check your queries.

Approach 2: Integration Tests Against a Real Database

Prisma's documentation recommends running a separate database, usually in Docker, with its own connection string, and applying migrations before the tests run.

# docker-compose.test.yml
services:
  db-test:
    image: postgres:17
    environment:
      POSTGRES_USER: test
      POSTGRES_PASSWORD: test
      POSTGRES_DB: blog_test
    ports:
      - "5433:5432"        # a different port from the development database

# .env.test
DATABASE_URL="postgresql://test:test@localhost:5433/blog_test"

// package.json
"scripts": {
  "test:unit": "vitest run test/*.unit.test.ts",
  "test:int":  "docker compose -f docker-compose.test.yml up -d && dotenv -e .env.test -- prisma migrate deploy && dotenv -e .env.test -- vitest run test/*.int.test.ts --no-file-parallelism"
}

dotenv -e .env.test (from the dotenv-cli package) makes every step use the test database. migrate deploy applies existing migrations without generating new ones, exactly as in production. --no-file-parallelism runs test files one after another so they don't fight over the same tables.

Make sure you're pointing at the test database

Integration tests delete data. Add a check at the top of your setup file — for example, refuse to run unless DATABASE_URL contains _test — so a mistake in your environment can never wipe your development data.

A clean slate for every test

Each test should start from a known state. Deleting table by table (in child-to-parent order, as in Chapter 4) works for small schemas; Prisma's docs suggest truncating every table for anything larger:

// test/reset.ts  (PostgreSQL)
import { prisma } from "../lib/prisma";

export async function resetDatabase() {
  if (!process.env.DATABASE_URL?.includes("_test")) throw new Error("Not a test database!");

  const tables = await prisma.$queryRaw<{ tablename: string }[]>`
    SELECT tablename FROM pg_tables
    WHERE schemaname = 'public' AND tablename <> '_prisma_migrations'
  `;
  const list = tables.map((t) => `"${t.tablename}"`).join(", ");
  await prisma.$executeRawUnsafe(`TRUNCATE TABLE ${list} RESTART IDENTITY CASCADE`);
}

This uses $executeRawUnsafe from Chapter 3 — acceptable here because the table names come from the database's own catalogue, never from user input. CASCADE removes the need for a deletion order, RESTART IDENTITY resets auto-increment IDs, and _prisma_migrations is kept so the migration history survives.

// test/posts.int.test.ts
import { describe, it, expect, beforeEach, afterAll } from "vitest";
import { prisma } from "../lib/prisma";
import { createPost } from "../src/posts";
import { resetDatabase } from "./reset";

beforeEach(resetDatabase);
afterAll(() => prisma.$disconnect());

it("refuses a second post with the same slug", async () => {
  const author = await prisma.user.create({ data: { email: "a@test.local", name: "A" } });
  const input = { title: "Hello World", content: "...", authorId: author.id };

  await createPost(prisma, input);
  await expect(createPost(prisma, input)).rejects.toMatchObject({ code: "P2002" });
});

This is exactly the bug a mock can't find: createPost has no handling for duplicate slugs, and only a real database with a real @unique constraint reveals it.

Choosing Between Them

	Mocked client	Real test database
Speed	Milliseconds	Seconds
Setup	A library	Docker, a second database, migrations
Checks	Your branching, validation, which queries you call	That the queries really work: constraints, relations, defaults, transactions
Risk	Passing tests for broken queries	Slow or flaky tests if isolation is sloppy

A practical split

Test pure logic with plain unit tests, use mocks for code with lots of branches, and give every query that matters — especially anything with transactions, constraints or raw SQL — at least one integration test against a real database.

Hands-On Exercises

Exercise 1

Using a mocked client, test Chapter 1's savePost (optimistic concurrency): one test where updateMany reports count: 1, and one where it reports count: 0 and the function throws.

📄 View solution

Exercise 2

Fix createPost so a duplicate slug gets a numeric suffix (hello-world-2), and prove it with an integration test. Explain why the mocked test from the chapter would still pass on the unfixed version.

📄 View solution

Exercise 3

Write integration tests for Chapter 1's deleteUserKeepingPosts: prove that a plain user.delete on a user with posts fails with P2003, and that the function succeeds — moving the posts to the placeholder account and removing the user's profile along with them. Why couldn't a mock test catch either behaviour?

📄 View solution

Chapter 5 Quick Reference

Pass the client in as a parameter (dependency injection) to make code easy to test
Mocks: mockDeep<PrismaClient>(), mockReset in beforeEach, mockResolvedValue, toHaveBeenCalledWith
Mocks test your logic, not your queries — no constraints, relations or defaults
Integration: a separate Docker database, .env.test, migrate deploy, then tests
Reset between tests with TRUNCATE ... RESTART IDENTITY CASCADE, keeping _prisma_migrations
Guard against running destructive setup on a non-test database; run test files one at a time