Well, it's good enough for my current needs.

I can generate typed CRUD operations for the style of queries that I'm writing.

Supports SELECT, UPDATE, INSERT, and DELETE, with JOINs (automatically makes fields nullable for OUTER JOINs). Aliased columns, COUNT, SELECT * ... and RETURNING.

Uses nominal types for primary and foreign keys.

For the following query

INSERT INTO
  users AS u (
    telegram_id,
    first_name,
    last_name,
    username,
    photo_url
  )
VALUES
  (
    $telegram_id,
    $first_name,
    $last_name,
    $username,
    $photo_url
  )
ON CONFLICT(telegram_id) DO
UPDATE
  SET
    first_name = excluded.first_name,
    last_name = excluded.last_name,
    username = excluded.username,
    photo_url = excluded.photo_url
RETURNING *;

Output looks roughly like this

import type { Brand } from "../../brand.ts";
import Database from "bun:sqlite";

// This file is automatically generated and should not be edited by hand
type MakeBunHappy = Record<string, never>;
export type UserUserId = Brand<number, 'user:user_id'>;

export interface UpsertUserInput {
    readonly $telegram_id: number;
    readonly $first_name: string;
    readonly $last_name: string;
    readonly $username: string;
    readonly $photo_url: string;
}

export interface UpsertUserOutput {
    readonly user_id: UserUserId;
    readonly telegram_id: number;
    readonly first_name: string;
    readonly last_name: string;
    readonly username: string;
    readonly photo_url: string;
    readonly approved: number;
}

export function getUpsertUserQuery(connection: Database) {
    return connection.query<UpsertUserOutput, UpsertUserInput & MakeBunHappy>(`... query text here ...`);
}

❯ cloc .
      11 text files.
      11 unique files.
       0 files ignored.

github.com/AlDanial/cloc v 1.90  T=0.02 s (603.6 files/s, 44449.2 lines/s)
-------------------------------------------------------------------------------
Language                     files          blank        comment           code
-------------------------------------------------------------------------------
TypeScript                      11             79              4            727
-------------------------------------------------------------------

Not too bad...

It's too crusty to be published – but if you're interested drop me a line.

#schema #typesafe

So, had to do a little pivot. @typed-query-builder does not support sqlite out of the box, so instead I went the route of parsing queries...

Maybe it was a big mistake :D

I've gotten to a point where I can traverse my codebase with a glob expression, parse typescript files with ts-morph, find all constants with an sql sigil. Pass those to sql-parser-cst, walk the AST and compile a bunch of information about columns, joins, where clauses, etc...

So now given a query

SELECT
  first_name,
  u.user_id as biba,
  u.first_name as boba,
  r.*,
  r.role_id
FROM
  users u
  JOIN user_role ur ON ur.user_id = u.user_id
  LEFT OUTER JOIN roles r ON ur.role_id = r.role_id
WHERE
  u.user_id = $user_id;

I can generate the following type for the query output, based on the schema (which I've derived in previous steps).

export interface GetUserByIdOutput {
    readonly first_name: string;
    readonly biba: number;
    readonly boba: string;
    readonly role_id?: number;
    readonly name?: string;
    readonly description?: string;
    readonly slug?: string;
    readonly role_id?: number;
}

Still need some cleanup removing duplicates, but it's starting to shape up.

Definitely not good enough as a generic implementation, the amount of various quirky bits in queries is very high, but good enough for my needs...

Next step is deriving the interface for all of the where clause parameters.

Also gotta put the nominal types for keys and IDs back.

#ast #sqlite #schema #typesafe

So, I've gotten to a point where the generator is able to produce a schema typescript file that is compatible with my current database.

The code can be found here.

It currently runs on Bun, but can be trivially adapted for Node (w/ better-sqlite3).

It will likely require additional changes to handle other cases my schema might end up with, but for now I'll be enjoying type-safe queries that would break the build if the schema changes.

#codegen #ast #bun #sqlite #schema #typesafe

Started working on a type-safe schema generator based off the sqlite schema.

I'm pulling out the database schema using #bun's native sqlite driver with a simple query:

SELECT
   tbl_name,
   name,
   type,
   sql
 FROM
   sqlite_schema
 WHERE
   1=1
   AND type = 'table'
   AND sql IS NOT NULL
   AND name != 'sqlite_sequence'

Then aggregating all the SQL statements, passing them through sql-parser-cst, walking the AST collecting tables, columns, primary keys, etc...

Then passing it through ts-morph to emit typescript source code with all the types. Could potentially scan queries, and try to derive parameter types based on schema, that would be pretty sweet.

In plans:

• Nominally-typed ID's, with Brand types. No more accidentally passing UserId into a ProductId.
• Could leverage runtypes to produce contracts...

Maybe even create contracts and derive types from that instead... Not a bad idea...

Will post a gist once done.

#codegen #bun #sqlite #schema #typesafe