SQL to TypeScript Type Mapping Reference
A comprehensive reference for mapping SQL column types to TypeScript types. Use this guide when building TypeScript interfaces from database schemas or when working with ORMs like Prisma and Drizzle.
SQL to TypeScript / Prisma / Drizzle Converter
Convert SQL CREATE TABLE statements into TypeScript interfaces, Prisma schema, or Drizzle ORM table definitions. Supports PostgreSQL, MySQL, and SQLite syntax.
SQL Type Mapping Reference
| SQL Type | TypeScript | Prisma |
|---|---|---|
| INT / INTEGER / SERIAL | number | Int |
| BIGINT / BIGSERIAL | number | BigInt |
| VARCHAR / TEXT / CHAR | string | String |
| BOOLEAN / BOOL | boolean | Boolean |
| TIMESTAMP / DATETIME | Date | DateTime |
| DECIMAL / NUMERIC | number | Decimal |
| JSON / JSONB | Record<string, unknown> | Json |
| UUID | string | String |
| BYTEA / BLOB | Buffer | Bytes |
How It Works
- SQL Parser -- parses CREATE TABLE statements supporting PostgreSQL, MySQL, and SQLite syntax including quoted identifiers, multi-word types, and constraints.
- TypeScript -- generates typed interfaces with SQL type mapping, nullable fields as
| nullor optional?. - Prisma -- generates Prisma models with @id, @default, @unique, @map, and @db.* annotations.
- Drizzle ORM -- generates table definitions using pgTable/mysqlTable/sqliteTable with correct column types and modifiers.
- Foreign keys -- detected from both inline REFERENCES and table-level FOREIGN KEY constraints.
- Everything runs in your browser -- no data is sent over the network.
Numeric types
SQL INT, INTEGER, SMALLINT, TINYINT, MEDIUMINT, BIGINT, SERIAL, and BIGSERIAL all map to TypeScript number. FLOAT, DOUBLE, DOUBLE PRECISION, REAL, NUMERIC, and DECIMAL also map to number. In Prisma, integers map to Int or BigInt, while decimals map to Decimal or Float. For JavaScript safety with very large integers (BIGINT), consider using BigInt type instead of number.
String and text types
VARCHAR(n), CHAR(n), TEXT, and all text variants (TINYTEXT, MEDIUMTEXT, LONGTEXT) map to TypeScript string. UUID also maps to string since TypeScript has no built-in UUID type. ENUM types typically map to string, though you can use string literal union types for type safety (e.g., 'active' | 'inactive').
Date, time, and boolean types
TIMESTAMP, TIMESTAMPTZ, DATETIME, DATE, TIME, and TIME WITH TIME ZONE all map to TypeScript Date. BOOLEAN and BOOL map to boolean. In Prisma, date types map to DateTime and boolean types map to Boolean. Drizzle preserves the specific column type function (timestamp, date, time) for accurate database mapping.
JSON and binary types
JSON and JSONB map to TypeScript Record<string, unknown> (or you can use a more specific type). BYTEA, BLOB, BINARY, and VARBINARY map to Buffer. In Prisma, JSON types map to Json and binary types map to Bytes. When working with JSON columns, consider defining a specific TypeScript interface for the expected shape of the JSON data.
Frequently Asked Questions
Should I use number or BigInt for BIGINT columns?
JavaScript numbers are safe up to 2^53 - 1 (Number.MAX_SAFE_INTEGER). If your BIGINT values exceed this range, use the TypeScript bigint type. For most applications (auto-increment IDs, timestamps), number is sufficient. Prisma uses BigInt type for BIGINT columns by default.
How should I handle nullable columns in TypeScript?
There are two common patterns: use union types (field: string | null) for explicit nullability, or use optional properties (field?: string) for fields that may be absent. The union type approach is more precise since it distinguishes between 'not set' and 'explicitly null'. Most ORMs including Prisma use the optional (?) pattern.
Related Convert Tools
JSON to Zod Converter
Convert JSON or JSON Schema to Zod validation schemas with $ref resolution, allOf/oneOf/anyOf, enum, format constraints, and required/optional fields
GraphQL to TypeScript
Convert GraphQL SDL schemas to TypeScript interfaces, types, enums, unions, and operations
TypeScript to JavaScript
Convert TypeScript to JavaScript — strip types, interfaces, enums, generics, and access modifiers to get clean JS output
JSON to SQL Converter
Convert JSON arrays to SQL CREATE TABLE and INSERT statements for PostgreSQL, MySQL, and SQLite with automatic type inference