import Database from ‘better-sqlite3’; //////////////////////////////////////// // 1. Types //////////////////////////////////////// export type SchemaDefinition = Record<string, any>; export interface CreateDBOptions { idColumn?: string; jsonColumn?: string; debugSql?: boolean; } //////////////////////////////////////// // 2. The shape of what we return //////////////////////////////////////// export interface DBClient<TSchema extends SchemaDefinition> { /** Reader: returns plain JS objects, no Proxy. */ rdr: { [TableName in keyof TSchema]: TableReader<TSchema[TableName]> }; /** Writer: partial updates (Proxies). */ wtr: { [TableName in keyof TSchema]: TableWriter<TSchema[TableName]> }; } /** Reader interface: bracket-get returns plain objects from memory. */ export interface TableReader<TRow> { [rowId: string]: TRow | undefined; forEach(callback: (id: string, rowData: TRow) => void): void; keys(): string[]; values(): TRow[]; entries(): Array<[string, TRow]>; dict(): Record<string, TRow>; has(id: string): boolean; } /** Writer interface: bracket-get returns a nested Proxy for partial JSON updates. */ export interface TableWriter<TRow> { [rowId: string]: TRowProxy<TRow>; forEach(callback: (id: string, rowProxy: TRowProxy<TRow>) => void): void; keys(): string[]; entries(): Array<[string, TRowProxy<TRow>]>; has(id: string): boolean; } /** * A nested Proxy that allows partial updates to single fields. * If you do `writer.users[‘bob’].nested.foo = 123`, * it calls `json_set(…, ‘$.nested.foo’, 123)` in the DB. */ export type TRowProxy<TRow> = TRow & { [nestedKey: string]: any; }; //////////////////////////////////////// // 3. Main entry point //////////////////////////////////////// export function createDatabaseClient<TSchema extends SchemaDefinition>( db: Database.Database, schema: TSchema, options: CreateDBOptions = {} ): DBClient<TSchema> { const idColumn = options.idColumn ?? ‘id’; const jsonColumn = options.jsonColumn ?? ‘data’; const debugSql = !!options.debugSql; //////////////////////////////////////// // A) In-memory cache: Map> //////////////////////////////////////// const memoryCache = new Map<string, Map<string, any>>(); for (const tableName of Object.keys(schema)) { memoryCache.set(tableName, new Map()); } //////////////////////////////////////// // B) Precompiled statements for each table //////////////////////////////////////// function wrapStmt(stmt: ReturnType<Database.Database[‘prepare’]>, label: string) { return { get(…args: any[]) { if (debugSql) { console.log(`[SQL GET] ${label}, params: ${JSON.stringify(args)}`); } return stmt.get(…args); }, run(…args: any[]) { if (debugSql) { console.log(`[SQL RUN] ${label}, params: ${JSON.stringify(args)}`); } return stmt.run(…args); }, all(…args: any[]) { if (debugSql) { console.log(`[SQL ALL] ${label}, params: ${JSON.stringify(args)}`); } return stmt.all(…args); }, }; } const stmts = new Map< string, { selectRow: ReturnType<typeof wrapStmt>; upsertWholeRow: ReturnType<typeof wrapStmt>; deleteRow: ReturnType<typeof wrapStmt>; jsonSet: ReturnType<typeof wrapStmt>; jsonRemove: ReturnType<typeof wrapStmt>; checkExistence: ReturnType<typeof wrapStmt>; selectAllIds: ReturnType<typeof wrapStmt>; } >(); function getStatementsForTable(tableName: string) { if (stmts.has(tableName)) { return stmts.get(tableName)!; } const selectRowSQL = ` SELECT ${jsonColumn} AS jsonData FROM ${tableName} WHERE ${idColumn} = ?`; const upsertWholeRowSQL = ` INSERT OR REPLACE INTO ${tableName} (${idColumn}, ${jsonColumn}) VALUES (?, json(?))`; const deleteRowSQL = ` DELETE FROM ${tableName} WHERE ${idColumn} = ?`; const jsonSetSQL = ` UPDATE ${tableName} SET ${jsonColumn} = json_set(${jsonColumn}, ?, json(?)) WHERE ${idColumn} = ?`; const jsonRemoveSQL = ` UPDATE ${tableName} SET ${jsonColumn} = json_remove(${jsonColumn}, ?) WHERE ${idColumn} = ?`; const checkExistenceSQL = ` SELECT 1 FROM ${tableName} WHERE ${idColumn} = ?`; const selectAllIdsSQL = ` SELECT ${idColumn} AS id FROM ${tableName}`; const prepared = { selectRow: wrapStmt(db.prepare(selectRowSQL), `${tableName}:selectRow`), upsertWholeRow: wrapStmt(db.prepare(upsertWholeRowSQL), `${tableName}:upsertWholeRow`), deleteRow: wrapStmt(db.prepare(deleteRowSQL), `${tableName}:deleteRow`), jsonSet: wrapStmt(db.prepare(jsonSetSQL), `${tableName}:jsonSet`), jsonRemove: wrapStmt(db.prepare(jsonRemoveSQL), `${tableName}:jsonRemove`), checkExistence: wrapStmt(db.prepare(checkExistenceSQL), `${tableName}:checkExistence`), selectAllIds: wrapStmt(db.prepare(selectAllIdsSQL), `${tableName}:selectAllIds`), }; stmts.set(tableName, prepared); return prepared; } //////////////////////////////////////// // C) Helper: load a row’s JSON into memory cache if not loaded //////////////////////////////////////// function loadRow(tableName: string, rowId: string) { const cacheForTable = memoryCache.get(tableName)!; if (cacheForTable.has(rowId)) { return; // already in memory } const { selectRow } = getStatementsForTable(tableName); const row = selectRow.get(rowId); if (!row) return; // not found in DB try { cacheForTable.set(rowId, JSON.parse(row.jsonData)); } catch { cacheForTable.set(rowId, null); } } //////////////////////////////////////// // D) JSON path helpers for partial updates //////////////////////////////////////// function pathToJsonPathString(path: string[]) { if (!path.length) return ‘$’; return ‘$.’ + path.map(escapeJsonKey).join(‘.’); } function escapeJsonKey(k: string): string { // naive return k.replace(/“/g, ‘\\”‘); } //////////////////////////////////////// // E) Row-level Proxy for partial updates //////////////////////////////////////// function createRowProxy(tableName: string, rowId: string, pathSoFar: string[] = []): any { return new Proxy( {}, { get(_, propKey) { if (typeof propKey === ‘symbol’) { return Reflect.get(_, propKey); } loadRow(tableName, rowId); const cacheForTable = memoryCache.get(tableName)!; if (!cacheForTable.has(rowId)) { throw new Error(`Row ‘${rowId}‘ not found in table ‘${tableName}‘ (read).`); } const rowData = cacheForTable.get(rowId); const newPath = […pathSoFar, propKey.toString()]; let current: any = rowData; for (const p of newPath) { if (current == null || typeof current !== ‘object’) { return undefined; } current = current[p]; } // If object or array, return deeper proxy so we can do partial updates if (current && typeof current === ‘object’) { return createRowProxy(tableName, rowId, newPath); } return current; }, set(_, propKey, value) { loadRow(tableName, rowId); const cacheForTable = memoryCache.get(tableName)!; if (!cacheForTable.has(rowId)) { throw new Error(`Row ‘${rowId}‘ not found in table ‘${tableName}‘ (write).`); } const { jsonSet } = getStatementsForTable(tableName); const newPath = […pathSoFar, propKey.toString()]; const jsonPath = pathToJsonPathString(newPath); jsonSet.run(jsonPath, JSON.stringify(value), rowId); // Update local cache const rowData = cacheForTable.get(rowId); let cursor: any = rowData; for (let i = 0; i < newPath.length – 1; i++) { const seg = newPath[i]; if (cursor[seg] == null || typeof cursor[seg] !== ‘object’) { cursor[seg] = {}; } cursor = cursor[seg]; } cursor[newPath[newPath.length – 1]] = value; return true; }, deleteProperty(_, propKey) { loadRow(tableName, rowId); const cacheForTable = memoryCache.get(tableName)!; if (!cacheForTable.has(rowId)) { throw new Error(`Row ‘${rowId}‘ not found in table ‘${tableName}‘ (delete).`); } // If it looks like a numeric index => forbid const keyString = propKey.toString(); if (/^\d+$/.test(keyString)) { throw new Error( `Deleting array elements by index is not allowed: .${keyString}` ); } const { jsonRemove } = getStatementsForTable(tableName); const newPath = […pathSoFar, keyString]; const jsonPath = pathToJsonPathString(newPath); jsonRemove.run(jsonPath, rowId); // Update in-memory object const rowData = cacheForTable.get(rowId); let cursor: any = rowData; for (let i = 0; i < newPath.length – 1; i++) { const seg = newPath[i]; if (cursor[seg] == null || typeof cursor[seg] !== ‘object’) { return true; } cursor = cursor[seg]; } delete cursor[newPath[newPath.length – 1]]; return true; }, has(_, propKey) { if (typeof propKey === ‘symbol’) { return Reflect.has(_, propKey); } loadRow(tableName, rowId); const cacheForTable = memoryCache.get(tableName)!; if (!cacheForTable.has(rowId)) { return false; } const rowData = cacheForTable.get(rowId); let current = rowData; for (const p of pathSoFar) { if (current == null || typeof current !== ‘object’) { return false; } current = current[p]; } if (current && typeof current === ‘object’) { return Object.prototype.hasOwnProperty.call(current, propKey); } return false; }, } ); } //////////////////////////////////////// // F) Create the “Reader” table object //////////////////////////////////////// function createTableReader(tableName: string): TableReader<any> { const { selectAllIds, checkExistence } = getStatementsForTable(tableName); const cacheForTable = memoryCache.get(tableName)!; const readerImplementation = { forEach(callback: (id: string, data: any) => void) { const rows = selectAllIds.all() as Array<{ id: string }>; for (const r of rows) { loadRow(tableName, r.id); const cached = cacheForTable.get(r.id); if (cached !== undefined) { callback(r.id, cached); } } }, keys(): string[] { return selectAllIds.all().map((r: any) => r.id); }, values(): any[] { return selectAllIds.all().map((r: any) => cacheForTable.get(r.id)); }, dict(): Record<string, any> { return selectAllIds.all().reduce((acc, r: any) => { acc[r.id] = cacheForTable.get(r.id); return acc; }, {} as Record<string, any>); }, entries(): Array<[string, any]> { return selectAllIds.all().map((r: any) => { loadRow(tableName, r.id); return [r.id, cacheForTable.get(r.id)] as [string, any]; }); }, has(id: string) { if (cacheForTable.has(id)) return true; const row = checkExistence.get(id); return !!row; }, }; return new Proxy(readerImplementation, { get(target, propKey, receiver) { if (typeof propKey === ‘symbol’) { return Reflect.get(target, propKey, receiver); } if (Reflect.has(target, propKey)) { return Reflect.get(target, propKey, receiver); } // otherwise treat propKey as rowId const rowId = propKey.toString(); loadRow(tableName, rowId); return cacheForTable.get(rowId); }, set() { throw new Error(`Cannot write via Reader API`); }, deleteProperty() { throw new Error(`Cannot delete via Reader API`); }, has(target, propKey) { if (typeof propKey === ‘symbol’) { return Reflect.has(target, propKey); } if (Reflect.has(target, propKey)) { return true; } const rowId = propKey.toString(); if (cacheForTable.has(rowId)) { return true; } const row = checkExistence.get(rowId); return !!row; }, }) as TableReader<any>; } //////////////////////////////////////// // G) Create the “Writer” table object //////////////////////////////////////// function createTableWriter(tableName: string): TableWriter<any> { const { checkExistence, selectAllIds, upsertWholeRow, deleteRow } = getStatementsForTable(tableName); const cacheForTable = memoryCache.get(tableName)!; const writerImplementation = { forEach(callback: (id: string, rowProxy: any) => void) { const rows = selectAllIds.all() as Array<{ id: string }>; for (const r of rows) { loadRow(tableName, r.id); callback(r.id, createRowProxy(tableName, r.id)); } }, keys(): string[] { return selectAllIds.all().map((r: any) => r.id); }, entries(): Array<[string, any]> { return selectAllIds.all().map((r: any) => { loadRow(tableName, r.id); return [r.id, createRowProxy(tableName, r.id)] as [string, any]; }); }, has(id: string) { if (cacheForTable.has(id)) return true; const row = checkExistence.get(id); return !!row; }, }; return new Proxy(writerImplementation, { get(target, propKey, receiver) { if (typeof propKey === ‘symbol’) { return Reflect.get(target, propKey, receiver); } if (Reflect.has(target, propKey)) { return Reflect.get(target, propKey, receiver); } const rowId = propKey.toString(); loadRow(tableName, rowId); return createRowProxy(tableName, rowId); }, set(_, rowId, value) { // upsert entire row const idString = rowId.toString(); cacheForTable.set(idString, value); upsertWholeRow.run(idString, JSON.stringify(value)); return true; }, deleteProperty(_, rowId) { const idString = rowId.toString(); cacheForTable.delete(idString); deleteRow.run(idString); return true; }, has(target, propKey) { if (typeof propKey === ‘symbol’) { return Reflect.has(target, propKey); } if (Reflect.has(target, propKey)) { return true; } const rowId = propKey.toString(); if (cacheForTable.has(rowId)) { return true; } const row = checkExistence.get(rowId); return !!row; }, }) as TableWriter<any>; } //////////////////////////////////////// // H) Build the overall “rdr” and “wtr” objects //////////////////////////////////////// const rdrObj = {} as DBClient<TSchema>[‘rdr’]; const wtrObj = {} as DBClient<TSchema>[‘wtr’]; for (const tableName of Object.keys(schema)) { Object.defineProperty(rdrObj, tableName, { value: createTableReader(tableName), enumerable: true, configurable: false, writable: false, }); Object.defineProperty(wtrObj, tableName, { value: createTableWriter(tableName), enumerable: true, configurable: false, writable: false, }); } return { rdr: rdrObj, wtr: wtrObj, }; }