How to implement Many-To-Many association in MySQL/Node.js API using async/await

The following article is part of a series dedicated to the three types of relationships in a MySQL database: one-to-one, one-to-many, and many-to-many and how to use them in a Node.js API.
This article provided a step-by-step tutorial showing how to easily represent and implement a Many-To-Many association in MySQL/Node.js API using async/await calls.

Related Articles:

This article will cover:

How to define a Many-To-Many association in MYSQL?
Developing our MySQL/Node.js boilerplate API
Implementation of MANY-TO-MANY association in MySQL using async/await
Writing the One-To-Many association queries using promises
Adding API Routes into apiRouter.js
Testing our API routes with Postman
1. Create a record in the junction table Employee_project
2. Get an employee projects
3. Get a project employees
4. Get all projects and the employees working on them
Conclusion
You might also like:

How to define a Many-To-Many association in MYSQL?

In a relational database, there are three ways in which a table can be related to another table: one-to-one, one-to-many, and Many-to-many. The two first ones are the easiest to handle by adding the primary key of one table into the other table as a foreign key. However, the Many-to-many association is the trickiest relationship to represent and implement in a relational database. Relational systems usually don’t allow direct implementation of the many-to-many relationship between two tables. So to avoid duplicated data, we break the many-to-many relationship into two one-to-many relationships by using a third table, called a join table.

That is why the many-to-many association is the trickiest relationship to represent and implement in a relational database. It is a relationship where more than one record in table A is related to more than one record in another table B. An example of a many-to-many relationship is the one between employees and projects. An employee can work on many projects, and a project can include and be done by many employees.

The following example shows an Employees table, which contains a record for each employee, and a project table, including each project. A join table, Employee_project, creates two one-to-many relationships between each of the two tables.

Each record in a join table Employee_project includes a match field that contains the value of the primary keys of the two tables Employee and Project as foreign keys.

Developing our MySQL/Node.js boilerplate API

This section will implement the example given in the previous quarter and define the one-to-many association between the two tables Employee and Company in a Node.js and MySQL boilerplate API named “manyToMany-example”. The project directory contains the following files:

server.js: Our boilerplate for the node.js server uses express as a web server. We mount the existing apiRouter into server.js.
.env: To save all your environment variables.
db.js: In this file, we will write our queries to connect the database and interact with its tables. We define these queries as methods of the db object in db.js.
config.js: This file is used to save the configuration data and database connection options for the MySQL database.
apiRouter.js: This apiRouter file will be the starting point for all your API routes.

If you want to know more details about the start-server we are using in this article, go ahead and check this article: Complete Guide to Build a RESTful API with Node.js and Express.

server.js

 require("dotenv").config();
const express =require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const morgan = require('morgan');
  
const apiRouter = require('./apiRouter');
  
const app = express();
  
  
  
const PORT= process.env.PORT;
  
app.use(bodyParser.json());
app.use(cors());
app.use(morgan('dev'));
  
app.use('/apiRouter',apiRouter)
  
app.listen(PORT, ()=>{
    console.log(`server is listening  on ${PORT}`);
});
  
module.exports = app;

.env

 PORT = 3000
DB_PORT = “yourDBport”
DB_HOST = localhost
DB_USER = “yourusername”
DB_PASS = “yourDBpassword”
MYSQL_DB = “yourDBname”
CONNECTION_LIMIT = 10

package.json

 //package.json
  
{
  "name": "manyToMany-example",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon server.js "
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "cors": "^2.8.5",
    "dotenv": "^8.2.0",
    "express": "^4.17.1",
    "morgan": "^1.10.0",
     "mysql": "^2.18.1",
    "nodemon": "^2.0.4",
    "save": "^2.4.0"
  }
}

apiRouter.js

 //apiRouter.js
  
const express =require('express');
const apiRouter = express.Router();
  
  
module.exports = apiRouter;

In this tutorial, we will store our data in a MySQL database named “manyToManyDB” with two tables named “Employee“ and “Company”.

