SQL to TypeScript: The Complete Type Mapping Guide

Writing TypeScript interfaces by hand from a SQL schema is tedious and error-prone. The tricky part isn't just mapping VARCHAR to string — it's handling nullability, dates, JSON columns, arrays, and foreign key relationships correctly.

This guide provides a complete reference for mapping SQL column types to TypeScript types across PostgreSQL, MySQL, and SQLite.

The Fundamental Mapping

At the highest level, SQL types map to TypeScript types in these broad categories:

SQL Category	TypeScript Type
Integer columns	number
Floating-point columns	number
String / text columns	string
Boolean columns	boolean
Timestamp / datetime columns	Date or string
UUID columns	string
JSON / JSONB columns	Record<string, unknown>
Binary data (BYTEA, BLOB)	Buffer or Uint8Array
Array columns (PostgreSQL)	Type[]

PostgreSQL Type Mappings

PostgreSQL has the richest type system of any open-source database. Here are the important mappings:

Integer Types

-- PostgreSQL
id SERIAL PRIMARY KEY           → id: number
user_id INTEGER NOT NULL        → userId: number
big_counter BIGINT              → bigCounter: number   // Use bigint for > 2^53
small_num SMALLINT              → smallNum: number

Note: PostgreSQL's BIGINT can hold values larger than JavaScript's safe integer limit (Number.MAX_SAFE_INTEGER = 2^53 - 1). If your BIGINT columns could hold values exceeding this, consider using TypeScript's bigint type instead of number.

String Types

name VARCHAR(100) NOT NULL      → name: string
bio TEXT                        → bio: string | null
code CHAR(5)                    → code: string

Boolean

is_active BOOLEAN DEFAULT TRUE  → isActive: boolean
deleted BOOLEAN                 → deleted: boolean | null

Dates and Times

This is where it gets nuanced. When data comes from a PostgreSQL database through a Node.js driver like pg, dates are typically already parsed to JavaScript Date objects. When data comes through an HTTP API (JSON), dates are ISO 8601 strings:

created_at TIMESTAMP            → createdAt: Date    // Direct DB connection
created_at TIMESTAMP            → createdAt: string  // Via REST API (JSON)
updated_at TIMESTAMPTZ          → updatedAt: Date    // Timezone preserved
birth_date DATE                 → birthDate: string  // 'YYYY-MM-DD' string
start_time TIME                 → startTime: string  // 'HH:MM:SS' string

The safest approach is to define both and use the one that matches your data layer:

interface User {
  createdAt: Date;    // When using pg/postgres.js directly
  // or
  createdAt: string;  // When receiving from a REST API
}

UUID

id UUID DEFAULT gen_random_uuid() → id: string

PostgreSQL UUIDs are stored as 16 bytes internally but exposed as 36-character hyphenated strings (a1b2c3d4-e5f6-...) in all drivers. Always type them as string in TypeScript.

JSON and JSONB

settings JSONB                  → settings: Record<string, unknown>
metadata JSON                   → metadata: Record<string, unknown>

For JSON columns with known shapes, define a specific interface:

interface UserSettings {
  theme: 'light' | 'dark';
  notifications: boolean;
  language: string;
}

interface User {
  settings: UserSettings;
}

Arrays

tags TEXT[]                     → tags: string[]
scores INTEGER[]                → scores: number[]
flags BOOLEAN[]                 → flags: boolean[]

Handling Nullability Correctly

This is the most important distinction. SQL has three states for a column:

1. NOT NULL — column will always have a value
2. No constraint (nullable) — column might be NULL
3. DEFAULT value — column has a default but can still be NULL

In TypeScript with strict null checks enabled (which you should always use), these map to:

// NOT NULL column → required, non-optional
interface User {
  name: string;        // Always present
  age: number;         // Always present
  isActive: boolean;   // Always present
}

// NULL column → optional or nullable
interface User {
  bio?: string;         // Optional (undefined or string)
  // or
  bio: string | null;   // Explicitly null-able
  phone: string | null; // From database: null if not set
}

Best practice: use string | null for database-facing types (where the DB driver returns null), and use field?: string for API-facing types (where the field might simply be absent from the JSON).

MySQL Type Mappings

MySQL type mapping is similar to PostgreSQL with a few differences:

-- MySQL specific
id INT AUTO_INCREMENT PRIMARY KEY   → id: number
is_active TINYINT(1)                → isActive: boolean  // Cast 0/1 to bool
created_at DATETIME                 → createdAt: Date
metadata JSON                       → metadata: Record<string, unknown>
status ENUM('active','inactive')    → status: 'active' | 'inactive'

For MySQL's TINYINT(1) boolean, many ORMs handle the 0/1 → boolean conversion automatically. If you're using raw queries, you'll need to cast explicitly: Boolean(row.is_active).

SQLite Type Mappings

SQLite uses a dynamic type system with only 5 storage classes: NULL, INTEGER, REAL, TEXT, BLOB. Most SQLite types are "type affinities" rather than strict types:

-- SQLite
id INTEGER PRIMARY KEY              → id: number
price REAL                         → price: number
name TEXT NOT NULL                 → name: string
created_at TEXT                    → createdAt: string  // ISO 8601 stored as TEXT
is_active INTEGER                  → isActive: boolean  // Cast 0/1
data BLOB                          → data: Uint8Array

The key challenge with SQLite is that boolean and date types don't exist natively. You must cast them in your TypeScript code:

const isActive = Boolean(row.is_active);
const createdAt = new Date(row.created_at);

Generating TypeScript Interfaces Automatically

Instead of manually mapping types, use SchemaForge's free SQL to TypeScript generator. Paste any CREATE TABLE statement from PostgreSQL, MySQL, or SQLite and get complete TypeScript interfaces instantly — with correct nullability, typed arrays, and navigation properties for foreign keys.

The generator runs entirely in your browser. Your database schema never leaves your machine.