To get started with more normal databases, let’s see how we can use SQL from Node.js. First, we’ll use SQLite3, which is a lightweight, simple-to-set-up database engine eminently suitable for many applications.
The primary advantage of SQLite3 is that it doesn’t require a server; it is a self- contained, no-set-up-required SQL database. The SQLite3 team also claims that it is very fast and that large, high-throughput applications have been built with it. The downside to the SQLite3 package is that its API requires callbacks, so we’ll have to use the Promise wrapper pattern.
The first step is to install the module:
$ npm install sqlite3@5.x –save
This, of course, installs the sqlite3 package.
To manage a SQLite3 database, you’ll also need to install the SQLite3 command-line tools. The project website has precompiled binaries for most operating systems. You’ll also find the tools available in most package management systems.
One management task that we can use is setting up the database tables, as we will see in the next section.
1. The SQLite3 database schema
Next, we need to make sure that our database is configured with a database table suitable for the Notes application. This is an example database administrator task, as mentioned at the end of the previous section. To do this, we’ll use the sqlite3 command-line tool. The sqlite3.org website has precompiled binaries, or the tool can be installed through your operating system’s package management system—for example, you can use apt-get on Ubuntu/Debian and MacPorts on macOS.
For Windows, make sure you have installed the Chocolatey package manager tool from https://chocolatey.org. Then start a PowerShell with Administrator privileges, and run “choco install sqlite”. That installs the SQLite3 DLL’s and its command-line tools, letting you run the following instructions.
We’re going to use the following SQL table definition for the schema (save it as models/schema-sqlite3.sql):
CREATE TABLE IF NOT EXISTS notes (
notekey VARCHAR(255),
title VARCHAR(255),
body TEXT
);
To initialize the database table, we run the following command:
$ sqlite3 chap07.sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter “.help” for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS notes (
…> notekey VARCHAR(255),
…> title VARCHAR(255),
…> body TEXT
…> );
sqlite> .schema notes CREATE TABLE notes (
notekey VARCHAR(255),
title VARCHAR(255),
body TEXT
);
sqlite> ^D
$ ls -l chap07.sqlite3
-rwx—— 1 david staff 8192 Jan 14 20:40 chap07.sqlite3
While we can do this, however, the best practice is to automate all the administrative processes. To that end, we should instead write a little bit of script to initialize the database.
Fortunately, the sqlite3 command offers us a way to do this. Add the following to the scripts section of package.json:
“sqlite3-setup”: “sqlite3 chap07.sqlite3 –init models/schema- sqlite3.sql”,
Run the setup script:
$ npm run sqlite3-setup
> notes@0.0.0 sqlite3-setup /home/david/Chapter07/notes
> sqlite3 sqlite3 –init models/schema-sqlite3.sql
— Loading resources from models/schema-sqlite3.sql SQLite version 3.30.1 2019-10-10 20:19:45
Enter “.help” for usage hints. sqlite> .schema notes
CREATE TABLE notes (
notekey VARCHAR(255),
title VARCHAR(255),
body TEXT
);
sqlite> ^D
This isn’t fully automated since we have to press Ctrl + D at the sqlite prompt, but at least we don’t have to use our precious brain cells to remember how to do this. We could have easily written a small Node.js script to do this; however, by using the tools provided by the package, we have less code to maintain in our own project.
With the database table set up, let’s move on to the code to interface with SQLite3.
2. The SQLite3 model code
We are now ready to implement an AbstractNotesStore implementation for SQLite3.
Create the models/notes-sqlite3.mjs file:
import util from ‘util’;
import { Note, AbstractNotesStore } from ‘./Notes.mjs’;
import { default as sqlite3 } from ‘sqlite3’;
import { default as DBG } from ‘debug’;
const debug = DBG(‘notes:notes-sqlite3’);
const error = DBG(‘notes:error-sqlite3’);
let db;
async function connectDB() {
if (db) return db;
const 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 imports the required packages and makes the required declarations.
The connectDB function has a similar purpose to the one in notes-level.mjs: to manage the database connection. If the database is not open, it’ll go ahead and open it, and it will even make sure that the database file is created (if it doesn’t exist). If the database is already open, it’ll simply be returned.
Since the API used in the sqlite3 package requires callbacks, we will have to wrap every function call in a Promise wrapper, as shown here.
Now, add the following to models/notes-sqlite3.mjs:
export default class SQLITE3NotesStore extends AbstractNotesStore {
// See implementation below
}
Since there are many member functions, let’s talk about them individually:
async close() {
const _db = db; db = undefined; return _db ?
new Promise((resolve, reject) => {
_db.close(err => {
if (err) reject(err);
else resolve();
});
}) : undefined;
}
In close, the task is to close the database. There’s a little dance done here to make sure the global db variable is unset while making sure we can close the database by saving db as _db. The sqlite3 package will report errors from db.close, so we’re making sure we report any errors:
async update(key, title, body) {
const db = await connectDB();
const 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;
}
async create(key, title, body) {
const db = await connectDB();
const 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;
}
We are now justified in defining to have separate create and update operations for the Notes model because the SQL statement for each function is different. The create function, of course, requires an INSERT INTO statement, while the update function, of course, requires an UPDATE statement.
The db.run function, which is used several times here, executes a SQL query while giving us the opportunity to insert parameters in the query string.
This follows a parameter substitution paradigm that’s common in SQL programming interfaces. The programmer puts the SQL query in a string and then places a question mark anywhere that the aim is to insert a value in 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 also preventing SQL injection attacks.
The db.run function simply runs the SQL query it is given and does not retrieve any data:
async read(key) {
const db = await connectDB();
const 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. Since our read method only returns one item, we use the db.get function to retrieve just the first row of the result set. By contrast, the db.all function returns all of the rows of the result set at once, and the db.each function retrieves one row at a time, while still allowing the entire result set to be processed.
By the way, this read function has a bug in it—see whether you can spot the error. We’ll read more about this in Chapter 13, Unit Testing and Functional Testing, when our testing efforts uncover the bug:
async destroy(key) {
const db = await connectDB();
return await new Promise((resolve, reject) => {
db.run(“DELETE FROM notes WHERE notekey = ?;”, [ key ], err =>
{
});
}
});
if (err) return reject(err);
resolve();
In our destroy method, we simply use db.run to execute the DELETE FROM statement to delete the database entry for the associated note:
async keylist() {
const db = await connectDB();
const keyz = await new Promise((resolve, reject) => {
const keyz = [];
db.all(“SELECT notekey FROM notes”, (err, rows) => {
if (err) return reject(err);
resolve(rows.map(row => {
return row.notekey;
}));
});
});
return keyz;
}
In keylist, the task is to collect the keys for all of the Note instances. As we said, db.get returns only the first entry of the result set, while the db.all function retrieves all the rows of the result set. Therefore, we use db.all, although db.each would have been a good alternative.
The contract for this function is to return an array of note keys. The rows object from db.all is an array of results from the database that contains the data we are to return, but we use the map function to convert the array into the format required by this function:
async count() {
const db = await connectDB();
const 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;
}
In count, the task is similar, but we simply need a count of the rows in the table. SQL provides a count() function for this purpose, which we’ve used, and then because this result only has one row, we can again use db.get.
This enables us to run Notes with NOTES_MODEL set to sqlite3. With our code now set up, we can now proceed to run Notes with this database.
3. Running Notes with SQLite3
We’re now ready to run the Notes application with SQLite3. Add the following code to the scripts section of package.json:
“sqlite3-setup”: “sqlite3 chap07.sqlite3 –init models/schema-sqlite3.sql”,
“sqlite3-start”: “cross-env SQLITE_FILE=chap07.sqlite3 DEBUG=notes:* NOTES_MODEL=sqlite3 node./app.mjs”,
“sqlite3-server1”: “cross-env SQLITE_FILE=chap07.sqlite3 NOTES_MODEL=sqlite3 PORT=3001 node./app.mjs”,
“sqlite3-server2”: “cross-env SQLITE_FILE=chap07.sqlite3 NOTES_MODEL=sqlite3 PORT=3002 node./app.mjs”,
This sets up the commands that we’ll use to test Notes on SQLite3. We can run the server as follows:
$ npm run sqlite3-start
> notes@0.0.0 sqlite3-start /home/david/Chapter07/notes
> cross-env SQLITE_FILE=chap07.sqlite3 DEBUG=notes:*
NOTES_MODEL=sqlite3 node ./app.mjs
notes:debug Listening on port 3000 +0ms
You can now browse the application at http://localhost:3000 and run it through its paces, as before.
Because we still haven’t made any changes to the View templates or CSS files, the application will look the same as before.
Of course, you can use the sqlite command, or other SQLite3 client applications, to inspect the database:
$ sqlite3 chap07.sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter “.help” for usage hints.
sqlite> select * from notes;
hithere|Hi There||ho there what there
himom|Hi Mom||This is where we say thanks
The advantage of installing the SQLite3 command-line tools is that we can perform any database administration tasks without having to write any code.
We have seen how to use SQLite3 with Node.js. It is a worthy database for many sorts of applications, plus it lets us use a SQL database without having to set up a server.
The next package that we will cover is an Object Relations Management (ORM) system that can run on top of several SQL databases.
Source: Herron David (2020), Node.js Web Development: Server-side web development made easy with Node 14 using practical examples, Packt Publishing.