A recent project has prompted me to adopt Node.js, a popular by-design lean and mean server, as the server-side tech stack. With the requirement for a rather UI feature-rich web application, I include React (a.k.a. ReactJS) as part of the tech stack. A backend database is needed, so I pick PostgreSQL. Thus, this is a deviation from the Scala
/ Akka Actor
/ Akka Stream
tech stack I’ve been using in recent years.
PostgreSQL has always been one of my favorite database choices whenever a robust RDBMS with decent scalability is required for a given R&D project. With Node.js being the chosen app/API server and React the UI library for the project at hands, I decided to use Sequelize, a popular ORM tool in the Node
ecosystem, as the ORM tool.
First and foremost, I must acknowledge the effective documentation on Sequelize’s official website, allowing developers new to it to quickly pick up the essential know-how’s from:
to the more advanced topics like:
Getting started
Assuming the Node.js module is already in place, to install PostgreSQL driver and Sequelize, simply do the following under the Node project root subdirectory:
$ npm install --save pg pg-hstore $ npm install --save sequelize
Next, create a configuration script ${node-project-root}/app/config/db.config.js
for PostgreSQL like below:
module.exports = { HOST: "localhost", USER: "leo", PASSWORD: "changeme!", DB: "leo", dialect: "postgres", pool: { max: 5, min: 0, acquire: 30000, idle: 10000 } };
For the data model, let’s create script files for a few sample tables under ${node-project-root}/app/models/
:
# user.model.js module.exports = (sequelize, Sequelize) => { const User = sequelize.define("users", { username: { type: Sequelize.STRING }, email: { type: Sequelize.STRING }, password: { type: Sequelize.STRING }, firstName: { type: Sequelize.STRING }, lastName: { type: Sequelize.STRING } }); return User; };
# role.model.js module.exports = (sequelize, Sequelize) => { const Role = sequelize.define("roles", { id: { type: Sequelize.INTEGER, primaryKey: true }, name: { type: Sequelize.STRING } }); return Role; };
# order.model.js module.exports = (sequelize, Sequelize) => { const Order = sequelize.define("orders", { orderDate: { type: Sequelize.DATE }, userId: { type: Sequelize.INTEGER }, // add other attributes here ... }); return Order; };
# item.model.js module.exports = (sequelize, Sequelize) => { const Item = sequelize.define("items", { serialNum: { type: Sequelize.STRING }, orderId: { type: Sequelize.INTEGER }, // add other attributes here ... }); return Item; };
Sequelize instance
Note that within the above data model scripts, each of the table entities is represented by a function with two arguments — Sequelize
refers to the Sequelize
library, whereas sequelize
is an instance of it. The instance is what’s required to connect to a given database. It has a method define()
responsible for specifying the table definition including the table attributes and the by-default pluralized table name.
Also note that it looks as though the typical primary key column id
is missing in most of the above table definitions. That’s because Sequelize would automatically create an auto-increment integer column id
if none is specified. For a table intended to be set up with specific primary key values, define it with explicitly (similar to how table roles
is set up in our sample models).
The Sequelize
instance is created and initialized within ${node-project-root}/app/models/index.js
as shown below.
# ${node-project-root}/app/models/index.js const config = require("../config/db.config.js"); const Sequelize = require("sequelize"); const sequelize = new Sequelize( config.DB, config.USER, config.PASSWORD, { host: config.HOST, dialect: config.dialect, pool: { max: config.pool.max, min: config.pool.min, acquire: config.pool.acquire, idle: config.pool.idle } } ); const db = {}; db.Sequelize = Sequelize; db.sequelize = sequelize; db.user = require("../models/user.model.js")(sequelize, Sequelize); db.role = require("../models/role.model.js")(sequelize, Sequelize); db.order = require("../models/order.model.js")(sequelize, Sequelize); db.item = require("../models/item.model.js")(sequelize, Sequelize); db.role.belongsToMany(db.user, { through: "user_role" }); db.user.belongsToMany(db.role, { through: "user_role" }); db.user.hasMany(db.order, { as: "order" }); db.order.belongsTo(db.user, { foreignKey: "userId", as: "user" }); db.order.hasMany(db.item, { as: "item" }); db.item.belongsTo(db.order, { foreignKey: "orderId", as: "order" }); db.ROLES = ["guest", "user", "admin"]; module.exports = db;
Data model associations
As can be seen from the index.js
data model script, after a Sequelize instance is instantiated, it loads the database configuration information from db.config.js
as well as the table definitions from the individual model scripts.
Also included in the index.js
script are examples of both the one-to-many and many-to-many association types. For instance, the relationship between table users
and orders
is one-to-many with userId
as the foreign key:
db.user.hasMany(db.order, { as: "order" }); db.order.belongsTo(db.user, { foreignKey: "userId", as: "user" });
whereas relationship between users
and roles
is many-to-many.
db.role.belongsToMany(db.user, { through: "user_role" }); db.user.belongsToMany(db.role, { through: "user_role" });
Database schema naming conventions
Contrary to the camelCase
naming style for variables in programming languages such as JavaScript, Java, Scala, conventional RDBMSes tend to use snake_case
naming style for table and column names. To accommodate the different naming conventions, Sequelize
automatically converts database schemas’ snake_case
style to JavaScript objects’ camelCase
. To keep the database schema in snake_case
style one can customize the Sequelize
instance by specifying underscored: true
within the define {}
segment as shown below.
As mentioned in an earlier section, Sequelize
pluralizea database table names by default. To suppress the auto-pluralization, specifying also freezeTableName: true
within define {}
followed by defining the table with singular names within the individual model scripts.
const sequelize = new Sequelize( config.DB, config.USER, config.PASSWORD, { host: config.HOST, dialect: config.dialect, pool: { max: config.pool.max, min: config.pool.min, acquire: config.pool.acquire, idle: config.pool.idle }, define: { underscored: true, freezeTableName: true } } );
An “inconvenience” in PostgreSQL
Personally, I prefer keeping database table names singular. However, I have a table I’d like to name it user
which is disallowed within PostgreSQL’s default schema namespace. That’s because PostgreSQL makes user
a reserved keyword.
A work-around would be to define a custom schema that serves as a namespace in which all user-defined entities are contained. An inconvenient consequence is that when performing queries using tools like psql
, one would need to alter the schema search path from the default public
schema to the new one.
ALTER ROLE leo SET search_path TO myschema;
After weighing the pros and cons, I decided to go with Sequelize
‘s default pluralized table naming. Other than this minor inconvenience, I find Sequelize
an easy-to-pick-up ORM for wiring programmatic CRUD operations with PostgreSQL from within Node’s controller modules.
The following sample snippet highlights what a simple find-by-primary-key
select
and update
might look like in a Node controller:
const db = require("../models"); const User = db.user; ... exports.find = (req, res) => { const id = req.params.id; User.findByPk(id) .then(data => { if (data) { res.send(data); } else { res.status(404).send({ message: `ERROR finding user with id=${id}!` }); } }) .catch(err => { res.status(500).send({ message: `ERROR retrieving user data!` }); }); }; exports.update = (req, res) => { const id = req.params.id; User.update(req.body, { where: { id: id } }) .then(num => { if (num == 1) { res.send({ message: "User was updated successfully!" }); } else { res.send({ message: `ERROR updating user with id=${id}!` }); } }) .catch(err => { res.status(500).send({ message: `ERROR updating user data!` }); }); };
In the next blog post, we’ll shift our focus towards the popular UI library React and how state changes propagate across the UI components and the React Redux central store.