Prisma Database Testing in 2026: Unit Tests, Seeding, and Isolated Test Databases

Testing code that touches the database is the part most teams skip or do badly. Either they mock Prisma (which tests nothing real), run tests against a shared development database (causing flaky tests), or run one test at a time to avoid conflicts (making the suite 10x slower than it needs to be).

The production approach: each test suite gets its own isolated database, seeded with factories, cleaned up after. Tests run in parallel without conflicts. The database layer is tested with real SQL, not mocks.

This post covers the complete setup: test database provisioning, Prisma client isolation, factory-based seeding with @snaplet/seed, transaction rollback for fast cleanup, and Vitest configuration that works.

---

Architecture: Three Levels of Database Testing

Use mocks for pure business logic. Use real databases for anything that touches SQL—joins, transactions, constraints, triggers. Don't use a shared dev database for tests.

---

Setup: Test Database per Suite

vitest.config.ts

import { defineConfig } from "vitest/config";
import path from "path";

export default defineConfig({
  test: {
    globals: true,
    environment: "node",
    setupFiles: ["./tests/setup/global.ts"],
    // Run test files in parallel (each gets its own DB)
    pool: "threads",
    poolOptions: {
      threads: {
        singleThread: false,
        minThreads: 1,
        maxThreads: 4,  // Limit to avoid overwhelming Postgres
      },
    },
    // Timeout for DB operations
    testTimeout: 30_000,
    hookTimeout: 30_000,
    // Coverage
    coverage: {
      provider: "v8",
      reporter: ["text", "json", "lcov"],
      include: ["src/**/*.ts"],
      exclude: ["src/**/*.test.ts", "src/generated/**"],
    },
  },
  resolve: {
    alias: {
      "@": path.resolve(__dirname, "./src"),
    },
  },
});

tests/setup/global.ts — runs once before all tests

import { execSync } from "child_process";

// Ensure test databases are clean before the whole suite
// Individual test files handle their own DB creation
export async function setup() {
  // Verify test environment
  if (process.env.NODE_ENV !== "test") {
    throw new Error("Tests must run with NODE_ENV=test");
  }

  if (!process.env.DATABASE_URL?.includes("test")) {
    throw new Error(
      "DATABASE_URL must point to a test database (must contain 'test' in the name)"
    );
  }

  console.log("✓ Test environment verified");
}

---

Test Database Helpers

// tests/helpers/database.ts
import { PrismaClient } from "@prisma/client";
import { execSync } from "child_process";
import { randomUUID } from "crypto";

const TEST_DATABASE_BASE_URL = process.env.DATABASE_URL!;

/**
 * Creates an isolated database for a test suite.
 * Returns a PrismaClient connected to it and a cleanup function.
 */
export async function createTestDatabase(): Promise<{
  prisma: PrismaClient;
  databaseUrl: string;
  cleanup: () => Promise<void>;
}> {
  const dbName = `test_${randomUUID().replace(/-/g, "_")}`;

  // Parse base URL to get admin connection
  const baseUrl = new URL(TEST_DATABASE_BASE_URL);
  const adminUrl = new URL(TEST_DATABASE_BASE_URL);
  adminUrl.pathname = "/postgres"; // Connect to postgres DB to create new one

  // Create the test database
  const adminPrisma = new PrismaClient({
    datasources: { db: { url: adminUrl.toString() } },
  });

  try {
    await adminPrisma.$executeRawUnsafe(`CREATE DATABASE "${dbName}"`);
  } finally {
    await adminPrisma.$disconnect();
  }

  // Build URL for the new database
  const databaseUrl = new URL(TEST_DATABASE_BASE_URL);
  databaseUrl.pathname = `/${dbName}`;
  const databaseUrlStr = databaseUrl.toString();

  // Run migrations
  execSync(`npx prisma migrate deploy`, {
    env: { ...process.env, DATABASE_URL: databaseUrlStr },
    stdio: "pipe",
  });

  // Create Prisma client for this database
  const prisma = new PrismaClient({
    datasources: { db: { url: databaseUrlStr } },
    log: process.env.PRISMA_LOG ? ["query", "error"] : ["error"],
  });

  await prisma.$connect();

  const cleanup = async () => {
    await prisma.$disconnect();

    // Drop the test database
    const cleanupPrisma = new PrismaClient({
      datasources: { db: { url: adminUrl.toString() } },
    });
    try {
      // Force-disconnect all connections to the test DB
      await cleanupPrisma.$executeRawUnsafe(`
        SELECT pg_terminate_backend(pid)
        FROM pg_stat_activity
        WHERE datname = '${dbName}'
          AND pid <> pg_backend_pid()
      `);
      await cleanupPrisma.$executeRawUnsafe(`DROP DATABASE IF EXISTS "${dbName}"`);
    } finally {
      await cleanupPrisma.$disconnect();
    }
  };

  return { prisma, databaseUrl: databaseUrlStr, cleanup };
}

