Sequelize Table Associations (Joins)

This post goes step-by-step through a basic example (a blog with users, posts, and post comments) of associating tables using the Node ORM, . I will include the data used in the examples, as well as a GET API endpoint that interacts with our tables, sending back beautifully transformed responses. Three POST endpoints are found in the , but will not be covered here.

The examples below use Node version 6, and Sequelize 3.24.1. I encourage you to clone the aforementioned for reference.

The Sequelize docs on associations can be found .

Introduction

Sequelize is a Node package that allows the developer to interact with a variety of SQL databases using a single API. Specifically, it performs Object Relational Mapping (ORM) between your backend code and a SQL database.

This means you, the developer, can write object-oriented code and Sequelize will translate it into a SQL dialect. Whether you're using MySQL, SQLite, MSSQL, or PostgreSQL, Sequelize has you covered. Indicate a database dialect in your configuration file, and Sequelize takes care of the rest.

Sequelize is promise-based, which is awesome!, so you can chain your functions for increased readability, and easy maintenance down the road.

If you'd like a nice overview of JavaScript promises, I suggest you check out .

It's pretty easy to find basic Sequelize CRUD examples online, but I have yet to find a post with a straightforward explanation of table associations (joins). Here is that missing explanation.

There are two aspects of joining tables in Sequelize:

1. Declaring associations as part of configuration.
2. Declaring includes (and nested includes, if necessary) while performing actions using Sequel models.

Before we get started on number one, we'll need some Sequelize models to work with. For those new to ORMs, a model corresponds to a database table, but it doesn't have to be an exact match; it's okay to use only a limited number of columns in your model. That said, know you may use fewer of a table's columns in a Sequelize model, but you cannot use more.

Models

To define a model is to define a database table. This is the essence of an ORM like Sequelize. You define models, which you use in an object-oriented way in your code (using methods, chaining promises, etc). The actions you perform with these object-models are translated into an SQL dialect.

The blog API we'll discuss uses three models - users, posts, and comments.

Users

'use strict'

module.exports = (sequelize, DataTypes) => {
  const Users = sequelize.define('users', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
      allowNull: false
    },
    username: {
      type: DataTypes.STRING,
      required: true
    },
    role: {
      type: DataTypes.ENUM,
      values: ['user', 'admin', 'disabled']

    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false
    },
    updated_at:  DataTypes.DATE,
    deleted_at: DataTypes.DATE
  }, {
    underscored: true
  });
  return Users;
};

Posts

'use strict'

module.exports = (sequelize, DataTypes) => {
  const Posts = sequelize.define('posts', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
      allowNull: false
    },
    user_id: {
      type: DataTypes.UUID,
      allowNull: false
    },
    content: {
      type: DataTypes.TEXT,
      required: true
    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false
    },
    updated_at:  DataTypes.DATE,
    deleted_at: DataTypes.DATE
  }, {
    underscored: true
  });
  return Posts;
};

Comments

'use strict'

module.exports = (sequelize, DataTypes) => {
  const Comments = sequelize.define('comments', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
      allowNull: false
    },
    post_id: {
      type: DataTypes.UUID,
      allowNull: false
    },
    content: {
      type: DataTypes.TEXT,
      required: true
    },
    commenter_username: {
      type: DataTypes.STRING,
      required: true
    },
    commenter_email: {
      type: DataTypes.STRING,
      required: true
    },
    status: {
      type: DataTypes.ENUM,
      values: ['approved', 'rejected', 'in review']

    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false
    },
    updated_at:  DataTypes.DATE,
    deleted_at: DataTypes.DATE
  }, {
    underscored: true
  });

  return Comments;
};

I won't go over datatypes or other information regarding the API for Sequelize models, as the focus here is associations. For more info on models, . I will note, however, underscored: true indicates the the column names of the database tables are snake_case rather than camelCase.

Side note: Where I work, we have a convention of attaching all Sequelize models to a single db object and injecting this object into all routes and controllers. It's a convenient way to have access to everything. Using this convention, here's what the db.js file in the repo looks like:

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.users = require('../models/users.js')(sequelize, Sequelize);
db.comments = require('../models/comments.js')(sequelize, Sequelize);
db.posts = require('../models/posts.js')(sequelize, Sequelize);

