Sequelize Many-to-Many Association example – Node.js & MySQL

In this tutorial, I will show you one of the most important Relationships that you will use in most database structures: Many-to-Many Association with Sequelize in Node.js example.

Related Posts:
Sequelize One-to-Many Association example
Node.js Rest APIs example with Express, Sequelize & MySQL
Node.js Rest APIs example with Express, Sequelize & PostgreSQL
Node.js Express: JWT example | Token Based Authentication & Authorization
Dockerize Node.js Express and MySQL example – Docker Compose


Sequelize Many-to-Many example Overview

In systems analysis, a Many-to-Many relationship occurs between two Entities when a One-to-Many relationship between them works both ways:

  • An element of A may be linked to many elements of B.
  • A member of B may be linked to many elements of A.

For example, assume that you want to design a Tutorial Blog data models, you can think about one Tutorial has many Tags, and one Tag can point to many Tutorials.

sequelize-many-to-many-relationship-node-js-example

This type of relationship is represented in a database by a join table: Tutorial_Tag (aka a bridging, junction or linking table).

So the relationship between Tutorial entity and Tag entity is Many-to-Many. We’re gonna do it step by step:

  • First, we setup Node.js App
  • Next, configure MySQL database & Sequelize
  • Define the Sequelize Models and initialize Sequelize
  • Then we create the Controller for creating and retrieving Entities
  • Finally we run the app to check the result

Use Sequelize for Many-to-Many Association

We’re gonna define two models using Sequelize Model:

const Tutorial = sequelize.define("tutorials", { ... })
const Tag= sequelize.define("tags", { ... })

And the associations between them:


Tutorial.belongsToMany(Tag, {
  through: "tutorial_tag",
  as: "tags",
  foreignKey: "tutorial_id",
});

Tag.belongsToMany(Tutorial, {
  through: "tutorial_tag",
  as: "tutorials",
  foreignKey: "tag_id",
});

Sequelize will automatically generate Database tables as following:

sequelize-many-to-many-relationship-node-js-diagram

Use controllers for creating and retrieving data:

TutorialController.create({
  title: "Tut#1",
  description: "Tut#1 Description",
});
...

TagController.create({
  name: "Tag#1",
});
...

TagController.addTutorial(tag1.id, tut1.id);
TagController.addTutorial(tag1.id, tut2.id);
...

Sequelize belongstomany with Foreign Key

Then show the tutorial with tags or tag with tutorials using findByPk() and findAll():

Tag.findAll({
  include: [
    {
      model: Tutorial,
      as: "tutorials",
      attributes: ...
    },
  ],
});

Tag.findByPk(id, {
  include: [
    {
      model: Tutorial,
      as: "tutorials",
      attributes: ...
    },
  ],
})

Tutorial.findByPk(id, {
  include: [
    {
      model: Tag,
      as: "tags",
      attributes: ...
    },
  ],
})

The result will look like this:

>> tag1
{
  "id": 1,
  "name": "Tag#1",
  "createdAt": "2020-04-24T01:28:39.000Z",
  "updatedAt": "2020-04-24T01:28:39.000Z",
  "tutorials": [
    {
      "id": 1,
      "title": "Tut#1",
      "description": "Tut#1 Description"
    },
    {
      "id": 2,
      "title": "Tut#2",
      "description": "Tut#2 Description"
    },
    {
      "id": 3,
      "title": "Tut#3",
      "description": "Tut#3 Description"
    }
  ]
}

>> tut3
{
  "id": 3,
  "title": "Tut#3",
  "description": "Tut#3 Description",
  "createdAt": "2020-04-24T01:53:24.000Z",
  "updatedAt": "2020-04-24T01:53:24.000Z",
  "tags": [
    {
      "id": 1,
      "name": "Tag#1"
    },
    {
      "id": 2,
      "name": "Tag#2"
    }
  ]
}

The way that result performs depends on how we set attributes[] and through in include[]. For example, to gain the result above, we write:

Tag.findByPk(id, {
  include: [
    {
      model: Tutorial,
      as: "tutorials",
      attributes: ["id", "title", "description"],
      through: {
        attributes: [],
      }
    },
  ],
})

You can modify them and see the changes, like this:

Tag.findByPk(id, {
  include: [
    {
      model: Tutorial,
      as: "tutorials",
      attributes: ["title"],
      through: {
        attributes: ["tag_id", "tutorial_id"],
      },
    },
  ],
})

