Unit Testing Prisma Services with pgLite

Test Prisma database services using pgLite for fast, isolated tests without Docker. Covers setup, migrations, test factories, and real service testing patterns.

prisma
testing
typescript

Testing database services typically requires spinning up Docker containers, managing test databases, and dealing with slow setup times. pgLite changes this by providing a complete PostgreSQL implementation that runs in-memory using WebAssembly.

pgLite is a lightweight PostgreSQL implementation compiled to WebAssembly that runs entirely in-memory within your Node.js process. It provides full SQL compatibility without external dependencies.

When we built the MakerKit ORM variants (Prisma and Drizzle kits), we needed a different testing approach than our Supabase kits. The Supabase kits use pgtap to test Row Level Security policies directly in the database. But the ORM kits handle authorization at the service layer, making pgLite the better fit: we get real PostgreSQL behavior for testing business logic without the overhead of spinning up Supabase or Docker for every test run.

MakerKit uses pgLite with Prisma and Drizzle for all database service tests in the ORM kits. Tests run in milliseconds, require no external dependencies, and execute real SQL against a real PostgreSQL-compatible database.

Tested with: Prisma 7.0, pgLite 0.3.14, pglite-prisma-adapter 0.7.1, Vitest 4.0, Node.js 20+

What Are Prisma Services?

A Prisma service is a TypeScript function or class that encapsulates database operations using Prisma Client. Instead of scattering prisma.user.findMany() calls throughout your application, you create dedicated service functions that handle business logic alongside database queries.

export function createSeatEnforcementService(db: PrismaClient) {
  return {
    async assertCanInviteMember(params: { organizationId: string }) {
      const [subscription, memberCount, pendingCount] = await Promise.all([
        db.subscription.findFirst({
          where: { referenceId: params.organizationId, status: 'active' },
        }),
        db.member.count({
          where: { organizationId: params.organizationId },
        }),
        db.invitation.count({
          where: { organizationId: params.organizationId, status: 'pending' },
        }),
      ]);
      if (!subscription) return; // No seat limit on free tier
      const usedSeats = memberCount + pendingCount;
      if (usedSeats >= (subscription.seats ?? Infinity)) {
        throw new SeatLimitReachedError();
      }
    },
  };
}

This pattern provides several benefits:

  • Testability: Services accept a database client as a parameter, making it easy to inject a test database
  • Encapsulation: Business rules live in one place, not scattered across API routes and components
  • Type safety: Prisma's generated types flow through your service layer
  • Reusability: The same service works in API routes, Server Actions, and background jobs

The MakerKit ORM kits use this service pattern extensively. Authorization checks, seat enforcement, invitation handling, and subscription logic all live in dedicated services that we test with pgLite.

Why pgLite for Database Testing

Traditional approaches to testing database code have significant tradeoffs:

  • Mocking the database catches typos but misses real issues. Your mock returns what you tell it to return, so constraint violations, query errors, and relationship problems slip through to production.
  • Docker-based testing catches real issues but adds friction. Starting containers takes seconds, tests run slower, and CI pipelines need Docker support.
  • pgLite gives you both: real PostgreSQL behavior without the infrastructure overhead. It's compiled to WebAssembly, runs entirely in-process, and supports the SQL features you actually use in application code.

For MakerKit's service tests, pgLite provides:

  • Full PostgreSQL SQL support (constraints, transactions, CASCADE)
  • Sub-second test startup
  • No Docker or external database required
  • Real Prisma queries against real data

Setting Up pgLite with Prisma

Dependencies

Install the required packages in your database package:

pnpm add -D @electric-sql/pglite pglite-prisma-adapter

Your package.json devDependencies should include:

{
  "devDependencies": {
    "@electric-sql/pglite": "^0.3.14",
    "pglite-prisma-adapter": "^0.7.1",
    "@prisma/client": "^7.0.0",
    "vitest": "^4.0.0"
  }
}

Creating the Test Database Utility

Create a utility that initializes pgLite, applies migrations, and provides cleanup functions.

