How to implement One-To-Many association in MySQL/Node.js API

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 efficiently represent and implement a One-To-Many association in MySQL/Node.js API using async/await calls.

Related Articles:

This article will cover:

What is One-To-Many association in a relational database?
The MySQL/Node.js boilerplate API
Implementation of ONE-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- Route for Getting a company employees
2- Route for Getting an employee company
Conclusion
You might also like:

What is One-To-Many association in a relational database?

A one-to-many relationship in a database is the most common relational database design. We say that a one-to-many relationship exists between two tables A and B, when one row in table A may be linked with many rows in table B. One row in table B is attached to only one row in table A.

For example, in a system for Employees management, a company could have many employees, but an employee can work for only one company at the same time. So we can translate and define this relation by using the one-to-many association in a database. The following diagrams explain perfectly the relation in question.

In the above example, the primary key in the Companies table, companyID, is designed to contain unique values. The foreign key in the Employees table, companyID, is designed to allow multiple instances of the same value. The foreign key is used to define a relation between two tables.

For the rest of this article, It’s required that you have already set up MySQL on your computer and added it to your path. If not, you can use the following links:

Install MySQL Server and MySQL Workbench on Windows

How to add MySQL to the path in mac OS

How to install MySQL Workbench on mac OS

The 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 “oneToMany-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": "oneToMany-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 “oneToManyDB” 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 are using 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 ONE-TO-MANY association in MySQL using async/await

To implement a one-to-many relationship, we need two tables. That means we need to create two tables Employee and Company. We add the company ID as a foreign key to the Employee table to relate these tables.

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,' +
        'company_id int not null,'+
        'foreign key (company_id) references  Company(id))', function (err, result) {
            if (err) throw err;
            console.log("Employee table created");
          }
    );

Query to create Company table :

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

Writing the One-To-Many association queries using promises

In the following, we will define two functions as methods of the db object in our db.js.

The first one selects a company including all the employees working for this specific company:

  
  db.companyEmployees= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT * FROM Employee WHERE company_id = ?', [id], (error, employees)=>{
            if(error){
                return reject(error);
            }
            return resolve(employees);

        })
    })
}

And the second one is to get an employee’s company.

 
  
db.employeeCompany= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Company.name FROM Employee, Company WHERE Employee.id= ? and Company.id = Employee.company_id ', [id], (error, employeeCompany)=>{
            if(error){
                return reject(error);
            }
            return resolve(employeeCompany);

        })
    })
}

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 Company table
  pool.query('create table if not exists Company (' +
  'id int auto_increment primary key,' +
  'name varchar(255) not null)'  , function (err, result) {
      if (err) throw err;
      console.log("Company table created");
    }
);



//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,' +
        'company_id int not null,'+
        'foreign key (company_id) references  Company(id))', function (err, result) {
            if (err) throw err;
            console.log("Employee table created");
          }
    );




   



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

db.companyEmployees= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT * FROM Employee WHERE company_id = ?', [id], (error, employees)=>{
            if(error){
                return reject(error);
            }
            return resolve(employees);

        })
    })
}







db.employeeCompany= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Company.name FROM Employee, Company WHERE Employee.id= ? and Company.id = Employee.company_id ', [id], (error, employeeCompany)=>{
            if(error){
                return reject(error);
            }
            return resolve(employeeCompany);

        })
    })
}








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
 
const express =require('express');
const apiRouter = express.Router();
const db = require('./db.js');
 

 


// Get company with its employees


 apiRouter.param('companyId', async (req, res, next, companyId)=> {
    try{
        const employees = await db.companyEmployees(companyId);
        req.employees = employees;
        next(); // go to apiRouter.get('/company/:companyId')
    } catch(e) {
        console.log(e);
        res.sendStatus(404);
    }
 });
   
   
   
 apiRouter.get('/company/:companyId',  (req, res, next)=>{
    res.status(200).json({employees: req.employees});
 });
   



// Get an employee s company

 apiRouter.param('employeeId', async (req, res, next, employeeId)=> {
    try{
        const employeeCompany = await db.employeeCompany(employeeId);
        req.employeeCompany = employeeCompany;
        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({employeeCompany: req.employeeCompany});
 });
   



 
 
module.exports = apiRouter;

Testing our API routes with Postman

Before you start testing the API with Postman, you need first to populate our tables by inserting the following data. 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 add Routes to insert data into MySQL database-related tables in Node.js API?

Employee table:

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

Company table:

companyId	name
1	company1
2	company2

1- Route for Getting a company employees

To get a company employees with companyId = 1, use the following setting in your Postman:

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

2- Route for Getting an employee company

To get an employee company with employeeId = 2, use the following setting in your Postman:

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

Conclusion

This article intends to show you how to define and use the one-to-many association in a MySQL database using async/await in our requests. I hope this was helpful for you and if you want to read our article on how to define and use many-to-many and one-to-one associations, please go here:

To implement your API using the initialize() function to connect MySQL server, connect a database with Sequelize ORM, and initialize the models, go here: Example How to use initialize() Function in Node.js/Express API.