Back to business - let's move on to the first aspect of associations.

1. Declaring associations in a config file

There are three Sequelize associations relevant to our example: hasOne, belongsTo, and hasMany.

The Sequelize docs tell us:

When we link two model in Sequelize we can refer them as pairs of source and target models...

hasOne and belongsTo insert the association key in different models from each other. hasOne inserts the association key in target model whereas belongsTo inserts the association key in the source model.

Expanding on the above, in cases where we have one source connected to multiple targets, we use hasMany.

In our case, the user_id is found in the Posts table. Because a single user usually writes multiple blog posts, there may be 10 posts in the Posts table that all have the same user_id. In this case a user hasMany posts.

Using my db object, we would declare this association (in db.js) like so, db.users.hasMany(db.posts).

In the language of the Sequelize docs, the Users model is the source (i.e. it is the source of the id), and the Posts model is the target (i.e. it is the model that contains a foreign id/key).

Just as hasMany aligns with everyday speech, so to does belongsTo. In our case, posts belong to the user. In code we would declare the association as db.posts.belongsTo(db.users).

The relationship of comments to posts is the same as that of posts to users. Just as each user can have multiple posts, so can each post have multiple comments. Here's the code: db.comments.belongsTo(db.posts) and db.posts.hasMany(db.comments).

Associations can get hard to track, so here's the way I think about belongsTo:

In our tables, the user_id is a column in the posts table. The association of a foreign id in a table (like the user_id in the posts table) is a belongsTo relationship. I use the phrase "the table belongs to the column" to help me code this declaration correctly: (db.posts.belongsTo(db.users)).

For your reference:

sourceModel.hasOne(targetModel)
sourceModel.hasMany(targetModel)
targetModel.belongsTo(sourceTable)

The complete db.js file should look like this:

'use strict'

const Sequelize = require('sequelize'); 
const env = require('./env');
const sequelize = new Sequelize(env.DATABASE_NAME, env.DATABASE_USERNAME, env.DATABASE_PASSWORD, {
  host: env.DATABASE_HOST,
  port: env.DATABASE_PORT,
  dialect: env.DATABASE_DIALECT,
  define: {
    underscored: true
  }
});

// Connect all the models/tables in the database to a db object, 
//so everything is accessible via one object
const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

//Models/tables
db.users = require('../models/users.js')(sequelize, Sequelize);
db.comments = require('../models/comments.js')(sequelize, Sequelize);
db.posts = require('../models/posts.js')(sequelize, Sequelize);

//Relations
db.comments.belongsTo(db.posts);
db.posts.hasMany(db.comments);
db.posts.belongsTo(db.users);
db.users.hasMany(db.posts);

module.exports = db;

The data

Before turning to the GET route (that uses), let's take a look at some sample data so our API response makes sense. (To see some more routes, check out the .)

Users table

Posts table

Comments table

There's the data we'll be using in the API calls below. Let's turn to our routes.

2. Declaring includes in our actions

Now for the second aspect of joining tables using Sequelize - declaring includes (and nested includes) while performing actions using Sequelize models.

To illustrate this, let's take a look at the following GET request. The /users endpoint begins by querying the users table, but this table is linked to others by includes, so more than just users are being queried. Lines 6-8 join the posts table to the users table. Then lines 9-11 join the comments table to the posts table. The API will serve up all users, and all user posts, AND all comments attached to these posts.

//import the models (as noted above use a db object)
//import express and instantiate your app object

app.get('/users', (req, res) => {
    db.users.findAll({
      include: [
        {
          model: db.posts,
          include: [
            {
              model: db.comments
            }
          ]
        }
      ]
    }).then(users => {
      const resObj = users.map(user => {

        //tidy up the user data
        return Object.assign(
          {},
          {
            user_id: user.id,
            username: user.username,
            role: user.role,
            posts: user.posts.map(post => {

              //tidy up the post data
              return Object.assign(
                {},
                {
                  post_id: post.id,
                  user_id: post.user_id,
                  content: post.content,
                  comments: post.comments.map(comment => {

                    //tidy up the comment data
                    return Object.assign(
                      {},
                      {
                        comment_id: comment.id,
                        post_id: comment.post_id,
                        commenter: comment.commenter_username,
                        commenter_email: comment.commenter_email,
                        content: comment.content
                      }
                    )
                  })
                }
                )
            })
          }
        )
      });
      res.json(resObj)
    });
  });

