Real SQL with native QueryBuilder and SQLite transactions

Total.js 5 · total5@0.0.19 total5reference Executed against Total.js 5

This example connects Total.js's native DB()/NEWDB() to a real file-backed SQLite database using Node's built-in node:sqlite. The example uses Node 24.14.1 and total5@0.0.19, without an npm SQL dependency. Check the Node 24 SQLite documentation for runtime availability and API stability. DatabaseSync is synchronous: this small driver demonstrates correctness, not a recommendation for heavy blocking work on a busy HTTP thread.

This is an application-owned driver, not the separate Total.js QueryBuilder SQL package, nor a PostgreSQL/MySQL compatibility claim. It deliberately supports only one allowlisted table, equality filters, selected columns, sort, bounded pagination and basic CRUD. Unsupported operations fail closed. The broader builder contract is in query-builder-contract.

Values use bound ? parameters. Table/column names and order directions are selected by server allowlists; never concatenate arbitrary identifiers from requests. Update/delete require a filter. The HTTP fixture uses fixed demonstration data; add authentication and ownership constraints before exposing real records.

A queue of DB() operations is not an atomic transaction. The transfer function explicitly owns one connection, begins a transaction, makes both changes synchronously, commits on success and rolls back on any error. Do not insert asynchronous waits into this single shared connection transaction. Multi-connection async drivers need connection reservation and their own transaction API.

definitions/sqlite.js

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(); }
};

controllers/sqlite.js

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