Advanced Testing Patterns

Testing Database Interactions

Test database operations with isolation, factory functions, and strategies that keep tests fast and reliable.

The Database Testing Challenge

Database tests are inherently more complex than pure unit tests because they have state. Each test must start from a known, clean state and leave no residue that would affect subsequent tests.

Three common strategies:

  1. Transaction wrapping — each test runs in a transaction that rolls back after the test
  2. Table truncation — truncate tables in beforeEach
  3. Database-per-test — create a fresh database schema for each test (slowest but most isolated)

Transaction Wrapping (Recommended)

Wrapping each test in a transaction and rolling back is the fastest approach:

typescript
import { db } from '@/lib/db';

describe('UserRepository', () => {
  beforeEach(async () => {
    // Start a transaction — all test operations are inside it
    await db.transaction.begin();
  });

  afterEach(async () => {
    // Roll back everything the test did — instant cleanup
    await db.transaction.rollback();
  });

  it('creates and retrieves a user', async () => {
    const created = await UserRepository.create({
      email: 'alice@example.com',
      name: 'Alice',
    });

    expect(created.id).toBeDefined();

    const found = await UserRepository.findById(created.id);
    expect(found.email).toBe('alice@example.com');
  });
});

Factory Functions

Repetitive test data setup leads to brittle, hard-to-read tests. Factory functions create test data with sensible defaults:

typescript
// test/factories.ts
let idCounter = 1;

export function createUserData(overrides: Partial<User> = {}): CreateUserInput {
  return {
    email: `test-${idCounter++}@example.com`,
    name: 'Test User',
    role: 'user',
    ...overrides,
  };
}

export async function createTestUser(overrides: Partial<User> = {}) {
  return db.users.create(createUserData(overrides));
}

export async function createTestPost(authorId: string, overrides: Partial<Post> = {}) {
  return db.posts.create({
    title: 'Test Post',
    content: 'Test content here.',
    authorId,
    ...overrides,
  });
}
typescript
// Tests become clean and readable
it('user can view their own posts', async () => {
  const user = await createTestUser();
  const post1 = await createTestPost(user.id, { title: 'First Post' });
  const post2 = await createTestPost(user.id, { title: 'Second Post' });

  const posts = await PostRepository.findByUser(user.id);
  expect(posts).toHaveLength(2);
  expect(posts.map(p => p.title)).toContain('First Post');
});

Testing Constraints and Validations

typescript
it('enforces unique email constraint', async () => {
  const userData = createUserData({ email: 'alice@example.com' });
  await db.users.create(userData);

  await expect(db.users.create(userData))
    .rejects
    .toThrow(/unique constraint|duplicate/i);
});

it('enforces foreign key constraint on post creation', async () => {
  await expect(
    db.posts.create({ authorId: 'non-existent-user-id', title: 'Test' })
  ).rejects.toThrow(/foreign key/i);
});

Testing with Supabase

typescript
// Use Supabase local development for tests
// supabase start

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // Service role bypasses RLS
);

it('row-level security prevents cross-user data access', async () => {
  const userAClient = createClient(url, key, {
    global: { headers: { Authorization: `Bearer ${userAToken}` } },
  });

  // Create a post as User A
  const { data: post } = await supabase.from('posts').insert({
    user_id: userA.id,
    content: 'Private post',
  }).select().single();

  // User B tries to read User A's post — should get nothing
  const { data: unauthorized } = await userBClient
    .from('posts')
    .select()
    .eq('id', post.id)
    .single();

  expect(unauthorized).toBeNull();
});

Key Takeaways

  • Transaction wrapping is the fastest database test isolation strategy — each test rolls back all changes
  • Factory functions reduce test data boilerplate and make tests readable
  • Test database constraints explicitly — unique violations, foreign key failures, not-null constraints
  • Use the service role key for test setup to bypass RLS; use regular auth tokens when testing RLS behavior
  • Never rely on data from other tests — each test must be fully independent

Example

typescript
// Repository tests with factories and transaction isolation
import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { db } from '@/lib/db';
import { createTestUser, createTestPost } from '../test/factories';
import { PostRepository } from './PostRepository';

describe('PostRepository', () => {
  beforeEach(() => db.transaction.begin());
  afterEach(() => db.transaction.rollback());

  describe('findByUser', () => {
    it('returns only posts belonging to the specified user', async () => {
      const alice = await createTestUser({ name: 'Alice' });
      const bob = await createTestUser({ name: 'Bob' });

      await createTestPost(alice.id, { title: 'Alice Post 1' });
      await createTestPost(alice.id, { title: 'Alice Post 2' });
      await createTestPost(bob.id, { title: 'Bob Post' });

      const alicePosts = await PostRepository.findByUser(alice.id);
      expect(alicePosts).toHaveLength(2);
      expect(alicePosts.every(p => p.authorId === alice.id)).toBe(true);
    });

    it('returns empty array when user has no posts', async () => {
      const user = await createTestUser();
      const posts = await PostRepository.findByUser(user.id);
      expect(posts).toHaveLength(0);
    });
  });
});
Try it yourself — TYPESCRIPT