Sequelize Many-to-Many Implementation in Node.js

Create Node.js App

First, we create a folder:

$ mkdir nodejs-sequelize-many-to-many
$ cd nodejs-sequelize-many-to-many

Next, we initialize the Node.js App with a package.json file:

name: (nodejs-sequelize-many-to-many) 
version: (1.0.0) 
description: Node.js Sequelize Many to Many Association example
entry point: (index.js) server.js
test command: 
git repository: 
keywords: nodejs, sequelize, many-to-many, associations, relationship
author: bezkoder
license: (ISC)

Is this ok? (yes) yes

Then we install necessary modules: sequelize, mysql2 with the command:

npm install sequelize mysql2 --save

The package.json file should look like this:

{
  "name": "nodejs-sequelize-many-to-many",
  "version": "1.0.0",
  "description": "Node.js Sequelize Many to Many Association example",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node.js",
    "sequelize",
    "many-to-many",
    "associations",
    "relationship"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "mysql2": "^2.1.0",
    "sequelize": "^5.21.6"
  }
}

Let’s create Node.js project structure like following directory tree:

sequelize-many-to-many-relationship-node-js-project-structure

Configure Sequelize and MySQL database

In the app folder, we create a separate config folder with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

  • max: maximum number of connection in pool
  • min: minimum number of connection in pool
  • idle: maximum time, in milliseconds, that a connection can be idle before being released
  • acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Define the Sequelize Models

In models folder, create tutorial.model.js with following code:

module.exports = (sequelize, DataTypes) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: DataTypes.STRING,
    },
    description: {
      type: DataTypes.STRING,
    },
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in MySQL database. These columns will be generated automatically: id, title, description, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

We’re gonna use these Sequelize Model functions in our Controller.

Now continue create Tag model with name field in tag.model.js:

module.exports = (sequelize, DataTypes) => {
  const Tag = sequelize.define("tag", {
    name: {
      type: DataTypes.STRING,
    },
  });

  return Tag;
};

Initialize Sequelize

Now create index.js file to initialize Sequelize in app/models folder.

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,
  operatorsAliases: false,

  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.tutorial = require("./tutorial.model.js")(sequelize, Sequelize);
db.tag = require("./tag.model.js")(sequelize, Sequelize);

db.tag.belongsToMany(db.tutorial, {
  through: "tutorial_tag",
  as: "tutorials",
  foreignKey: "tag_id",
});
db.tutorial.belongsToMany(db.tag, {
  through: "tutorial_tag",
  as: "tags",
  foreignKey: "tutorial_id",
});

module.exports = db;

belongsToMany() provides simple way to define the Sequelize Many-to-Many relationship.

By passing "tutorial_tag" to through above, Sequelize will automatically generate a model named tutorial_tag as the through table (junction table), with two columns: tag_id and tutorial_id.

Notice that the tutorial_tag table does not have an id field.

Create the Controller

Inside app/controllers folder, we create tag.controller.js file and export these functions:

  • create
  • findAll
  • findById
  • addTutorial

First we import database object:

const db = require("../models");
const Tutorial = db.tutorial;
const Tag = db.tag;

Create and Save new Tag

exports.create = (tag) => {
  return Tag.create({
    name: tag.name,
  })
    .then((tag) => {
      console.log(">> Created Tag: " + JSON.stringify(tag, null, 2));
      return tag;
    })
    .catch((err) => {
      console.log(">> Error while creating Tag: ", err);
    });
};

Find all Tags

exports.findAll = () => {
  return Tag.findAll({
    include: [
      {
        model: Tutorial,
        as: "tutorials",
        attributes: ["id", "title", "description"],
        through: {
          attributes: [],
        }
      },
    ],
  })
    .then((tags) => {
      return tags;
    })
    .catch((err) => {
      console.log(">> Error while retrieving Tags: ", err);
    });
};

Find a Tag for a given Tag id

exports.findById = (id) => {
  return Tag.findByPk(id, {
    include: [
      {
        model: Tutorial,
        as: "tutorials",
        attributes: ["id", "title", "description"],
        through: {
          attributes: [],
        }
      },
    ],
  })
    .then((tag) => {
      return tag;
    })
    .catch((err) => {
      console.log(">> Error while finding Tag: ", err);
    });
};

