SQL Schema to TypeScript Types: A Practical Mapping Guide (2026)

Generating a TypeScript interface from a SQL table is useful when an application needs a shared view of database rows. It is not a schema migration and it is not runtime validation. SQL and TypeScript describe different layers: SQL controls storage and constraints, while TypeScript helps application code reason about values after they have crossed a database driver boundary.

1. The mapping problem

A table column has a database type, nullability, default, index, and constraint. A TypeScript property normally has a static type and perhaps an optional marker. Some database facts map cleanly; others require a runtime parser or a deliberate application convention.

SQL concern	TypeScript question
NOT NULL	Can the property be null at this boundary?
DEFAULT	Is the field absent on insert or always returned on select?
DECIMAL	Can JavaScript number precision represent it safely?
DATE	Will the driver return a string, Date, or another object?
Foreign key	Is the relation an ID, a nested object, or both?

2. Common type mappings

CREATE TABLE invoices (
  id BIGINT NOT NULL,
  customer_id UUID NOT NULL,
  total DECIMAL(12, 2) NOT NULL,
  paid BOOLEAN NOT NULL DEFAULT false,
  issued_at TIMESTAMP WITH TIME ZONE NOT NULL,
  note TEXT
);

A reasonable first draft might use bigint, string, number, boolean, string, and string | null. But the driver may return a large integer as a string, and money should often be represented by a decimal library or integer minor units. The generated type should reflect the values the application actually receives, not only the SQL spelling.

Common mappings are: integer types to number when range is safe, text and UUID to string, boolean to boolean, arrays to a typed array, and JSON columns to a dedicated interface or unknown until validated. Database-specific types such as geography, ranges, and vendor extensions need explicit adapters.

The table below is the mapping most projects settle on once they have checked what their driver actually returns. Treat it as a starting position rather than a law: the right-hand column only becomes correct after one real query has been run and its values inspected.

SQL type	TypeScript type	Caveat
SMALLINT, INTEGER, INT	number	Every value is exactly representable in a double; no caveats.
BIGINT, BIGSERIAL	number or string	JavaScript doubles are exact only up to 2^53-1. Counters, snowflake IDs, and imported legacy keys can exceed that, and several drivers then return a string instead. Decide once per project and keep the decision consistent.
NUMERIC, DECIMAL	number | string	Precision and scale survive in SQL, not in JavaScript. Money needs exact values, so prefer integer minor units or a decimal library; keep the string form if the value is ever written back to the database.
REAL, DOUBLE PRECISION	number	Binary floating point: 0.1 + 0.2 is not 0.3. Fine for measurements, wrong for exact equality.
BOOLEAN	boolean	PostgreSQL returns real booleans; MySQL and SQLite drivers may hand back 0 or 1 and need a conversion at the boundary.
CHAR, VARCHAR, TEXT	string	Length limits are a database concern; the TypeScript type does not need to encode them.
UUID	string	No native type exists. A branded string subtype prevents accidental mixing with other identifiers.
DATE	string | Date	An ISO string keeps the no-timezone meaning of DATE intact; a Date object invites local-time coercion.
TIMESTAMP, TIMESTAMPTZ	string | Date	Driver-dependent. Pick one representation per codebase and convert only at the edges.
JSON, JSONB	unknown	The column guarantees syntax, not shape. Narrow it with a validator before reading properties.
BYTEA, BLOB	Uint8Array	Node buffers and browser arrays differ; normalize immediately after the driver returns.
Array columns (INTEGER[], TEXT[])	number[], string[]	Element nullability is separate: an array column can hold NULL elements, which gives (string | null)[].
Database ENUM types	"draft" | "sent" | "paid"	Only when the database actually enforces the set; see section 4.
Vendor types (INET, ranges, geography)	string or an adapter	They arrive as strings or driver objects depending on the client, so write the conversion explicitly.

