Vasili's Blog

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