Last active
December 31, 2024 09:15
-
-
Save vedantroy/df6b18fa89bc24acfe89fc8493743378 to your computer and use it in GitHub Desktop.
SQLite-backed key-value store with JS-like object manipulation and automatic JSON serialization.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import Database from 'better-sqlite3'; | |
import { createDatabaseClient } from './proxy.ts'; | |
// 1) Create an in-memory DB and your table(s). | |
const db = new Database(':memory:'); | |
db.exec(` | |
CREATE TABLE users ( | |
id TEXT PRIMARY KEY, | |
data JSON | |
); | |
`); | |
// 2) Create the parallel client with separate 'rdr' & 'wtr'. | |
const { rdr, wtr } = createDatabaseClient(db, { users: {} }); | |
// 3) Insert a row in the 'users' table: | |
wtr.users['alice'] = { name: 'Alice', age: 30 }; | |
// 4) Check it via the reader: | |
console.log(rdr.users['alice']); | |
// => { name: 'Alice', age: 30 } | |
// 5) Partial update: only set 'nested.foo' -> 42 | |
wtr.users['alice'].nested = { foo: 42 }; | |
// 6) Now the row has been updated in the DB: | |
console.log(rdr.users['alice']); | |
/* | |
{ | |
name: 'Alice', | |
age: 30, | |
nested: { foo: 42 } | |
} | |
*/ | |
// 7) Check presence: | |
console.log('alice' in rdr.users); // true | |
console.log(rdr.users.has('bob')); // false | |
// 9) Partial delete: | |
delete wtr.users['alice'].nested.foo; | |
// => Removes only nested.foo | |
// 10) Full delete: | |
delete wtr.users['alice']; | |
console.log(rdr.users['alice']); | |
// => undefined |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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<tableName, Map<rowId, object>> | |
//////////////////////////////////////// | |
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, | |
}; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Tests: