Savvi Studio

Type System Mappings

Purpose: PostgreSQL ↔ TypeScript/Zod type conversions
Last Updated: 2024-11-28

Overview

The codegen system maps PostgreSQL types to TypeScript types with Zod validation. This document describes how each PostgreSQL type category translates to TypeScript.

Base Type Mappings

PostgreSQL TypeScript Zod Schema
integer, int4 number z.number().int()
bigint, int8 bigint z.bigint()
smallint, int2 number z.number().int()
numeric, decimal number z.number()
real, float4 number z.number()
double precision, float8 number z.number()
text string z.string()
varchar(n) string z.string()
char(n) string z.string()
boolean boolean z.boolean()
timestamp Date z.string().datetime()
timestamptz Date z.string().datetime()
date Date z.string().date()
time string z.string().time()
interval string z.string()
json unknown z.unknown()
jsonb unknown z.unknown()
uuid string z.string().uuid()
bytea Buffer z.instanceof(Buffer)
ltree string z.string()

Composite Types (Records)

PostgreSQL Definition

CREATE TYPE graph.node_data AS (
    id bigint,
    type_namespace ltree,
    data jsonb,
    created_at timestamp
);

Generated TypeScript

export const nodeDataSchema = z.object({
  id: z.bigint(),
  type_namespace: z.string(),
  data: z.unknown(),
  created_at: z.string().datetime()
});

export type NodeData = z.infer<typeof nodeDataSchema>;

Enum Types

PostgreSQL Definition

CREATE TYPE auth.algorithm AS ENUM (
    'RS256',
    'RS384',
    'RS512',
    'ES256',
    'ES384'
);

Generated TypeScript

export const algorithmSchema = z.enum([
  'RS256',
  'RS384',
  'RS512',
  'ES256',
  'ES384'
]);

export type Algorithm = z.infer<typeof algorithmSchema>;
// Type: 'RS256' | 'RS384' | 'RS512' | 'ES256' | 'ES384'

Array Types

PostgreSQL Definition

CREATE FUNCTION get_user_tags(p_user_id bigint)
RETURNS text[]

Generated TypeScript

export async function getUserTags(
  client: PoolClient,
  params: { p_user_id: bigint }
): Promise<string[]>

Nested Arrays

PostgreSQL: integer[][]
TypeScript: number[][]
Zod: z.array(z.array(z.number().int()))

Domain Types

Domains are constrained base types.

PostgreSQL Definition

CREATE DOMAIN email AS text
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Generated TypeScript

export const emailSchema = z.string().refine(
  (val) => /^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/.test(val),
  { message: 'Invalid email format' }
);

export type Email = z.infer<typeof emailSchema>;

Common Domain Patterns

Numeric bounds:

CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0);

→ z.number().int().min(1)

String length:

CREATE DOMAIN username AS varchar(50) CHECK (LENGTH(VALUE) >= 3);

→ z.string().min(3).max(50)

Range Types

PostgreSQL Definition

CREATE FUNCTION get_date_range()
RETURNS daterange;

Generated TypeScript

export const dateRangeSchema = z.object({
  lower: z.string().date(),
  upper: z.string().date(),
  lowerInclusive: z.boolean(),
  upperInclusive: z.boolean()
});

export type DateRange = z.infer<typeof dateRangeSchema>;

Range Type Mappings

PostgreSQL Lower/Upper Type
int4range number
int8range bigint
numrange number
tsrange Date
tstzrange Date
daterange Date

Nullable Types

PostgreSQL Definition

CREATE FUNCTION find_user(p_email text)
RETURNS bigint  -- can return NULL

Generated TypeScript

export async function findUser(
  client: PoolClient,
  params: { p_email: string }
): Promise<bigint | null>

Optional Parameters

PostgreSQL Definition

CREATE FUNCTION create_user(
    p_email text,
    p_name text DEFAULT NULL,
    p_age integer DEFAULT 18
)
RETURNS bigint

Generated TypeScript

export async function createUser(
  client: PoolClient,
  params: {
    p_email: string;
    p_name?: string | null;  // Optional and nullable
    p_age?: number;           // Optional with default
  }
): Promise<bigint>

Table Return Types

PostgreSQL Definition

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

Generated TypeScript

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

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

SETOF Return Types

Similar to TABLE return types - both return arrays.

PostgreSQL Definition

CREATE FUNCTION get_active_users()
RETURNS SETOF auth.user_record

Generated TypeScript

export async function getActiveUsers(
  client: PoolClient
): Promise<UserRecord[]>

Void Return Type

PostgreSQL Definition

CREATE FUNCTION log_action(p_message text)
RETURNS void

Generated TypeScript

export async function logAction(
  client: PoolClient,
  params: { p_message: string }
): Promise<void>

Type Safety Guarantees

Compile-Time Safety

// ✅ Valid - all types match
await createUser(client, {
  p_email: 'user@example.com',
  p_age: 25
});

// ❌ Compile error - wrong type
await createUser(client, {
  p_email: 'user@example.com',
  p_age: '25'  // string instead of number
});

// ❌ Compile error - missing required parameter
await createUser(client, {
  p_age: 25  // missing p_email
});

Runtime Validation

try {
  await createUser(client, {
    p_email: untrustedInput,
    p_age: userAge
  });
} catch (error) {
  if (error instanceof ZodError) {
    console.error('Validation failed:', error.errors);
  }
}

Special Cases

Overloaded Functions

PostgreSQL:

CREATE FUNCTION process_data(p_id bigint) RETURNS text;
CREATE FUNCTION process_data(p_id bigint, p_flag boolean) RETURNS text;

Generated TypeScript (first signature only):

export async function processData(
  client: PoolClient,
  params: { p_id: bigint }
): Promise<string>

Workaround: Create wrapper functions with different names.

Custom Types

Unsupported PostgreSQL types (geometric, network):

  • Map to unknown or string
  • Add custom validation if needed
  • Document expected format

Type Inference

Using z.infer

const userSchema = z.object({
  id: z.bigint(),
  email: z.string(),
  age: z.number().int()
});

// Infer TypeScript type from schema
type User = z.infer<typeof userSchema>;
// Equivalent to:
// type User = {
//   id: bigint;
//   email: string;
//   age: number;
// }

Examples

For detailed code examples, see:

Key Takeaway: Every PostgreSQL type maps to a TypeScript type with Zod validation, ensuring both compile-time and runtime safety.