Vasili's Blog

This time we focus on #security


Updated the blog engine to a new version and that broke the colors... Gotta figure out what's up.

Update: it was actually loading the custom CSS file :facepalm:

Skipping episode 14 for the time being, as I have to re-record bits of audio.

Change my mind.

Discovering complexity of the thing you set out to implement on minimal, happy-path only requirements when everything is already tight and on fire just is not tenable. It's a cavalcade of shitty compromises and corner-cutting at it's finest. Backlog inflation is real folks. We keep the pretense that our ticket economy is strong, but the backlog depth doesn't lie... If I were to be allowed to use shitty central banking analogy.

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

  users AS u (
ON CONFLICT(telegram_id) DO
    first_name = excluded.first_name,
    last_name = excluded.last_name,
    username = excluded.username,
    photo_url = excluded.photo_url

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. 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.

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

  u.user_id as biba,
  u.first_name as boba,
  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
  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

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:

   AND type = 'table'
   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