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
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.
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.
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.
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:
Brand types. No more accidentally passing UserId into a ProductId.runtypes to produce contracts...Maybe even create contracts and derive types from that instead... Not a bad idea...
Will post a gist once done.