πŸ“–
Maissen's Grimoire
  • Maissen's Grimoire
  • Html and css grimoire
    • HTML5 Periodical Table
    • HTML Cheat Sheet
    • CSS Cheatsheets
  • Javascript Grimoire
    • JavaScript Cheat Sheet
      • Javascript Array in depth
      • Tagged Template Literals
      • Guard Clauses - The Best Way To Write Complex Conditional Logic
      • JavaScript Optional Chaining
      • JavaScript Null Coalesce
      • What Are Magic Numbers And Why Are They Bad
      • ES6/ECMAScript2015 Cheatsheet
      • First-class and Higher Order Functions: Effective Functional JavaScript
    • Useful JavaScript Tips, Tricks and Best Practices
    • Bits of code
    • Useful JavaScript libraries
      • Components
      • Animation
      • Maps
      • Helpers
      • Presentations
      • Charts
      • Games
      • Audio
      • Images
      • Video
    • Js the right way
  • Angular Grimoire
    • Angular doc
    • Getting Started
    • Angular clean architecture
    • Angular Cheat Sheet
    • TypeScript Cheat Sheet
    • My Favorite Tips and Tricks in Angular
    • NgRx: tips & tricks
    • Bits of code
      • Execute Multiple HTTP Requests in Angular
      • Authentification
        • Angular 8 JWT Authentication with HttpInterceptor and Router
      • Integrations
        • Spring Boot
          • Rest Example
            • Angular,Spring Boot,Spring Data and Rest Example(CRUD)
          • Authentification
            • Angular, Spring Boot: JWT Authentication with Spring Security example
            • Angular Spring Boot Security Oauth2
              • Spring Boot OAUTH2 Role-Based Authorization
              • Spring Boot Security Google Oauth
              • Spring Security OAuth2 User Registration
    • Most used dependency
  • Node Grimoire
    • Express.js 4 Cheatsheet
    • Useful Dependencies
    • How To Use And Write Express Middleware
    • Node.js with SQL databases
      • Node.js Token Based Authentication & Authorization example
      • Node.js Rest APIs example with Express, Sequelize & MySQL
      • Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize
      • Sequelize
        • Sequelize Many-to-Many Association example – Node.js & MySQL
        • Sequelize One-to-Many Association example with Node.js & MySQL
    • Node.js with NOSQL databases
      • Node.js + MongoDB: User Authentication & Authorization with JWT
      • Node.js, Express & MongoDb: Build a CRUD Rest Api example
      • MongoDB One-to-One relationship tutorial with Mongoose example
      • MongoDB One-to-Many Relationship tutorial with Mongoose examples
      • MongoDB Many-to-Many Relationship with Mongoose examples
  • Upload files
    • How to upload multiple files in Node.js
    • Upload & resize multiple images in Node.js using Express, Multer, Sharp
    • Upload/store images in MySQL using Node.js, Express & Multer
    • How to upload/store images in MongoDB using Node.js, Express & Multer
  • React Grimoire
    • React Doc
    • React Grimoire
    • React Cheat Sheet
  • spring boot Grimoire
    • Getting started
    • Spring Boot, Spring Data JPA – Rest CRUD API example
    • Spring Boot Token based Authentication with Spring Security & JWT
  • Mongo Grimoire
    • MongoDb-Mongoose Cheat Sheet
  • Development tools
    • Design Patterns
  • maissen_grimoire
Powered by GitBook
On this page
  • Sequelize Many-to-Many example Overview
  • Use Sequelize for Many-to-Many Association
  • Sequelize Many-to-Many Implementation in Node.js

Was this helpful?

  1. Node Grimoire
  2. Node.js with SQL databases
  3. Sequelize

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

PreviousSequelizeNextSequelize One-to-Many Association example with Node.js & MySQL

Last updated 1 year ago

Was this helpful?

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:

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:

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

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;

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:

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

For more details, please visit .

create a new Tutorial: (object)

find a Tutorial by id: (id)

get all Tutorials: ()

, ,…

provides simple way to define the Sequelize Many-to-Many relationship.

API Reference for the Sequelize constructor
create
findByPk
findAll
update
destroy
belongsToMany()
sequelize-many-to-many-relationship-node-js-example-table-entity-a
sequelize-many-to-many-relationship-node-js-example-table-entity-b
sequelize-many-to-many-relationship-node-js-example-junction-table