Sequelize

It’s a new year and we’ve started some new projects at work. Over the next few months I’m working on a project to push our specification products on using newer technologies. Traditionally, I’ve worked mostly with a Microsoft Stack – SQL Server and .NET (EntityFramework, WinForms or  WebAPI and ASP.NET). However, a hobby of mine (and part of my role at work) is to keep tabs on latest technologies. I’ve been following the various emerging JavaScript frameworks closely over the last few years – EmberJS, Angular, VueJS, NodeJS, Express (I’ve not looked at ReactJS yet but mean to). One thing I tell everyone who will listen is to bookmark the ThoughWorks technology radar.

For the new project, I want to use JavaScript only – Angular2 on the front end and, NodeJS/Express on the back-end. The main motivation is one of cost and scalability – JavaScript runs on pretty much anything, the ecosystem is full of open source solutions and the stack is now fairly mature (with successful production usage of many of the frameworks). I considered .NET Core but from a previous prototype, the toolset isn’t mature enough yet (maybe it will be when the next version of VisualStudio is released). I also have to admit, I found the whole .NET Core experience quite frustrating during that prototype with tools being marked as RC1 (DNC, DNX etc) only to be totally re-written in RC2 (dotnet cli). Good reason, but changes were so fundamental and should have gone back to a beta/preview status.

The first area I started looking at was the backend data model, API and database. It was during reviewing GraphQL that I happened upon an excellent video by Lee Benson where he showed implementing a GraphQL API backed by a database that used Sequelize as the data access component. As mentioned, I’m used to EntityFramework so I’m familiar with ORMs – I’ve just never used an ORM written in JavaScript!

This blog post will cover a very simple example of creating a NodeJS app and Sequalize model that backs a Postgres database.

Step1

Our first step is to create a new node app and add the necessary dependancies.

$ npm init
$ npm install sequelize -save

# Package for Postgres support
$ npm install pg -save

Step2

We’re going to create a very simple model to store Uniclass 2015 in a database. We will model this as 2 tables:

erd

Simple Entity-Relationship-Diagram

The classification table will store the name of the classification; the classificationItems table will store all of the entries in Uniclass 2015. ClassificationItems will be a self-referencing table so that we can model Uniclass 2015 as a tree.

Step3

We’re going to use Atom, a fantastic text editor, to write our JavaScript. First, we need to create a new .js file to add our database model to. We’ll call this new file “db.js”.

First off, we need to import the sequelize library and create our database connection

const Sequelize = require('sequelize');

const Conn = new Sequelize(
 'classification',
 'postgres',
 'postgres',
 {
 dialect: 'postgres',
 host: 'localhost',
 }
);

Sequelize supports a number of different databases – MySQL, MariaDb, SQlite, Postgres and MS SQL Server. In this example, we’re using the Postgres provider.

Next we define our two models:

const Classification = Conn.define('classification', {
  title: {
    type: Sequelize.STRING,
    allowNull: false,
    comment: 'Classification system name'
  },
  publisher: {
    type: Sequelize.STRING,
    allowNull: true,
    description: 'The author of the classification system'
  },
});

const ClassificationItem = Conn.define('classificationItem', {
  notation: {
    type: Sequelize.STRING,
    allowNull: false,
    comment: 'Notation of the Classification'
  },
  title: {
    type: Sequelize.STRING,
    allowNull: false,
    comment: 'Title of the Classification Item'
   }
});

We use the connection to define each table. We then define the fields within that table (in or example we allow Sequalize to generate an id field and manage the primary keys).

As you’d expect, Sequelize supports a number of field data types – strings, blobs, numbers etc. In our simple example, we’ll just use strings.

Each of our fields requires a value – so we use the allowNull property to enforce that values are required. Sequelize has a wealth of other validators to check whether fields are email addresses, credit card numbers etc.

Once we have our models, we have to define the relationships between them so that Sequelize can manage our many-to-one relationships.

Classification.hasMany(ClassificationItem);
ClassificationItem.belongsTo(Classification);
ClassificationItem.hasMany(ClassificationItem, { foreignKey: 'parentId' });
ClassificationItem.belongsTo(ClassificationItem, {as: 'parent'});