Nullability layers on top of every row in that table: a nullable column adds | null to whatever the base mapping produced, while a DEFAULT changes nothing on read — it only matters when you build an insert type. Normalizing the DDL first also helps review: running the statement through the SQL Formatter makes long definitions diffable before they reach a generator.

3. Nullability, defaults, and output shapes

NOT NULL usually means a selected row cannot contain SQL NULL, but it does not always mean the property is present in every API response. A serializer may omit undefined values or use a projection that excludes the column. Conversely, a nullable column may become a required property whose value is explicitly null.

Do not confuse optional and nullable. name?: string allows the property to be missing; name: string | null requires the property but allows null. The correct choice comes from the boundary being modeled: insert input, database row, update patch, or public API response.

Defaults affect writes. A database can fill a missing field during insert, so an insert DTO may make it optional even though a selected row always contains it. Generate separate input and output types when the lifecycle differs.

The classic bug comes from a LEFT JOIN, where two different events collapse into one word. A column can be NULL because the row exists but the value is unknown, or the property can be missing because the join produced no related row at all:

SELECT c.id, c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.id = c.last_order_id;

When a customer has never ordered, the query still returns one row and o.total is SQL NULL, so total: number | null is the honest type. But when the serializer omits empty fields, or a projection simply does not include the column, the property disappears from the payload entirely and only total?: number compiles. Model the two cases separately: nullable means the property is always present but may hold null, while optional means the property may be absent. Code that calls row.total.toFixed(2) without a guard fails at runtime in exactly the situation the type was supposed to prevent.

A practical rule follows from this: generate row types in which every selected column is required and typed with the schema's nullability, then derive the other shapes. Insert types make defaulted and generated columns optional, update or patch types make every property optional, and public response types drop or reshape anything the API should not expose. Three explicit declarations — InvoiceRow, InvoiceInsert, InvoicePatch — remove a whole category of insert bugs and make each call site say which boundary it is working at.

4. Names, enums, and relationships

Choose whether snake_case database names remain in TypeScript or are transformed to camelCase. A rename improves application ergonomics but requires a mapping layer and consistent serialization. The SQL to TypeScript Generator can produce a starting style, but a project should use one naming convention everywhere.

SQL enums and check constraints can become TypeScript unions, but only if the set is stable and the database actually enforces it. A free-text column with a comment saying “one of three values” should not be presented as a guaranteed union without validation.

A foreign key does not tell you the response shape. One endpoint may return customer_id, another may join a customer object, and a third may return neither. Model the query or API response that the code consumes rather than blindly modeling every table relation.

Enums deserve a closer look, because two different TypeScript features are in play. A union of string literals is the correct model for a closed set: it autocompletes in the editor, narrows inside conditionals, and produces real compile errors when a case is missed. A single literal type models one fixed value, which is what a status column pinned by a trigger or a one-value CHECK constraint looks like.

// from: CREATE TYPE invoice_status AS ENUM ('draft', 'sent', 'paid');
export type InvoiceStatus = "draft" | "sent" | "paid";

export function label(s: InvoiceStatus): string {
  switch (s) {
    case "draft": return "Not sent";
    case "sent": return "Awaiting payment";
    case "paid": return "Settled";
  }
}

Exhaustiveness is the payoff: add a fourth value in the database, forget the new branch, and the compiler flags the switch instead of production finding a missing label. The escape hatch matters too — a value that arrived from JSON or a text field is plain string at runtime, and a type assertion does not turn it into an InvoiceStatus. Only a runtime check or a validated mapper does that, which is why generated unions belong next to a validator at the boundary.

Naming needs the same single-source discipline. Pick one transform and apply it everywhere: split on underscores, keep the first segment lowercase, capitalize the rest — customer_id becomes customerId, issued_at becomes issuedAt. Decide how acronym runs behave (api_key_id should become apiKeyId, not apiKeyID), what happens to a leading underscore, and what to do when a column is named after a reserved word such as default or class. A small transform makes renames mechanical instead of frightening:

