Database Testing

Purpose: Write effective database integration tests
Last Updated: 2024-11-26

Overview

Database testing uses ephemeral databases and test fixtures to provide isolated, fast test environments. Tests should use generated wrappers from @db rather than raw SQL.

Core principle: Tests mirror production code by using generated functions.

For complete test examples, see Testing Examples.

Testing Philosophy

Use Generated Wrappers in Tests

✅ Correct: Use generated wrappers ❌ Wrong: Use raw SQL queries

Tests should use the same API as production code for consistency and type safety.

Benefits

• Type safety: Compile-time checks on test code
• Refactoring: Tests break when functions change
• Consistency: Same API in tests and production
• Documentation: Tests show how to use functions

Test Fixtures

Available Fixtures

Location: tests/config/database.context.ts

Fixture	Scope	Purpose
mgmtPool	Worker	System-level database pool for admin operations
mgmtClient	Worker	Client for database administration
emptyDbPool	Worker	Pool for empty database (no migrations)
emptyDbClient	Worker	Client for empty database
migration	Worker	Migration result with statistics
newDbPool	Worker	Pool for fresh database with all migrations
newDbClient	Test	Client for migrated database (auto-cleanup)

Worker-scoped: Created once per worker, shared across tests
Test-scoped: Created per test, isolated

Basic Usage

import { test } from '@/test-utils-integration/config/database.context';
import { createResource } from '@db/graph';

test('uses fresh database', async ({ newDbClient }) => {
  const id = await createResource(newDbClient, {
    p_type_namespace: 'test.user'
  });
  
  expect(id).toBeGreaterThan(0);
});

Test Patterns

For complete examples, see Testing Examples.

Pattern 1: Simple Query Test

See: Example 1

Use test fixtures to test basic database operations.

Pattern 2: Factory Pattern

See: Example 2

Create reusable factory functions for common test data:

// tests/utils/factories/database-fixtures.ts
export async function createTestUser(client: PoolClient, overrides?) {
  return await createResource(client, {
    p_type_namespace: 'test.user',
    p_external_id: overrides?.external_id ?? `user-${Date.now()}`,
    p_data: overrides?.data ?? { role: 'tester' }
  });
}

Pattern 3: Error Handling

See: Error Handling Examples

Test error cases like duplicate entries and validation failures.

Pattern 4: Transaction Test

See: Example 6

Test transaction rollback behavior with proper isolation.

Pattern 5: Migration Testing

See: Example 4

Verify migrations complete successfully and schema changes are applied.

Ephemeral Databases

Structure

interface EphemeralDatabase {
  name: string;           // Database name
  pool: Pool;            // Connection pool
  migration: MigrationResult;  // Migration stats
}

Usage

FP Version: src/lib/db/fp/ephemeral.ts
Promises Version: src/lib/db/promises/ephemeral.ts

Most tests should use fixtures rather than creating ephemeral databases directly.

Test Isolation

Parallel Execution

Tests run in parallel by default with isolated databases per worker.

Configuration (vitest.config.ts):

export default defineConfig({
  test: {
    pool: 'threads',
    poolOptions: {
      threads: {
        singleThread: false,  // Enable parallel
        minThreads: 1,
        maxThreads: 4
      }
    }
  }
});

Cleanup Strategies

Automatic (recommended):

test('auto cleanup', async ({ newDbClient }) => {
  // Client released automatically
  // Database dropped after worker completes
});

Manual (when needed):

test('manual cleanup', async ({ newDbClient }) => {
  const id = await createResource(newDbClient, { ... });
  await newDbClient.query('DELETE FROM graph.resource WHERE id = $1', [id]);
});

Performance Optimization

Template Database Pattern

Using template databases provides 500x faster test database creation:

• Without template: ~5s per test worker
• With template: ~10ms per test worker

See Local Development for details.

Connection Pooling

Test pools use fewer connections than production:

const testPool = await getPool('test-worker', {
  max: 5,  // Fewer connections for tests
  idleTimeoutMillis: 1000,
  application_name: `test-worker-${workerId}`
});

CI/CD Integration

GitHub Actions

services:
  postgres:
    image: postgres:18
    env:
      POSTGRES_DB: savvi_studio_template
      POSTGRES_USER: savvi
      POSTGRES_PASSWORD: password
    options: >-
      --health-cmd pg_isready
      --health-interval 10s

steps:
  - name: Run tests
    run: pnpm test:integration

Docker Compose

services:
  postgres-test:
    image: postgres:18-alpine
    environment:
      POSTGRES_DB: test_template
    volumes:
      - ./db:/docker-entrypoint-initdb.d:ro

Troubleshooting

Tests Hang

Cause: Connections not released

Solution: Always use fixtures or ensure manual cleanup with try/finally

Database Not Cleaned

Cause: Tests sharing state

Solution: Use test-scoped fixtures or wrap in transactions

Slow Tests

Check:

• Using template database?
• Running in parallel?
• Batching operations?

Optimize: Batch multiple operations in single test

Best Practices

✅ Do

• Use generated wrappers from @db
• Use test fixtures for database setup
• Test error cases
• Use factories for common test data
• Run tests in parallel

❌ Don't

• Use raw SQL in tests
• Share mutable state between tests
• Leave connections open
• Hardcode test data
• Skip error case testing

Related Documentation

• Best Practices - Using generated wrappers
• Testing Examples - Complete test examples
• Local Development - Template database pattern
• Database Libraries - FP and promises APIs

Quick Reference

Task	Fixture	See
Basic test	newDbClient	Example 1
Admin operation	mgmtClient	Fixtures section above
Empty database	emptyDbClient	Migration testing
Verify migrations	migration	Example 4

---

Next steps: See Testing Examples for complete code examples, or Best Practices for general database patterns.