Sequelize Many-to-Many association – NodeJS/Express, MySQL

sequelize-many-to-many-association-nodejs-express-mysql-feature-image

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.

sequelize many-to-many nodejs-express - normal tables

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.

sequelize many-to-many nodejs-express - tables with alias

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:

sequelize many-to-many nodejs-express - user table

sequelize many-to-many nodejs-express - project table

sequelize many-to-many nodejs-express - join table

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

3 thoughts on “Sequelize Many-to-Many association – NodeJS/Express, MySQL”

Leave a Reply

Your email address will not be published. Required fields are marked *