Prisma

Testing Prisma ORM: Unit Tests with Mocking and Integration Tests with TestContainers

HelpMeTest

HelpMeTest

— 6 min read
Testing Prisma ORM: Unit Tests with Mocking and Integration Tests with TestContainers

This post covers two complementary strategies for testing Prisma ORM: fast unit tests using jest.mock to stub @prisma/client, and reliable integration tests using TestContainers to spin up a real Postgres instance. You'll learn how to keep your test suite both fast and trustworthy by knowing when to use each approach.

Key Takeaways

Mock Prisma at the module boundary Unit tests should stub the entire PrismaClient so no real database is involved — this keeps tests fast and deterministic.

TestContainers gives you a real database in CI Spinning up Postgres via TestContainers means your integration tests run against the actual engine, catching dialect-specific bugs that SQLite can't.

Run prisma migrate deploy inside your test setup Always apply migrations to the test container before running integration tests — never assume a clean schema.

Use transactions for test isolation Wrapping each test in a transaction that rolls back on teardown is faster than truncating tables and prevents data leaking between tests.

Separate unit and integration test suites Configure Jest projects or separate scripts so unit tests run on every save and integration tests run in CI — don't mix them.

Testing database code is one of the trickiest parts of a backend application. You need confidence that your queries actually work, but a slow or flaky test database kills developer productivity. With Prisma, you have two powerful options: mock the client entirely for unit tests, or run a real Postgres instance for integration tests. This post shows you both, and when to use each.

Setting Up Your Project

Start with a typical Prisma setup. Assume a simple schema for a user management service:

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prismaClient"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        Int    @id @default(autoincrement())
  title     String
  published Boolean @default(false)
  authorId  Int
  author    User   @relation(fields: [authorId], references: [id])
}

Install the test dependencies:

npm install --save-dev jest ts-jest @types/jest jest-environment-node
npm install --save-dev @testcontainers/postgresql testcontainers

Configure Jest for Node environment in jest.config.ts:

import type { Config } from 'jest';

const config: Config = {
  projects: [
    {
      displayName: 'unit',
      testMatch: ['**/*.unit.test.ts'],
      preset: 'ts-jest',
      testEnvironment: 'node',
    },
    {
      displayName: 'integration',
      testMatch: ['**/*.integration.test.ts'],
      preset: 'ts-jest',
      testEnvironment: 'node',
      testTimeout: 60000, // containers take time to start
    },
  ],
};

export default config;

Using jest-environment-node (rather than jsdom) is important — Prisma's client uses Node-specific APIs that break in browser-like environments.

Unit Tests: Mocking PrismaClient

Unit tests should not touch a database. The goal is to verify your service layer logic — error handling, data transformation, conditional branching — without the overhead of a real connection.

Creating a Reusable Mock

Create a centralized mock factory that mirrors PrismaClient's structure:

// src/__mocks__/prisma.ts
import { PrismaClient } from '@prisma/client';

type MockPrismaClient = {
  [K in keyof PrismaClient]: {
    [M in keyof PrismaClient[K]]: jest.Mock;
  };
};

const createMockPrismaClient = (): MockPrismaClient => ({
  user: {
    findUnique: jest.fn(),
    findMany: jest.fn(),
    create: jest.fn(),
    update: jest.fn(),
    delete: jest.fn(),
    count: jest.fn(),
  },
  post: {
    findMany: jest.fn(),
    create: jest.fn(),
    update: jest.fn(),
    delete: jest.fn(),
  },
  $transaction: jest.fn(),
  $disconnect: jest.fn(),
} as unknown as MockPrismaClient);

export const prismaMock = createMockPrismaClient();

jest.mock('@prisma/client', () => ({
  PrismaClient: jest.fn(() => prismaMock),
}));

Writing a Service to Test

// src/services/userService.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export async function getUserById(id: number) {
  const user = await prisma.user.findUnique({
    where: { id },
    include: { posts: true },
  });

  if (!user) {
    throw new Error(`User with id ${id} not found`);
  }

  return user;
}

export async function createUser(email: string, name: string) {
  const existing = await prisma.user.findUnique({ where: { email } });

  if (existing) {
    throw new Error(`Email ${email} is already taken`);
  }

  return prisma.user.create({
    data: { email, name },
  });
}

