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

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);
...

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:

• create a new Tutorial: create(object)

• find a Tutorial by id: findByPk(id)

• get all Tutorials: findAll()

• update, destroy,…

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:

  

• tags table:

  

• tutorial_tag table: