sqliteAdapter.js

'use strict';

const BetterSqlite3 = require('better-sqlite3');
const { performance } = require('node:perf_hooks');

function invoke(statement, method, params) {
    if (params === undefined) return statement[method]();
    if (Array.isArray(params)) return statement[method](...params);
    return statement[method](params);
}

/**
 * Promise-shaped adapter around better-sqlite3. Existing callers keep their
 * async contract while each short SQLite operation executes atomically in one
 * event-loop turn. Network/Discord awaits stay outside database transactions.
 */
class SqliteAdapter {
    constructor(filename, options = {}) {
        this.filename = filename;
        this.raw = new BetterSqlite3(filename, options);
        this.closed = false;
        this.metrics = { operations: 0, errors: 0, busyErrors: 0, totalMs: 0, maxMs: 0, byMethod: {} };
    }

    _measure(method, operation) {
        const started = performance.now();
        try {
            return operation();
        } catch (error) {
            this.metrics.errors += 1;
            if (error?.code === 'SQLITE_BUSY' || error?.code === 'SQLITE_LOCKED') this.metrics.busyErrors += 1;
            throw error;
        } finally {
            const elapsed = performance.now() - started;
            this.metrics.operations += 1;
            this.metrics.totalMs += elapsed;
            this.metrics.maxMs = Math.max(this.metrics.maxMs, elapsed);
            const entry = this.metrics.byMethod[method] || { operations: 0, totalMs: 0, maxMs: 0 };
            entry.operations += 1;
            entry.totalMs += elapsed;
            entry.maxMs = Math.max(entry.maxMs, elapsed);
            this.metrics.byMethod[method] = entry;
        }
    }

    async exec(sql) {
        this._measure('exec', () => this.raw.exec(sql));
    }

    async run(sql, params) {
        const info = this._measure('run', () => invoke(this.raw.prepare(sql), 'run', params));
        return {
            changes: info.changes,
            lastID: typeof info.lastInsertRowid === 'bigint' ? Number(info.lastInsertRowid) : info.lastInsertRowid,
        };
    }

    async get(sql, params) {
        return this._measure('get', () => invoke(this.raw.prepare(sql), 'get', params));
    }

    async all(sql, params) {
        return this._measure('all', () => invoke(this.raw.prepare(sql), 'all', params));
    }

    async backup(destination, options) {
        const started = performance.now();
        try {
            return await this.raw.backup(destination, options);
        } catch (error) {
            this.metrics.errors += 1;
            throw error;
        } finally {
            const elapsed = performance.now() - started;
            this.metrics.operations += 1;
            this.metrics.totalMs += elapsed;
            this.metrics.maxMs = Math.max(this.metrics.maxMs, elapsed);
            const entry = this.metrics.byMethod.backup || { operations: 0, totalMs: 0, maxMs: 0 };
            entry.operations += 1;
            entry.totalMs += elapsed;
            entry.maxMs = Math.max(entry.maxMs, elapsed);
            this.metrics.byMethod.backup = entry;
        }
    }

    async transaction(callback, mode = 'immediate') {
        if (typeof callback !== 'function') throw new TypeError('transaction callback must be a function');
        const runner = this.raw.transaction(() => {
            const result = callback(this.raw);
            if (result && typeof result.then === 'function') {
                throw new TypeError('SQLite transaction callback must be synchronous; move network/await work outside it');
            }
            return result;
        });
        if (!['deferred', 'immediate', 'exclusive'].includes(mode)) throw new Error(`invalid transaction mode: ${mode}`);
        return this._measure('transaction', () => runner[mode]());
    }

    getMetrics() {
        const byMethod = {};
        for (const [method, value] of Object.entries(this.metrics.byMethod)) {
            byMethod[method] = {
                operations: value.operations,
                averageMs: value.operations ? Number((value.totalMs / value.operations).toFixed(3)) : 0,
                maxMs: Number(value.maxMs.toFixed(3)),
            };
        }
        return {
            operations: this.metrics.operations,
            errors: this.metrics.errors,
            busyErrors: this.metrics.busyErrors,
            averageMs: this.metrics.operations ? Number((this.metrics.totalMs / this.metrics.operations).toFixed(3)) : 0,
            maxMs: Number(this.metrics.maxMs.toFixed(3)),
            byMethod,
        };
    }

    async close() {
        if (this.closed) return;
        this.raw.close();
        this.closed = true;
    }
}

async function openDatabase(filename, options) {
    return new SqliteAdapter(filename, options);
}

module.exports = { SqliteAdapter, openDatabase };