If you want to know how to create a database using node.js, click here: How to create MySQL database using node.js.

We use connection pooling instead of a single connection to connect our database. If you want to know more about connection pooling, read this article: Why is Connection Pooling better than Single Connection?.

db.js

 //db.js
  
const mysql = require('mysql');
 
  
  
const pool = mysql.createPool({
    connectionLimit: process.env.CONNECTION_LIMIT,    // the number of connections node.js will hold open to our database
    password: process.env.DB_PASS,
    user: process.env.DB_USER,
    database: process.env.MYSQL_DB,
    host: process.env.DB_HOST,
    port: process.env.DB_PORT
  
});
  
  
 
  
let db = {}; //create an empty object you will use later to write  and export your queries. 
  
module.exports = db

Implementation of MANY-TO-MANY association in MySQL using async/await

To implement a many-to-many relationship between two tables, we must create a third table called a join table. So to define the many-to-many association between Project and Employee tables, we need a Junction table called Employee_project, which has two columns, employee_id and project_id. The composite of these two keys will form a unique key for the join table.

You can use the following queries to create them:

Query to create Employee table :

 
 //create Employee table
pool.query('create table if not exists Employee (' +
'id int auto_increment primary key,' +
'name varchar(255) not null,'+
'position varchar(255) not null,'+
'wage int not null)' , function (err, result) {
    if (err) throw err;
    console.log("Employee table created");
  }
);

Query to create Project table :

 
//create Project table
 pool.query('create table if not exists Project (' +
 'id int auto_increment primary key,' +
 'name varchar(255) not null)'  , function (err, result) {
     if (err) throw err;
     console.log("Project table created");
   }
);

Query to create Employee_project table :

 
 //create Employee_project table
pool.query('create table if not exists Employee_project (' +
'employee_id int not null,' +
'project_id int not null,' +
'foreign key (employee_id) references  Employee(id),' +
'foreign key (project_id) references  Project(id) )', function (err, result) {
    if (err) throw err;
    console.log("Employee_project table created");
  }
);

Note: You can add more columns in the Employee_project table to store additional data depending on your needs.

Writing the One-To-Many association queries using promises

In this section, we will write our queries inside functions which we will define these functions as methods of the db object in our db.js.

1- insertEmployeeProject(): This function will insert data to the Employee_project tables, but to that, we should have the employee_id and the project_id already exist in the corresponding tables.

  
 db.insertEmployeeProject = (employee_id, project_id) =>{
    return new Promise((resolve, reject)=>{
        pool.query('INSERT INTO Employee_project (employee_id, project_id) VALUES (?, ?)', [employee_id, project_id], (error, result)=>{
            if(error){
                return reject(error);
            }
            
              return resolve(result.insertId);
        });
    });
};

2- The employeeProjects() takes the EmployeeId as a parameter and returns the employee with all his work projects.

 
  db.employeeProjects= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Project.id, Project.name FROM Project JOIN  Employee_project ON Employee_project.employee_id = ? and Employee_project.project_id = Project.id', [id], (error, projects)=>{
            if(error){
                return reject(error);
            }
            return resolve(projects);
  
        })
    })
}

3- The next function is projectEmployees(id) will get all the employees assigned to a project.id = id.

 
 db.projectEmployees= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Employee.id, Employee.name, Employee.position, Employee.wage FROM Employee JOIN  Employee_project ON Employee_project.project_id = ? and Employee_project.employee_id = Employee.id ', [id], (error, employees)=>{
            if(error){
                return reject(error);
            }
            return resolve(employees);
  
        })
    })
}

4- The allProjectsEmployees() function will get all the projects, including their employees.

 
  
db.allProjectsEmployees= ()=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Employee.id, Employee.name, Employee.position, Employee.wage, Project.name as projectName FROM Employee INNER JOIN  Employee_project  ON Employee_project.employee_id = Employee.id  INNER JOIN Project ON Employee_project.project_id = Project.id GROUP BY  Employee.id, Project.id ORDER BY Project.id ', (error, projectsEmployees)=>{
            if(error){
                return reject(error);
            }
            return resolve(projectsEmployees);
  
        })
    })
}

