; Date: Tue Aug 25 2020

Tags: Node.JS »»»» Node.js Database

Like many web applications, the TODO application we are developing with Express and Bootstrap v5 must persist its data somewhere. For that purpose we will use Sequelize to to interface with an SQL database (SQLite3). Sequelize is a high level ORM (Object Relations Manager) for Node.js that supports a long list of SQL databases.

This article is part 2 of a multi-part series going over implementing a web application using Node.js 14, ExpressJS, Bootstrap v5, and Socket.IO. In this section we will set up a simple database layer using Sequelize. For an overview of the project, and index of the articles, see: Single page multi-user application with Express, Bootstrap v5, Socket.IO, Sequelize

In the project setup tutorial, we installed various packages, including js-yaml, sequelize and sqlite3. These gives us the ability to read YAML files, use Sequelize, and support the SQLite3 database engine.

It will be useful (but not required) to install the SQLite3 command-line tools on your laptop. For that purpose go to https://sqlite.org/index.html and follow the directions. If you are using a package management system for your laptop like MacPorts (macOS), Chocolatey (Windows), etc, then SQLite3 is probably already available and installable with a command as simple as apt-get install -y sqlite3 (for Ubuntu/Debian).

The Todo application, like most applications, needs to persist its data, and we have chosen to use a database. Object-Relations-Manager (ORM) libraries are popular since they make it so easy to use databases. Instead of writing raw SQL, Sequelize (a popular ORM) takes care of generating SQL.

Sequelize (see https://sequelize.org/) is an object-relations-manager (ORM) for SQL databases, which means it gives us a high level interface to SQL databases. While it supports a long list of SQL database engines, we will use SQLite3. That's because SQLite3 does not require any server setup. But we'll design this to enable easily switching to a different database engine, like MySQL, simply by using a different Sequelize configuration, and installing the corresponding Node.js database driver.

The Sequelize configuration will come from a simple YAML file. With it we will can easily use other databases, just by supplying a different configuration file.

The TODO data model

This is a simple application to keep a TODO list. At this stage we will keep one database table, with a small number of columns. For that purpose let's define a JavaScript class to serve as the model for our TODO objects.

Create a file named models/Todo.mjs containing:

const _todo_id = Symbol('id');
const _todo_title = Symbol('title');
const _todo_body = Symbol('body');
const _todo_precedence = Symbol('priority');

export class Todo {
    constructor(id, title, body, precedence) {
        this[_todo_id] = id; 
        this[_todo_title] = title; 
        this[_todo_body] = body; 
        this[_todo_precedence] = precedence; 
    }

    get id() { return this[_todo_id]; }
    set id(newID) { this[_todo_id] = newID; }
    get title() { return this[_todo_title]; }
    set title(newTitle) { this[_todo_title] = newTitle; }
    get body() { return this[_todo_body]; }
    set body(newBody) { this[_todo_body] = newBody; }
    get precedence() { return this[_todo_precedence]; }
    set precedence(newPrecedence) { this[_todo_precedence] = newPrecedence; }
}

This is a simple JavaScript class definition that describes the attributes of a Todo item. This is fairly straight-forward, in that we've defined four fields, and have defined setter and getter functions for each field. We are using Symbol instances to aid in storing the field values, because this provides a small measure of data hiding.

Each Symbol instance (a.k.a. Symbol('title')) is unique from every other Symbol instance, even if you executed Symbol('title') a second time. Hence this[_note_title] references a unique field in the Todo instance, and it is difficult for outside code to work out how to access that field.

• To learn more about Symbols see A deep dive guide to JavaScript Symbols (ES-6 and beyond)

This means each Todo instance has a set of data fields (id, title, body and precedence) that are somewhat insulated from being inappropriately modified by other code.

The four fields represent three basic fields of a Todo item, plus an instance identifier. The id field is meant to be used in a database to distinguish each Todo instance from every other. The title is meant to be the public description of the Todo item. The body is meant to be where we have more information, like use case descriptions, or links to supporting information. The precedence field is meant to capture how important that Todo item is.

Let's next see how to use Sequelize to store Todo items in a database.

Implementing the TODO model as a Sequelize table

That class, as useful as it is, does not give us database access. To do that with Sequelize, we must create a Schema which Sequelize uses to define the database table.

Create a file named models/sequlz.mjs and let's start with the following:

import { promises as fs } from 'fs';
import { default as jsyaml } from 'js-yaml';
import Sequelize from 'sequelize';

import { default as DBG } from 'debug';
const debug = DBG('todos:seqlz');
const error = DBG('todos:error-seqlz');
import util from 'util';

import { Todo } from './Todo.mjs';

This imports the necessary packages. The fs package is imported from the promises sub-package to get the promisified file system functions. We also bring in the Todo class.

export class SQTodo extends Sequelize.Model {}

The class SQTodo is a subclass of the Sequelize.Model class, and it will contain the data required by Sequelize to manage the database table. There will be additional initialization of this class when we tell Sequelize the Schema to use.

const _todo_sequlz = Symbol('sequlz');

export class TodoStore {
    constructor(sequlz) {
        this[_todo_sequlz] = sequlz;
    }
}

The TodoStore class is meant to encapsulate access to the database. This is just a starting point, since we will add a bunch of methods to this class. Primarily this class is meant to contain all functions for dealing with database entries.

Add this in TodoStore:

static async connect() {
    const yamltext = await fs.readFile(process.env.SEQUELIZE_CONNECT, 'utf8');
    const params = await jsyaml.safeLoad(yamltext, 'utf8');
    let sequlz = new Sequelize(params.dbname,
                    params.username, params.password,
                    params.params);
    await sequlz.authenticate();

    SQTodo.init({
        id: { type: Sequelize.INTEGER, autoIncrement: true,
            primaryKey: true },
        title: Sequelize.DataTypes.STRING,
        body: Sequelize.DataTypes.TEXT,
        precedence: Sequelize.DataTypes.INTEGER
    }, {
        sequelize: sequlz,
        modelName: 'SQTodo'
    });
    await SQTodo.sync();

    return new TodoStore(sequlz);
}

close() {
    if (this[_todo_sequlz]) this[_todo_sequlz].close();
    this[_todo_sequlz] = undefined;
}

The connect function is static to the TodoStore class, and the intent is for this to be a Factory Function. That is, elsewhere in the Todo application we will call TodoStore.connect() to create the database connection and define the schema. As we've already seen, this function is called from app.mjs. The close method is to be called when the application needs to close the database connection.

First we read in the configuration file specified in the SEQUELIZE_CONNECT environment variable. This file contains the connection parameters required to use Sequelize to connect to a database. We'll go over the file format in a minute.

We then create a Sequelize instance using the parameters in the configuration file. And calling the authorize function connects to the database and ensures everything is fine.

The SQTodo.init call initializes the database schema. We describe the fields of the schema in the parameters to this function call. The fields we describe match the fields of the Todo class we defined earlier.

In Sequelize, database schema's are defined with objects structured like this. The fields correspond to columns in the database table, with each field describing the column data type. When we get to running the application, we'll see the SQTodo schema corresponds to this SQL statement:

CREATE TABLE IF NOT EXISTS `SQTodos` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
    `title` VARCHAR(255),
    `body` TEXT,
    `precedence` INTEGER,
    `createdAt` DATETIME NOT NULL,
    `updatedAt` DATETIME NOT NULL
);

