sqlite usage

index.js

//@ts-check
'use strict';

/** @typedef {import('sqlite3').Database} Database */
/** @typedef {import('sqlite3').Statement} Statement */

const VERBOSE = false;

const sqlite = VERBOSE ? require('sqlite3').verbose() : require('sqlite3');
const fs = require('fs');

const dbFile = 'test.sqlite';

/**
 * @param {string} path 
 * 
 * @returns {Promise<Database>}
 */
function openDB(path) {
    return new Promise((resolve, reject) => {
        const db = new sqlite.Database(dbFile, error => {
            if (error) {
                return reject(error);
            }

            // Re-try for up to 5s if the DB is busy
            db.configure('busyTimeout', 5000);

            // Check for errors
            db.addListener('error', error => console.error(error));

            resolve(db);
        });
    });
}

/**
 * @param {Database} db
 * 
 * @returns {Promise<void>}
 */
function ensureTables(db) {

    // UNIQUE:              ensures that all values in a column are different
    // ON CONFLICT REPLACE: the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior 
    //                      to inserting or updating the current row and the command continues executing normally
    // ON CONFLICT FAIL:    when an applicable constraint violation occurs, the FAIL resolution algorithm aborts the current 
    //                      SQL statement with an SQLITE_CONSTRAINT error
    // BLOB:                The value is a blob of data, stored exactly as it was input.
    // NOT NULL:            Value can not be NULL
    return exec(db, 'CREATE TABLE IF NOT EXISTS GlobalItemTable (key TEXT UNIQUE ON CONFLICT REPLACE, value BLOB NOT NULL ON CONFLICT FAIL)');
}

/**
 * @param {Database} db
 * @param {string} sql
 * 
 * @returns {Promise<void>}
 */
function exec(db, sql) {
    return new Promise((resolve, reject) => {
        db.exec(sql, error => {
            if (error) {
                return reject(error);
            }

            resolve();
        });
    });
}

/**
 * @param {Database} db
 * @param {string} sql
 * @param {(row) => void} callback
 * 
 * @returns {Promise<void>}
 */
function each(db, sql, callback) {
    return new Promise((resolve, reject) => {
        db.each(sql, (error, row) => {
            if (error) {
                return reject(error);
            }

            callback(row);
        }, error => {
            if (error) {
                return reject(error);
            }

            resolve();
        });
    });
}

/**
 * @param {Database} db
 * @param {Error?} error
 * 
 * @returns {Promise<void>}
 */
function closeDB(db, error) {
    if (error) {
        console.error(error);
    }

    // Close DB
    return new Promise((resolve, reject) => {
        db.close(error => {
            if (error) {
                return reject(error);
            }

            resolve();
        });
    });
}

/**
 * @param {Database} db
 * @param {() => void} transactions
 * 
 * @returns {Promise<void>}
 */
function transaction(db, transactions) {
    return new Promise((resolve, reject) => {
        db.serialize(() => {
            db.exec('BEGIN TRANSACTION');

            transactions();

            db.exec('END TRANSACTION', error => {
                if (error) {
                    return reject(error);
                }

                resolve();
            });
        });
    });
}

/**
 * @param {Database} db
 * @param {Map<string, string>} keyValueMap
 */
function setItems(db, keyValueMap) {
    return transaction(db, () => {
        const stmt = db.prepare('INSERT INTO GlobalItemTable VALUES (?,?)');

        keyValueMap.forEach((value, key) => {
            stmt.run([key, value]);
        });

        stmt.finalize();
    });
}

/**
 * @param {Database} db
 * @param {string[]} keys
 */
async function deleteItems(db, keys) {
    return transaction(db, () => {
        const stmt = db.prepare('DELETE FROM GlobalItemTable WHERE key=?');

        keys.forEach(key => {
            stmt.run(key);
        });

        stmt.finalize();
    });
}

/**
 * @param {Database} db
 * 
 * @returns {Promise<Map<string, string>>}
 */
async function getItems(db) {
    const items = new Map();

    await each(db, 'SELECT * FROM GlobalItemTable', row => {
        items.set(row.key, row.value);
    });

    return items;
}

class Perf {
    constructor() {
        this._now = Date.now();
    }

    measure(msg) {
        const now = Date.now();

        console.log(`${msg}: ${now - this._now}ms`);

        this._now = now;
    }
}

async function main() {
    if (fs.existsSync(dbFile)) {
        const perf = new Perf();
        const db = await openDB(dbFile);
        perf.measure('open existing DB');
        await closeDB(db, null);

        fs.unlinkSync(dbFile);
    }

    const map = new Map();
    const keys = [];
    const data = JSON.parse(fs.readFileSync('data-5MB.txt').toString());
    Object.keys(data).forEach(key => {
        map.set(key, data[key]);
        keys.push(key);
    });

    const perf = new Perf();

    const db = await openDB(dbFile);
    perf.measure('openDB');

    await ensureTables(db);
    perf.measure('ensureTables');

    await setItems(db, map);
    perf.measure('setItems');

    const items = await getItems(db);
    perf.measure(`getItems (found ${items.size} items, expected ${map.size})`);

    deleteItems(db, keys);
    perf.measure('deleteItems');

    await closeDB(db, null);
    perf.measure('closeDB');
}

main().then(null, console.error);