Codegen Quick Reference

Purpose: One-page reference for common tasks and patterns
Last Updated: 2024-11-28

Common Commands

Command	Purpose	When to Use
pnpm db:codegen	Generate all schemas	After SQL changes
pnpm db:codegen --schema <name>	Generate one schema	Working on specific schema
pnpm db:codegen --verbose	Debug output	Troubleshooting issues
pnpm db:codegen --check	Verify up-to-date	CI/CD pipelines
pnpm db:codegen --force	Force regeneration	Cache issues

See: CLI Reference for all options

Import Patterns

Import All Schemas

import { studio, auth, graph } from '@db';

// Use schema-namespaced functions
await graph.createResource(client, params);

Import Specific Functions

import { createResource, getResource } from '@db/graph';
import { createUser, findUser } from '@db/auth';

// Use directly
await createResource(client, params);

Import Types

import type { NodeData, EdgeType } from '@db/graph/types';
import type { UserRecord } from '@db/auth/types';

Basic Usage Pattern

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

// Simple operation
const id = await withClient(async (client) => {
  return await createResource(client, {
    p_type_namespace: 'test.user',
    p_data: { name: 'John' }
  });
});

Common Workflows

1. After SQL Changes

# 1. Edit SQL files
vim db/300_graph.sql

# 2. Apply migrations
pnpm db:reset  # or pnpm db:migrate

# 3. Regenerate wrappers
pnpm db:codegen

# 4. Restart dev server
pnpm dev

2. Transaction Pattern

import { withTransaction } from '@/lib/db';
import { createResource, linkResources } from '@db/graph';

await withTransaction(async (client) => {
  const id1 = await createResource(client, { /*...*/ });
  const id2 = await createResource(client, { /*...*/ });
  await linkResources(client, { 
    p_from_id: id1, 
    p_to_id: id2,
    p_edge_type: 'authored'
  });
});

3. Error Handling

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

try {
  await createResource(client, params);
} catch (error) {
  if (error instanceof ZodError) {
    // Validation error
    console.error('Invalid input:', error.errors);
  } else if (error instanceof DatabaseError) {
    // Database error
    console.error('DB error:', error.message);
  }
}

4. Batch Operations

const results = await withClient(async (client) => {
  return await Promise.all([
    createResource(client, { p_type_namespace: 'test.user', p_data: {} }),
    createResource(client, { p_type_namespace: 'test.post', p_data: {} }),
    createResource(client, { p_type_namespace: 'test.comment', p_data: {} })
  ]);
});

Function Signatures Quick Guide

Simple Function (No Parameters)

await getUserCount(client)
// Returns: Promise<bigint>

Function with Required Parameters

await findUser(client, { 
  p_email: 'user@example.com' 
})
// Returns: Promise<bigint | null>

Function with Optional Parameters

await createUser(client, {
  p_email: 'user@example.com',    // Required
  p_name: 'John Doe',              // Optional
  p_age: 30                        // Optional with default
})
// Returns: Promise<bigint>

Function Returning Table

const rows = await listUsers(client)
// Returns: Promise<Array<{ id: bigint, email: string, ... }>>

Type System Quick Reference

PostgreSQL Type	TypeScript Type	Example
text	string	'hello'
integer	number	42
bigint	bigint	123n
boolean	boolean	true
jsonb	unknown	{ key: 'value' }
timestamp	Date	new Date()
text[]	string[]	['a', 'b']
enum	'value1' | 'value2'	'active'

See: Type System for complete mappings

Troubleshooting Quick Fixes

Problem	Solution	Command
Type errors	Regenerate code	pnpm db:codegen
Missing function	Check schema included	pnpm db:codegen --verbose
Validation error	Check parameter types	See error message
Stale types	Force regeneration	pnpm db:codegen --force
IDE not updating	Restart TS server	Cmd/Ctrl + Shift + P → "Restart TS Server"

Generated Code Structure

src/__generated__/
├── index.ts              # Export all schemas
├── audit/
│   ├── index.ts         # Audit schema functions
│   └── types.ts         # Audit types
├── auth/
│   ├── index.ts         # Auth schema functions
│   └── types.ts         # Auth types
├── graph/
│   ├── index.ts         # Graph schema functions
│   └── types.ts         # Graph types
└── studio/
    ├── index.ts         # Studio schema functions
    └── types.ts         # Studio types

Parameter Conventions

Naming

• Parameters prefixed with p_: p_user_id, p_email
• Preserved in TypeScript: Use exact names from SQL

Required vs Optional

// Required (no DEFAULT in SQL)
{ p_email: string }

// Optional with default
{ p_age?: number }

// Optional nullable
{ p_name?: string | null }

JSON Parameters

// jsonb parameters accept any JSON-serializable value
{ p_data: unknown }

// Type it when you know the structure
{ p_data: { name: string; age: number } }

Testing Patterns

Mock Client

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

const result = await myFunction(mockClient, params);

Integration Test

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();
});

Performance Tips

Connection Pooling

✅ Always use withClient or withTransaction

await withClient(async (client) => {
  return await operation(client);
});

❌ Never manage connections manually

Batch Queries

// Parallel (independent operations)
await Promise.all([
  operation1(client),
  operation2(client)
]);

// Sequential (dependent operations)
const result1 = await operation1(client);
const result2 = await operation2(client, result1);

Documentation Quick Links

Topic	Document	Description
Commands	CLI Reference	All CLI options
Overview	Overview	Architecture & design
Types	Type System	Type mappings
Usage	Usage	Development workflow
Functions	Function Wrappers	Generated functions
Examples	Examples	Code examples
Issues	Troubleshooting	Common problems

Getting Help

Common Questions:

1. Function missing? → Check schema name: pnpm db:codegen --verbose
2. Type error? → Regenerate: pnpm db:codegen
3. Validation fails? → Check parameters match SQL signature
4. Performance issue? → See Database Patterns

Resources:

• Full Documentation
• Examples Directory
• Troubleshooting Guide

---

Pro Tip: Bookmark this page for quick access to common patterns and commands.