Database Patterns Catalog

Purpose: Quick reference for common database interaction patterns
Last Updated: 2024-11-26

Overview

This catalog provides quick examples of common database patterns. For detailed explanations, see Best Practices.

For complete code examples, see the examples directory.

Core principle: Use generated wrappers from @db, not raw SQL.

Basic Operations

Simple Query

See: Example 1 - Simple Query

Use withClient with a generated wrapper for basic queries.

Create Resource

See: Example 2 - Create Resource

Create resources using generated wrappers with type-safe parameters.

See: Example 4 - Multiple Related Operations

Reuse connection for related operations within a single withClient call.

Authentication

See: Example 5 - Authentication Flow

Login and retrieve session information using generated wrappers.

Logout Flow

See: Example 6 - Logout Flow

End user session with logout wrapper.

Session Check

See: Example 7 - Session Check

Check if user has active session.

Transactions

Simple Transaction

See: Example 1 - Simple Transaction

Use setup/teardown options in withClient for transaction management.

With Savepoints

See: Example 2 - Transaction with Savepoints

Use savepoints for partial rollback within transactions.

Error Handling

Basic Error Handling

See: Example 1 - Basic Error Handling

Handle ZodError for validation and PostgreSQL error codes for database errors.

Retry on Serialization Failure

See: Example 3 - Retry on Serialization Failure

Implement retry logic for transient database errors.

Testing

Basic Test

See: Example 1 - Basic Test with Fixtures

Use test fixtures for database tests.

Error Case Test

See: Example 2 - Handling Duplicate External ID

Test error handling scenarios.

Transaction Test

See: Example 4 - Transaction Test

Test transaction rollback behavior.

Advanced

Cursor Pagination

import { withClient } from '@/lib/db';
import { listNodes } from '@db/graph';
import { encodeCursor, decodeCursor } from '@/lib/cursor';

async function paginateNodes(cursor?: string) {
  return await withClient(async (client) => {
    const limit = 50;
    const cursorData = cursor ? decodeCursor(cursor) : null;
    
    const nodes = await listNodes(client, {
      limit: limit + 1,
      after_id: cursorData?.lastId
    });
    
    const hasMore = nodes.length > limit;
    const results = hasMore ? nodes.slice(0, limit) : nodes;
    
    const nextCursor = hasMore
      ? encodeCursor({
          lastId: results[results.length - 1].id,
          limit,
          direction: 'forward',
          timestamp: Date.now(),
          version: 1
        })
      : null;
    
    return { nodes: results, cursor: nextCursor, hasMore };
  });
}

Details: See Cursor Patterns

Dynamic Filtering

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

interface SearchFilters {
  name?: string;
  status?: string[];
  createdAfter?: Date;
}

function buildSearchQuery(filters: SearchFilters) {
  const conditions: string[] = [];
  const params: any[] = [];
  
  if (filters.name) {
    conditions.push(`name ILIKE $${params.length + 1}`);
    params.push(`%${filters.name}%`);
  }
  
  if (filters.status?.length) {
    conditions.push(`status = ANY($${params.length + 1})`);
    params.push(filters.status);
  }
  
  if (filters.createdAfter) {
    conditions.push(`created_at >= $${params.length + 1}`);
    params.push(filters.createdAfter);
  }
  
  const where = conditions.length > 0 
    ? `WHERE ${conditions.join(' AND ')}` 
    : '';
    
  return {
    sql: `SELECT * FROM nodes ${where} ORDER BY created_at DESC LIMIT 100`,
    params
  };
}

// Usage
const results = await withClient(async (client) => {
  const { sql, params } = buildSearchQuery(filters);
  return await client.query(sql, params);
});

Note: Use parameterized queries, never string interpolation

Batch Operations

import { withClient } from '@/lib/db';
import { createResource } from '@db/graph';

const ids = await withClient(async (client) => {
  return await Promise.all([
    createResource(client, { p_type_namespace: 'type1', ... }),
    createResource(client, { p_type_namespace: 'type2', ... }),
    createResource(client, { p_type_namespace: 'type3', ... })
  ]);
});

Migration Examples

Before (Raw SQL)

// ❌ Old pattern
const result = await client.query(
  'SELECT auth.session_id() AS session_id'
);
const sessionId = result.rows[0].session_id;

After (Generated Wrapper)

// ✅ New pattern
import { sessionId } from '@db/auth';
const sid = await sessionId(client);

Details: See Best Practices

Quick Reference

Task	Pattern	Document
Simple query	Use generated wrapper	Best Practices
Multiple queries	Reuse client in `withClient`	Best Practices
Transaction	Use setup/teardown options	Best Practices
Error handling	Catch ZodError & PG errors	Best Practices
Testing	Use test fixtures	Testing
Pagination	Use cursor pattern	Cursor Patterns
Migrations	Add SQL file, run codegen	Migrations

Anti-Patterns

❌ Never do these:

// String interpolation (SQL injection!)
const query = `SELECT * FROM users WHERE id = '${userId}'`;

// Raw SQL when wrapper exists
await client.query('SELECT studio.login($1)', [token]);

// Holding connection during processing
const data = await withClient(async (client) => {
  const result = await getResource(client, { ... });
  return result.map(item => expensiveOperation(item)); // Bad!
});

// Multiple connections for atomic operation
await withClient(async (c) => { await op1(c); });
await withClient(async (c) => { await op2(c); }); // Should be same client!