SQL-Typechecker

SQL-Typechecker is a CLI tool written in TypeScript, typechecking PostgreSQL files and generating TypeScript type definitions for them.

It reads your SQL DDL statements (CREATE TABLE, etc) on one hand and your SQL functions (CREATE FUNCTION ...) on the other. It then typechecks your SQL functions and generates TypeScript files for them.

Design goals:

• Don't rely on PostgreSQL for types. Postgres itself doesn't perform rigorous enough type-checking for our purposes.
• Don't just generate TypeScript types; also type-check SQL statements.
• Support branded types (aka Haskell/Rust newtypes).
• Support JSON output with nested data type parsing.
• Support as much useful PostgreSQL syntax as possible.

Small example

Consider the following DDL file:

1-- sql/datamodel.sql
2CREATE TABLE my_table (
3  id int8 NOT NULL PRIMARY KEY,
4  name text
5);

And the following functions file:

1-- sql/functions.sql
2CREATE FUNCTION my_function() RETURNS SETOF record AS $$
3  SELECT id, name 
4    FROM my_table
5$$ LANGUAGE sql;

Running SQL-Typechecker as follows:

1> sql-typechecker --dir ./sql --out ./sql

Will generate a TypeScript function with the following type:

1async function my_function(pool: Pool, args: {}): Promise<{ id: number; name: string | null }[]>

The following functions file will fail to typecheck:

1-- functions.sql
2CREATE FUNCTION my_function() RETURNS SETOF record AS $$
3  SELECT id, name 
4    FROM my_table
5   WHERE name = 2 --> type error
6$$ LANGUAGE sql;

Extended example

Consider the following DDL file:

1-- sql/datamodel.sql
2CREATE DOMAIN customer_id AS int8;
3CREATE DOMAIN order_id AS int8;
4
5CREATE TABLE customers (
6  id customer_id NOT NULL PRIMARY KEY,
7  name text
8);
9CREATE TABLE orders (
10  id order_id NOT NULL PRIMARY KEY,
11  customer_id customer_id NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
12  description text,
13);

And the following functions file:

1-- sql/functions.sql
2CREATE FUNCTION get_customers(customer_ids customer_id[]) RETURNS SETOF record AS $$
3  SELECT id, name, grouped_orders.grouped_orders
4    FROM customers
5    LEFT OUTER JOIN (SELECT customer_id, 
6                            ARRAY_AGG(JSONB_BUILD_OBJECT(
7                              'id', id,
8                              'description', description
9                            )) AS grouped_orders
10                       FROM orders
11                      GROUP BY customer_id
12                    ) AS grouped_orders
13                       ON grouped_orders.customer_id = customers.id
14  WHERE customers.id = ANY(customer_ids)
15$$ LANGUAGE sql;

Running SQL-Typechecker as follows:

1> sql-typechecker --dir ./extendedexample --out ./extendedexample/out

Will generate a TypeScript function with the following type:

1async function get_customers(
2  pool: Pool,
3  args: { customer_ids: types.customer_id[] }
4): Promise<
5  {
6    id: types.customer_id;
7    name: string | null;
8    grouped_orders:
9      | {
10          id: types.order_id;
11          description: string | null;
12        }[]
13      | null;
14  }[]
15>

Note types.customer_id, declared as branded type:

1// types.ts
2export type customer_id = number & { readonly __tag: "customer_id" };
3```;
4
5## Project status
6Is this project finished? No, quite a few functions, syntax elements, etc., are not implemented yet. It has some rough edges, and performance can also be significantly improved.
7
8That said, I've been using this library for years in multiple commercial projects, and it works very well within its current limitations.
9
10In the future, I would love to add support for `plpgsql`. The biggest blocking factor is the lack of support in the parsing library SQL-Typechecker is built upon.
11
12PRs, questions, remarks, and advice are all very welcome!