export function toCamel(name: string): string {
  return name.replace(/_([a-z0-9])/g, (_, c) => c.toUpperCase());
}

toCamel("customer_id"); // "customerId"
toCamel("issued_at");   // "issuedAt"

Whatever convention you choose, apply it in exactly one place — the generator — and let serialization follow it, so database rows, JSON payloads, and generated interfaces never disagree about spelling. A mismatch here shows up as a field that is silently undefined after a rename, which is one of the hardest bugs to see in review.

5. A safe generation workflow

1. Start with the exact CREATE TABLE statement or a schema export, not a screenshot.
2. Generate an initial interface locally.
3. Check database driver behavior for dates, big integers, decimals, JSON, and binary values.
4. Split row, insert, update, and API response types when their shapes differ.
5. Add runtime validation at boundaries using a schema library or database mapper.
6. Compile the generated types and run fixture tests against real query results.
7. Regenerate only as part of an intentional schema-change review.

Use the SQL to JSON Schema Generator when a JSON-facing contract is more useful, and validate sample payloads with the JSON Validator.

6. What TypeScript cannot express alone

• Database precision: JavaScript numbers cannot exactly represent every large integer or decimal.
• Foreign-key existence: a type cannot prove a related row exists.
• Transaction rules: static types cannot express isolation or commit behavior.
• Permission filtering: an interface cannot guarantee a user may see a field.
• Runtime shape: data from a driver or network must still be validated.
• Temporal semantics: a timestamp type does not explain timezone or business-calendar rules.

Generated code is valuable when it is honest about these boundaries. Add comments or adapters where a database value does not have a safe one-to-one TypeScript representation.

7. How the generator works, step by step

Understanding the pipeline explains why generated types are occasionally wrong and what to check when they are. Every SQL-to-TypeScript generator, including the SQL to TypeScript Generator, runs the same four stages, and each stage has a characteristic failure.

Stage	Input	Output	Typical failure
1. Parse	CREATE TABLE text	Abstract syntax tree	Dialect-specific syntax such as SERIAL, ARRAY, or a trailing COLLATE
2. Normalize	AST nodes	Canonical column records	Unsigned integers, domains, and enum types left unrecognized
3. Map	Column records plus options	TypeScript type strings	NUMERIC silently mapped to number
4. Emit	Type strings plus naming rules	An interface file	Reserved words, or two columns colliding after camelCasing

The parse stage tokenizes the statement and builds a tree: a table node holds column nodes, and each column carries a type name, its modifiers such as NOT NULL, and a default expression. The normalize stage collapses dialect spellings into one vocabulary — INT and INTEGER become the same record, CHARACTER VARYING becomes VARCHAR, and a default of nextval(...) marks the column as database-generated rather than caller-supplied.

type Column = {
  name: string;
  type: string;        // canonical: "integer", "text", "timestamptz"
  nullable: boolean;
  hasDefault: boolean;
  isGenerated: boolean;
};

The mapping stage is a lookup table — literally the table in section 2 — applied column by column, with modifiers folded in afterward: nullable columns gain | null, and the naming rule rewrites each key. The emit stage writes export interface Invoice { ... }, stamps a header comment naming the source table, and optionally writes a companion contract. That last part is why cross-language work gets cheap: run SQL to JSON Schema against the same DDL and both artifacts derive from one source instead of drifting apart.

Because the pipeline is deterministic, identical input always produces identical output. That property is what makes regeneration safe in code review: a diff in the generated file can only have come from a schema change or an options change, so reviewers know exactly where to look.

8. Keeping generated types in sync with migrations

Generated code starts out accurate and decays the moment someone edits the schema without regenerating. The symptoms are quiet rather than loud: a new column exists in production but not in the interface, so serialization drops it on the way out; a renamed column still compiles everywhere because nothing checked the type against the database.

The fix is procedural rather than clever. Treat the generated file as a committed build artifact and make regeneration part of every migration:

