Database Best Practices

Purpose: Golden rules for database interaction
Last Updated: 2024-11-26

Overview

This document defines the mandatory patterns for interacting with the PostgreSQL database. These rules ensure type safety, maintainability, and consistency across the codebase.

Golden Rules

🔐 Database Interaction Requirements

1. Application code MUST call PostgreSQL functions via generated wrappers in src/__generated__ (aliased as @db)
2. Use withClient + generated functions for all business logic
3. Raw client.query() is allowed only for:
   • Migrations & schema management
   • Admin/maintenance scripts
   • Diagnostics/troubleshooting
   • Explicitly documented performance-critical paths
4. Never use string interpolation for SQL queries (SQL injection risk)

Using Generated Wrappers

Import Pattern

// ✅ CORRECT: Import from @db alias
import { withClient } from '@/lib/db';
import { createResource, getResource } from '@db/graph';
import { sessionId, subjectId } from '@db/auth';
import { login, logout } from '@db/studio';

Basic Query Pattern

See: Basic Usage Examples

// ✅ CORRECT: Use generated wrapper
const id = await withClient(async (client) => {
  return await createResource(client, {
    p_type_namespace: 'test.user',
    p_external_id: 'alice',
    p_data: { name: 'Alice' }
  });
});

// ❌ WRONG: Raw SQL query
const result = await withClient(async (client) => {
  return await client.query(
    'SELECT graph.create_resource($1, $2, $3)',
    ['test.user', 'alice', '{"name": "Alice"}']
  );
});

Why Generated Wrappers?

1. Type Safety

Compile-time errors for invalid code:

• Missing required parameters caught at build time
• Wrong parameter types caught at build time
• Return types automatically inferred

2. Runtime Validation

Zod validates untrusted input at runtime, catching data issues before they reach the database.

3. IDE Support

Full autocomplete for parameters, types, and documentation.

4. Refactoring Safety

Changing SQL function signatures breaks TypeScript builds, forcing updates.

Common Patterns

For complete code examples, see:

• Basic Usage Examples
• Transaction Examples
• Error Handling Examples

Pattern 1: Simple Query

See: Example 1 - Simple Query

Use withClient with a generated wrapper for basic queries.

Pattern 2: Create and Verify

See: Example 3 - Create and Verify

Reuse connection for related operations in a single withClient call.

Pattern 3: Authentication Flow

See: Example 5 - Authentication Flow

Login and retrieve session information using generated wrappers.

Pattern 4: Transaction

See: Example 1 - Simple Transaction

Use setup/teardown options in withClient for transaction management.

Pattern 5: Error Handling

See: Example 1 - Basic Error Handling

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

Anti-Patterns

❌ Anti-Pattern 1: Raw SQL

// NEVER DO THIS
const result = await client.query(
  'SELECT graph.create_resource($1, $2)',
  [typeNamespace, externalId]
);

Why wrong: No type safety, no validation, breaks on schema changes

Fix: Use generated wrapper

❌ Anti-Pattern 2: String Interpolation

// NEVER DO THIS - SQL INJECTION!
const userId = req.params.id;
const query = `SELECT * FROM users WHERE id = '${userId}'`;
await client.query(query);

Attack: userId = "1'; DROP TABLE users; --" executes arbitrary SQL

Fix: Always use parameterized queries or generated wrappers

❌ Anti-Pattern 3: Holding Connections

// DON'T: Process data while holding connection
const result = await withClient(async (client) => {
  const data = await getResource(client, { ... });
  return data.map(item => expensiveTransform(item));  // Bad!
});

Fix: Release connection first, then process data

❌ Anti-Pattern 4: Manual Type Casting

// DON'T: Manual type conversion
const result = await client.query('SELECT * FROM nodes WHERE id = $1', [id]);
const node = { id: parseInt(result.rows[0].id) };

Fix: Generated functions handle types automatically

❌ Anti-Pattern 5: Ignoring Errors

// DON'T: Swallow errors silently
try {
  await createResource(client, { ... });
} catch (error) {
  // Silent failure - BAD!
}

Fix: Log and rethrow or handle appropriately

When Raw SQL is Acceptable

Raw SQL is permitted only for:

1. Migrations: Schema changes (CREATE TABLE, ALTER TABLE, etc.)
2. Admin Operations: VACUUM, REINDEX, maintenance tasks
3. Diagnostics: Monitoring queries, performance analysis
4. Dynamic Filtering: Complex queries with variable conditions (use parameterized queries)

Requirements:

• ✅ Must use parameterized queries (never string interpolation)
• ✅ Must document why generated wrapper not used
• ✅ Must be reviewed carefully
• ✅ Should be rare exceptions

Testing with Generated Wrappers

See: Testing Examples

Tests should mirror production code by using generated functions:

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

test('creates and retrieves resource', async ({ newDbClient }) => {
  const id = await createResource(newDbClient, {
    p_type_namespace: 'test.user',
    p_external_id: 'test-user'
  });
  
  const resources = await getResource(newDbClient, { p_id: id });
  expect(resources[0].external_id).toBe('test-user');
});

Code Review Checklist

When reviewing database code, verify:

• Uses generated wrappers from @db
• No raw SQL (unless justified)
• No string interpolation in queries
• Proper error handling
• Connections released promptly
• Appropriate use of transactions
• Tests use generated wrappers

Performance Considerations

Connection Pooling

Connection pooling is automatic via withClient - connections are acquired from the pool and released automatically.

Connection Reuse

Nested withClient calls automatically reuse the outer connection when possible.

Batch Operations

Batch multiple operations in a single withClient call:

const ids = await withClient(async (client) => {
  return await Promise.all([
    createResource(client, { ... }),
    createResource(client, { ... }),
    createResource(client, { ... })
  ]);
});

Related Documentation

• Codegen System - How wrappers are generated
• Testing - Testing with wrappers
• Patterns - More examples
• Database Libraries - Connection management
• Examples - Complete code examples

Quick Reference

Pattern	Link
Simple query	Basic Usage
Create and verify	Basic Usage
Authentication	Basic Usage
Transaction	Transactions
Error handling	Error Handling
Testing	Testing

---

Remember: If you're writing SQL strings in application code, you're probably doing it wrong. Use generated wrappers from @db.