|
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, |
|
}; |
|
} |