/**
 * Faster alternative: use transaction rollback instead of recreating the DB.
 * Use this for test-level isolation within a suite that already has a DB.
 */
export function createTransactionalTestContext(prisma: PrismaClient) {
  type PrismaTransactionClient = Parameters<Parameters<PrismaClient["$transaction"]>[0]>[0];

  let tx: PrismaTransactionClient;
  let rollback: () => void;

  const context = {
    get db(): PrismaTransactionClient {
      return tx;
    },

    async begin() {
      // This is a trick: start a transaction and never commit it
      // The test rolls back at the end
      await new Promise<void>((resolve, reject) => {
        prisma.$transaction(async (transaction) => {
          tx = transaction;
          resolve();

          // Wait until rollback() is called
          await new Promise<void>((innerResolve, innerReject) => {
            rollback = () => innerReject(new Error("rollback"));
          });
        }).catch((err) => {
          // Suppress the intentional rollback error
          if (err.message !== "rollback") reject(err);
        });
      });
    },

    async end() {
      rollback?.();
      // Wait a tick for rollback to complete
      await new Promise((resolve) => setTimeout(resolve, 10));
    },
  };

  return context;
}

---

Factory-Based Seeding

Instead of hardcoded INSERT statements, use factories that produce valid test data:

// tests/factories/index.ts
import { PrismaClient, User, Team, Project, Subscription } from "@prisma/client";
import { faker } from "@faker-js/faker";

type TransactionClient = Omit<
  PrismaClient,
  "$connect" | "$disconnect" | "$on" | "$transaction" | "$use" | "$extends"
>;

// ─── User Factory ────────────────────────────────────────────────────────────

interface UserFactoryOptions {
  email?: string;
  name?: string;
  role?: "user" | "admin";
  emailVerified?: boolean;
}

export async function createUser(
  db: TransactionClient,
  options: UserFactoryOptions = {}
): Promise<User> {
  return db.user.create({
    data: {
      email: options.email ?? faker.internet.email().toLowerCase(),
      name: options.name ?? faker.person.fullName(),
      role: options.role ?? "user",
      emailVerifiedAt: options.emailVerified !== false ? new Date() : null,
      passwordHash: "$2b$10$testhashtesthashtesthash", // Fixed hash for tests
    },
  });
}

// ─── Team Factory ────────────────────────────────────────────────────────────

interface TeamFactoryOptions {
  name?: string;
  ownerId?: string;
  plan?: "free" | "pro" | "enterprise";
  createOwner?: boolean;
}

export async function createTeam(
  db: TransactionClient,
  options: TeamFactoryOptions = {}
): Promise<Team & { owner: User }> {
  let owner: User;

  if (options.ownerId) {
    owner = await db.user.findUniqueOrThrow({ where: { id: options.ownerId } });
  } else if (options.createOwner !== false) {
    owner = await createUser(db);
  } else {
    throw new Error("createTeam: either ownerId or createOwner=true required");
  }

  const team = await db.team.create({
    data: {
      name: options.name ?? faker.company.name(),
      slug: faker.helpers.slugify(faker.company.name()).toLowerCase(),
      plan: options.plan ?? "free",
      ownerId: owner.id,
      members: {
        create: {
          userId: owner.id,
          role: "owner",
        },
      },
    },
  });

  return { ...team, owner };
}

// ─── Project Factory ──────────────────────────────────────────────────────────

interface ProjectFactoryOptions {
  teamId?: string;
  name?: string;
  status?: "active" | "archived" | "draft";
}