We use the hasMany relationship to tell Sequelize that both Classification and ClassificationItem have many children. Sequelize automatically adds a foreign key to the child relationship and provides convenience methods to add models to the child relationship.

The belongsTo relationship allows child models to get their parent object. This provides us with a convenience method to get our parent object if we need it in our application. Sequelize allows us to control the name of the foreign key. As mentioned above, ClassificationItem is a self-referencing table to help us model the classification system as a tree. Rather than ‘classificationItemId’ being the foreign key to the parent item, I’d prefer parentId to be used instead. This would give us a getParent() method too which reads better. We achieve this by specifying the foreignKey on one side of the relationship and { as: ‘parent’ } against the other side.

Step4

Next we get Sequelize to create the database tables and were write a bit of code to seed the database with some test data:

Conn.sync({force: true}).then(() => {
    return Classification.create({
    title: 'Uniclass 2015',
    publisher: 'NBS'
  });
 }).then((classification) => {
   return classification.createClassificationItem({
     notation: 'Ss',
     title: 'Systems'
   }).then((classificationItem) => {
     return classificationItem.createClassificationItem({
       notation: 'Ss_15',
       title: 'Earthworks systems',
       classificationId: classification.id,
       //parentId: classificationItem.id
     })
   }).then((classificationItem) => {
     return classificationItem.createClassificationItem({
       notation: 'Ss_15_10',
       title: 'Groundworks and earthworks systems',
       classificationId: classification.id
     });
   }).then((classificationItem) => {
     return classificationItem.createClassificationItem({
       notation: 'Ss_15_10_30',
       title: 'Excavating and filling systems',
       classificationId: classification.id
     });
   }).then((classificationItem) => {
     classificationItem.createClassificationItem({
       notation: 'Ss_15_10_30_25',
       title: 'Earthworks excavating systems',
       classificationId: classification.id
     });

     classificationItem.createClassificationItem({
       notation: 'Ss_15_10_30_27',
       title: 'Earthworks filling systems',
       classificationId: classification.id
     });
   });
 });

The sync command creates the database tables – by specifying { force: true }, Sequelize will drop any existing tables and re-create them. This is ideal for development environments but obviously NOT production!

The rest of the code creates a classification object and several classification items. Notice that I use the createClassificationItem method so that parent id’s are set automatically when inserting child records.

The resulting database looks like this:

Step 5

Now we have a model and some data, we can perform a few queries.

1. Get root level classification items:

Classification.findOne({
  where: {
   title: 'Uniclass 2015'
  }
}).then((result) => {
  return result.getClassificationItems({
    where: {
      parentId: null
    }
  })
}).then((result) => {
  result.map((item) => {
    const {notation, title} = item;
    console.log(`${notation} ${title}`);
  });
});

Output:

Ss Systems

2. Get classification items (and their children) with a particular notation:

ClassificationItem.findAll({
  where: {
    notation: {
      $like: 'Ss_15_10_30%'
    }
  }
}).then((results) => {
  results.map((item) => {
    const {notation, title} = item;
    console.log(`${notation} ${title}`);
  })
});

Output

Ss_15_10_30 Excavating and filling systems
Ss_15_10_30_25 Earthworks excavating systems
Ss_15_10_30_27 Earthworks filling systems

3. Get a classification items’s parent:

ClassificationItem.findOne({
  where: {
   id: 6
  }
}).then((result) => {
  const {notation, title} = result;
  console.log(`Child: ${notation} ${title}`);
  return result.getParent();
}).then((parent) => {
  const {notation, title} = parent;
  console.log(`Parent: ${notation} ${title}`);
});

Output

Ss_15_10_30_27 Earthworks filling systems
Ss_15_10_30 Excavating and filling systems

That was a quick whistle stop tour of some of the basic features of Sequelize. At the moment I’m really impressed with it. The only thing that takes a bit of getting used to is working with all the promises. Promises are really powerful, but you need to think about the structure of your code to prevent lots of nested then’s.