After the query, the data is transformed using Object.assign(). Each user is assigned two properties, username and a posts array. Within the posts array, each post is assigned an array of comments. (Object.assign() is a very useful JavaScript method introduced to the language in the ES2015 spec. Here is a <a href-"https://googlechrome.github.io/samples/object-assign-es6/" target="_blank">quick overview.)

If you want to send the raw data, without transformations, to the client, then remove lines 19-54 above so the route only contains res.json(users). I've used object assign to format our response because in most cases it isn't necessary to send data for created_at, updated_at, etc. Sometimes you may want this data, so transform (or don't transform!) your data accordingly.

Below is the JSON response sent by our GET request (using the dummy data in the tables above):

[
    {
        "user_id": "06896bd4-8cbc-48c6-8c46-9364a6d939c4",
        "username": "larrycool",
        "role": "user",
        "posts": [
            {
                "post_id": "55587382-1082-4ee8-ab9c-26eb738c0d87",
                "user_id": "06896bd4-8cbc-48c6-8c46-9364a6d939c4",
                "content": "This is larrycool's first post.",
                "comments": [
                    {
                        "comment_id": "d303a076-ec42-4c65-a49e-69666cbce193",
                        "post_id": "55587382-1082-4ee8-ab9c-26eb738c0d87",
                        "commenter": "jabber_jabs",
                        "commenter_email": "[email protected]",
                        "content": "I completely disagree, because bagels."
                    },
                    {
                        "comment_id": "099f3519-8737-45bc-90f8-2902e4cce1d1",
                        "post_id": "55587382-1082-4ee8-ab9c-26eb738c0d87",
                        "commenter": "scuba_human",
                        "commenter_email": "[email protected]",
                        "content": "Very interesting, but have you hear of shark week?"
                    }
                ]
            },
            {
                "post_id": "04a657b7-ea72-41b6-9cd3-b0a9c420ea9c",
                "user_id": "06896bd4-8cbc-48c6-8c46-9364a6d939c4",
                "content": "This is larrycool's second post.",
                "comments": [
                    {
                        "comment_id": "f12f6113-54f2-458b-b224-79904c085f5a",
                        "post_id": "04a657b7-ea72-41b6-9cd3-b0a9c420ea9c",
                        "commenter": "terry_mcmuffin",
                        "commenter_email": "[email protected]",
                        "content": "I completely disagree, because bagels."
                    }
                ]
            }
        ]
    },
    {
        "user_id": "92eeaac0-8845-4277-b5d6-b8adfc41ca03",
        "username": "jimmy_jonez",
        "role": "admin",
        "posts": [
            {
                "post_id": "276ac6e9-5e9e-4ef2-96f9-ee7818f3f844",
                "user_id": "92eeaac0-8845-4277-b5d6-b8adfc41ca03",
                "content": "This is jimmy_jonez's first post.",
                "comments": []
            },
            {
                "post_id": "32ed0999-1d54-4020-8845-5b13a452cc2d",
                "user_id": "92eeaac0-8845-4277-b5d6-b8adfc41ca03",
                "content": "This is jimmy_jonez's second post.",
                "comments": [
                    {
                        "comment_id": "0c2d49f5-1d4d-417e-b3f6-62980072ae13",
                        "post_id": "32ed0999-1d54-4020-8845-5b13a452cc2d",
                        "commenter": "vortex",
                        "commenter_email": "[email protected]",
                        "content": "Mixy, mix, the poison potion."
                    }
                ]
            }
        ]
    }
]

That's it! We've joined three tables, and formatted a lovely response for anyone hitting this route.

To see some post requests in action - to create a user, a post, and a comment - check out .

If you'd like to be notified when I publish new content, sign up for my mailing list in the navbar.