Now, we can write code to use this database in the Notes application.

Create models/notes-sqlite3.mjs file:

import util from 'util';
import Note from './Note';
import sqlite3 from 'sqlite3';
import DBG from 'debug';
const debug = DBG('notes:notes-sqlite3'); 
const error = DBG('notes:error-sqlite3'); 

var db; // store the database connection here 
 
async function connectDB() { 
    if (db) return db; 
    var dbfile = process.env.SQLITE_FILE || "notes.sqlite3"; 
    await new Promise((resolve, reject) => {
        db = new sqlite3.Database(dbfile, 
            sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, 
            err => { 
                if (err) return reject(err); 
                resolve(db);
        });
    });
    return db;
}

This serves the same purpose as the connectDB function in notes-level.mjs: to manage the database connection. If the database is not open, it'll go ahead and do so, and even make sure that the database file is created (if it doesn't exist). But if it is already open, it is immediately returned:

export async function create(key, title, body) { 
    var db = await connectDB();
    var note = new Note(key, title, body); 
    await new Promise((resolve, reject) => { 
        db.run("INSERT INTO notes ( notekey, title, body) "+ 
            "VALUES ( ?, ? , ? );", [ key, title, body ], err => { 
                if (err) return reject(err); 
                resolve(note); 
        }); 
    });
    return note;
}
 
export async function update(key, title, body) { 
    var db = await connectDB();
    var note = new Note(key, title, body); 
    await new Promise((resolve, reject) => { 
        db.run("UPDATE notes "+ 
            "SET title = ?, body = ? WHERE notekey = ?", 
            [ title, body, key ], err => { 
                if (err) return reject(err); 
                resolve(note); 
        }); 
    });
    return note;
}

These are our create and update functions. As promised, we are now justified in defining the Notes model to have separate functions for create and update operations, because the SQL statement for each is different.

Calling db.run executes an SQL query, giving us the opportunity to insert parameters into the query string.

The sqlite3 module uses a parameter substitution paradigm that's common in SQL programming interfaces. The programmer puts the SQL query into a string, and then places a question mark in each place where the aim is to insert a value into the query string. Each question mark in the query string has to match a value in the array provided by the programmer. The module takes care of encoding the values correctly so that the query string is properly formatted, while preventing SQL injection attacks.

The db.run function simply runs the SQL query it is given, and does not retrieve any data. Because the sqlite3 module doesn't produce any kind of Promise, we have to wrap function calls in a Promise object:

export async function read(key) {
  var db = await connectDB();
  var note = await new Promise((resolve, reject) => {
    db.get("SELECT * FROM notes WHERE notekey = ?", [key], (err,row) => {
        if (err) return reject(err);
        const note = new Note(row.notekey, row.title, row.body);
        resolve(note);
     });
  });
  return note;
}

To retrieve data using the sqlite3 module, you use the db.get, db.all, or db.each functions. The db.get function used here returns only the first row of the result set. The db.all function returns all rows of the result set at once, which can be a problem for available memory if the result set is large. The db.each function retrieves one row at a time, while still allowing processing of the entire result set.

For the Notes application, using db.get to retrieve a note is sufficient because there is only one note per notekey. Therefore, our SELECT query will return at most one row anyway. But what if your application will see multiple rows in the result set? We'll see what to do about that in a minute.

By the way, this read function has a bug in it. See if you can spot the error. We'll read more about this in Chapter 11, Unit Testing and Functional Testing, when our testing efforts uncover the bug:

export async function destroy(key) {
  var db = await connectDB();
  return await new Promise((resolve, reject) => {
    db.run("DELETE FROM notes WHERE notekey = ?;", [key], err => {
        if (err) return reject(err);
        resolve();
    });
  });
}

To destroy a note, we simply execute the DELETE FROM statement:

export async function keylist() {
    var db = await connectDB();
    var keyz = await new Promise((resolve, reject) => {
        var keyz = [];
        db.all("SELECT notekey FROM notes", (err, rows) => {
                if (err) return reject(err);
                resolve(rows.map(row => row.notekey ));
            });
    });
    return keyz;
}

The db.all function retrieves all rows of the result set. 

The contract for this function is to return an array of note keys. The rows object is an array of results from the database that contains the data we are to return, but in a different format. Therefore, we use the map function to convert the array into the format required to fulfill the contract:

export async function count() {
    var db = await connectDB();
    var count = await new Promise((resolve, reject) => {
        db.get("select count(notekey) as count from notes",(err, row) 
        => {
                if (err) return reject(err);
                resolve(row.count);
            });
    });
    return count;
}

export async function close() {
    var _db = db;
    db = undefined;
    return _db ? new Promise((resolve, reject) => {
            _db.close(err => {
                if (err) reject(err);
                else resolve();
            });
        }) : undefined;
}

We can simply use SQL to count the number of notes for us. In this case, db.get returns a row with a single column, count, which is the value we want to return.