We used promises in our queries instead of callback functions; if you want to know why, please go ahead and read this article: How to interact with MySQL database using async/await promises in node.js ?

db.js

 
  //db.js
 
const mysql = require('mysql');

 const database = process.env.MYSQL_DB;
 
const pool = mysql.createPool({
    connectionLimit: process.env.CONNECTION_LIMIT,    // the number of connections node.js will hold open to our database
    password: process.env.DB_PASS,
    user: process.env.DB_USER,
    database: process.env.MYSQL_DB,
    host: process.env.DB_HOST,
    port: process.env.DB_PORT
 
});
 
 // create db tables
 
  

//create Employee table
pool.query('create table if not exists Employee (' +
'id int auto_increment primary key,' +
'name varchar(255) not null,'+
'position varchar(255) not null,'+
'wage int not null)' , function (err, result) {
    if (err) throw err;
    console.log("Employee table created");
  }
);





 //create Project table
 pool.query('create table if not exists Project (' +
 'id int auto_increment primary key,' +
 'name varchar(255) not null)'  , function (err, result) {
     if (err) throw err;
     console.log("Project table created");
   }
);

   

//create Employee_project table
pool.query('create table if not exists Employee_project (' +
'employee_id int not null,' +
'project_id int not null,' +
'foreign key (employee_id) references  Employee(id),' +
'foreign key (project_id) references  Project(id) )', function (err, result) {
    if (err) throw err;
    console.log("Employee_project table created");
  }
);

 
let db = {}; //create an empty object you will use later to write  and export your queries. 
 




db.insertEmployeeProject = (employee_id, project_id) =>{
    return new Promise((resolve, reject)=>{
        pool.query('INSERT INTO Employee_project (employee_id, project_id) VALUES (?, ?)', [employee_id, project_id], (error, result)=>{
            if(error){
                return reject(error);
            }
            
              return resolve(result.insertId);
        });
    });
};





db.employeeProjects= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Project.id, Project.name FROM Project JOIN  Employee_project ON Employee_project.employee_id = ? and Employee_project.project_id = Project.id', [id], (error, projects)=>{
            if(error){
                return reject(error);
            }
            return resolve(projects);
  
        })
    })
}





db.projectEmployees= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Employee.id, Employee.name, Employee.position, Employee.wage FROM Employee JOIN  Employee_project ON Employee_project.project_id = ? and Employee_project.employee_id = Employee.id ', [id], (error, employees)=>{
            if(error){
                return reject(error);
            }
            return resolve(employees);
  
        })
    })
}
   


db.allProjectsEmployees= ()=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Employee.id, Employee.name, Employee.position, Employee.wage, Project.name as projectName FROM Employee INNER JOIN  Employee_project  ON Employee_project.employee_id = Employee.id  INNER JOIN Project ON Employee_project.project_id = Project.id GROUP BY  Employee.id, Project.id ORDER BY Project.id ', (error, projectsEmployees)=>{
            if(error){
                return reject(error);
            }
            return resolve(projectsEmployees);
  
        })
    })
}


module.exports = db

Adding API Routes into apiRouter.js

Before adding routes to your apiRouter.js using HTTP methods, first, you need to pull in the functions from db.js by adding this line of code to your apiRouter.js. To write our requests routes, we use the async functions.

 
 const db = require('./db.js');

For more details about async/await functions and promises, go ahead and check this article: How to interact with MySQL database using async/await promises in node.js?

apiRouter.js

 
   //apiRouter.js
 
const express =require('express');
const apiRouter = express.Router();
const db = require('./db.js');
 

 


// Create a record in the junction table Employee_project.
  