import * as fs from 'node:fs';
import * as path from 'node:path';
import { PGlite } from '@electric-sql/pglite';
import { PrismaPGlite } from 'pglite-prisma-adapter';
import { PrismaClient } from '../prisma/generated/index';
export interface TestDatabase {
  db: PrismaClient;
  client: PGlite;
  cleanup: () => Promise<void>;
  close: () => Promise<void>;
}
export async function createTestDatabase(): Promise<TestDatabase> {
  const client = new PGlite();
  // Apply migrations from the migrations folder
  const migrationsDir = path.join(
    import.meta.dirname,
    '..',
    'prisma',
    'migrations',
  );
  const migrations = fs
    .readdirSync(migrationsDir)
    .filter((dir) => fs.statSync(path.join(migrationsDir, dir)).isDirectory())
    .sort();
  for (const migration of migrations) {
    const sqlPath = path.join(migrationsDir, migration, 'migration.sql');
    if (fs.existsSync(sqlPath)) {
      const sql = fs.readFileSync(sqlPath, 'utf-8');
      await client.exec(sql);
    }
  }
  const adapter = new PrismaPGlite(client);
  const db = new PrismaClient({ adapter });
  await db.$connect();
  return {
    db,
    client,
    async cleanup() {
      // Truncate all tables in dependency order
      await client.exec(`
        TRUNCATE TABLE "rateLimit" CASCADE;
        TRUNCATE TABLE member CASCADE;
        TRUNCATE TABLE invitation CASCADE;
        TRUNCATE TABLE "organizationRole" CASCADE;
        TRUNCATE TABLE subscription CASCADE;
        TRUNCATE TABLE organization CASCADE;
        TRUNCATE TABLE session CASCADE;
        TRUNCATE TABLE account CASCADE;
        TRUNCATE TABLE "twoFactor" CASCADE;
        TRUNCATE TABLE verification CASCADE;
        TRUNCATE TABLE "user" CASCADE;
      `);
    },
    async close() {
      await db.$disconnect();
      await client.close();
    },
  };
}
export function createTestId(id: string): string {
  return `test_${id}_${Date.now()}`;
}

Key implementation details:

  1. Migration application: Reads actual Prisma migration files and executes them in order. Your test database has the exact same schema as production.
  2. CASCADE truncation: The cleanup() function truncates tables with CASCADE to handle foreign key relationships. Order matters less with CASCADE, but listing tables explicitly makes the cleanup predictable.
  3. Unique test IDs: The createTestId() helper generates unique identifiers with timestamps, preventing collisions when tests run in parallel.
  4. Node.js requirement: The import.meta.dirname syntax requires Node.js 20.11+ or 21+.

Exporting Test Utilities

Export the utilities from your database package:

export * from './pglite-db';
export * from './factories';

Add the export path to your package.json:

{
  "exports": {
    ".": "./src/index.ts",
    "./testing": "./src/test-utils/index.ts",
    "./testing/pglite": "./src/test-utils/pglite-db.ts"
  }
}

Test Data Factories

Factory functions create consistent test data with sensible defaults while allowing overrides for specific test cases.

import { randomBytes } from 'crypto';
/**
 * Factory for creating test user data
 */
export const createTestUser = (overrides: Partial<TestUser> = {}): TestUser => {
  const id = randomId();
  return {
    id,
    email: `test-${id}@makerkit.dev`,
    emailVerified: true,
    name: `Test User ${id.slice(0, 4)}`,
    image: null,
    createdAt: new Date(),
    updatedAt: new Date(),
    ...overrides,
  };
};
/**
 * Factory for creating test organization data
 */
export const createTestOrganization = (
  overrides: Partial<TestOrganization> = {},
): TestOrganization => {
  const id = randomId();
  return {
    id,
    name: `Test Organization ${id.slice(0, 4)}`,
    slug: `test-org-${id}`,
    logo: null,
    metadata: JSON.stringify({}),
    createdAt: new Date(),
    ...overrides,
  };
};
/**
 * Factory for creating test member relationship
 */
export const createTestMember = (
  overrides: Partial<TestMember> = {},
): TestMember => {
  return {
    id: randomId(),
    userId: '',
    organizationId: '',
    role: 'member',
    createdAt: new Date(),
    ...overrides,
  };
};
/**
 * Factory for creating test subscription data
 */
