Prisma is an incredibly powerful ORM for Typescript. You can make incredibly fast, without worrying about type breakages lurking in the codebase, and write code that makes so much more sense to look at than a SQL query. Prisma combined with tRPC and zod, is one of the magical experiences you can ask for when writing a Typescript based API. The end to end type safety is brilliant.

There’s just one problem, testing the backend code that uses Prisma.

Prisma offers a guide for unit testing functions that depend on Prisma, but this code pattern quickly becomes a nightmare in practice. Having to mock every single call to the Prisma library makes the tests highly cumbersome to write, and in my experience, if testing is a pain in the a**, developers don’t want to do it. But an even bigger concern is that mocking the Prisma client loses important functionality. For example, what if my query depends on finding every blog post published after a certain date that is passed in?

async function getBlogsAfter(date: Date) {
	const blogs = await prisma.blog.findMany({
		where: {
			publishedAt: {
				gte: date,
			},
		},
	});
	return blogs;
}

If I were to then unit test this function with the strategy documented by Prisma, it would look like the following,

it("Should return the last 3 months of blogs", async () => {
	const after = new Date(Date.now() - (1000 * 60 * 60 * 24 * 90));
  prismaMock.blog.findMany.mockResolvedValue([ { title: "Lorem ipsum", ... }, ... ]);
  await expect(getBlogsAfter(after)).resolves.toContain(...)
});

But what am I actually proving with this test? That if I override await prisma.blog.findMany({, with the exact data I want returned, it will return it. This test is useless.

This leads us to integration testing. In an ideal world, we could use an in-memory database for maximum efficiency, but pg-mem is still not supported by prisma. This means we need to use a real Postgres database. Prisma offers documentation on setting up integration testing, but I wanted to offer a small twist you might find useful.

Instead of testing against (& wiping) the database you’re using locally for development, you can create multiple schemas within your database, isolated to the tests you’re running. Here is some general pseudo-code you can follow,

import { execSync } from "child_process";
import { join } from "path";
import { URL } from "url";
import { randomUUID } from "crypto";
import { PrismaClient } from "@prisma/client";
import { config } from "dotenv";

const prismaEnvPath = join(__dirname, ".env");
config({ path: prismaEnvPath });

const generateDatabaseURL = (schema: string) => {
  if (!process.env.DATABASE_URL) {
    throw new Error("please provide a database url");
  }
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.append("schema", schema);
  return url.toString();
};

const schemaId = `test-${randomUUID()}`;
const prismaBinaryPath = join(__dirname, "node_modules", ".bin", "prisma");
const prismaSchemaPath = join(__dirname, "prisma", "schema.prisma");

const url = generateDatabaseURL(schemaId);
process.env.DATABASE_URL = url;

const prisma = new PrismaClient({
  datasources: { db: { url } },
});

jest.mock("src/your-prisma-client-import", () => ({
  __esModule: true,
  default: prisma,
}));

beforeAll(() => {
  execSync(`${prismaBinaryPath} db push --schema ${prismaSchemaPath}`, {
    env: {
      ...process.env,
      DATABASE_URL: generateDatabaseURL(schemaId),
    },
  });
});

afterAll(async () => {
  await prisma.$executeRawUnsafe(`DROP SCHEMA IF EXISTS "${schemaId}" CASCADE;`);
  await prisma.$disconnect();
});