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
Contents
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.
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);
...
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:
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 poolmin
: minimum number of connection in poolidle
: maximum time, in milliseconds, that a connection can be idle before being releasedacquire
: 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:
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
Thanks a lot for this article, bro!
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?
hello, can I find source code from this tutorial many to many sequelize? Thanks
Can this tags be used with Categories and filter tutorials further by tags inside a category.
how i can additional field on through tutorial_tag?
hi, did you get to do it?
You need to define the model for join table
https://sequelize.org/master/class/lib/associations/belongs-to-many.js~BelongsToMany.html
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
Greetings to you sir!
on additional column how you access that column using findAll()
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
very helpfull thanks…no source file for this 🙂
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 🙂
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
Hi, remember that ‘tag’ in
tag.addTutorial
is an object, not Sequelize model as you wrote:Tag.addTutorial
.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.
I had the same problem, I have changed to: through: ‘name of your intermadiate table’
Can you send example of these code?
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)