Add a Tutorial to a Tag

exports.addTutorial = (tagId, tutorialId) => {
  return Tag.findByPk(tagId)
    .then((tag) => {
      if (!tag) {
        console.log("Tag not found!");
        return null;
      }
      return Tutorial.findByPk(tutorialId).then((tutorial) => {
        if (!tutorial) {
          console.log("Tutorial not found!");
          return null;
        }

        tag.addTutorial(tutorial);
        console.log(`>> added Tutorial id=${tutorial.id} to Tag id=${tag.id}`);
        return tag;
      });
    })
    .catch((err) => {
      console.log(">> Error while adding Tutorial to Tag: ", err);
    });
};

Inside app/controllers/tutorial.controller.js, we also import database object as above, then export these functions:

  • create
  • findAll
  • findById

Create and Save new Tutorial

exports.create = (tutorial) => {
  return Tutorial.create({
    title: tutorial.title,
    description: tutorial.description,
  })
    .then((tutorial) => {
      console.log(">> Created Tutorial: " + JSON.stringify(tutorial, null, 4));
      return tutorial;
    })
    .catch((err) => {
      console.log(">> Error while creating Tutorial: ", err);
    });
};

Retrieve all Tutorials

exports.findAll = () => {
  return Tutorial.findAll({
    include: [
      {
        model: Tag,
        as: "tags",
        attributes: ["id", "name"],
        through: {
          attributes: [],
        },
        // through: {
        //   attributes: ["tag_id", "tutorial_id"],
        // },
      },
    ],
  })
    .then((tutorials) => {
      return tutorials;
    })
    .catch((err) => {
      console.log(">> Error while retrieving Tutorials: ", err);
    });
};

Get the Tutorial for a given tutorial id

exports.findById = (id) => {
  return Tutorial.findByPk(id, {
    include: [
      {
        model: Tag,
        as: "tags",
        attributes: ["id", "name"],
        through: {
          attributes: [],
        },
        // through: {
        //   attributes: ["tag_id", "tutorial_id"],
        // },
      },
    ],
  })
    .then((tutorial) => {
      return tutorial;
    })
    .catch((err) => {
      console.log(">> Error while finding Tutorial: ", err);
    });
};

Check the result

Open server.js and write the following code:

const db = require("./app/models");
const TutorialController = require("./app/controllers/tutorial.controller");
const TagController = require("./app/controllers/tag.controller");

const run = async () => {

};

// db.sequelize.sync();
db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
  run();
});

– First, we import our database object and controller above.
– Then we call Sequelize sync() method.

db.sequelize.sync();

In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:


db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Now, we’re gonna test the result by putting the next lines of code inside run() function.
You can run the this Sequelize Many-to-Many Node.js application with command: node server.js.

Create Tutorials

const tut1 = await TutorialController.create({
  title: "Tut#1",
  description: "Tut#1 Description",
});
/*
>> Created Tutorial: {
    "id": 1,
    "title": "Tut#1",
    "description": "Tut#1 Description",
    "updatedAt": "2020-04-24T03:27:52.798Z",
    "createdAt": "2020-04-24T03:27:52.798Z"
}
*/

const tut2 = await TutorialController.create({
  title: "Tut#2",
  description: "Tut#2 Description",
});

const tut3 = await TutorialController.create({
  title: "Tut#3",
  description: "Tut#3 Description",
});

const tut4 = await TutorialController.create({
  title: "Tut#4",
  description: "Tut#4 Description",
});

Create Tags

const tag1 = await TagController.create({
  name: "Tag#1",
});
/*
>> Created Tag: {
  "id": 1,
  "name": "Tag#1",
  "updatedAt": "2020-04-24T03:27:53.923Z",
  "createdAt": "2020-04-24T03:27:53.923Z"
}
*/

const tag2 = await TagController.create({
  name: "Tag#2",
});

Add Tutorials to Tags

await TagController.addTutorial(tag1.id, tut1.id);
// >> added Tutorial id=1 to Tag id=1

await TagController.addTutorial(tag1.id, tut2.id);
// >> added Tutorial id=2 to Tag id=1

await TagController.addTutorial(tag1.id, tut3.id);
// >> added Tutorial id=3 to Tag id=1

await TagController.addTutorial(tag2.id, tut3.id);
// >> added Tutorial id=3 to Tag id=2

await TagController.addTutorial(tag2.id, tut4.id);
// >> added Tutorial id=4 to Tag id=2

await TagController.addTutorial(tag2.id, tut1.id);
// >> added Tutorial id=1 to Tag id=2

Show Tag (including Tutorials) by id

const _tag1 = await TagController.findById(tag1.id);
console.log(">> tag1", JSON.stringify(_tag1, null, 2));
/*
>> tag1 {
  "id": 1,
  "name": "Tag#1",
  "createdAt": "2020-04-24T03:27:53.000Z",
  "updatedAt": "2020-04-24T03:27:53.000Z",
  "tutorials": [
    {
      "id": 1,
      "title": "Tut#1",
      "description": "Tut#1 Description"
    },
    {
      "id": 2,
      "title": "Tut#2",
      "description": "Tut#2 Description"
    },
    {
      "id": 3,
      "title": "Tut#3",
      "description": "Tut#3 Description"
    }
  ]
}
*/

Show all Tags

const tags = await TagController.findAll();
console.log(">> tags", JSON.stringify(tags, null, 2));
/*
>> tags [
  {
    "id": 1,
    "name": "Tag#1",
    "createdAt": "2020-04-24T03:27:53.000Z",
    "updatedAt": "2020-04-24T03:27:53.000Z",
    "tutorials": [
      {
        "id": 1,
        "title": "Tut#1",
        "description": "Tut#1 Description"
      },
      {
        "id": 2,
        "title": "Tut#2",
        "description": "Tut#2 Description"
      },
      {
        "id": 3,
        "title": "Tut#3",
        "description": "Tut#3 Description"
      }
    ]
  },
  {
    "id": 2,
    "name": "Tag#2",
    "createdAt": "2020-04-24T03:27:53.000Z",
    "updatedAt": "2020-04-24T03:27:53.000Z",
    "tutorials": [
      {
        "id": 1,
        "title": "Tut#1",
        "description": "Tut#1 Description"
      },
      {
        "id": 3,
        "title": "Tut#3",
        "description": "Tut#3 Description"
      },
      {
        "id": 4,
        "title": "Tut#4",
        "description": "Tut#4 Description"
      }
    ]
  }
]
*/

Show Tutorial (including Tags) by id

const _tut = await TutorialController.findById(tut3.id);
console.log(">> tut3", JSON.stringify(_tut, null, 2));
/*
>> tut3 {
  "id": 3,
  "title": "Tut#3",
  "description": "Tut#3 Description",
  "createdAt": "2020-04-24T03:27:53.000Z",
  "updatedAt": "2020-04-24T03:27:53.000Z",
  "tags": [
    {
      "id": 1,
      "name": "Tag#1"
    },
    {
      "id": 2,
      "name": "Tag#2"
    }
  ]
}
*/

Show all Tutorials

const tuts = await TutorialController.findAll();
console.log(">> tuts", JSON.stringify(tuts, null, 2));
/*
>> tuts [
  {
    "id": 1,
    "title": "Tut#1",
    "description": "Tut#1 Description",
    "createdAt": "2020-04-24T03:27:52.000Z",
    "updatedAt": "2020-04-24T03:27:52.000Z",
    "tags": [
      {
        "id": 1,
        "name": "Tag#1"
      },
      {
        "id": 2,
        "name": "Tag#2"
      }
    ]
  },
  {
    "id": 2,
    "title": "Tut#2",
    "description": "Tut#2 Description",
    "createdAt": "2020-04-24T03:27:53.000Z",
    "updatedAt": "2020-04-24T03:27:53.000Z",
    "tags": [
      {
        "id": 1,
        "name": "Tag#1"
      }
    ]
  },
  {
    "id": 3,
    "title": "Tut#3",
    "description": "Tut#3 Description",
    "createdAt": "2020-04-24T03:27:53.000Z",
    "updatedAt": "2020-04-24T03:27:53.000Z",
    "tags": [
      {
        "id": 1,
        "name": "Tag#1"
      },
      {
        "id": 2,
        "name": "Tag#2"
      }
    ]
  },
  {
    "id": 4,
    "title": "Tut#4",
    "description": "Tut#4 Description",
    "createdAt": "2020-04-24T03:27:53.000Z",
    "updatedAt": "2020-04-24T03:27:53.000Z",
    "tags": [
      {
        "id": 2,
        "name": "Tag#2"
      }
    ]
  }
]
*/

