TypeSQL

What?!

TypeSQL extracts type definitions from inline SQL CREATE TABLE statements. SQLite was used as a baseline dialect. Things may work for other dialects too, maybe?

Screenshots below are taken from SQLite’s CREATE TABLE documentation. Dashed shapes are not implemented.

create-table-stmt

Note: unlike SQLite, TypeSQL accepts trailing commas after column definitions.

column-def

column-constraint

type-name

Note: unlike SQLite, TypeSQL has no limit for the number of type parameters.

Why?!

Oh! It has future applications in propulsion, energy creation, data transmission, you name it!

Usage

import { GetType } from "@izeau/typesql";

type ExampleType = GetType<SQL, Table, Options>;
// type ExampleType = {
//   id: number,
//   isOk?: boolean | null | undefined
// }

// SQL must be a const string containing at least one CREATE TABLE statement:
const sql = `create table example (id int not null, is_ok boolean);` as const;

// Table can be either a ["schema-name", "table-name"] tuple, or a "table-name":
type Table = "example";

// Options is an optional parameter with the following properties:
//   - camelCase (boolean, defaults to false): wether to export column names as camelCased properties
type Options = { camelCase: true };

Live demo

You can try it out on the TypeScript Playground.

Type mappings

SQLite’s determination of column affinity rules are used.

| Rule order | SQL type | TypeScript type | | :--------: | --------------------------------- | --------------- | | 1 | contains INT | number | | 2 | contains CHAR, CLOB or TEXT | string | | 3 | contains BLOB | Uint8Array | | 4 | contains REAL, FLOA or DOUB | number | | 5† | contains BOOL | boolean | | 6† | contains DATE or TIME | Date | | 99 | otherwise | number |

†: behavior deviates from SQLite’s in order to accomodate standard JS types

Inspiration

• HypeScript, a simplified implementation of TypeScript’s type system written in TypeScript’s type annotations. TypeSQL borrows primitives and general principles from HypeScript.
• ts-sql, a crazy SQL database implemented purely in TypeScript type annotations.