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.

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.

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.

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.

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.