The createdAt and updatedAt columns are created by Sequelize. Otherwise these correspond directly to the parameters specified to SQTodo.init.

The second parameter to SQTodo.init has additional information, about how to connect to the database, and the name of the database table. Sequelize supports a number of other parameters, such as lifecycle hooks so that our code can be called as events happen in the database.

You'll see the Sequelize constructor takes parameters for describing the database connection. We could hard-code those parameters in the code, but then we'd have to change the code to change the database connection. It's a better practice to have a configuration file for these parameters. Which is why we added support for reading a YAML file specified in the SEQUELIZE_CONNECT variable.

We can create a file named models/sequelize-sqlite.yaml as a sample database configuration file:

dbname: todo 
username: 
password: 
params: 
    dialect: sqlite 
    storage: todo.sqlite3 

The first three fields describe the database name, and any username or password required. For SQLite3 no username or password is required. The params field describe any additional parameters required. The dialect field tells Sequelize which database driver to use, in this case SQLite3. And for this database the storage field says what file name to use for the database.

Pay attention and see that the fields of this file correspond to the parameters of the Sequelize constructor.

new Sequelize(params.dbname, params.username, params.password, params.params);

They match up exactly. That lets us read about configuration parameters in the Sequelize documentation and try them directly in the configuration file.

To use a different database either create another configuration file, or override fields appropriately using the other environment variables. For example, using this with MySQL would require a configuration file like this one:

dbname: todo 
username: todo
password: todo1234
params: 
    host: localhost
    port: 3306
    dialect: mysql

These aren't the safest configuration parameters - a better practice would be using a more obtuse password, for instance - but this demonstrates how easily one can reconfigure a Sequelize connection using this approach.

The next required steps are to ensure having a matching database server, and to install the mysql2 package for Node.js.

Utility functions for database access

So far we've seen how to initialize a database connection and Schema with Sequelize. Let's add a few convenience functions to TodoStore to make it easier on other parts of the application. The Todo application needs to do things like create Todo instances, delete them, and edit them.

