sqlite.js (3461B)
1 // src/lib/db/sqlite.js - sqlite driver built on node:sqlite 2 // 3 // node:sqlite is synchronous. The methods here are async purely so that 4 // callers can treat every dialect identically; there is no thread pool behind 5 // them. Because each statement runs to completion before the event loop 6 // turns, a transaction on this driver cannot interleave with another within 7 // the same process. 8 9 import { DatabaseSync } from 'node:sqlite'; 10 import { compileCached, bindParams } from './query.js'; 11 12 export function openSqlite(cfg) { 13 const db = new DatabaseSync(cfg.database.path); 14 15 // WAL keeps readers from blocking the writer. 16 db.exec('PRAGMA journal_mode = WAL'); 17 db.exec('PRAGMA foreign_keys = ON'); 18 db.exec('PRAGMA busy_timeout = 5000'); 19 db.exec('PRAGMA synchronous = NORMAL'); 20 21 const statements = new Map(); 22 23 function prepare(sql) { 24 const compiled = compileCached(sql, 'sqlite'); 25 let stmt = statements.get(compiled.sql); 26 if (!stmt) { 27 stmt = db.prepare(compiled.sql); 28 statements.set(compiled.sql, stmt); 29 } 30 return { stmt, keys: compiled.keys }; 31 } 32 33 function fail(e, sql) { 34 const err = new Error(`sqlite: ${e.message}\n sql: ${sql.trim().split('\n')[0]}`); 35 err.cause = e; 36 err.code = e.code; 37 throw err; 38 } 39 40 // node:sqlite hands back null prototype objects. mysql2 and pg both return 41 // ordinary objects, so rows are converted here rather than leaving callers 42 // to discover the difference. 43 const toPlain = (row) => (row === undefined ? undefined : { ...row }); 44 45 const api = { 46 dialect: 'sqlite', 47 48 async all(sql, params = {}) { 49 const { stmt, keys } = prepare(sql); 50 const values = bindParams(keys, params, sql); 51 try { 52 return stmt.all(...values).map(toPlain); 53 } catch (e) { 54 fail(e, sql); 55 } 56 }, 57 58 async get(sql, params = {}) { 59 const { stmt, keys } = prepare(sql); 60 const values = bindParams(keys, params, sql); 61 try { 62 return toPlain(stmt.get(...values)); 63 } catch (e) { 64 fail(e, sql); 65 } 66 }, 67 68 async run(sql, params = {}) { 69 const { stmt, keys } = prepare(sql); 70 const values = bindParams(keys, params, sql); 71 try { 72 const r = stmt.run(...values); 73 return { 74 changes: Number(r.changes), 75 lastInsertId: r.lastInsertRowid === undefined ? null : Number(r.lastInsertRowid), 76 }; 77 } catch (e) { 78 fail(e, sql); 79 } 80 }, 81 82 // Raw DDL, no bind markers are interpreted. 83 async exec(sql) { 84 try { 85 db.exec(sql); 86 } catch (e) { 87 fail(e, sql); 88 } 89 }, 90 91 async close() { 92 statements.clear(); 93 db.close(); 94 }, 95 }; 96 97 // Nesting uses savepoints so that a helper which opens a transaction can be 98 // composed inside a larger one. 99 let depth = 0; 100 api.transaction = async function transaction(fn) { 101 const name = `sp_${depth}`; 102 if (depth === 0) db.exec('BEGIN IMMEDIATE'); 103 else db.exec(`SAVEPOINT ${name}`); 104 depth += 1; 105 try { 106 const result = await fn(api); 107 depth -= 1; 108 if (depth === 0) db.exec('COMMIT'); 109 else db.exec(`RELEASE ${name}`); 110 return result; 111 } catch (e) { 112 depth -= 1; 113 try { 114 if (depth === 0) db.exec('ROLLBACK'); 115 else db.exec(`ROLLBACK TO ${name}`); 116 } catch { 117 // The original error is more useful than a rollback failure. 118 } 119 throw e; 120 } 121 }; 122 123 return api; 124 }