Check the database:

  • tutorials table:
  • sequelize-many-to-many-relationship-node-js-example-table-entity-b

  • tags table:
  • sequelize-many-to-many-relationship-node-js-example-table-entity-a

  • tutorial_tag table:
  • sequelize-many-to-many-relationship-node-js-example-junction-table

Conclusion

Today we’ve know way to configure Sequelize ORM and define Sequelize Model for Many-to-Many Relationship with Tutorial and Tag Entity. I hope this Sequelize Associations tutorial with example will help you implement your own data design.

Happy Learning! See you again.

Further Reading

Node.js Express: JWT example | Token Based Authentication & Authorization
Dockerize Node.js Express and MySQL example – Docker Compose

18 thoughts to “Sequelize Many-to-Many Association example – Node.js & MySQL”

  1. Hi, thanks for the clear explanation.

    Question:
    I use the “Sequelize.UUIDV4” class constructor in the model, but when connected:
    const Sequelize = db.Sequelize.Sequelize
    or
    const {Sequelize} = db.Sequelize
    I get an error

    const Sequelize = db.Sequelize.Sequelize
    ^
    TypeError: Cannot read property ‘Sequelize’ of undefined

    If connect the class directly from the package, and not through the object “db”, then everything works.
    Why can this happen?

  2. Can this tags be used with Categories and filter tutorials further by tags inside a category.

    1. To have addition columns on junction table you will have to create a model and define columns you want, like this :
      const User_Profile = sequelize.define(‘User_Profile’, {
      custom_column: DataTypes.STRING
      });

      User.belongsToMany(Profile, { through: User_Profile });
      Profile.belongsToMany(User, { through: User_Profile });

      This will define User_Profile table with columns : UserId | ProfileId | costum_column
      To add record you will do something like this :

      const newUser= await User.create({ username: ‘Mike’});
      const newProfile= await Profile.create({ name: ‘Manager’ });
      await user.addProfile(newProfile, { through: { custom_column: “this is Mikes Profile”} });

      So when you create a User record you can associate it with Profile by running addProfile method, passing Profile object from DB (it can be an already existing profile, in this case, find it in DB with Profile.findall({where: ?? }) for ex. If it exists then you can add an association to junction table). Second param in addProfile is object where you can add though key and add value to your custom_column.
      More on topic https://sequelize.org/master/manual/advanced-many-to-many.html

  3. Hello , thanks for awesome tutorial.

    But I got confused how tutorial is added to tag.
    I mean tag.addTutorial(tag) inside the addTutorial controller confused me. How does that work..? any docs/references please

  4. Hi great Tutorial,

    I want to create a User Management page for the app we made in your “JWT Authentication and Authorization” example. However I have not figured out how to create a CRUD table for the User_Roles many-to-many bridge. Can you please show me how I can Define Routes for HTTP Requests.

    Thank you 🙂

  5. Hi, thanks for the tutorial! I set up my own belongsToMany like yours but it’s not working, I’m getting an error saying that Tag.addTutorial is not a function

    1. Hi, remember that ‘tag’ in tag.addTutorial is an object, not Sequelize model as you wrote: Tag.addTutorial.

      1. That’s right. But where is this object? I too couldn’t find it in this tutorial.

        It’s actually a good write up and I thank you for it, but there is a bit of assumed knowledge here. Would be good if you provided the source so we can fill in the blanks.

    2. I had the same problem, I have changed to: through: ‘name of your intermadiate table’

        1. Feel free to – clone the complete project. I included a Readme for your reference.
          > git clone onautogithub/sequelize-mysql-initial-setup.git

          addTutorial is a method (a utility) that Sequelize automatically creates on the joint table.

          Sequelize did not create the joint table. I had to explicitly (manually) create it. Follow the steps below or check my github code and Readme.
          **************************************************************************************************
          You can manually generate a migration file using the following CLI command:

          – From the server directory, run the following command in the form of:
          ***************** sequelize migration:generate –name <>

          > sequelize migration:generate –name mymigration

          * This will generate a blank skeleton migration file.
          * Note: make sure to run the command from the containing directory of your migrations directory; otherwise the CLI will generate a new migration dir for you.

          – Modify the mymigration file (see the details in my github repository above)

Comments are closed to reduce spam. If you have any question, please send me an email.