Node.js, PostgreSQL With Sequelize

Leave a reply

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.

Leave a Reply

Your email address will not be published. Required fields are marked *