export const createTestSubscription = (
  overrides: Partial<TestSubscription> = {},
): TestSubscription => {
  const id = randomId();
  return {
    id,
    plan: 'pro-monthly',
    referenceId: '',
    customer_id: `cus_test_${id}`,
    subscription_id: `sub_test_${id}`,
    status: 'active',
    periodStart: new Date(),
    periodEnd: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000),
    cancelAtPeriodEnd: false,
    cancelAt: null,
    canceledAt: null,
    endedAt: null,
    seats: 1,
    trialStart: null,
    trialEnd: null,
    ...overrides,
  };
};
function randomId(): string {
  return randomBytes(16).toString('hex');
}
// Type definitions for test data
export interface TestUser {
  id: string;
  email: string;
  emailVerified: boolean;
  name: string;
  image: string | null;
  createdAt: Date;
  updatedAt: Date;
}
export interface TestOrganization {
  id: string;
  name: string;
  slug: string;
  logo: string | null;
  metadata: string;
  createdAt: Date;
}
export interface TestMember {
  id: string;
  userId: string;
  organizationId: string;
  role: 'owner' | 'admin' | 'member';
  createdAt: Date;
}
export interface TestSubscription {
  id: string;
  plan: string;
  referenceId: string;
  customer_id: string | null;
  subscription_id: string | null;
  status: string;
  periodStart: Date | null;
  periodEnd: Date | null;
  cancelAtPeriodEnd: boolean | null;
  cancelAt: Date | null;
  canceledAt: Date | null;
  endedAt: Date | null;
  seats: number | null;
  trialStart: Date | null;
  trialEnd: Date | null;
}

Factory design principles:

  • Arrow function syntax: Consistent with modern TypeScript style
  • Spread overrides last: { ...defaults, ...overrides } lets callers override any field
  • Generated IDs by default: Each factory call produces unique data
  • Realistic defaults: Subscriptions get 30-day periods, users are email-verified
  • Type safety: TypeScript interfaces ensure factories match your schema

Writing Database Service Tests

Basic Test Structure

Here's the pattern for testing a service that interacts with the database:

import { afterAll, beforeAll, beforeEach, describe, expect, it } from 'vitest';
import {
  type TestDatabase,
  createTestDatabase,
  createTestId,
} from '../test-utils/pglite-db';
import { createTestUser, createTestOrganization } from '../test-utils/factories';
import { createExampleService } from './example.service';
describe('ExampleService', () => {
  let testDb: TestDatabase;
  let service: ReturnType<typeof createExampleService>;
  beforeAll(async () => {
    testDb = await createTestDatabase();
    service = createExampleService(testDb.db);
  });
  beforeEach(async () => {
    await testDb.cleanup();
  });
  afterAll(async () => {
    await testDb.close();
  });
  it('should perform operation', async () => {
    // Arrange: Create test data
    const user = createTestUser();
    await testDb.db.user.create({ data: user });
    // Act: Call the service
    const result = await service.someOperation(user.id);
    // Assert: Verify the result
    expect(result).toBeDefined();
  });
});

The lifecycle hooks serve distinct purposes:

  • beforeAll: Creates one database instance for the entire test file. Migration application happens once.
  • beforeEach: Truncates all data between tests. Each test starts with an empty database.
  • afterAll: Closes connections and releases resources.

Note that test files can be colocated with the service file (as shown above) or placed in a __tests__ subdirectory. MakerKit uses both patterns depending on the package.

Testing Seat Enforcement (Real Example)

This example from MakerKit tests the seat enforcement service, which validates whether an organization can add more members based on their subscription:

