Testing Utilities

Database testing utilities for unit and integration tests in the Next.js Drizzle SaaS Kit.

The @kit/database package includes comprehensive testing utilities for writing unit and integration tests with real database interactions. No mocking required.

Overview

The testing utilities provide:

PGlite - In-memory PostgreSQL database for fast, isolated tests
Real PostgreSQL - Connection for integration tests requiring full PostgreSQL features
Factory functions - Generate test data with sensible defaults
Auth context utilities - Test multi-tenant authorization

Installation

The testing utilities are included in @kit/database. Import from:

// Main testing utilities (factories)
import { createTestUser, createTestOrganization } from '@kit/database/testing';
// PGlite-specific utilities
import { createTestDatabase } from '@kit/database/testing/pglite';

In-Memory Database (PGlite)

PGlite provides a WebAssembly-based PostgreSQL implementation for fast, isolated testing without external dependencies.

createTestDatabase()

Creates an in-memory PostgreSQL database with the full schema applied:

import { createTestDatabase, type TestDatabase } from '@kit/database/testing/pglite';
import { user } from '@kit/database';
describe('User Service', () => {
  let testDb: TestDatabase;
  beforeAll(async () => {
    testDb = await createTestDatabase();
  });
  afterEach(async () => {
    await testDb.cleanup(); // Clear all data between tests
  });
  afterAll(async () => {
    await testDb.close(); // Close connection
  });
  it('should create a user', async () => {
    const result = await testDb.db.insert(user).values({
      id: 'user_123',
      email: 'test@example.com',
      name: 'Test User',
      emailVerified: true,
      createdAt: new Date(),
      updatedAt: new Date(),
    }).returning();
    expect(result[0].email).toBe('test@example.com');
  });
});

TestDatabase Interface

interface TestDatabase {
  db: DrizzleInstance;           // Drizzle ORM instance
  client: PGlite;                // Raw PGlite client
  cleanup: () => Promise<void>;  // Truncate all tables
  close: () => Promise<void>;    // Close connection
}

Tables Cleared by cleanup()

The cleanup() method truncates these tables with CASCADE:

member
subscription
organization
user
account
session
verification
invitation
two_factor
organization_role
rate_limit

Real PostgreSQL Testing

For integration tests requiring a real PostgreSQL instance, the kit includes setupTestDatabase() in the internal test utilities. This connects to an actual PostgreSQL database for tests that need full PostgreSQL features.

setupTestDatabase()

Located at packages/database/src/test-utils/test-db.ts:

packages/database/src/test-utils/test-db.ts

import { setupTestDatabase } from './test-db';
let testDb: Awaited<ReturnType<typeof setupTestDatabase>>;
beforeAll(async () => {
  testDb = await setupTestDatabase();
});
afterEach(async () => {
  await testDb.cleanup();
});
afterAll(async () => {
  await testDb.teardown();
});

When to use real PostgreSQL

Use PGlite (via createTestDatabase()) for most tests. Use real PostgreSQL only when testing features PGlite doesn't fully support, like specific extensions or performance benchmarks.

Environment Variables

Variable	Description	Default
`TEST_DATABASE_URL`	Test database connection string (preferred)	-
`DATABASE_URL`	Fallback connection string	-
-	Default if neither is set	`postgresql://postgres:postgres@localhost:5432/postgres`

Never test against production

Always use a dedicated test database. The cleanup() method truncates all tables. Using your production or development database will result in data loss.

Factory Functions

Generate test data with sensible defaults:

createTestUser()

import { createTestUser } from '@kit/database/testing';
// With defaults
const user = createTestUser();
// { id: 'abc123...', email: 'test-abc1@makerkit.dev', name: 'Test User abc1', ... }
// With overrides
const admin = createTestUser({
  email: 'admin@example.com',
  name: 'Admin User',
});

Default Values:

Field	Default
`id`	Random 32-character hex string
`email`	`test-{id}@makerkit.dev`
`emailVerified`	`true`
`name`	`Test User {first 4 chars of id}`
`image`	`null`
`createdAt`	Current date
`updatedAt`	Current date

createTestOrganization()

import { createTestOrganization } from '@kit/database/testing';
const org = createTestOrganization({
  name: 'Acme Corp',
  slug: 'acme-corp',
});

Default Values:

Field	Default
`id`	Random 32-character hex string
`name`	`Test Organization {first 4 chars of id}`
`slug`	`test-org-{id}`
`logo`	`null`
`metadata`	`"{}"`
`createdAt`	Current date

createTestMember()

import { createTestUser, createTestOrganization, createTestMember } from '@kit/database/testing';
const user = createTestUser();
const org = createTestOrganization();
const member = createTestMember({
  userId: user.id,        // Required
  organizationId: org.id, // Required
  role: 'owner',
});

Default Values:

Field	Default
`id`	Random 32-character hex string
`userId`	Empty string (must override)
`organizationId`	Empty string (must override)
`role`	`'member'`
`createdAt`	Current date

createTestSubscription()

import { createTestOrganization, createTestSubscription } from '@kit/database/testing';
const org = createTestOrganization();
const subscription = createTestSubscription({
  referenceId: org.id,
  plan: 'enterprise-annual',
  seats: 10,
});

Default Values:

