SQL DDL to TypeScript Generator

SQL to TypeScript: Generating Types from Your Database Schema

Your database schema is the most authoritative description of your data model — but TypeScript knows nothing about it. Every time you cast a query result to any, or maintain a User interface that silently drifts from the real table, you're creating a gap that produces runtime bugs. Generating TypeScript types directly from SQL closes that gap permanently.

SQL Type Mapping Reference

-- SQL column type            → TypeScript type
-- INT, BIGINT, SMALLINT      → number
-- FLOAT, DOUBLE, DECIMAL     → number  (but see DECIMAL pitfall below)
-- VARCHAR, TEXT, CHAR        → string
-- BOOLEAN, TINYINT(1)        → boolean
-- DATE, DATETIME, TIMESTAMP  → string  (ISO 8601 from most drivers)
-- JSON, JSONB                → Record<string, unknown>  (or a specific interface)
-- UUID                       → string
-- ENUM('a','b','c')          → 'a' | 'b' | 'c'
-- NULL allowed               → T | null
-- NOT NULL                   → T

Basic Table to Interface

-- Input SQL
CREATE TABLE products (
  id          INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku         VARCHAR(64)  NOT NULL UNIQUE,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  price_cents INT          NOT NULL,
  in_stock    BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at  DATETIME
);

// Generated TypeScript
export interface Product {
  id:          number;
  sku:         string;
  name:        string;
  description: string | null;   // TEXT with no NOT NULL → nullable
  price_cents: number;
  in_stock:    boolean;
  created_at:  string;
  deleted_at:  string | null;
}

Row, Insert, and Update: Three Separate Types

Using a single interface for all three operations is a common mistake. The database auto-generates id and created_at, so those must not appear in your insert payload:

// What SELECT returns
export interface ProductRow {
  id:          number;
  sku:         string;
  name:        string;
  description: string | null;
  price_cents: number;
  in_stock:    boolean;
  created_at:  string;
  deleted_at:  string | null;
}

// For INSERT — omit auto-generated columns, make defaulted ones optional
export type ProductInsert = Omit<ProductRow, 'id' | 'created_at'> & {
  in_stock?: boolean;
  deleted_at?: string | null;
};

// For PATCH — everything optional except the primary key
export type ProductUpdate = Partial<Omit<ProductRow, 'id' | 'created_at'>>;

Enum Columns: Union Types over TypeScript Enums

Prefer union literal types for SQL enum columns. They don't generate any runtime code and work naturally with string comparisons:

-- SQL
CREATE TABLE tickets (
  priority ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium'
);

// ✅ Union literal type — no runtime overhead, serializes cleanly
export type TicketPriority = 'low' | 'medium' | 'high' | 'critical';

export interface TicketRow {
  priority: TicketPriority;
}

const nextPriority: Record<TicketPriority, TicketPriority> = {
  low: 'medium', medium: 'high', high: 'critical', critical: 'critical',
};

// ❌ TypeScript enum — generates runtime code, no benefit over union types here

JSON / JSONB Columns

Typing JSON columns as Record<string, unknown> destroys type safety. Give them a specific interface:

CREATE TABLE events (
  id      INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
  type    VARCHAR(64) NOT NULL,
  payload JSON        NOT NULL
);

// ✅ Discriminated union — TypeScript narrows correctly
interface UserCreatedPayload { userId: string; email: string; }
interface OrderPlacedPayload { orderId: number; totalCents: number; }

type AppEvent =
  | { type: 'user.created'; payload: UserCreatedPayload }
  | { type: 'order.placed'; payload: OrderPlacedPayload };

function handle(event: AppEvent) {
  if (event.type === 'user.created') {
    console.log(event.payload.email); // ✅ typed
  }
}

Foreign Keys and JOIN Results

export type OrderStatus = 'pending' | 'paid' | 'shipped' | 'cancelled';

export interface OrderRow {
  id:          number;
  customer_id: number;
  status:      OrderStatus;
  total_cents: number;
  placed_at:   string;
}

// INNER JOIN — customer is always present
export interface OrderWithCustomer extends OrderRow {
  customer: CustomerRow;
}

// LEFT JOIN — customer may be absent
export interface OrderWithOptionalCustomer extends OrderRow {
  customer: CustomerRow | null;
}

Integration with Query Builders

// Kysely — reference the generated types directly
import type { ProductRow, ProductInsert } from './db-types';

async function createProduct(data: ProductInsert): Promise<ProductRow> {
  return db
    .insertInto('products')
    .values(data)
    .returningAll()
    .executeTakeFirstOrThrow();
}

// Drizzle ORM — infer types from the schema definition
import { mysqlTable, int, varchar, boolean, datetime } from 'drizzle-orm/mysql-core';
import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';

export const products = mysqlTable('products', {
  id:          int('id').autoincrement().primaryKey(),
  sku:         varchar('sku', { length: 64 }).notNull(),
  price_cents: int('price_cents').notNull(),
  in_stock:    boolean('in_stock').notNull().default(true),
});

export type ProductRow    = InferSelectModel<typeof products>;
export type ProductInsert = InferInsertModel<typeof products>;

Common Pitfalls

• DECIMAL as number: MySQL DECIMAL columns are returned as strings by most Node.js drivers to preserve precision. Type them as string and parse explicitly — don't assume they arrive as number.
• DATETIME and timezones: MySQL DATETIME stores no timezone. Your driver may interpret it as local time or UTC depending on config. Use TIMESTAMP columns (stored as UTC) or append Z explicitly when constructing Date objects.
• LEFT JOIN nullability: A LEFT JOIN makes every column from the right table potentially null, even columns marked NOT NULL in the table definition. Always type the right side as T | null.
• Exposing Row types to API clients: Your raw row may contain sensitive columns like password_hash. Define a separate public type with Omit for API responses rather than returning the raw row.

FAQ

Q: Should I hand-write these types or generate them from the live schema?
A: For any project that's actively changing, generate from the live schema so types can't silently drift. Drizzle, Prisma, and Kysely all have first-class codegen. Hand-written types are fine for one-off scripts but become a liability the moment the schema changes.

Q: How do I handle BIGINT columns?
A: JavaScript number can't safely represent integers larger than 2⁵³. Most drivers return BIGINT as a string to avoid precision loss. Type them as string in your interface, and use BigInt() only if you need arithmetic on those values.

Q: How should I handle snake_case database columns in a camelCase TypeScript codebase?
A: Keep the Row type in snake_case (matching the actual column names) and transform at the boundary — either in a mapping layer or by configuring your query builder to camelCase results automatically. Mixing cases in the Row type itself creates confusion about what the real column name is.

Q: What about views and stored procedures?
A: Views should be typed like tables — they have a stable shape. Stored procedures that return varying result sets are harder to type statically; wrap them in a function with an explicit return type and validate the shape with Zod if the schema can vary.