import { afterAll, beforeAll, beforeEach, describe, expect, it } from 'vitest';
import {
  type TestDatabase,
  createTestDatabase,
  createTestId,
} from '@kit/database/testing/pglite';
import {
  SeatLimitReachedError,
  createSeatEnforcementService,
} from '../seat-enforcement.service';
const now = new Date();
describe('SeatEnforcementService', () => {
  let testDb: TestDatabase;
  let service: ReturnType<typeof createSeatEnforcementService>;
  beforeAll(async () => {
    testDb = await createTestDatabase();
    service = createSeatEnforcementService(testDb.db);
  });
  beforeEach(async () => {
    await testDb.cleanup();
  });
  afterAll(async () => {
    await testDb.close();
  });
  async function seedOrgBase(orgSuffix = '1') {
    const orgId = createTestId(`org-${orgSuffix}`);
    const userId1 = createTestId('u-1');
    const userId2 = createTestId('u-2');
    const userId3 = createTestId('u-3');
    // Create test users efficiently with createMany
    await testDb.db.user.createMany({
      data: [
        {
          id: userId1,
          email: `test_u1@example.com`,
          name: 'User 1',
          emailVerified: true,
          createdAt: now,
          updatedAt: now,
        },
        {
          id: userId2,
          email: `test_u2@example.com`,
          name: 'User 2',
          emailVerified: true,
          createdAt: now,
          updatedAt: now,
        },
        {
          id: userId3,
          email: `test_u3@example.com`,
          name: 'User 3',
          emailVerified: true,
          createdAt: now,
          updatedAt: now,
        },
      ],
    });
    // Create test organization
    await testDb.db.organization.create({
      data: {
        id: orgId,
        name: 'Org',
        slug: `test_org-${orgSuffix}`,
        createdAt: now,
        metadata: '{}',
      },
    });
    return { orgId, userIds: [userId1, userId2, userId3] as const };
  }
  describe('assertCanInviteMember', () => {
    it('allows invite when under seat limit', async () => {
      const { orgId, userIds } = await seedOrgBase();
      // Create subscription with 5 seats
      await testDb.db.subscription.create({
        data: {
          id: createTestId('sub'),
          referenceId: orgId,
          plan: 'team',
          status: 'active',
          periodStart: now,
          periodEnd: new Date(now.getTime() + 30 * 24 * 60 * 60 * 1000),
          seats: 5,
        },
      });
      // Add 2 members
      await testDb.db.member.createMany({
        data: [
          {
            id: createTestId('m-1'),
            organizationId: orgId,
            userId: userIds[0],
            role: 'owner',
            createdAt: now,
          },
          {
            id: createTestId('m-2'),
            organizationId: orgId,
            userId: userIds[1],
            role: 'member',
            createdAt: now,
          },
        ],
      });
      await expect(
        service.assertCanInviteMember({ organizationId: orgId }),
      ).resolves.not.toThrow();
    });
    it('blocks invite when at seat limit', async () => {
      const { orgId, userIds } = await seedOrgBase();
      // Create subscription with only 2 seats
      await testDb.db.subscription.create({
        data: {
          id: createTestId('sub'),
          referenceId: orgId,
          plan: 'team',
          status: 'active',
          periodStart: now,
          periodEnd: new Date(now.getTime() + 30 * 24 * 60 * 60 * 1000),
          seats: 2,
        },
      });
      // Fill both seats
      await testDb.db.member.createMany({
        data: [
          {
            id: createTestId('m-1'),
            organizationId: orgId,
            userId: userIds[0],
            role: 'owner',
            createdAt: now,
          },
          {
            id: createTestId('m-2'),
            organizationId: orgId,
            userId: userIds[1],
            role: 'member',
            createdAt: now,
          },
        ],
      });
      await expect(
        service.assertCanInviteMember({ organizationId: orgId }),
      ).rejects.toBeInstanceOf(SeatLimitReachedError);
    });
    it('counts pending invitations toward seat usage', async () => {
      const { orgId, userIds } = await seedOrgBase();
      // Create subscription with 3 seats
      await testDb.db.subscription.create({
        data: {
          id: createTestId('sub'),
          referenceId: orgId,
          plan: 'team',
          status: 'active',
          periodStart: now,
          periodEnd: new Date(now.getTime() + 30 * 24 * 60 * 60 * 1000),
          seats: 3,
        },
      });
      // Add 2 members
      await testDb.db.member.createMany({
        data: [
          {
            id: createTestId('m-1'),
            organizationId: orgId,
            userId: userIds[0],
            role: 'owner',
            createdAt: now,
          },
          {
            id: createTestId('m-2'),
            organizationId: orgId,
            userId: userIds[1],
            role: 'member',
            createdAt: now,
          },
        ],
      });
      // Add 1 pending invitation
      await testDb.db.invitation.create({
        data: {
          id: createTestId('inv-1'),
          organizationId: orgId,
          email: `test_pending@example.com`,
          role: 'member',
          status: 'pending',
          inviterId: userIds[0],
          createdAt: now,
          expiresAt: new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000),
        },
      });
      // 2 members + 1 pending = 3 seats used, at limit
      await expect(
        service.assertCanInviteMember({ organizationId: orgId }),
      ).rejects.toBeInstanceOf(SeatLimitReachedError);
    });
  });
});

This test demonstrates several important patterns:

  1. Helper functions for complex setup: seedOrgBase handles the multi-table setup, keeping individual tests focused on the behavior being verified.
  2. Batch operations: Using createMany instead of multiple create calls improves test performance.
  3. Testing business rules: The seat enforcement logic considers both active members and pending invitations. The tests verify this counting behavior.
  4. Error type assertions: Using toBeInstanceOf(SeatLimitReachedError) verifies the service throws the correct error type, not just any error.

Testing Time-Dependent Logic

For services that depend on time (rate limiting, expiration checks), use Vitest's fake timers:

import { afterEach, beforeEach, describe, expect, it, vi } from 'vitest';
import { createTestDatabase } from '../test-utils/pglite-db';
import { createRateLimitService } from './rate-limit.service';
describe('RateLimitService', () => {
  let testDb: Awaited<ReturnType<typeof createTestDatabase>>;
  beforeEach(async () => {
    testDb = await createTestDatabase();
  });
  afterEach(async () => {
    vi.useRealTimers();
    await testDb.close();
  });
  it('blocks requests after limit reached within window', async () => {
    vi.useFakeTimers();
    vi.setSystemTime(0);
    const service = createRateLimitService({
      database: testDb.db as unknown as typeof import('../client').db,
    });
    const key = 'rate:limit';
    const windowSeconds = 10;
    const max = 3;
    // First 3 requests succeed
    for (let i = 0; i < max; i++) {
      const result = await service.limit(key, { windowSeconds, max });
      expect(result.success).toBe(true);
    }
    // 4th request is blocked
    const blocked = await service.limit(key, { windowSeconds, max });
    expect(blocked.success).toBe(false);
    expect(blocked.retryAfter).toBe(10);
  });
  it('resets after window expires', async () => {
    vi.useFakeTimers();
    vi.setSystemTime(0);
    const service = createRateLimitService({
      database: testDb.db as unknown as typeof import('../client').db,
    });
    const key = 'rate:reset';
    const windowSeconds = 10;
    const max = 2;
    // Use the limit
    await service.limit(key, { windowSeconds, max });
    await service.limit(key, { windowSeconds, max });
    // Blocked after hitting max
    const blocked = await service.limit(key, { windowSeconds, max });
    expect(blocked.success).toBe(false);
    // Advance past the window
    vi.advanceTimersByTime(11_000);
    // Should succeed again
    const afterWindow = await service.limit(key, { windowSeconds, max });
    expect(afterWindow.success).toBe(true);
  });
});

Timer testing considerations:

  • Set time to zero: vi.setSystemTime(0) gives predictable timestamps
  • Restore real timers: Always call vi.useRealTimers() in afterEach to prevent timer leakage between tests
  • Advance time explicitly: vi.advanceTimersByTime() moves the clock forward without waiting

Handling Prisma Type Casting

When passing the test database to services that expect the production Prisma client, you may need type casting. This is necessary when a service accepts a narrower type than PrismaClient:

// When service expects a specific database client type
const service = createRateLimitService({
  database: testDb.db as unknown as typeof import('../client').db,
});
// When service accepts PrismaClient directly, no casting needed
const service = createSeatEnforcementService(testDb.db);

The need for casting depends on how the service's database parameter is typed.

Vitest Configuration

