There are several popular SQL database engines, such as PostgreSQL, MySQL, and MariaDB. Corresponding to each are Node.js client modules that are similar in nature to the sqlite3 module that we just used. The programmer is close to SQL, which can be good in the same way that driving a stick shift car is fun. But what if we want a higher-level view of the database so that we can think in terms of objects, rather than rows of a database table? ORM systems provide a suitable higher-level interface, and even offer the ability to use the same data model with several databases. Just as driving an electric car provides lots of benefits at the expense of losing out on the fun of stick-shift driving, ORM produces lots of benefits, while also distancing ourselves from the SQL.
The Sequelize package (http://www.sequelizejs.com/) is Promise-based, offers strong, well-developed ORM features, and can connect to SQLite3, MySQL, PostgreSQL, MariaDB, and MSSQL databases. Because Sequelize is Promise-based, it will fit naturally with the Promise-based application code we’re writing.
A prerequisite to most SQL database engines is having access to a database server. In the previous section, we skirted around this issue by using SQLite3, which requires no database server setup. While it’s possible to install a database server on your laptop, right now, we want to avoid the complexity of doing so, and so we will use Sequelize to manage a SQLite3 database. We’ll also see that it’s simply a matter of using a configuration file to run the same Sequelize code against a hosted database such as MySQL. In Chapter 11, Deploying Node.js Microservices with Docker, we’ll learn how to use Docker to easily set up a service, including database servers, on our laptop and deploy the exact same configuration to a live server. Most web-hosting providers offer MySQL or PostgreSQL as part of their service.
Before we start on the code, let’s install two modules:
$ npm install sequelize@6.x –save
$ npm install js-yaml@3.13.x –save
The first obviously installs the Sequelize package. The second, js-yaml, is installed so that we can implement a YAML-formatted file to store the Sequelize connection configuration. YAML is a human-readable data serialization language, which simply means it is an easy-to-use text file format to describe data objects.
Let’s start this by learning how to configure Sequelize, then we will create an AbstractNotesStore instance for Sequelize, and finally, we will test Notes using Sequelize.
1. Configuring Sequelize and connecting to a database
We’ll be organizing the code for Sequelize support a little differently from before. We foresee that the Notes table is not the only data model that the Notes application will use. We could support additional features, such as the ability to upload images for a note or to allow users to comment on notes. This means having additional database tables and setting up relationships between database entries. For example, we might have a class named AbstractCommentStore to store comments, which will have its own database table and its own modules to manage the commented data. Both the Notes and Comments storage areas should be in the same database, and so they should share a database connection.
With that in mind, let’s create a file, models/sequlz.mjs, to hold the code to manage the Sequelize connection:
import { promises as fs } from ‘fs’;
import { default as jsyaml } from ‘js-yaml’;
import Sequelize from ‘sequelize’;
let sequlz;
export async function connectDB() {
if (typeof sequlz === ‘undefined’) {
const yamltext = await fs.readFile(process.env.SEQUELIZE_CONNECT, ‘utf8’);
const params = jsyaml.safeLoad(yamltext, ‘utf8’);
if (typeof process.env.SEQUELIZE_DBNAME !== ‘undefined’ && process.env.SEQUELIZE_DBNAME !== ”) {
params.dbname = process.env.SEQUELIZE_DBNAME;
}
if (typeof process.env.SEQUELIZE_DBUSER !== ‘undefined’ && process.env.SEQUELIZE_DBUSER !== ”) {
params.username = process.env.SEQUELIZE_DBUSER;
}
if (typeof process.env.SEQUELIZE_DBPASSWD !== ‘undefined’ && process.env.SEQUELIZE_DBPASSWD !== ”) {
params.password = process.env.SEQUELIZE_DBPASSWD;
}
if (typeof process.env.SEQUELIZE_DBHOST !== ‘undefined’ && process.env.SEQUELIZE_DBHOST !== ”) {
params.params.host = process.env.SEQUELIZE_DBHOST;
}
if (typeof process.env.SEQUELIZE_DBPORT !== ‘undefined’ && process.env.SEQUELIZE_DBPORT !== ”) {
params.params.port = process.env.SEQUELIZE_DBPORT;
}
if (typeof process.env.SEQUELIZE_DBDIALECT !== ‘undefined’ && process.env.SEQUELIZE_DBDIALECT !== ”) {
params.params.dialect = process.env.SEQUELIZE_DBDIALECT;
}
sequlz = new Sequelize(params.dbname,
params.username, params.password, params.params);
await sequlz.authenticate();
}
return sequlz;
}
export async function close() {
if (sequlz) sequlz.close();
sequlz = undefined;
}
As with the SQLite3 module, the connectDB function manages the connection through Sequelize to a database server. Since the configuration of the Sequelize connection is fairly complex and flexible, we’re not using environment variables for the whole configuration, but instead we use a YAML-formatted configuration file that will be specified in an environment variable. Sequelize uses four items of data—the database name, the username, the password, and a parameters object.
When we read in a YAML file, its structure directly corresponds to the object structure that’s created. Therefore, with a YAML configuration file, we don’t need to use up any brain cells developing a configuration file format. The YAML structure is dictated by the Sequelize params object, and our configuration file simply has to use the same structure.
We also allow overriding any of the fields in this file using environment variables. This will be useful when we deploy Notes using Docker so that we can configure database connections without having to rebuild the Docker container.
For a simple SQLite3-based database, we can use the following YAML file for configuration and name it models/sequelize-sqlite.yaml:
dbname: notes
username:
password:
params:
dialect: sqlite
storage: notes-sequelize.sqlite3
The params.dialect value determines what type of database to use; in this case, we’re using SQLite3. Depending on the dialect, the params object can take different forms, such as a connection URL to the database. In this case, we simply need a filename, which is given here.
The authenticate call is there to test whether the database connected correctly. The close function does what you expect—it closes the database connection.
With this design, we can easily change the database to use other database servers, just by adding a runtime configuration file. For example, it is easy to set up a MySQL connection; we just create a new file, such as models/sequelize-mysql.yaml, containing something similar to the following code:
dbname: notes
username: .. user
name password: .. password
params:
host: localhost
port: 3306
dialect: mysql
This is straightforward. The username and password fields must correspond to the database credentials, while host and port will specify where the database is hosted. Set the database’s dialect parameter and other connection information and you’re good to go.
To use MySQL, you will need to install the base MySQL driver so that Sequelize can use MySQL:
$ npm install mysql@2.x –save
Running Sequelize against the other databases it supports, such as PostgreSQL, is just as simple. Just create a configuration file, install the Node.js driver, and install/configure the database engine.
The object returned from connectDB is a database connection, and as we’ll see that it is used by Sequelize. So, let’s get going with the real goal of this section—to define the SequelizeNotesStore class.
2. Creating a Sequelize model for the Notes application
As with the other data storage engines we’ve used, we need to create a subclass of AbstractNotesStore for Sequelize. This class will manage a set of notes using a Sequelize Model class.
Let’s create a new file, models/notes-sequelize.mjs:
import { Note, AbstractNotesStore } from ‘./Notes.mjs’;
import Sequelize from ‘sequelize’;
import {
connectDB as connectSequlz,
close as closeSequlz
} from ‘./sequlz.mjs’;
import DBG from ‘debug’;
const debug = DBG(‘notes:notes-sequelize’);
const error = DBG(‘notes:error-sequelize’);
let sequelize;
export class SQNote extends Sequelize.Model {}
async function connectDB() {
if (sequelize) return;
sequelize = await connectSequlz();
SQNote.init({
notekey: { type: Sequelize.DataTypes.STRING, primaryKey: true, unique: true },
title: Sequelize.DataTypes.STRING, body: Sequelize.DataTypes.TEXT
}, {
});
sequelize, modelName: ‘SQNote’
await SQNote.sync();
}
The database connection is stored in the sequelize object, which is established by the connectDB function that we just looked at (which we renamed connectSequlz) to instantiate a Sequelize instance. We immediately return if the database is already connected.
In Sequelize, the Model class is where we define the data model for a given object. Each Model class corresponds to a database table. The Model class is a normal ES6 class, and we start by subclassing it to define the SQNote class. Why do we call it SQNote? That’s because we already defined a Note class, so we had to use a different name in order to use both classes.
By calling SQNote.init, we initialize the SQNote model with the fields—that is, the schema—that we want it to store. The first argument to this function is the schema description and the second argument is the administrative data required by Sequelize.
As you would expect, the schema has three fields: notekey, title, and body. Sequelize supports a long list of data types, so consult the documentation for more on that. We are using STRING as the type for notekey and title since both handle a short text string up to 255 bytes long. The body field is defined as TEXT since it does not need a length limit. In the notekey field, you see it is an object with other parameters; in this case, it is described as the primary key and the notekey values must be unique.
That manages the database connection and sets up the schema. Now, let’s add the SequelizeNotesStore class to models/notes-sequelize.mjs:
export default class SequelizeNotesStore extends AbstractNotesStore {
async close() {
closeSequlz();
sequelize = undefined;
}
async update(key, title, body) {
await connectDB();
const note = await SQNote.findOne({ where: { notekey: key
} })
if (!note) {
throw new Error(‘No note found for ${key}’);
} else {
await SQNote.update({ title, body },
{ where: { notekey: key } });
return this.read(key);
}
}
async create(key, title, body) {
await connectDB();
const sqnote = await SQNote.create({
notekey: key, title, body
});
return new Note(sqnote.notekey, sqnote.title, sqnote.body);
}
async read(key) {
await connectDB();
const note = await SQNote.findOne({ where: { notekey: key
} });
if (!note) {
throw new Error(‘No note found for ${key}’);
} else {
return new Note(note.notekey, note.title, note.body);
}
}
async destroy(key) {
await connectDB();
await SQNote.destroy({ where: { notekey: key } });
}
async keylist() {
await connectDB();
const notes = await SQNote.findAll({ attributes: [ ‘notekey’
] });
const notekeys = notes.map(note => note.notekey);
return notekeys;
}
async count() {
await connectDB();
const count = await SQNote.count();
return count;
}
}
The first thing to note is that in each function, we call static methods defined in the SQNote class to perform database operations. Sequelize model classes work this way, and there is a comprehensive list of these static methods in its documentation.
When creating a new instance of a Sequelize model class—in this case, SQNote—there are two patterns to follow. One is to call the build method and then to create the object and the save method to save it to the database. Alternatively, we can, as is done here, use the create method, which does both of these steps. This function returns an SQNote instance, called sqnote here, and if you consult the Sequelize documentation, you will see that these instances have a long list of methods available. The contract for our create method is to return a note, so we construct a Note object to return.
In this, and some other methods, we do not want to return a Sequelize object to our caller. Therefore, we construct an instance of our own Note class in order to return a clean object.
Our update method starts by calling SQNote.findOne. This is done to ensure that there is an entry in the database corresponding to the key that we’re given. This function looks for the first database entry where notekey matches the supplied key. Following the happy path, where there is a database entry, we then use SQNote.update to update the title and body values, and by using the same where clause, it ensures the update operation targets the same database entry.
The Sequelize where clause offers a comprehensive list of matching operators. If you ponder this, it’s clear it roughly corresponds to SQL as follows:
SELECT SQNotes SET title = ?, body = ? WHERE notekey = ?
That’s what Sequelize and other ORM libraries do—convert the high-level API into database operations such as SQL queries.
To read a note, we use the findOne operation again. There is the possibility of it returning an empty result, and so we have to throw an error to match. The contract for this function is to return a Note object, so we take the fields retrieved using Sequelize to create a clean Note instance.
To destroy a note, we use the destroy operation with the same where clause to specify which entry to delete. This means that, as in the equivalent SQL statement (DELETE FROM SQNotes WHERE notekey = ?), if there is no matching note, no error will be thrown.
Because the keylist function acts on all Note objects, we use the findAll operation. The difference between findOne and findAll is obvious from the names.
While findOne returns the first matching database entry, findAll returns all of them. The attributes specifier limits the result set to include the named field—namely, the notekey field. This gives us an array of objects with a field named notekey. We then use a .map function to convert this into an array of note keys.
For the count function, we can just use the count() method to calculate the required result.
This allows us to use Sequelize by setting NOTES_MODEL to sequelize.
Having set up the functions to manage the database connection and defined the
SequelizeNotesStore class, we’re now ready to test the Notes application.
2. Running the Notes application with Sequelize
Now, we can get ready to run the Notes application using Sequelize. We can run it against any database server, but let’s start with SQLite3. Add the following declarations to the scripts entry in package.json:
“sequelize-start”: “cross-env DEBUG=notes:* SEQUELIZE_CONNECT=models/sequelize-sqlite.yaml NOTES_MODEL=sequelize node ./app.mjs”,
“sequelize-server1”: “cross-env SEQUELIZE_CONNECT=models/sequelize- sqlite.yaml NOTES_MODEL=sequelize PORT=3001 node ./app.mjs”,
“sequelize-server2”: “cross-env SEQUELIZE_CONNECT=models/sequelize- sqlite.yaml NOTES_MODEL=sequelize PORT=3002 node ./a[[.mjs”,
This sets up commands to run a single server instance (or two).
Then, run it as follows:
$ npm run sequelize-start
> notes@0.0.0 sequelize-start /home/david/Chapter07/notes
> cross-env DEBUG=notes:* SEQUELIZE_CONNECT=models/sequelize- yaml NOTES_MODEL=sequelize node ./app.mjs
notes:debug Listening on port 3000 +0ms
As before, the application looks exactly the same because we haven’t changed the View templates or CSS files. Put it through its paces and everything should work.
You will be able to start two instances; use separate browser windows to visit both instances and see whether they show the same set of notes.
To reiterate, to use the Sequelize-based model on a given database server, do the following:
- Install and provision the database server instance; otherwise, get the connection parameters for an already-provisioned database server.
- Install the corresponding js driver.
- Write a YAML configuration file corresponding to the connection parameters
- Create new scripts entries in json to automate starting Notes against the database.
By using Sequelize, we have dipped our toes into a powerful library for managing data in a database. Sequelize is one of several ORM libraries available for Node.js. We’ve already used the word comprehensive several times in this section as it’s definitely the best word to describe Sequelize.
An alternative that is worthy of exploration is not an ORM library but is what’s called a query builder. knex supports several SQL databases, and its role is to simplify creating SQL queries by using a high-level API.
In the meantime, we have one last database to cover before wrapping up this chapter: MongoDB, the leading NoSQL database.
Source: Herron David (2020), Node.js Web Development: Server-side web development made easy with Node 14 using practical examples, Packt Publishing.