export async function getActiveAuthors() {
  return prisma.user.findMany({
    where: {
      posts: {
        some: { published: true },
      },
    },
    include: { _count: { select: { posts: true } } },
    orderBy: { createdAt: 'desc' },
  });
}

Unit Tests Using the Mock

// src/services/userService.unit.test.ts
import { prismaMock } from '../__mocks__/prisma';
import { getUserById, createUser, getActiveAuthors } from './userService';

beforeEach(() => {
  jest.clearAllMocks();
});

describe('getUserById', () => {
  it('returns the user when found', async () => {
    const mockUser = {
      id: 1,
      email: 'alice@example.com',
      name: 'Alice',
      createdAt: new Date(),
      posts: [],
    };

    prismaMock.user.findUnique.mockResolvedValue(mockUser);

    const result = await getUserById(1);

    expect(result).toEqual(mockUser);
    expect(prismaMock.user.findUnique).toHaveBeenCalledWith({
      where: { id: 1 },
      include: { posts: true },
    });
  });

  it('throws when the user is not found', async () => {
    prismaMock.user.findUnique.mockResolvedValue(null);

    await expect(getUserById(999)).rejects.toThrow('User with id 999 not found');
  });
});

describe('createUser', () => {
  it('creates a user when the email is available', async () => {
    prismaMock.user.findUnique.mockResolvedValue(null);
    prismaMock.user.create.mockResolvedValue({
      id: 2,
      email: 'bob@example.com',
      name: 'Bob',
      createdAt: new Date(),
    });

    const result = await createUser('bob@example.com', 'Bob');

    expect(result.email).toBe('bob@example.com');
    expect(prismaMock.user.create).toHaveBeenCalledWith({
      data: { email: 'bob@example.com', name: 'Bob' },
    });
  });

  it('throws when email is already taken', async () => {
    prismaMock.user.findUnique.mockResolvedValue({
      id: 1,
      email: 'alice@example.com',
      name: 'Alice',
      createdAt: new Date(),
    });

    await expect(createUser('alice@example.com', 'Alice 2')).rejects.toThrow(
      'Email alice@example.com is already taken'
    );

    expect(prismaMock.user.create).not.toHaveBeenCalled();
  });
});

The key pattern here: jest.clearAllMocks() in beforeEach ensures mock call counts and return values don't bleed between tests.

Integration Tests: TestContainers with Real Postgres

Unit tests tell you your logic is right. Integration tests tell you your queries actually work. Prisma has subtleties — include, nested writes, @@index behavior, unique constraint errors — that only appear against a real database.

Global Setup with TestContainers

Create a Jest global setup file that starts Postgres once for the entire integration suite:

// jest.integration.setup.ts
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
import { execSync } from 'child_process';

let container: StartedPostgreSqlContainer;

export default async function globalSetup() {
  container = await new PostgreSqlContainer('postgres:16-alpine')
    .withDatabase('testdb')
    .withUsername('testuser')
    .withPassword('testpass')
    .start();

  const connectionString = container.getConnectionUri();
  process.env.DATABASE_URL = connectionString;

  // Run all migrations against the fresh container
  execSync('npx prisma migrate deploy', {
    env: { ...process.env, DATABASE_URL: connectionString },
    stdio: 'inherit',
  });

  // Store container reference for teardown
  (global as any).__PG_CONTAINER__ = container;
}
// jest.integration.teardown.ts
export default async function globalTeardown() {
  const container = (global as any).__PG_CONTAINER__;
  if (container) {
    await container.stop();
  }
}

Update your Jest config to wire these up:

// jest.config.ts (integration project)
{
  displayName: 'integration',
  testMatch: ['**/*.integration.test.ts'],
  preset: 'ts-jest',
  testEnvironment: 'node',
  testTimeout: 60000,
  globalSetup: './jest.integration.setup.ts',
  globalTeardown: './jest.integration.teardown.ts',
}

Transaction Rollback for Test Isolation

Rather than truncating tables between tests (which is slow and requires knowing your schema), wrap each test in a transaction that rolls back on teardown:

// src/test-utils/withRollback.ts
import { PrismaClient } from '@prisma/client';