apiRouter.post('/employee-project',  async (req, res, next)=>{
    try{
        const employeeId = req.body.employeeProject.EmployeeId;
        const projectId = req.body.employeeProject.ProjectId;
        console.log(employeeId);
        console.log(projectId);
         
              if (!employeeId || !projectId) {
                return res.sendStatus(400);
             }
   
        const employeeProject =  await db.insertEmployeeProject(employeeId, projectId).then(() => res.json({ message: 'Employee_project created.' }));
          
          
   
    } catch(e){
        console.log(e);
        res.sendStatus(400);
    }
 });
  
    
  

 
 
// Get an employee projects
 
 apiRouter.param('employeeId', async (req, res, next, employeeId)=> {
    try{
        const projects = await db.employeeProjects(employeeId);
        console.log(projects);
        req.projects = projects;
        next(); // go to apiRouter.get('/employee/:employeeId')
    } catch(e) {
        console.log(e);
        res.sendStatus(404);
    }
 });
   
   
   
   
 apiRouter.get('/employee/:employeeId',  (req, res, next)=>{
    res.status(200).json({projects: req.projects});
 });
 
 
 
 
 
 
 
 
// Get all projects and the employees  working on them using the junction table.
   
apiRouter.get('/project-employee', async (req, res, next)=>{
    try {
        const projectsEmployees = await db.allProjectsEmployees();
        res.status(200).json({projectsEmployess: projectsEmployees});
    } catch(e) {
        console.log(e);
        res.sendStatus(500);
    }
 });
 
 
 
 // Get a project employees
  
 apiRouter.param('projectId', async (req, res, next, projectId)=> {
    try{
        const employees = await db.projectEmployees(projectId);
        req.employees = employees;
        next(); // go to apiRouter.get('/project/:projectId')
    } catch(e) {
        console.log(e);
        res.sendStatus(404);
    }
 });
   
   
 apiRouter.get('/project/:projectId',  (req, res, next)=>{
    res.status(200).json({employees: req.employees});
 });
 
 
 
 
  

 
 
module.exports = apiRouter;

Testing our API routes with Postman

Before you start testing the API with Postman, we need first to populate our tables by inserting the data in the following tables. You can do that manually by using the Workbench interface or creating routes in your API. To see how to do that, you can check here: How to interact with MySQL database using async/await promises in node.js ?

Employee table:

id	name	position	wage
1	employee1	manager	8000
2	employee2	engineer	6000
3	employee3	designer	5000
4	employee4	architect	6000

Project table:

id	name
1	project1
2	project2
3	project3

Employee_project table:

Employee_id	Project_id
1	1
1	2
3	2
4	1
4	3
2	3

1. Create a record in the junction table Employee_project

Assigning an employee with an EmployeeId: 1 to a project with ProjectId: 2 means inserting the combination (1,2) to the Employee_project table. To do that, you need to use the following setting in your Postman:

Path: apiRouter/employee-project
Method: POST
URL: http://localhost:3000/apiRouter/employee-project
Change the body type to “raw”
Change the format to “JSON”
The json object: {“employeeProject” : {“EmployeeId”:1 ,”ProjectId”: 2}}

2. Get an employee projects

To Get the projects of an employee with EmployeeId: 1, use the following setting in your Postman:

Path: apiRouter/employee/:employeeId
Method: GET
URL: http://localhost:3000/apiRouter/employee/1
Change the body type to “raw”
Change the format to “JSON”

3. Get a project employees

To Get all employees working on a project with projectId: 2, use the following setting in your Postman:

Path: apiRouter/project/:projectId
Method: GET
URL: http://localhost:3000/apiRouter/project/2
Change the body type to “raw”
Change the format to “JSON”

4. Get all projects and the employees working on them

To get all projects and the employees working on them using the junction table, use the following setting in your Postman:

Path: apiRouter/project-employee
Method: GET
URL: http://localhost:3000/apiRouter/project-employee
Change the body type to “raw”
Change the format to “JSON”

Conclusion

This article showed you how to represent and implement a Many-To-Many association in a MySQL database using async/await requests to interact with the relational database in a Node.js API.

If you want to see our implementation for the one-to-many and one-to-one associations, go ahead and check these two articles: