Applied Programming/Databases/JavaScript SQLite
databases.js edit
/* 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 edit
Copy and paste the code above into one of the following free online development environments or use your own JavaScript compiler / interpreter / IDE.