# Testing Utilities

> Database testing utilities for unit and integration tests in the TanStack Start Drizzle SaaS Kit.

*Canonical: https://makerkit.dev/docs/tanstack-drizzle/database/testing*

---

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:

```typescript
// 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:

```typescript
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

```typescript
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`:

```typescript {% title="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();
});
```

{% callout type="default" title="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.
{% /callout %}

### 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` |

{% callout type="warning" title="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.
{% /callout %}

## Factory Functions

Generate test data with sensible defaults:

### createTestUser()

```typescript
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()

```typescript
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()

```typescript
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()

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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

```typescript
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:

```typescript {% title="vitest.config.ts" %}
import { defineConfig } from 'vitest/config';

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

{% faq
   title="Frequently Asked Questions"
   items=[
     {"question": "What is PGlite and why is it used for testing?", "answer": "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."},
     {"question": "When should I use PGlite vs real PostgreSQL for tests?", "answer": "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."},
     {"question": "Why does cleanup() use TRUNCATE instead of DELETE?", "answer": "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."},
     {"question": "How do I test multi-tenant authorization?", "answer": "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](./rate-limit-service)