Shared Configuration

Create a shared Vitest configuration that all packages inherit:

import { defineConfig } from 'vitest/config';
export default defineConfig({
  resolve: {
    alias: {
      'server-only': new URL('./src/server-only-mock.ts', import.meta.url)
        .pathname,
    },
  },
  test: {
    globals: true,
    environment: 'node',
    passWithNoTests: true,
    pool: 'forks',
    hookTimeout: 20_000,
    coverage: {
      provider: 'v8',
      reporter: ['text', 'json', 'html'],
      include: ['src/services/**/*.ts'],
      exclude: [
        '**/*.spec.ts',
        '**/*.integration.test.ts',
        '**/*.edge-cases.test.ts',
        '**/types/**',
      ],
    },
  },
});

Configuration highlights:

  • pool: 'forks': Each test file runs in a separate process, preventing test pollution
  • hookTimeout: 20_000: 20 seconds for beforeAll/afterAll hooks accommodates database setup
  • server-only mock: Prevents Next.js server-only import errors in tests
  • coverage excludes: Keeps coverage focused on service code, not test files

Package Configuration

Each package uses the shared config:

import vitestConfig from '@kit/vitest';
export default vitestConfig;

Server-Only Mock

Create a mock for the server-only package that Next.js uses:

// Empty export satisfies the import without throwing
export {};

This prevents the "server-only module cannot be imported" error when testing server-side code.

Common Patterns and Gotchas

Testing Error Conditions

Always test that services throw the correct errors:

it('throws NotFoundError when organization does not exist', async () => {
  await expect(
    service.getOrganization('non-existent-id'),
  ).rejects.toBeInstanceOf(NotFoundError);
});
it('throws ValidationError with specific message', async () => {
  await expect(
    service.createInvitation({ email: 'invalid' }),
  ).rejects.toThrow('Invalid email format');
});

Verifying Database State

Sometimes you need to verify the database state directly, not just the service return value:

it('creates member record when accepting invitation', async () => {
  const { orgId, invitationId, userId } = await seedInvitation();
  await service.acceptInvitation({ invitationId, userId });
  // Verify the member was created
  const member = await testDb.db.member.findFirst({
    where: { userId, organizationId: orgId },
  });
  expect(member).not.toBeNull();
  expect(member?.role).toBe('member');
  // Verify the invitation was consumed
  const invitation = await testDb.db.invitation.findUnique({
    where: { id: invitationId },
  });
  expect(invitation?.status).toBe('accepted');
});

Test Isolation Failures

If tests fail intermittently, check for:

  1. Missing cleanup: Ensure beforeEach calls testDb.cleanup()
  2. Shared state: Don't store test data in module-level variables
  3. Timer leakage: Always restore real timers in afterEach
// Bad: Shared state between tests
let testUser: TestUser;
beforeAll(async () => {
  testUser = await createUser(); // Other tests see this user
});
// Good: Fresh data per test
beforeEach(async () => {
  await testDb.cleanup();
});
it('test 1', async () => {
  const user = await createUser(); // Only this test sees this user
});

Testing Server Actions

Server Actions can't be tested directly with pgLite because they depend on Next.js runtime features (form data, headers, cookies). The solution: keep actions thin and push logic into services.

The Pattern: Thin Actions, Fat Services

Create Server Actions that only handle:

  1. Input parsing with Zod
  2. Authentication checks
  3. Delegating to a service
'use server';
import { z } from 'zod';
import { revalidatePath } from 'next/cache';
import { requireAuth } from '@kit/auth/server';
import { createInvitationService } from '@kit/organization/services';
const InviteMemberSchema = z.object({
  organizationId: z.string().min(1),
  email: z.string().email(),
  role: z.enum(['admin', 'member']),
});
export async function inviteMemberAction(formData: FormData) {
  // 1. Parse and validate input
  const input = InviteMemberSchema.parse({
    organizationId: formData.get('organizationId'),
    email: formData.get('email'),
    role: formData.get('role'),
  });
  // 2. Authenticate
  const { user } = await requireAuth();
  // 3. Delegate to service (this is what we test)
  const service = createInvitationService();
  await service.inviteMember({
    inviterId: user.id,
    ...input,
  });
  // 4. Revalidate and return
  revalidatePath(`/home/${input.organizationId}/members`);
  return { success: true };
}