Add this to the TodoStore class in models/sequlz.mjs:

async getAll() {
    let todos = await SQTodo.findAll();
    return todos.map(todo => {
        return (Todo.fromSQ(todo)).sanitized();
    })
    .sort(function compare(a, b) {
        if (a.precedence < b.precedence) {
        return 1;
        }
        if (a.precedence > b.precedence) {
        return -1;
        }
        // a must be equal to b
        return 0;
    });
}

This returns every Todo in the database, sorted by order of the precedence value. The Sequelize findAll function does what the name suggests, and returns all items in the database. We could add a query descriptor to limit the items to be returned, but in this case the goal is to display all items on the screen. These items are in an Array, and therefore we can use the map and sort array operations.

We've used two functions in the Todo class that haven't been shown yet. Let's add this in Todo.mjs to the Todo class.

export class Todo {
    ...
    sanitized() {
        return {
            id: this[_todo_id],
            title: this[_todo_title],
            body: this[_todo_body],
            precedence: this[_todo_precedence]
        }
    }

    static fromSQ(sqtodo) {
        return new Todo(sqtodo.id, sqtodo.title,
                        sqtodo.body, sqtodo.precedence);
    }
...
}

When we retrieve an SQTodo instance it has a whole lot of Sequelize functions attached. Those functions shouldn't be exposed to other code. In TodoStore.getAll, and some other functions, the Todo instance we return must not have all the bells and whistles of a SQTodo instance. The fromSQ and sanitized functions take care of this need.

The fromSQ function is a static factory function for the Todo class. It is meant to receive a SQTodo instance, and instantiate a Todo instance.

The sanitized function returns a simple plain anonymous JavaScript object from a Todo instance.

If we return to this line:

(Todo.fromSQ(todo)).sanitized()

The code Todo.fromSQ(todo) passes todo, an SQTodo instance, to the Todo.fromSQ function, from which it creates a Todo instance. That much is within parenthesis to ensure that the call to sanitized happens on the Todo instance. By calling that function we then convert the Todo instance to a plain JavaScript object.

We do this because in the user interface layer we send these objects via Socket.IO to the browser. If we had not sanitized the objects, the browser-side code does not receive data in the objects. Apparently Socket.IO doesn't know how to handle an object where the fields are accessed via getter functions.

A good question is whether this module should sanitize the Todo instances, or whether that should happen in the user interface layer. Put your thinking caps on and see what you come up with.

Let's now return to the TodoStore class in models/sequlz.mjs and add this:

async create(todo) {
    await SQTodo.create({
        title: todo.title, body: todo.body, precedence: todo.precedence
    });
}

This creates a new Todo in the database. It is meant to be used when the application user requested to add a new Todo item.

async update(todo) {
    const sqtodo = await SQTodo.findOne({ where: { id: todo.id } });
    if (!sqtodo) {
        throw new Error(`No TODO found for ${todo.id}`);
    } else {
        await SQTodo.update({
            title: todo.title, body: todo.body, precedence: todo.precedence
        }, {
            where: { id: todo.id }
        });
    }
}

This updates an existing Todo item, and is meant to be used when the user edits an item. Unlike the create case, the update case is for an existing Todo, and therefore the incoming object description will have an id field. We use that id to search in the database for the item to modify.

The code reading { where: { id: todo.id } } is an example of a Sequelize query descriptor. In this case it matches database entries with the given value for id. Hence, we first call findOne to ensure the database has an item with this id, and then we use the SQTodo.update function to update that item with the new values.

The create and update functions are another instance worthy of pondering the correct return value. In this implementation the functions do not return a value. But maybe they should return the Todo instance that was created. Give it a think and see what you come up with.

async destroy(id) {
    const todo = await SQTodo.findOne({ where: { id } });
    if (todo) {
        todo.destroy();
    }
}

Here we use findOne to retrieve the matching item, and then if it was retrieved we call its destroy function.

Summary

In this tutorial we've added database support to the TODO application by bringing in the Sequelize package. We've constructed a schema using Sequelize, and seen how that translates to a database table definition. We've also seen how to easily reconfigure Sequelize configurations by using a simple YAML file.

But we still do not have a working application. We have one remaining task to accomplish, which is creating the user interface code. For that see: Using Bootstrap and Socket.IO for the user interface in an Express Node.js 14 application

To learn about the project setup: Project setup for Node.js 14, Express, Bootstrap v5, Socket.IO application

To learn about the Bootstrap user interface: Using Bootstrap and Socket.IO for the user interface in an Express Node.js 14 application

To learn about running the Todo application: Running the TODO application

To return to the project overview: Single page multi-user application with Express, Bootstrap v5, Socket.IO, Sequelize