– Tutorial “Sequelize belongstomany Example – Sequelize Many to Many Association Example with Nodejs/Express + MySQL”
In the post, we got started with Sequelize One-To-Many association. Today we’re gonna create Sequelize Many-To-Many association models with NodeJS/Express, MySQL.
Sequelize belongstomany Example – Sequelize Many-To-Many
Note: When calling a method such as Project.belongsToMany(User, {through: 'UserProject'})
, we say that the Project model is the source and the User model is the target.
We define 2 models:
const Project = sequelize.define('project', {
/*
attributes
*/
});
const User = sequelize.define('user', {
/*
attributes
*/
});
Many-To-Many associations are used to connect sources with multiple targets. And the targets can also have connections to multiple sources.
Project.belongsToMany(User, {through: 'UserProject'});
User.belongsToMany(Project, {through: 'UserProject'});
A new model UserProject will be created with projectId and userId.

through
is required. Sequelize will create accessors for both Project and User instances: getUsers
, setUsers
, addUser
, addUsers
to Project, and getProjects
, setProjects
, addProject
, and addProjects
to User.
To modify the name of models in associations, we can use the alias with as
option:
User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId', otherKey: 'projectId'});
Project.belongsToMany(User, { as: 'Workers', through: 'worker_tasks', foreignKey: 'projectId', otherKey: 'userId'}})
foreignKey
is used to set source model key in the through relation.
otherKey
is used to set target model key in the through relation.