Test the Service, Not the Action

The action is just glue code. Test the service where the business logic lives:

import { afterAll, beforeAll, beforeEach, describe, expect, it } from 'vitest';
import {
  type TestDatabase,
  createTestDatabase,
  createTestId,
} from '@kit/database/testing/pglite';
import { createInvitationService } from '../invitations.service';
describe('InvitationService', () => {
  let testDb: TestDatabase;
  let service: ReturnType<typeof createInvitationService>;
  beforeAll(async () => {
    testDb = await createTestDatabase();
    service = createInvitationService(testDb.db);
  });
  beforeEach(async () => {
    await testDb.cleanup();
  });
  afterAll(async () => {
    await testDb.close();
  });
  it('creates invitation with pending status', async () => {
    const { orgId, userId } = await seedOrgWithOwner();
    const result = await service.inviteMember({
      organizationId: orgId,
      inviterId: userId,
      email: 'newmember@example.com',
      role: 'member',
    });
    expect(result.status).toBe('pending');
    expect(result.email).toBe('newmember@example.com');
    // Verify in database
    const invitation = await testDb.db.invitation.findUnique({
      where: { id: result.id },
    });
    expect(invitation).not.toBeNull();
  });
  it('throws when inviting existing member', async () => {
    const { orgId, userId, memberEmail } = await seedOrgWithMember();
    await expect(
      service.inviteMember({
        organizationId: orgId,
        inviterId: userId,
        email: memberEmail,
        role: 'member',
      }),
    ).rejects.toThrow('User is already a member');
  });
});

Why This Pattern Works

  1. Zod handles validation: Invalid input throws before hitting your service. You don't need to test that Zod works.
  2. Auth is a separate concern: Test auth in E2E tests where you have a real session. Don't mock it in unit tests.
  3. Services are pure business logic: They take typed inputs and return results. No request context, no form data, no cookies.
  4. Actions are thin glue: If an action is just parse → auth → service → revalidate, there's little to test. The service tests cover the logic.

This separation also makes your code more reusable. The same service works in Server Actions, API routes, background jobs, and CLI scripts.

When to Use pgLite vs Other Testing Approaches

pgLite testing is ideal for service-layer business logic but doesn't replace all database testing scenarios.

Use pgLite when:

  • Testing business logic that involves multiple tables
  • Verifying constraint enforcement (unique, foreign keys)
  • Testing transaction behavior
  • The query logic is complex enough that mocking would be unreliable
  • You're not using RLS (Row Level Security) policies
  • Testing services that bypass Supabase and use Prisma/Drizzle directly

Avoid pgLite when:

  • Testing Row Level Security (RLS) policies (use pgtap instead)
  • Verifying database-level security constraints
  • Testing PostgreSQL functions and triggers
  • You need to test the exact database behavior with authenticated Supabase users

Use mocks when:

  • Testing code that calls external services (auth providers, payment APIs)
  • The database interaction is trivial (simple CRUD)
  • You need to test specific error scenarios from the database layer
  • Testing retry logic or connection handling

If unsure:

Start with pgLite. It catches more bugs than mocks and runs faster than Docker. Only switch to pgtap/Supabase tests when you have RLS policies, or to mocks when you need to simulate specific database failures.

pgLite Limitations

pgLite tests your service logic, not your database policies. If you're using Supabase with RLS, pgLite won't enforce those policies because:

  1. pgLite runs without the Supabase auth context
  2. RLS policies aren't applied in the pgLite environment
  3. Database functions that depend on Supabase-specific features won't work

For RLS testing, use Supabase's built-in testing or pgtap. pgLite is best for the Prisma/Drizzle approach where security is enforced at the service layer rather than the database layer.

How MakerKit Chooses Between Testing Approaches