1. Keep migrations and the generator version together in the repository.
2. Run the generator as the first step after applying migrations, inside the same job.
3. Fail the build when git diff --exit-code reports changes — that means someone edited the schema without regenerating, or edited the generated file by hand.
4. Run a typecheck such as tsc --noEmit after the diff check so mapping mistakes surface as compile errors instead of runtime surprises.

This slots into an existing pipeline instead of replacing it: the same workflow that already runs tests can regenerate and diff, and the GitHub Actions CI/CD guide shows the job structure to hang it on. The diff check is the half that matters. A job that regenerates without failing on drift simply produces a green build while stale artifacts stay committed.

Two habits prevent the remaining drift: never hand-edit the generated file — hold custom types in a separate module and compose them — and record which schema the output came from in the header comment, so an outdated copy is obvious the moment it is opened.

9. Typing query results and testing round trips

A generated interface only helps if query results are actually typed at the call site. The common pattern is a tagged template whose generic parameter declares the row shape the caller expects:

async function sql<T>(
  strings: TemplateStringsArray,
  ...values: unknown[]
): Promise<T[]> {
  // delegate to your driver; T comes from the caller, not from a runtime check
}

const rows = await sql<InvoiceRow>`
  SELECT id, customer_id, total, paid, issued_at, note
  FROM invoices
  WHERE paid = ${false}
`;

The generic is a claim, not a proof: TypeScript erases it at runtime, so a wrong T still compiles and fails later. Two habits keep the claim honest. First, keep the SELECT list and the row type in the same module — or generate both from the same table — so a column change forces both into one diff. Second, parse the first real row of each query family with a schema library and only then continue with the typed version. One validated row converts the whole query from believed-typed to observed-typed.

Round-trip tests close the loop between the database and the type. The pattern is small and repeats per table:

1. Insert a fixture row through the insert type, covering the edges: maximum numeric scale, an empty string, NULL in a nullable column, quotes and emoji in text.
2. Select it back through the query the application really uses — not a simplified test query.
3. Assert that the parsed result equals the fixture and that the validator accepts it.
4. Delete the fixture in a finally block so tests stay order-independent.

Run this against a real database in CI, such as a containerized PostgreSQL, because a mocked driver reproduces exactly the assumptions the test exists to challenge. JSON columns deserve the same treatment: serialize an unknown payload, parse it with a validator, and compare. Fixtures checked with the JSON Validator, plus contracts from JSON Schema validation and JSON Schema to TypeScript, give each test a second, independent opinion about the shape instead of trusting one generator twice.

Read the final types as documentation of intent. If a column is typed string | null and the interface never renders an empty state, the type is reporting an unhandled case; if nobody can explain why an identifier is string instead of number, the mapping was decided by default rather than on purpose.

Conclusion

SQL-to-TypeScript generation removes repetitive typing, but the important decisions are nullability, precision, naming, lifecycle, and runtime behavior. Generate from the real schema, inspect driver output, keep input and output types separate when needed, and validate data at the boundary.

Frequently Asked Questions

Can SQL types map directly to TypeScript?

Some do, but dates, decimals, large integers, JSON, and vendor-specific types need deliberate handling.

What is the difference between optional and nullable?

Optional means a property may be missing; nullable means the property exists but may contain null.

Should DECIMAL become number?

Only when the required precision is safe; financial values often need decimal libraries or integer minor units.

Should a foreign key become a nested object?

Not automatically. The response shape depends on the query or API endpoint.

Do generated interfaces validate data?

No. TypeScript types disappear at runtime; use a validator or mapper at the boundary.

Should I generate one type per table?

Often no. Insert, update, row, and public response shapes frequently differ.

How do I stop generated types from drifting from the schema?

Regenerate them as part of every migration in CI and fail the build when the committed output differs.

How should query results be typed?

Declare the expected row type as a generic on your query helper, then validate the first real row at runtime.