How to persist many-to-many entities into database?
User.create({
/*
attributes
*/
}).then(jack => {
let users = [jack];
return User.create({
/*
attributes
*/
}).then(mary => {
users.push(mary);
return users;
})
}).then(users => {
Project.create({
/*
attributes
*/
}).then(p123 => {
p123.setWorkers(users);
})
Project.create({
/*
attributes
*/
}).then(p456 => {
p456.setWorkers(users);
})
})
How to retrieve all entities?
With Belongs-To-Many you can query based on through relation by using through
option and select specific attributes by using attributes
option.
Project.findAll({
attributes: ['code', 'name'],
include: [{
model:User, as: 'Workers',
attributes: [['firstname', 'name'], 'age'],
through: {
attributes: ['projectId', 'userId'],
}
}]
})
Practice – Sequelize belongstomany Example
We create a NodeJs/Express project as below structure:
/nodejs-sequelizejs-many-to-many-mysql
/app
/config
db.config.js
env.js
/controller
project.controller.js
/model
project.model.js
user.model.js
/route
project.route.js
/node_modules
package.json
server.js
Setting up NodeJs/Express project
In the tutorial “Sequelize belongstomany Example”, we install Express, Sequelize, MySQL:
$npm install express sequelize mysql2 --save
-> ‘package.json’ file:
{
"name": "Sequelize-Many-to-Many",
"version": "1.0.0",
"description": "nodejs-express-sequelizejs-Many-to-Many-mysql",
"main": "server.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [
"NodeJs-Express-SequelizeJs-MySQL"
],
"author": "JSA",
"license": "ISC",
"dependencies": {
"express": "^4.16.3",
"mysql2": "^1.5.3",
"sequelize": "^4.37.6"
}
}
Setting up Sequelize MySQL connection
const env = {
database: 'testdb',
username: 'root',
password: '12345',
host: 'localhost',
dialect: 'mysql',
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};
module.exports = env;
– Setup Sequelize-MySQL connection in ‘./app/config/db.config.js’ file:
const env = require('./env.js');
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
host: env.host,
dialect: env.dialect,
operatorsAliases: false,
pool: {
max: env.max,
min: env.pool.min,
acquire: env.pool.acquire,
idle: env.pool.idle
}
});
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.project = require('../model/project.model.js')(sequelize, Sequelize);
db.user = require('../model/user.model.js')(sequelize, Sequelize);
db.project.belongsToMany(db.user, { as: 'Workers', through: 'worker_tasks', foreignKey: 'projectId', otherKey: 'userId'});
db.user.belongsToMany(db.project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId', otherKey: 'projectId'});
module.exports = db;
Define Sequelize models – Sequelize belongstomany Example
– Project model:
module.exports = (sequelize, Sequelize) => {
const Project = sequelize.define('project', {
code: {
type: Sequelize.STRING
},
name: {
type: Sequelize.STRING
}
});
return Project;
}
– User model:
module.exports = (sequelize, Sequelize) => {
const User = sequelize.define('user', {
firstname: {
type: Sequelize.STRING
},
lastname: {
type: Sequelize.STRING
},
age: {
type: Sequelize.INTEGER
}
});
return User;
}
Nodejs Express RestAPIs Router – Sequelize belongstomany Example
Define Project’s routes in ‘./app/controller/project.route.js’ file:
module.exports = function(app) {
const projects = require('../controller/project.controller.js');
// Init data: add Projects & Users
app.get('/api/projects/init', projects.init);
// Retrieve all Projects (include Users)
app.get('/api/projects/all', projects.findAll);
}
Nodejs Express Controller – Sequelize belongstomany Example
Implement Project’s controller in ‘./app/controller/project.controller.js’ file:
const db = require('../config/db.config.js');
const Project = db.project;
const User = db.user;
// Init data: Projects & Users
exports.init = (req, res) => {
User.create({
firstname: "Jack",
lastname: "Davis",
age: 37
}).then(jack => {
let users = [jack];
return User.create({
firstname: "Mary",
lastname: "Taylor",
age: 21
}).then(mary => {
users.push(mary);
return users;
})
}).then(users => {
Project.create({
code: 'P-123',
name: 'JSA - Branding Development'
}).then(p123 => {
p123.setWorkers(users);
})
Project.create({
code: 'P-456',
name: 'JSA - DataEntry Development'
}).then(p456 => {
p456.setWorkers(users);
})
}).then(() => {
res.send("OK");
});
};
// Fetch all Projects include Users
exports.findAll = (req, res) => {
Project.findAll({
attributes: ['code', 'name'],
include: [{
model:User, as: 'Workers',
attributes: [['firstname', 'name'], 'age'],
through: {
attributes: ['projectId', 'userId'],
}
}]
}).then(projects => {
res.send(projects);
});
};
Nodejs Express Server.js
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
app.use(bodyParser.json())
const db = require('./app/config/db.config.js');
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
console.log('Drop and Resync with { force: true }');
});
require('./app/route/project.route.js')(app);
// Create a Server
var server = app.listen(8081, function () {
var host = server.address().address
var port = server.address().port
console.log("App listening at http://%s:%s", host, port)
})
Run & Check results
Start Node.js server:
>node server.js
App listening at http://:::8081
Executing (default): DROP TABLE IF EXISTS `worker_tasks`;
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): DROP TABLE IF EXISTS `projects`;
Executing (default): DROP TABLE IF EXISTS `projects`;
Executing (default): CREATE TABLE IF NOT EXISTS `projects` (`id` INTEGER NOT NULL auto_increment , `code` VARCHAR(255), `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `projects`
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `firstname` VARCHAR(255), `lastname` VARCHAR(255), `age` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `users`
Executing (default): DROP TABLE IF EXISTS `worker_tasks`;
Executing (default): CREATE TABLE IF NOT EXISTS `worker_tasks` (`createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `projectId` INTEGER , `userId` INTEGER , PRIMARY KEY (`projectId`, `userId`), FOREIGN KEY (`projectId`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `worker_tasks`
Drop and Resync with { force: true }
Initial data
Request:
localhost:8081/api/projects/init
-> Logs:
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`age`,`createdAt`,`updatedAt`) VALUES (1,'Jack','Davis',37,'2018-04-24 23:06:41','2018-04-24 23:06:41');
Executing (default): INSERT INTO `users` (`id`,`firstname`,`lastname`,`age`,`createdAt`,`updatedAt`) VALUES (2,'Mary','Taylor',21,'2018-04-24 23:06:41','2018-04-24 23:06:41');
Executing (default): INSERT INTO `projects` (`id`,`code`,`name`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'P-123','JSA - Branding Development','2018-04-24 23:06:41','2018-04-24 23:06:41');
Executing (default): INSERT INTO `projects` (`id`,`code`,`name`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'P-456','JSA - DataEntry Development','2018-04-24 23:06:41','2018-04-24 23:06:41');
Executing (default): SELECT `createdAt`, `updatedAt`, `projectId`, `userId` FROM `worker_tasks` AS `worker_tasks` WHERE `worker_tasks`.`projectId` = 1;
Executing (default): SELECT `createdAt`, `updatedAt`, `projectId`, `userId` FROM `worker_tasks` AS `worker_tasks` WHERE `worker_tasks`.`projectId` = 2;
Executing (default): INSERT INTO `worker_tasks` (`createdAt`,`updatedAt`,`projectId`,`userId`) VALUES ('2018-04-24 23:06:41','2018-04-24 23:06:41',1,1),('2018-04-24 23:06:41','2018-04-24 23:06:41',1,2);
Executing (default): INSERT INTO `worker_tasks` (`createdAt`,`updatedAt`,`projectId`,`userId`) VALUES ('2018-04-24 23:06:41','2018-04-24 23:06:41',2,1),('2018-04-24 23:06:41','2018-04-24 23:06:41',2,2);
-> Results:



Fetch all entities
Request:
localhost:8081/api/projects/all
-> Logs:
Executing (default): SELECT `project`.`id`, `project`.`code`, `project`.`name`, `Workers`.`id` AS `Workers.id`, `Workers`.`firstname` AS `Workers.name`, `Workers`.`age` AS `Workers.age`, `Workers->worker_tasks`.`createdAt` AS `Workers.worker_tasks.createdAt`, `Workers->worker_tasks`.`updatedAt` AS `Workers.worker_tasks.updatedAt`, `Workers->worker_tasks`.`projectId` AS `Workers.worker_tasks.projectId`, `Workers->worker_tasks`.`userId` AS `Workers.worker_tasks.userId` FROM `projects` AS `project` LEFT OUTER JOIN ( `worker_tasks` AS `Workers->worker_tasks` INNER JOIN `users` AS `Workers` ON `Workers`.`id` = `Workers->worker_tasks`.`userId`) ON `project`.`id` = `Workers->worker_tasks`.`projectId`;
-> Result:
[
{
"code": "P-123",
"name": "JSA - Branding Development",
"Workers": [
{
"name": "Jack",
"age": 37,
"worker_tasks": {
"projectId": 1,
"userId": 1
}
},
{
"name": "Mary",
"age": 21,
"worker_tasks": {
"projectId": 1,
"userId": 2
}
}
]
},
{
"code": "P-456",
"name": "JSA - DataEntry Development",
"Workers": [
{
"name": "Jack",
"age": 37,
"worker_tasks": {
"projectId": 2,
"userId": 1
}
},
{
"name": "Mary",
"age": 21,
"worker_tasks": {
"projectId": 2,
"userId": 2
}
}
]
}
]
Read More
References Link: Sequelize Association