Architectural Sync: Mastering SQL-to-Zod Schema Generation

SQL to Zod: Type-Safe Database Access Without an ORM

Modern ORMs like Prisma and Drizzle generate TypeScript types automatically — but millions of Node.js applications still use raw pg, mysql2, or postgres clients where query results are typed as any[]. SQL-to-Zod bridges this gap: you convert your DDL to Zod schemas, then use those schemas to validate and type every query result at the repository layer. The database enforces constraints at write time; Zod enforces and types them at read time, catching schema drift before it reaches business logic.

Live Example: PostgreSQL DDL to Zod Repository

-- Input SQL DDL
CREATE TABLE users (
    id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    email      TEXT         NOT NULL UNIQUE,
    name       TEXT         NOT NULL,
    role       TEXT         NOT NULL DEFAULT 'user'
               CHECK (role IN ('admin', 'editor', 'user', 'guest')),
    age        INTEGER      CHECK (age >= 18),
    score      NUMERIC(5,2) DEFAULT 0,
    is_active  BOOLEAN      NOT NULL DEFAULT TRUE,
    metadata   JSONB,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

// Generated Zod Schema
import { z } from 'zod';

export const UserSchema = z.object({
  id:         z.string().uuid(),
  email:      z.string().email(),
  name:       z.string().min(1),
  role:       z.enum(['admin', 'editor', 'user', 'guest']),
  age:        z.number().int().min(18).nullable(),
  score:      z.number().multipleOf(0.01),    // NUMERIC(5,2) precision
  is_active:  z.boolean(),
  metadata:   z.record(z.unknown()).nullable(),  // JSONB
  created_at: z.coerce.date(),               // TIMESTAMPTZ → Date
  deleted_at: z.coerce.date().nullable(),
});

export type User = z.infer<typeof UserSchema>;

// Insert/Update schemas derived from the base schema
export const CreateUserSchema = UserSchema.pick({
  email: true, name: true, role: true,
}).extend({
  age: z.number().int().min(18).optional(),
});

export type CreateUserInput = z.infer<typeof CreateUserSchema>;

SQL Type to Zod Mapping Reference

// PostgreSQL types
const pgTypeMap = {
  // Integers
  'SMALLINT':  z.number().int(),
  'INTEGER':   z.number().int(),
  'BIGINT':    z.string(),          // BigInt exceeds JS number precision
  'SERIAL':    z.number().int().positive(),
  'BIGSERIAL': z.string(),

  // Floats
  'REAL':             z.number(),
  'DOUBLE PRECISION': z.number(),
  'FLOAT':            z.number(),

  // Exact numerics — use string to preserve precision
  'NUMERIC':  z.string().regex(/^-?\d+(\.\d+)?$/),
  'DECIMAL':  z.string().regex(/^-?\d+(\.\d+)?$/),
  'MONEY':    z.string(),           // e.g. "$49.99"

  // Strings
  'TEXT':       z.string(),
  'VARCHAR':    z.string(),
  'CHAR':       z.string(),
  'NAME':       z.string(),

  // Booleans
  'BOOLEAN': z.boolean(),

  // Dates and times
  'DATE':        z.string().date(),    // "2024-01-15"
  'TIME':        z.string().time(),    // "08:30:00"
  'TIMESTAMP':   z.coerce.date(),
  'TIMESTAMPTZ': z.coerce.date(),      // drivers return Date or string

  // JSON
  'JSON':  z.unknown(),
  'JSONB': z.unknown(),

  // Binary
  'BYTEA': z.instanceof(Buffer),

  // Special types
  'UUID':  z.string().uuid(),
  'INET':  z.string().ip(),
  'CIDR':  z.string(),
  'ARRAY': (inner: z.ZodType) => z.array(inner),
};

Nullable vs. NOT NULL: Getting Optionality Right

// SQL:  name TEXT NOT NULL
// Zod:  z.string()                    (required, non-null)

// SQL:  bio TEXT                       (nullable by default)
// Zod:  z.string().nullable()          (may be null)

// SQL:  deleted_at TIMESTAMPTZ         (nullable)
// Zod:  z.coerce.date().nullable()

// SQL:  nickname TEXT DEFAULT NULL     (nullable, has default)
// Zod:  z.string().nullable().optional()  // absent on INSERT → null at SELECT

// SELECT always returns every column (not optional in TypeScript sense)
// INSERT may omit columns with defaults — use .partial() for insert schemas

const UserSelectSchema = z.object({
  id:        z.string().uuid(),     // always present in SELECT
  email:     z.string().email(),    // NOT NULL
  nickname:  z.string().nullable(), // may be null
  deletedAt: z.coerce.date().nullable(),
});

// For INSERT: columns with defaults can be omitted
const UserInsertSchema = UserSelectSchema
  .omit({ id: true })    // generated by DB
  .extend({
    nickname:  z.string().optional(),  // absent = use default (NULL)
    deletedAt: z.coerce.date().optional(),
  });

Repository Pattern: Validate Every Query Result

import { Pool } from 'pg';
import { z } from 'zod';
import { UserSchema, CreateUserSchema, type User, type CreateUserInput } from './schemas/user';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export class UserRepository {
  // Validate query result — catches schema drift
  async findById(id: string): Promise<User | null> {
    const result = await pool.query(
      'SELECT id, email, name, role, age, score, is_active, metadata, created_at, deleted_at FROM users WHERE id = $1',
      [id]
    );
    if (result.rows.length === 0) return null;

    // Will throw if DB returns unexpected shape
    return UserSchema.parse(result.rows[0]);
  }

  async findAll(limit = 20): Promise<User[]> {
    const result = await pool.query(
      'SELECT * FROM users WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT $1',
      [limit]
    );

    // z.array() validates each row
    return z.array(UserSchema).parse(result.rows);
  }

  async create(input: CreateUserInput): Promise<User> {
    // Validate input before inserting
    const validated = CreateUserSchema.parse(input);

    const result = await pool.query(
      `INSERT INTO users (email, name, role, age)
       VALUES ($1, $2, $3, $4)
       RETURNING *`,
      [validated.email, validated.name, validated.role, validated.age]
    );

    return UserSchema.parse(result.rows[0]);
  }
}

Handling CHECK Constraints with .refine()

-- SQL: CHECK (end_date > start_date)
-- SQL: CHECK (price > 0 AND price <= 99999.99)
-- SQL: CHECK (status IN ('active', 'inactive', 'archived'))

import { z } from 'zod';

const OrderSchema = z.object({
  id:         z.string().uuid(),
  startDate:  z.coerce.date(),
  endDate:    z.coerce.date(),
  price:      z.number().positive().max(99999.99),
  status:     z.enum(['active', 'inactive', 'archived']),
}).refine(
  (data) => data.endDate > data.startDate,
  {
    message: "end_date must be after start_date",
    path: ["endDate"],
  }
);

// For application-layer validation BEFORE insert
// (DB constraint is the final guard; Zod gives better error messages)
const result = OrderSchema.safeParse(orderInput);
if (!result.success) {
  const errors = result.error.flatten().fieldErrors;
  return { status: 400, errors };
}

SQL ENUM Types

-- PostgreSQL ENUM
CREATE TYPE user_role AS ENUM ('admin', 'editor', 'user', 'guest');
CREATE TABLE users (
    id   UUID PRIMARY KEY,
    role user_role NOT NULL DEFAULT 'user'
);

-- Generated Zod enum
const UserRoleSchema = z.enum(['admin', 'editor', 'user', 'guest']);
type UserRole = z.infer<typeof UserRoleSchema>;
// → "admin" | "editor" | "user" | "guest"

// Useful for runtime check on user-provided values
function isValidRole(role: string): role is UserRole {
  return UserRoleSchema.safeParse(role).success;
}

Best Practices for Production

• Generate Zod schemas from the live database, not DDL files: Query information_schema.columns and information_schema.check_constraints to derive Zod schemas programmatically. Schema files drift; the live database doesn't.
• Use z.coerce.date() for all timestamp columns: Different PostgreSQL drivers return timestamps as Date objects, ISO strings, or Unix numbers depending on configuration. z.coerce.date() handles all three formats without special-casing.
• Use z.string() for NUMERIC/DECIMAL/BIGINT: JavaScript's number type loses precision beyond 2^53. Financial amounts and large IDs stored as NUMERIC should be parsed as strings and handled with a decimal library (Decimal.js, big.js).
• Validate at the repository boundary, not in every query: Create a thin repository layer that wraps your SQL client and validates every result. Business logic above the repository can trust that the data is correctly typed.

FAQ

Q: Why use SQL-to-Zod instead of Prisma or Drizzle?
A: For legacy databases, microservices that need minimal dependencies, or applications where a full ORM's abstraction cost is prohibitive. Raw SQL gives you access to every database feature without translation; Zod gives you the type safety. It's the right tradeoff when you need control more than abstraction.

Q: What about MySQL type mapping differences?
A: MySQL returns some types differently: TINYINT(1) maps to boolean, BIGINT as string, DATETIME without timezone as string, DECIMAL as string. The mapping is the same conceptually — use z.coerce.date() for dates, z.string() for BIGINT and DECIMAL. Use mysql2's typeCast option to control raw type coercion before Zod validation.

Q: How do I handle JSON/JSONB columns?
A: z.unknown() accepts any JSON value. For typed JSONB columns with a known structure, use a typed schema: z.object({ theme: z.string(), locale: z.string() }). If the structure varies per row, use z.record(z.unknown()) and validate the sub-schema separately.