konnosDashboard
query-builder.ts377 lines · main
1import type { ProjectTx } from './db.js';
2import type {
3 Ctx,
4 DbClient,
5 DeleteQuery,
6 InsertQuery,
7 SelectQuery,
8 TableQuery,
9 UpdateQuery,
10 VectorSearchInput,
11 VectorSearchQuery,
12} from '@briven/schema';
13
14/**
15 * Phase 1 query builder backed by postgres.js (DoltGres) and a per-invoke
16 * transaction.
17 *
18 * Scope: covers the 90% path from the `Ctx` interface in @briven/schema —
19 * select / insert / update / delete with where (equality), orderBy, limit,
20 * offset. Predicates beyond equality, joins, transactions exposed to user
21 * code, and parameterised raw queries land in Phase 2.
22 *
23 * Table and column names are validated to a strict identifier shape before
24 * being interpolated into SQL — never accept arbitrary strings here.
25 *
26 * @README-BRIVEN ADR 0001 — converged onto DoltGres (postgres.js driver),
27 * off the abandoned mysql2 detour:
28 *
29 * - Identifier quoting: `` `name` `` (MySQL backticks) → `"name"` (Postgres)
30 * - Parameter placeholders: `?` (MySQL positional) → `$1`, `$2`, … (Postgres)
31 * - `RETURNING`: now implemented for INSERT/UPDATE — `.returning()` returns
32 * the affected rows. DELETE is conservative (rowcount-only) until DoltGres
33 * DELETE … RETURNING is confirmed (see DeleteImpl).
34 * - Vector search: pgvector operators (`<->`, `<#>`, `<=>`) — `vectorSearch()`
35 * still throws; LanceDB embedded lands in Phase 5.
36 * - `mysql.PoolConnection` → `ProjectTx`
37 * - `conn.query(sql, params)` (returns `[rows]`) → `tx.unsafe(sql, params)`
38 * (returns the rows directly)
39 */
40
41const IDENT = /^[a-zA-Z_][a-zA-Z0-9_]{0,62}$/;
42
43/**
44 * @README-BRIVEN Postgres/DoltGres quote identifiers with double quotes,
45 * not MySQL backticks.
46 */
47function quote(name: string): string {
48 if (!IDENT.test(name)) {
49 throw new Error(`invalid identifier: ${JSON.stringify(name)}`);
50 }
51 return `"${name}"`;
52}
53
54function quoteList(names: readonly string[]): string {
55 return names.map(quote).join(', ');
56}
57
58class WhereClause {
59 private readonly cols: string[] = [];
60 private readonly params: unknown[] = [];
61
62 add(predicate: Record<string, unknown>): void {
63 for (const [col, value] of Object.entries(predicate)) {
64 this.cols.push(col);
65 this.params.push(value);
66 }
67 }
68
69 /**
70 * Render ` WHERE …` with `$N` positional placeholders. Postgres numbers
71 * placeholders across the whole statement, so callers that bind params
72 * before the WHERE (e.g. UPDATE … SET) pass the count already consumed as
73 * `offset`; the first WHERE placeholder is then `$(offset + 1)`.
74 */
75 sql(offset = 0): string {
76 if (this.cols.length === 0) return '';
77 const parts = this.cols.map((col, i) => `${quote(col)} = $${offset + i + 1}`);
78 return ` WHERE ${parts.join(' AND ')}`;
79 }
80
81 values(): unknown[] {
82 return this.params;
83 }
84}
85
86class SelectImpl implements SelectQuery {
87 private columns: readonly string[] | null = null;
88 private readonly w = new WhereClause();
89 private order: { col: string; dir: 'asc' | 'desc' } | null = null;
90 private _limit: number | null = null;
91 private _offset: number | null = null;
92
93 constructor(
94 private readonly tx: ProjectTx,
95 private readonly table: string,
96 columns?: readonly string[],
97 ) {
98 if (columns) this.columns = columns;
99 }
100
101 where(p: Record<string, unknown>): SelectQuery {
102 this.w.add(p);
103 return this;
104 }
105
106 orderBy(col: string, dir: 'asc' | 'desc' = 'asc'): SelectQuery {
107 this.order = { col, dir };
108 return this;
109 }
110
111 limit(n: number): SelectQuery {
112 this._limit = n;
113 return this;
114 }
115
116 offset(n: number): SelectQuery {
117 this._offset = n;
118 return this;
119 }
120
121 then<R1 = unknown[], R2 = never>(
122 onfulfilled?: ((value: unknown[]) => R1 | PromiseLike<R1>) | null,
123 onrejected?: ((reason: unknown) => R2 | PromiseLike<R2>) | null,
124 ): Promise<R1 | R2> {
125 return this.execute().then(onfulfilled as never, onrejected);
126 }
127
128 private async execute(): Promise<unknown[]> {
129 const cols = this.columns ? quoteList(this.columns) : '*';
130 let query = `SELECT ${cols} FROM ${quote(this.table)}${this.w.sql()}`;
131 if (this.order) query += ` ORDER BY ${quote(this.order.col)} ${this.order.dir.toUpperCase()}`;
132 if (this._limit !== null) query += ` LIMIT ${Number(this._limit)}`;
133 if (this._offset !== null) query += ` OFFSET ${Number(this._offset)}`;
134 const rows = await this.tx.unsafe(query, this.w.values() as never[]);
135 return rows as unknown[];
136 }
137}
138
139class InsertImpl implements InsertQuery {
140 private returningCols: readonly string[] | null = null;
141
142 constructor(
143 private readonly tx: ProjectTx,
144 private readonly table: string,
145 private readonly values: Record<string, unknown> | readonly Record<string, unknown>[],
146 ) {}
147
148 returning(cols?: readonly string[]): PromiseLike<unknown[]> {
149 this.returningCols = cols ?? [];
150 return this.execute();
151 }
152
153 then<R1 = unknown[], R2 = never>(
154 onfulfilled?: ((value: unknown[]) => R1 | PromiseLike<R1>) | null,
155 onrejected?: ((reason: unknown) => R2 | PromiseLike<R2>) | null,
156 ): Promise<R1 | R2> {
157 return this.execute().then(onfulfilled as never, onrejected);
158 }
159
160 private async execute(): Promise<unknown[]> {
161 const rows = Array.isArray(this.values) ? this.values : [this.values];
162 if (rows.length === 0) return [];
163 const cols = Object.keys(rows[0]!);
164 const colSql = quoteList(cols);
165 const params: unknown[] = [];
166 const valueRows = rows.map((r) => {
167 const placeholders = cols.map((c) => {
168 params.push(r[c]);
169 return `$${params.length}`;
170 });
171 return `(${placeholders.join(', ')})`;
172 });
173 let query = `INSERT INTO ${quote(this.table)} (${colSql}) VALUES ${valueRows.join(', ')}`;
174 // @README-BRIVEN DoltGres supports INSERT … RETURNING. `.returning()` with
175 // no columns means "all columns" (RETURNING *); otherwise the named columns.
176 if (this.returningCols !== null) {
177 query += ` RETURNING ${returningClause(this.returningCols)}`;
178 const out = await this.tx.unsafe(query, params as never[]);
179 return out as unknown[];
180 }
181 await this.tx.unsafe(query, params as never[]);
182 return [];
183 }
184}
185
186class UpdateImpl implements UpdateQuery {
187 private readonly w = new WhereClause();
188 private returningCols: readonly string[] | null = null;
189
190 constructor(
191 private readonly tx: ProjectTx,
192 private readonly table: string,
193 private readonly patch: Record<string, unknown>,
194 ) {}
195
196 where(p: Record<string, unknown>): UpdateQuery {
197 this.w.add(p);
198 return this;
199 }
200
201 returning(cols?: readonly string[]): PromiseLike<unknown[]> {
202 this.returningCols = cols ?? [];
203 return this.execute();
204 }
205
206 then<R1 = unknown[], R2 = never>(
207 onfulfilled?: ((value: unknown[]) => R1 | PromiseLike<R1>) | null,
208 onrejected?: ((reason: unknown) => R2 | PromiseLike<R2>) | null,
209 ): Promise<R1 | R2> {
210 return this.execute().then(onfulfilled as never, onrejected);
211 }
212
213 private async execute(): Promise<unknown[]> {
214 const setParts: string[] = [];
215 const params: unknown[] = [];
216 for (const [col, value] of Object.entries(this.patch)) {
217 params.push(value);
218 setParts.push(`${quote(col)} = $${params.length}`);
219 }
220 // WHERE placeholders continue numbering after the SET params.
221 const whereSql = this.w.sql(params.length);
222 const allParams = [...params, ...this.w.values()];
223 let query = `UPDATE ${quote(this.table)} SET ${setParts.join(', ')}${whereSql}`;
224 // @README-BRIVEN DoltGres supports UPDATE … RETURNING.
225 if (this.returningCols !== null) {
226 query += ` RETURNING ${returningClause(this.returningCols)}`;
227 const out = await this.tx.unsafe(query, allParams as never[]);
228 return out as unknown[];
229 }
230 await this.tx.unsafe(query, allParams as never[]);
231 return [];
232 }
233}
234
235class DeleteImpl implements DeleteQuery {
236 private readonly w = new WhereClause();
237 private returningCols: readonly string[] | null = null;
238
239 constructor(
240 private readonly tx: ProjectTx,
241 private readonly table: string,
242 ) {}
243
244 where(p: Record<string, unknown>): DeleteQuery {
245 this.w.add(p);
246 return this;
247 }
248
249 returning(cols?: readonly string[]): PromiseLike<unknown[]> {
250 this.returningCols = cols ?? [];
251 return this.execute();
252 }
253
254 then<R1 = unknown[], R2 = never>(
255 onfulfilled?: ((value: unknown[]) => R1 | PromiseLike<R1>) | null,
256 onrejected?: ((reason: unknown) => R2 | PromiseLike<R2>) | null,
257 ): Promise<R1 | R2> {
258 return this.execute().then(onfulfilled as never, onrejected);
259 }
260
261 private async execute(): Promise<unknown[]> {
262 let query = `DELETE FROM ${quote(this.table)}${this.w.sql()}`;
263 // @README-BRIVEN DoltGres supports DELETE … RETURNING.
264 if (this.returningCols !== null) {
265 query += ` RETURNING ${returningClause(this.returningCols)}`;
266 const out = await this.tx.unsafe(query, this.w.values() as never[]);
267 return out as unknown[];
268 }
269 await this.tx.unsafe(query, this.w.values() as never[]);
270 return [];
271 }
272}
273
274/**
275 * Render a RETURNING column list. `null`/empty → `*` (all columns), otherwise
276 * the named columns, double-quoted.
277 */
278function returningClause(cols: readonly string[] | null): string {
279 return cols && cols.length > 0 ? quoteList(cols) : '*';
280}
281
282/**
283 * @README-BRIVEN Phase 5: pgvector operators (`<->`, `<#>`, `<=>`)
284 * are Postgres-only. Vector search via LanceDB embedded replaces
285 * this implementation in Phase 5. For now, throws an error.
286 */
287class VectorSearchImpl implements VectorSearchQuery {
288 // Stub: execute() throws before any query runs, so where()/select() are
289 // no-ops and we store only what the error message reads. The full builder
290 // (with a `tx`-bound query) lands with the Phase 5 implementation.
291 constructor(
292 private readonly table: string,
293 private readonly input: VectorSearchInput,
294 ) {}
295
296 where(_p: Record<string, unknown>): VectorSearchQuery {
297 return this;
298 }
299
300 select(_cols: readonly string[]): VectorSearchQuery {
301 return this;
302 }
303
304 then<R1 = unknown[], R2 = never>(
305 onfulfilled?: ((value: unknown[]) => R1 | PromiseLike<R1>) | null,
306 onrejected?: ((reason: unknown) => R2 | PromiseLike<R2>) | null,
307 ): Promise<R1 | R2> {
308 return this.execute().then(onfulfilled as never, onrejected);
309 }
310
311 private async execute(): Promise<unknown[]> {
312 throw new Error(
313 `ctx.db('${this.table}').vectorSearch({ column: '${this.input.column}' }) ` +
314 'is not available yet. Vector search will ship with LanceDB in Phase 5.',
315 );
316 }
317}
318
319/**
320 * Build a `DbClient` and a Set the caller can read after the function
321 * resolves — every `ctx.db('<table>')` call records the table name. The
322 * realtime service uses this to decide which tables to watch for
323 * change-driven re-invocation.
324 *
325 * @README-BRIVEN Phase 2: The realtime service detects changes via Dolt
326 * commit-diff polling (the auto-commit-per-write in withProjectTx advances
327 * the Dolt version log on every mutating tx). The `touched` Set is still
328 * recorded; Phase 2 consumes it via the PollManager.
329 */
330export function buildDbClient(tx: ProjectTx): {
331 db: DbClient;
332 touched: Set<string>;
333} {
334 const touched = new Set<string>();
335 const dbFn = ((table: string): TableQuery => {
336 touched.add(table);
337 return {
338 select: (cols) => new SelectImpl(tx, table, cols),
339 insert: (values) => new InsertImpl(tx, table, values),
340 update: (patch) => new UpdateImpl(tx, table, patch),
341 delete: () => new DeleteImpl(tx, table),
342 vectorSearch: (input) => new VectorSearchImpl(table, input),
343 };
344 }) as DbClient;
345
346 dbFn.execute = async (sql: string, params: readonly unknown[] = []) => {
347 const rows = await tx.unsafe(sql, [...params] as never[]);
348 return rows as unknown[];
349 };
350
351 return { db: dbFn, touched };
352}
353
354export function makeCtx(
355 tx: ProjectTx,
356 request: {
357 requestId: string;
358 auth: Ctx['auth'];
359 env?: Readonly<Record<string, string>>;
360 log?: Ctx['log'];
361 },
362): { ctx: Ctx; touched: Set<string> } {
363 const { db, touched } = buildDbClient(tx);
364 const ctx: Ctx = {
365 db,
366 requestId: request.requestId,
367 log: request.log ?? {
368 debug: () => undefined,
369 info: () => undefined,
370 warn: () => undefined,
371 error: () => undefined,
372 },
373 env: Object.freeze({ ...(request.env ?? {}) }),
374 auth: request.auth,
375 };
376 return { ctx, touched };
377}