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.
Related posts:
– Sequelize One-To-Many association – NodeJS/Express, MySQL
– Sequelize One-To-One association – NodeJS/Express, MySQL
– Node.js RestAPIs Download File from Amazon S3 | using Express, AWS-SDK
Sequelize Many-To-Many Association Express/Nodejs
Many-to-many association with a join table.
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
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
– db.config.js
is used to define nodejs/sequelize database configuration
– project.controller.js
is used to define nodejs/express restapi.
– user.model.js
& project.model.js
are used to define sequelize many-to-many model association.
– project.route.js
is used to define express routing.
– server.js
is used to define nodejs server.
Setting up NodeJs/Express project
Following the guide to create a NodeJS/Express project
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
– 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; }
Express RestAPIs
Route
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); }
Controller
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); }); };
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 } } ] } ]
Sourcecode
Nodejs-Express-Sequelizejs-Many-to-Many-Mysql