Field	Default
`id`	Random 32-character hex string
`plan`	`'pro-monthly'`
`referenceId`	Empty string (should override)
`customer_id`	`cus_test_{id}`
`subscription_id`	`sub_test_{id}`
`status`	`'active'`
`periodStart`	Current date
`periodEnd`	30 days from now
`cancelAtPeriodEnd`	`false`
`seats`	`1`

Auth Context Utilities

Utilities for testing multi-tenant authorization:

createAuthContext()

Creates a basic authorization context for an authenticated user:

import { createAuthContext } from '@kit/database';
const ctx = createAuthContext(
  'user_123',    // userId
  null,          // organizationId (optional)
  null           // role (optional)
);
// Use in queries
const posts = await db.select()
  .from(postsTable)
  .where(ctx.user(postsTable)); // eq(postsTable.userId, 'user_123')

createOrgAuthContext()

Creates an organization-scoped authorization context:

import { createOrgAuthContext } from '@kit/database';
const ctx = createOrgAuthContext(
  'user_123',  // userId
  'org_456',   // organizationId (required)
  'admin'      // role (required)
);
// Filter by organization
const projects = await db.select()
  .from(projectsTable)
  .where(ctx.org(projectsTable)); // eq(projectsTable.organizationId, 'org_456')
// Auto-fill organizationId on insert
await db.insert(projectsTable).values(
  ctx.values(projectsTable, {
    name: 'New Project',
    // organizationId is automatically added
  })
);

AuthorizationError

Custom error class for authorization failures:

import { AuthorizationError } from '@kit/database';
throw AuthorizationError.notAuthenticated();
// Error: "Not authenticated"
throw AuthorizationError.noOrganization();
// Error: "No active organization"
throw AuthorizationError.notAuthorized('project');
// Error: "Not authorized to access project"

Complete Example

import { describe, it, expect, beforeAll, afterEach, afterAll } from 'vitest';
import { createTestDatabase } from '@kit/database/testing/pglite';
import {
  createTestUser,
  createTestOrganization,
  createTestMember,
  createTestSubscription,
} from '@kit/database/testing';
import { createOrgAuthContext } from '@kit/database';
import { user, organization, member, subscription } from '@kit/database';
describe('Organization Billing', () => {
  let testDb: Awaited<ReturnType<typeof createTestDatabase>>;
  beforeAll(async () => {
    testDb = await createTestDatabase();
  });
  afterEach(async () => {
    await testDb.cleanup();
  });
  afterAll(async () => {
    await testDb.close();
  });
  it('should create organization with subscription', async () => {
    // Create test data using factories
    const testUser = createTestUser();
    const testOrg = createTestOrganization();
    const testMember = createTestMember({
      userId: testUser.id,
      organizationId: testOrg.id,
      role: 'owner',
    });
    const testSub = createTestSubscription({
      referenceId: testOrg.id,
      plan: 'pro-monthly',
    });
    // Insert into database
    await testDb.db.insert(user).values(testUser);
    await testDb.db.insert(organization).values(testOrg);
    await testDb.db.insert(member).values(testMember);
    await testDb.db.insert(subscription).values(testSub);
    // Create auth context for queries
    const ctx = createOrgAuthContext(testUser.id, testOrg.id, 'owner');
    // Query with auth context
    const [result] = await testDb.db.select()
      .from(subscription)
      .where(ctx.org(subscription));
    expect(result.plan).toBe('pro-monthly');
    expect(result.status).toBe('active');
  });
});

Common Mistakes to Avoid

Forgetting to call cleanup() in afterEach: Tests that share data can produce flaky results. Always truncate tables between tests for isolation.

Using the global db instead of testDb.db: The global db connects to your development database. Always use testDb.db in tests.

Not awaiting async operations: Factory functions return data objects, not promises. But database operations return promises. Make sure to await insert(), select(), etc.

Testing against production/development database: Always use TEST_DATABASE_URL or the default local connection. Never run tests against databases with real data.

Vitest Configuration

For automatic database setup in all tests, configure Vitest:

vitest.config.ts

import { defineConfig } from 'vitest/config';
export default defineConfig({
  test: {
    globals: true,
    environment: 'node',
    include: ['**/*.test.ts'],
    setupFiles: ['./vitest.setup.ts'],
  },
});

Frequently Asked Questions

What is PGlite and why is it used for testing?

PGlite is PostgreSQL compiled to WebAssembly that runs in-memory. It provides real PostgreSQL behavior without Docker or external services, making tests fast, isolated, and portable. Each test suite gets a fresh database.

When should I use PGlite vs real PostgreSQL for tests?

Use PGlite for unit tests that need database interactions but don't depend on specific PostgreSQL features. Use real PostgreSQL (via setupTestDatabase) for integration tests that need features PGlite doesn't support, like certain extensions or advanced query features.

Why does cleanup() use TRUNCATE instead of DELETE?

TRUNCATE is faster than DELETE for clearing tables because it doesn't scan rows or fire triggers. The CASCADE option handles foreign key constraints. This keeps test isolation fast even with many tables.

How do I test multi-tenant authorization?

Use createOrgAuthContext() to create auth contexts for different users and organizations. Test that queries with ctx.org() only return data for the expected organization, and that inserts with ctx.values() correctly set organizationId.

Previous: Rate Limit Service