Real SQL med native QueryBuilder och SQLite transaktioner

var DatabaseSync = require('node:sqlite').DatabaseSync;
var db = new DatabaseSync(PATH.root('ledger.sqlite'));
db.exec('CREATE TABLE accounts (id TEXT PRIMARY KEY, name TEXT NOT NULL UNIQUE, balance INTEGER NOT NULL CHECK(balance >= 0)) STRICT');
db.prepare('INSERT INTO accounts VALUES (?, ?, ?)').run('a', 'Alice', 100);
db.prepare('INSERT INTO accounts VALUES (?, ?, ?)').run('b', 'Bob', 50);

function column(name) {
	if (!['id', 'name', 'balance'].includes(name))
		throw new Error('Column not allowed');
	return '"' + name + '"';
}
function where(options, params) {
	var filters = options.filter.map(function(f) {
		if (f.type !== 'where' || f.comparer !== '=')
			throw new Error('Filter not supported');
		params.push(f.value);
		return column(f.name) + ' = ?';
	});
	return filters.length ? ' WHERE ' + filters.join(' AND ') : '';
}
function integer(value, fallback, max) {
	if (value == null)
		return fallback;
	if (!Number.isInteger(value) || value < 0 || value > max)
		throw new Error('Invalid pagination');
	return value;
}
NEWDB('sqlite', function(options, callback) {
	try {
		if (options.table !== 'accounts')
			throw new Error('Table not allowed');
		var params = [];
		var condition = where(options, params);
		var result;
		if (['find','read','list','count'].includes(options.exec)) {
			var count = db.prepare('SELECT COUNT(*) AS value FROM accounts' + condition).get(...params).value;
			if (options.exec === 'count')
				result = count;
			else {
				var fields = (options.fields || ['id','name','balance']).map(column).join(',');
				var sort = (options.sort || ['id_asc']).map(function(s) {
					var match = /^(id|name|balance)_(asc|desc)$/.exec(s);
					if (!match)
						throw new Error('Sort not allowed');
					return column(match[1]) + ' ' + match[2];
				}).join(',');
				var take = integer(options.take, 100, 100);
				var skip = integer(options.skip, 0, 100000);
				var rows = db.prepare('SELECT ' + fields + ' FROM accounts' + condition + ' ORDER BY ' + sort + ' LIMIT ? OFFSET ?').all(...params, take, skip);
				result = options.exec === 'list' ? { items: rows, count: count } : rows;
			}
		} else if (options.exec === 'insert') {
			var p = options.payload;
			db.prepare('INSERT INTO accounts (id,name,balance) VALUES (?,?,?)').run(p.id,p.name,p.balance);
			result = { id: p.id };
		} else if (options.exec === 'update') {
			if (!condition)
				throw new Error('Filter required');
			var keys = Object.keys(options.payload);
			if (!keys.length)
				throw new Error('Empty update');
			var set = keys.map(key => column(key) + ' = ?').join(',');
			result = db.prepare('UPDATE accounts SET ' + set + condition).run(...keys.map(key => options.payload[key]), ...params).changes;
		} else if (options.exec === 'remove') {
			if (!condition)
				throw new Error('Filter required');
			result = db.prepare('DELETE FROM accounts' + condition).run(...params).changes;
		} else
			throw new Error('Operation not supported');
		callback(null, result);
	} catch (e) { callback(e); }
});

MAIN.transfer = function(amount, fail) {
	if (!Number.isSafeInteger(amount) || amount <= 0)
		throw new Error('Positive integer amount required');
	db.exec('BEGIN IMMEDIATE');
	try {
		db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?').run(amount, 'a');
		if (fail)
			throw new Error('Simulated failure');
		db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?').run(amount, 'b');
		db.exec('COMMIT');
	} catch (e) {
		db.exec('ROLLBACK');
		throw e;
	}
};
// Reopen the actual file to verify persisted values on a second connection.
MAIN.sqlSnapshot = function() {
	var reader = new DatabaseSync(PATH.root('ledger.sqlite'), { readOnly: true });
	try { return reader.prepare('SELECT id,balance FROM accounts ORDER BY id').all(); }
	finally { reader.close(); }
};

exports.install = function() {
	ROUTE('GET /sql/', exercise);
};
async function exercise($) {
	try {
		var out = {};
		out.initial = await DB().find('sqlite/accounts').fields('id,balance').promise();
		MAIN.transfer(10, false);
		out.committed = MAIN.sqlSnapshot();
		try { MAIN.transfer(20, true); } catch (e) { out.rollback = e.message; }
		out.afterRollback = MAIN.sqlSnapshot();
		try { MAIN.transfer(999, false); } catch (e) { out.constraint = e.message; }
		out.afterConstraint = MAIN.sqlSnapshot();
		var name = "x'); DROP TABLE accounts; --";
		await DB().insert('sqlite/accounts', { id: 'c', name: name, balance: 25 }).promise();
		out.literal = await DB().read('sqlite/accounts').where('name', name).promise();
		out.updated = await DB().update('sqlite/accounts', { balance: 30 }).id('c').promise();
		out.page = await DB().list('sqlite/accounts').sort('balance', true).page(2, 1).promise();
		try { await DB().insert('sqlite/accounts', { id: 'd', name: 'Alice', balance: 1 }).promise(); } catch (e) { out.duplicate = e.message; }
		try { await DB().find('sqlite/accounts').fields('password').promise(); } catch (e) { out.column = e.message; }
		try { await DB().remove('sqlite/accounts').promise(); } catch (e) { out.unsafe = e.message; }
		out.removed = await DB().remove('sqlite/accounts').id('c').promise();
		out.missing = await DB().read('sqlite/accounts').id('c').promise();
		out.count = await DB().count('sqlite/accounts').promise();
		$.json(out);
	} catch (e) { $.invalid(e); }
}