Savvi Studio

Generated Function Wrappers

Purpose: Using type-safe generated functions
Last Updated: 2024-11-28

Overview

Generated function wrappers provide type-safe interfaces to PostgreSQL functions with automatic parameter validation and return type checking.

For detailed examples and patterns: See Function Patterns

Basic Usage

Import and Call

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

const id = await withClient(async (client) => {
  return await createResource(client, {
    p_type_namespace: 'test.user',
    p_external_id: 'user-123',
    p_data: { name: 'John Doe' }
  });
});

Function Signatures

SQL Pattern TypeScript Signature
function_name() function(client: PoolClient): Promise<T>
function_name(p_x text) function(client, params: { p_x: string }): Promise<T>
function_name(p_x text DEFAULT NULL) function(client, params: { p_x?: string | null }): Promise<T>

Return Types

SQL Return Type TypeScript Type Example
Single value Promise<T> Promise<bigint>
Nullable Promise<T | null> Promise<string | null>
Table Promise<T[]> Promise<UserRow[]>
Void Promise<void> Promise<void>

Table Returns

SQL:

CREATE FUNCTION list_users()
RETURNS TABLE (id bigint, email text, created_at timestamp);

TypeScript:

type ListUsersRow = {
  id: bigint;
  email: string;
  created_at: Date;
};

function listUsers(client: PoolClient): Promise<ListUsersRow[]>

Parameter Conventions

Aspect Convention
Naming SQL p_ prefix preserved: p_user_id
Syntax Named object: { p_email: 'x' } (not positional)
Required No DEFAULT: { p_email: string }
Optional With DEFAULT: { p_age?: number }
Nullable DEFAULT NULL: { p_name?: string | null }

Type Safety

Compile-Time Validation

// ✅ Valid
await createResource(client, {
  p_type_namespace: 'test.user',
  p_data: { key: 'value' }
});

// ❌ Compile error - missing parameter
await createResource(client, { p_data: {} });

// ❌ Compile error - wrong type
await createResource(client, {
  p_type_namespace: 123,  // number not string
  p_data: {}
});

Runtime Validation

import { ZodError } from 'zod';

try {
  await createResource(client, {
    p_type_namespace: untrustedInput,
    p_data: userProvidedData
  });
} catch (error) {
  if (error instanceof ZodError) {
    console.error('Validation failed:', error.errors);
  }
}

Common Usage Patterns

Quick examples - see Function Patterns for comprehensive patterns.

Transaction

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

await withTransaction(async (client) => {
  const id1 = await createResource(client, params1);
  const id2 = await createResource(client, params2);
  await linkResources(client, { p_from_id: id1, p_to_id: id2 });
});

Error Handling

import { ZodError } from 'zod';
import { DatabaseError } from 'pg';

try {
  await createResource(client, params);
} catch (error) {
  if (error instanceof ZodError) {
    // Validation error
  } else if (error instanceof DatabaseError) {
    // Database error (constraints, etc.)
  }
}

Batch Operations

await withClient(async (client) => {
  return await Promise.all([
    createResource(client, params1),
    createResource(client, params2),
    createResource(client, params3)
  ]);
});

More patterns: Function Patterns

Testing

Unit Tests (Mock)

import { vi } from 'vitest';

const mockClient = {
  query: vi.fn().mockResolvedValue({
    rows: [{ result: 123n }]
  })
} as any;

const result = await createResource(mockClient, params);
expect(result).toBe(123n);

Integration Tests

import { withTestClient } from '@/test/utils';

await withTestClient(async (client) => {
  const id = await createResource(client, params);
  const resource = await getResource(client, { p_id: id });
  expect(resource).toBeDefined();
});

More testing patterns: Function Patterns

Performance

Connection Management

// ✅ Always use withClient/withTransaction
await withClient(async (client) => {
  return await operation(client);
});

// ❌ Don't manage connections manually

Parallel vs Sequential

// Parallel (independent)
await Promise.all([op1(client), op2(client)]);

// Sequential (dependent)
const r1 = await op1(client);
const r2 = await op2(client, r1);

More performance patterns: Function Patterns

Naming Conventions

SQL to TypeScript

PostgreSQL snake_case converts to TypeScript camelCase:

SQL Function TypeScript Function
create_user_account createUserAccount
get_user_by_email getUserByEmail
list_active_users listActiveUsers

Parameters preserve exact SQL names:

  • SQL: p_user_id → TypeScript: p_user_id

Generated Code

Each function includes:

  • Input validation (Zod schema)
  • Parameterized SQL query
  • Output validation
  • TypeScript types
  • JSDoc from SQL comments

IDE Support: Autocomplete, type checking, inline docs, go-to-definition, safe refactoring

Troubleshooting

Issue Solution
Type errors Regenerate: pnpm db:codegen
Function missing Check schema: pnpm db:codegen --verbose
Validation fails Check parameter types match SQL
Stale types Restart TypeScript server

More troubleshooting: Troubleshooting Guide

Core Documentation

Examples and Patterns

Advanced Topics

Key Takeaway: Generated functions provide type-safe, validated interfaces to database operations with zero boilerplate. For detailed patterns and examples, see Function Patterns.