Applied Programming/Databases/JavaScript SQLite

databases.js

/* This program demonstrates SQLite3 database processing.

Input:
    None

Output:
    Sample data.

References:
    https://www.sqlitetutorial.net/sqlite-nodejs/
    https://www.scriptol.com/sql/sqlite-async-await.php

*/

const sqlite3 = require('sqlite3');

let database = null;

if (typeof module != "undefined" && !module.parent) {
    main();
}

/**
 * Runs main program logic.
 */
async function main() {
    try {
        const path = "users.db";

        console.log("Users");
        database = await openDatabase(path);
        await createTable();
        await insertUsers(database);
        await displayUsers(database);

        console.log("Users After Update");
        await updateUser();
        await displayUsers();

        console.log("Users After Delete");
        await deleteUser();
        await displayUsers();

        database.close();
    } catch (error) {
        console.error(error)
    }
}

/**
 * Opens the given database path.
 * 
 * @param {string} path to database
 * @returns {object} database
 */
async function openDatabase(path) {
    return new Promise(function (resolve, reject) {
        let database = new sqlite3.Database(path, (error) => {
            if (error) {
                let message = `Error opening ${path}\n${error.message}`;
                reject(message);
            }
            else {
                resolve(database);
            }
        });
    });
}

/**
 * Run the given SQL statement.
 * 
 * @global {pbkect} database
 * @param {string} sql to run
 * @returns {promise}
 */
async function runSql(sql) {
    return new Promise(function (resolve, reject) {
        database.run(sql, (error) => {
            if (error) {
                let message = `Error running ${sql}\n${error.message}`;
                reject(message);
            } else {
                resolve(true);
            }
        });
    });
}

/**
 * Selects all rows in the given SQL statement.
 * 
 * @global {object} database
 * @returns {array} rows
 */
function selectAll(sql) {
    return new Promise(function (resolve, reject) {

        database.all(sql, (error, rows) => {
            if (error) {
                let message = `Error running ${sql}\n${error.message}`;
                reject(message);
            } else {
                resolve(rows);
            }
        });
    });
}

/**
 * Displays the users table.
 */
async function displayUsers() {
    const sql = "SELECT UserID, User FROM Users;";
    const users = await selectAll(sql);
    for (const row of users) {
        console.log(`UserID: ${row["UserID"]}, User: ${row["User"]}`);
    }
    console.log("");
}

/**
 * Creates the Users table.
 */
async function createTable() {
    let sql = "DROP TABLE IF EXISTS Users;";
    let ressult = await runSql(sql);

    sql = `
        CREATE TABLE IF NOT EXISTS Users(
            UserID INT PRIMARY KEY NOT NULL,
            User TEXT NOT NULL
        );
        `;
    result = await runSql(sql);
}

/**
 * Insert data into the Users table.
 */
async function insertUsers() {
    let sql = "INSERT INTO Users(UserID, User) VALUES(1, 'Moe');";
    await runSql(sql);

    sql = "INSERT INTO Users(UserID, User) VALUES(2, 'Larry');";
    await runSql(sql);

    sql = "INSERT INTO Users(UserID, User) VALUES(3, 'Curly');";
    await runSql(sql);
}

/**
 * Updates the Users table.
 */
async function updateUser() {
    let sql = `
        UPDATE Users
        SET User = 'Shemp'
        WHERE UserID = 3;
        `;
    await runSql(sql);
}

/**
 * Deletes a record from the Users table.
 */
async function deleteUser() {
    let sql = `
        DELETE FROM Users
        WHERE UserID = 3;
        `;
    await runSql(sql)
}
Try It

Copy and paste the code above into one of the following free online development environments or use your own JavaScript compiler / interpreter / IDE.
Applied Programming/Databases/JavaScript SQLite

databases.js

Try It

See Also
