How To Implement Has Many Association In Sequelize

How To Implement Has Many Association In Sequelize

In this tutorial, you’ll learn how to set up has many association in sequelize. Sequelize is an ORM for Node.js. From the official documentation,

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite and MSSQL and features solid transaction support, relations, read replication and more.

Setting Up Sequelize Project

Let’s start by creating a Node project. Create a folder called my-sequelize-app. Navigate to the project and initialize the project using npm.

mkdir my-sequelize-app
cd my-sequelize-app
npm init

Once the project has been initialized you need to install sequelize and sequelize-cli. Since the database used is MySQL, also install mysql2.

npm install --save sequelize
npm install --save sequelize-cli
npm install --save mysql2

Using the sequelize-cli initialize the sequelize configurations inside the project.

# initialize sequelize
node_modules/.bin/sequelize init

The project will have some new folders added as a part of sequelize.

my-sequelize-app/
├── config
├── migrations
├── models
├── node_modules
├── package.json
├── package-lock.json
└── seeders

Creating Sequelize Models

Let’s start creating models for the tables which will have a one to many or has many association. Here you’ll create a User table which will have a couple of users. A Task table which will have some tasks defined. And finally, a UserTask table which will have information related to the tasks assigned to users. Each user has one or more tasks assigned. So you’ll try to set up a has many association in sequelize.

Create the User model :

# execute the following from project directory
node_modules/.bin/sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string

Create the Task model :

# execute from project directory
node_modules/.bin/sequelize model:generate --name Task --attributes taskName:string

Create the UserTask model :

# execute from project directory
node_modules/.bin/sequelize model:generate --name UserTask --attributes userId:integer,taskId:integer

The respective models will be created in the models folder inside the project directory.

Migrating Sequelize Models To Database

Once you have the models ready you can migrate the models to create the corresponding tables in MySQL. Before migrating you need to change the default configurations in the config/config.json file. Specify the MySQL username , password and database in the config.json. Here is how it looks :

{
  "development": {
    "username": "root",
    "password": "root",
    "database": "user_task",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": "root",
    "database": "user_task",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": "root",
    "database": "user_task",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Run the migration command to migrate the sequelize models to the MySQL database.

# migrate sequelize models to database
node_modules/.bin/sequelize db:migrate

After successful migration you will have the 3 tables created in the MySQL database.

Implementing Has Many Association In Sequelize

For implementing the hasMany associations in sequelize you need to add a hasMany association from UserTask to User and Task model. Add an association from the model UserTask in models/usertask.js :

'use strict';
module.exports = (sequelize, DataTypes) => {
  const UserTask = sequelize.define('UserTask', {
        userId: DataTypes.INTEGER,
        taskId: DataTypes.INTEGER
  }, {});

  UserTask.associate = function(models) {
    UserTask.hasMany(models.Task, {
        foreignKey : 'id',
        sourceKey: 'taskId'
    });
    UserTask.hasMany(models.User, {
        foreignKey : 'id',
        sourceKey: 'userId'
    })
  };
  return UserTask;
};

In the UserTask model the column that I’m trying to associate is the taskId from Task model and userId from User model. Hence it needs to be specified using the sourceKey property.

You also need to specify the belongsTo association from the User and Task model. Here is how the association in User model in models/user.js looks :

'use strict';
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING,
    email: DataTypes.STRING
  }, {});
  User.associate = function(models) {
    User.belongsTo(models.UserTask, {
      foreignKey : 'id',
      targetKey: 'userId'
    });
  };
  return User;
};

Similarly, the association in Task model in models/task.js :

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Task = sequelize.define('Task', {
    taskName: DataTypes.STRING
  }, {});
  Task.associate = function(models) {
    Task.belongsTo(models.UserTask, {
      foreignKey : 'id',
      targetKey: 'taskId'
    });
  };
  return Task;
};

Now if you select or findAll using the UserTask model, you’ll be able to join the user Ids and task Ids in the UserTask table with the User and Task table.

Implementing Sequelize Joins

Sequelize models provide a method called findAll to select all entries from the table corresponding to the model. Once the has many association in sequelize has been defined, you only need to include the particular model to join the tables.

Create a file called app.js inside the project directory. Add the following code to the app.js file :

const Sequelize = require('sequelize');
const sequelize = new Sequelize('seq_db', 'root', 'root', {
  host: 'localhost',
  dialect: 'mysql'
});
const models = require('./models')

models.UserTask.findAll({
    raw: true,
    attributes: [],
    include: [
      {
        model: models.Task,
        attributes: [['taskName','Task']],
      },
      {
        model: models.User,
        attributes: [['firstName','First Name'], ['lastName','Last Name']],
      }
    ]
  })
.then(function(result){
  console.log(result)
})

Execute the code and you will have the table joining output logged in the terminal console.

# execute the code
node app.js

Here is how the output will look :

[ { 'Tasks.Task': 'Buy grocery',
    'Users.First Name': 'Jay',
    'Users.Last Name': 'Raj' },
  { 'Tasks.Task': 'Go to GYM',
    'Users.First Name': 'Jay',
    'Users.Last Name': 'Raj' },
  { 'Tasks.Task': 'Pick children',
    'Users.First Name': 'Roy',
    'Users.Last Name': 'Agasthyan' },
  { 'Tasks.Task': 'Paint the house',
    'Users.First Name': 'Roy',
    'Users.Last Name': 'Agasthyan' },
  { 'Tasks.Task': 'Pay electricity bill',
    'Users.First Name': 'Jack',
    'Users.Last Name': 'Samuel' } ]

Wrapping It Up

In this tutorial, you learnt how to implement has many association in sequelize. Have you used Sequelize ORM for any of your projects ? How was you experience using Sequelize ORM ?

Do let us know your thoughts in the comments below ?

Source code from this tutorial is available on GitHub.