export async function createProject(
  db: TransactionClient,
  options: ProjectFactoryOptions = {}
): Promise<Project> {
  const teamId = options.teamId ?? (await createTeam(db)).id;

  return db.project.create({
    data: {
      teamId,
      name: options.name ?? faker.commerce.productName(),
      slug: faker.helpers.slugify(faker.commerce.productName()).toLowerCase(),
      status: options.status ?? "active",
      description: faker.lorem.sentences(2),
    },
  });
}

// ─── Subscription Factory ─────────────────────────────────────────────────────

export async function createSubscription(
  db: TransactionClient,
  teamId: string,
  options: {
    plan?: "pro" | "enterprise";
    status?: "active" | "trialing" | "past_due" | "cancelled";
    trialDays?: number;
  } = {}
) {
  const now = new Date();
  const trialEndsAt = options.trialDays
    ? new Date(now.getTime() + options.trialDays * 86400000)
    : null;

  return db.subscription.create({
    data: {
      teamId,
      plan: options.plan ?? "pro",
      status: options.status ?? "active",
      stripeCustomerId: `cus_test_${faker.string.alphanumeric(14)}`,
      stripeSubscriptionId: `sub_test_${faker.string.alphanumeric(14)}`,
      currentPeriodStart: now,
      currentPeriodEnd: new Date(now.getTime() + 30 * 86400000),
      trialEndsAt,
    },
  });
}

---

Writing Integration Tests

// tests/repositories/project.repository.test.ts
import { describe, it, expect, beforeAll, afterAll } from "vitest";
import { createTestDatabase } from "../helpers/database";
import { createUser, createTeam, createProject } from "../factories";
import { ProjectRepository } from "@/repositories/project.repository";

describe("ProjectRepository", () => {
  let prisma: ReturnType<typeof createTestDatabase> extends Promise<infer T> ? T["prisma"] : never;
  let cleanup: () => Promise<void>;

  beforeAll(async () => {
    const db = await createTestDatabase();
    prisma = db.prisma;
    cleanup = db.cleanup;
  });

  afterAll(async () => {
    await cleanup();
  });

  describe("findByTeam", () => {
    it("returns only projects belonging to the team", async () => {
      const { id: teamAId } = await createTeam(prisma);
      const { id: teamBId } = await createTeam(prisma);

      const projectA1 = await createProject(prisma, { teamId: teamAId });
      const projectA2 = await createProject(prisma, { teamId: teamAId });
      await createProject(prisma, { teamId: teamBId }); // Should not appear

      const repo = new ProjectRepository(prisma);
      const results = await repo.findByTeam(teamAId);

      expect(results).toHaveLength(2);
      expect(results.map((p) => p.id)).toEqual(
        expect.arrayContaining([projectA1.id, projectA2.id])
      );
    });

    it("excludes archived projects by default", async () => {
      const { id: teamId } = await createTeam(prisma);
      await createProject(prisma, { teamId, status: "active" });
      await createProject(prisma, { teamId, status: "archived" });

      const repo = new ProjectRepository(prisma);
      const results = await repo.findByTeam(teamId);

      expect(results).toHaveLength(1);
      expect(results[0].status).toBe("active");
    });

    it("includes archived projects when includeArchived=true", async () => {
      const { id: teamId } = await createTeam(prisma);
      await createProject(prisma, { teamId, status: "active" });
      await createProject(prisma, { teamId, status: "archived" });

      const repo = new ProjectRepository(prisma);
      const results = await repo.findByTeam(teamId, { includeArchived: true });

      expect(results).toHaveLength(2);
    });
  });

  describe("create", () => {
    it("generates a unique slug from name", async () => {
      const { id: teamId } = await createTeam(prisma);
      const repo = new ProjectRepository(prisma);

      const project = await repo.create(teamId, { name: "My Awesome Project" });

      expect(project.slug).toMatch(/^my-awesome-project/);
    });

    it("appends suffix when slug already exists", async () => {
      const { id: teamId } = await createTeam(prisma);
      await createProject(prisma, { teamId, name: "My Project" });

      const repo = new ProjectRepository(prisma);
      const project = await repo.create(teamId, { name: "My Project" });

      expect(project.slug).not.toBe("my-project");
      expect(project.slug).toMatch(/^my-project-/);
    });
  });
});