We maintain both Supabase and ORM-based (Prisma/Drizzle) variants of MakerKit. Each uses a different testing strategy based on where authorization happens:

  • Supabase kits: Security lives in RLS policies. We use pgtap to test that policies correctly restrict access based on user context. These tests run against Supabase directly because RLS behavior can't be simulated elsewhere.
  • ORM kits (Prisma/Drizzle): Security lives in service functions. We use pgLite because we're testing TypeScript business logic, not database policies. The service layer checks permissions before queries run, so we need to test that logic with real database operations. This split emerged from experience: early on we tried mocking Prisma queries, but mocks don't catch issues like constraint violations or incorrect JOINs. pgLite gave us real PostgreSQL behavior without the CI complexity of Docker-based database testing.
  • If unsure: Start with pgLite for service tests. They catch business logic bugs and are fast. Add pgtap/Supabase tests only when you have RLS policies or database-level security to verify.

Running Database Tests

All Tests in a Package

pnpm --filter @kit/database test:unit

Watch Mode During Development

pnpm --filter @kit/database test:unit:watch

Specific Test File

pnpm --filter @kit/database exec vitest run src/services/rate-limit.service.test.ts

Coverage Report

pnpm --filter @kit/database test:unit:coverage

Frequently Asked Questions

Why use pgLite instead of a Docker PostgreSQL container?
pgLite runs in-process as WebAssembly, eliminating container startup time and external dependencies. Tests start in milliseconds instead of seconds. Your CI pipeline doesn't need Docker support, and developers can run tests without any setup.
Does pgLite support all PostgreSQL features?
pgLite supports the vast majority of PostgreSQL features used in application code: transactions, constraints, JSON operations, common SQL functions. Some advanced features like full-text search extensions may have limitations. For typical ORM usage through Prisma, pgLite provides complete compatibility.
Can I use pgLite with Drizzle instead of Prisma?
Yes. pgLite works with any ORM that supports PostgreSQL. MakerKit's Drizzle kit uses the same pgLite testing approach. The setup differs slightly (Drizzle uses drizzle-orm/pglite instead of pglite-prisma-adapter), but the testing patterns are identical.
How do I test Server Actions?
Don't test Server Actions directly. Instead, keep actions thin (parse input with Zod, check auth, call service) and test the underlying service with pgLite. The service contains the business logic; the action is just glue code.
How do I handle migrations in test databases?
The createTestDatabase utility reads your Prisma migration files and executes them in order. Your test database has the exact same schema as production. When you add new migrations, tests automatically pick them up.
Why use beforeAll instead of beforeEach for database creation?
Creating the database and applying migrations takes time. By creating once per test file (beforeAll) and truncating data between tests (beforeEach), you get isolation without the overhead of recreating the schema for every test.
How do I test services that need both database and external API mocks?
Inject the database through the service factory, and mock external dependencies with vi.mock(). The database tests real queries while external calls are controlled.
What if my tests are still slow?
Check for unnecessary database operations in test setup, reduce the number of records created per test, and ensure you're not recreating the database for each test. The pool: 'forks' config runs test files in parallel.

Next Steps

Some other posts you might like...
Smoke Testing Your SaaS: A Practical Guide for FoundersLearn how to implement effective smoke testing for your SaaS application. This guide covers essential test scenarios, implementation strategies, and best practices to quickly verify core functionality.
End-to-End Testing Your SaaS with Playwright: A Comprehensive GuideThis comprehensive article teaches end-to-end testing using Playwright, based on real-world examples from a Next.js SaaS application. You'll learn industry best practices, test architecture patterns, and practical implementation strategies.
Next.js 16 App Router Project Structure: The Definitive GuideLearn how to structure a production-grade Next.js 16 App Router project using Turborepo, route groups, feature packages, and battle-tested patterns from shipping real SaaS applications.
Generate Dynamic OG Images with Next.js 16Learn to generate dynamic Open Graph images in Next.js 16 using ImageResponse and Sharp. Covers the opengraph-image.tsx convention, custom fonts, and Vercel deployment.
Programmatic Authentication with Supabase and CypressTesting code that requires users to be signed in can be tricky. In this post, we show you how to sign in programmatically with Supabase Authentication to improve the speed of your Cypress tests and increase their reliability.