export async function withRollback(
  prisma: PrismaClient,
  fn: (tx: Omit<PrismaClient, '$connect' | '$disconnect' | '$on' | '$transaction' | '$use'>) => Promise<void>
): Promise<void> {
  // Prisma doesn't expose raw transaction rollback, so use $executeRaw to control it
  await prisma.$transaction(async (tx) => {
    await fn(tx);
    // Force a rollback by throwing after the test function completes
    throw new Error('__ROLLBACK__');
  }).catch((err) => {
    if (err.message !== '__ROLLBACK__') throw err;
  });
}

For a cleaner approach using beforeEach/afterEach:

// src/test-utils/transactionContext.ts
import { PrismaClient } from '@prisma/client';

export function useTransactionContext() {
  const prisma = new PrismaClient();
  let rollbackFn: () => void;

  beforeAll(async () => {
    await prisma.$connect();
  });

  afterAll(async () => {
    await prisma.$disconnect();
  });

  // For PostgreSQL: use SAVEPOINT-based isolation
  beforeEach(async () => {
    await prisma.$executeRaw`BEGIN`;
    await prisma.$executeRaw`SAVEPOINT test_savepoint`;
  });

  afterEach(async () => {
    await prisma.$executeRaw`ROLLBACK TO SAVEPOINT test_savepoint`;
    await prisma.$executeRaw`ROLLBACK`;
  });

  return prisma;
}

Integration Test Example

// src/services/userService.integration.test.ts
import { PrismaClient } from '@prisma/client';
import { getUserById, createUser } from './userService';

const prisma = new PrismaClient();

beforeAll(async () => {
  await prisma.$connect();
});

afterAll(async () => {
  await prisma.$disconnect();
});

beforeEach(async () => {
  // Clean state: delete in dependency order
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();
});

describe('createUser integration', () => {
  it('persists a user to the database', async () => {
    const user = await createUser('carol@example.com', 'Carol');

    // Verify it's actually in the DB
    const found = await prisma.user.findUnique({
      where: { email: 'carol@example.com' },
    });

    expect(found).not.toBeNull();
    expect(found!.name).toBe('Carol');
    expect(found!.id).toBe(user.id);
  });

  it('enforces unique email constraint', async () => {
    await createUser('dave@example.com', 'Dave');

    await expect(createUser('dave@example.com', 'Dave 2')).rejects.toThrow(
      'Email dave@example.com is already taken'
    );

    // Confirm only one Dave exists
    const count = await prisma.user.count({
      where: { email: 'dave@example.com' },
    });
    expect(count).toBe(1);
  });
});

describe('getUserById integration', () => {
  it('returns user with their posts', async () => {
    const user = await prisma.user.create({
      data: {
        email: 'eve@example.com',
        name: 'Eve',
        posts: {
          create: [
            { title: 'First Post', published: true },
            { title: 'Draft', published: false },
          ],
        },
      },
    });

    const result = await getUserById(user.id);

    expect(result.posts).toHaveLength(2);
    expect(result.posts.map((p) => p.title)).toContain('First Post');
  });
});

Running prisma migrate deploy in CI

In CI, your TestContainers approach means the container starts fresh every run. Always use migrate deploy (not migrate dev) in test environments — it applies existing migration files without prompting for new ones.

A minimal GitHub Actions step:

- name: Run integration tests
  env:
    # DATABASE_URL is set by globalSetup, but set a fallback for Prisma CLI
    DATABASE_URL: postgresql://testuser:testpass@localhost:5432/testdb
  run: npm run test:integration

The globalSetup overrides DATABASE_URL with the actual container port (which is random), so the hardcoded value above is only for tooling that runs before the container starts.

When to Use Each Approach

Use unit tests with mocks when:

  • Testing service layer logic, error handling, or data transformation
  • You want sub-second feedback during development
  • The test is about what you do with query results, not whether the query is correct

Use integration tests with TestContainers when:

  • Testing complex queries with include, groupBy, or raw SQL
  • Verifying that your schema constraints work (unique, foreign key)
  • Testing migrations and schema changes
  • Checking behavior that depends on Postgres-specific features

A healthy Prisma test suite has both. Unit tests run on every file save; integration tests run in CI. Neither replaces the other.

Read more