---

Testing Transactions

// tests/services/billing.service.test.ts
import { describe, it, expect, beforeAll, afterAll } from "vitest";
import { createTestDatabase } from "../helpers/database";
import { createTeam, createSubscription } from "../factories";
import { BillingService } from "@/services/billing.service";

describe("BillingService", () => {
  let prisma: any;
  let cleanup: () => Promise<void>;

  beforeAll(async () => {
    ({ prisma, cleanup } = await createTestDatabase());
  });

  afterAll(async () => { await cleanup(); });

  describe("downgradeToFree", () => {
    it("cancels subscription and downgrades team plan atomically", async () => {
      const { id: teamId } = await createTeam(prisma, { plan: "pro" });
      await createSubscription(prisma, teamId, { plan: "pro" });

      const billing = new BillingService(prisma);
      await billing.downgradeToFree(teamId);

      const [team, sub] = await Promise.all([
        prisma.team.findUniqueOrThrow({ where: { id: teamId } }),
        prisma.subscription.findFirst({ where: { teamId } }),
      ]);

      expect(team.plan).toBe("free");
      expect(sub?.status).toBe("cancelled");
      expect(sub?.cancelledAt).toBeTruthy();
    });

    it("rolls back if Stripe API fails", async () => {
      const { id: teamId } = await createTeam(prisma, { plan: "pro" });
      await createSubscription(prisma, teamId, { plan: "pro" });

      // Mock Stripe to throw
      const billing = new BillingService(prisma, {
        stripeClient: {
          subscriptions: {
            update: async () => { throw new Error("Stripe API error"); },
          },
        } as any,
      });

      await expect(billing.downgradeToFree(teamId)).rejects.toThrow("Stripe API error");

      // Database should be unchanged
      const team = await prisma.team.findUniqueOrThrow({ where: { id: teamId } });
      expect(team.plan).toBe("pro"); // Not changed
    });
  });
});

---

Testing with Constraint Violations

// Test that DB constraints enforce business rules
it("prevents duplicate team membership", async () => {
  const user = await createUser(prisma);
  const { id: teamId } = await createTeam(prisma);

  await prisma.teamMember.create({
    data: { userId: user.id, teamId, role: "member" },
  });

  // Second insert should violate unique constraint
  await expect(
    prisma.teamMember.create({
      data: { userId: user.id, teamId, role: "admin" },
    })
  ).rejects.toThrow(/Unique constraint/);
});

---

GitHub Actions: Test Database in CI

# .github/workflows/test.yml
name: Test

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
          POSTGRES_DB: postgres    # Base DB; test DBs are created dynamically
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    env:
      NODE_ENV: test
      DATABASE_URL: "postgresql://testuser:testpass@localhost:5432/postgres"

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: "22"
          cache: "npm"

      - run: npm ci

      - name: Generate Prisma client
        run: npx prisma generate

      - name: Run tests
        run: npx vitest run --reporter=verbose

      - name: Upload coverage
        uses: codecov/codecov-action@v4
        with:
          token: ${{ secrets.CODECOV_TOKEN }}

---

Cost and Timeline Estimates

Well-tested database code catches 80% of the production bugs that slip through unit tests—query correctness, constraint violations, transaction isolation issues—that would otherwise reach your users.

---

See Also

• Prisma Advanced Patterns — Prisma extensions, soft delete, audit logging
• Next.js Testing Strategy — Full testing pyramid for Next.js apps
• PostgreSQL Row-Level Security — Testing RLS policies
• Database Schema Versioning — Migrations that work with test setup

---

Working With Viprasol

We build and maintain comprehensive test suites for TypeScript SaaS products, including database integration tests, API tests, and end-to-end flows. Our engineering team has set up testing infrastructure for codebases ranging from early-stage startups to enterprise applications with 500K+ lines of code.

What we deliver:

• Complete Vitest + Prisma integration test setup
• Factory library for all your domain entities
• CI/CD configuration with isolated test databases
• Code coverage reporting and enforcement
• Test suite audits for existing codebases

Explore our web development services or contact us to improve your testing infrastructure.