ONE-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js

The Sequelize ORM supports various association types in a database. ONE-TO-MANY Association is one of these relationships, and it is known as the most common relational database design.

This tutorial provides a profound explanation of the one-to-many association and how to define and implement it using Sequelize async/await to interact with MySQL database in a Node.js API.

Related Articles:

MANY-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.
How to interact with MySQL database using async/await promises in node.js ?

This article will cover:

What is hasMany() association in Sequelize
Setting up the Node.js API
Setting Up the Database Connection using Sequelize
Defining the Sequelize Models
Convert Sequelize Models to SQL Tables
Defining the One-To-Many association using hasMany() Sequelize method
Creating Controllers using the One-To-Many association and Sequelize async/await
Adding API Routes using the One-To-Many association
Testing our API routes with Postman
1- Get a company with its employees.
2- Get all Companies with their employees.
Conclusion
You might also like:

What is hasMany() association in Sequelize

hasMany() is the Sequelize method used to perform the one-to-many association in a database by connecting one source with multiple targets. These targets are again related to exactly one specific source.

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, and it is automatically created while using Sequelize hasMany() method.

NOTE:

The foreign key is used to define a relation between two tables.
In the rest of this article, we will use the above example to explain the Sequelize hasMany() method.

Setting up the Node.js API

This tutorial will show you how to perform the one-to-many relationship explained in the previous section using the haMany method in a Node.js and MySQL boilerplate API. We will use Sequelize ORM to connect the database, define the database tables, and interact with these tables.

So 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:

To develop our project example named “hasMany-example”, we will write our code in JavaScript for Node.js and use Express as the webserver for the API.

We are starting with a boilerplate for the node.js server. 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.

Make sure that you have all the following packages installed and imported into your server.js:

express.
body-parser.
cors.
dotenv.

To do that, run the following command after initializing the npm in your project folder:

  npm install --save  express  body-parser  cors  dotenv

server.js

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

Save all the environment variables related to your API in your .env file, so you will be able to load and use them later.

.env

 
 APP_PORT = 3000

package.json

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

To set up the configuration of our database and initialize the Sequelize ORM, we need to follow these steps:

First, connect to MySQL server using mysql2 and create our database.
Next, use the Sequelize ORM to connect the database.
Then, initialize the Employee and Company models.
Finally, create the Employee and company tables in MySQL database by calling await sequelize.sync().

Setting Up the Database Connection using Sequelize

The first thing to do is create the database then establish a connection with it. You have two options to create your database:

Manually by using MySQL workbench interface; if you want to see how to do that, check HERE or go Here if you are a mac user.
OR by using query and that precisely what we will be doing in the db.js file by following these steps:

–> First, in your project folder “hasMany-example”, create a new JavaScript file and name it db.js.

–> Next, install the mysql2 package inside our project “hasMany-example”, then import it into db.js. Make sure it’s Mysql2 and not mysql.

 npm install --save mysql2

Then write an SQL query to create our MySQL database named “hasMany_db“. Executing this query will create our database if it doesn’t already exist.

db.js

const config = require('./config.js');
const mysql = require('mysql2');
 
 
 
 
 
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
    
    const pool =  mysql.createPool({ host, port, user, password });
      
   pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);

To save the configuration data and database connection options for the MySQL database, we create a separate file config.js file like this:

config.js

module.exports ={
   "database":{
        "host": "localhost",
        "port": 3306,
        "user": "root",
        "password": "enter your db password here",
        "database": "hasMany_db"
   }
   
 
 
};

If you want to know why we chose connection pooling instead of a single connection, read this article: Why is Connection Pooling better than Single Connection?.

The next step is to connect to the Mysql database that we have just created using Sequelize and mysql2 client; we need to install the sequelize package.

 npm install --save sequelize

In the db.js file, we require the Sequelize class from the sequelize package; we use the capital letter because it’s a class.

Then, we create a new instance of this class to connect the database by passing specific parameters and options. To know more about these parameters, check HERE.

In order to use the sequelize instance in the other files of our project, we will attach it to the exported db object.

db.js

const config = require('./config.js');
const mysql = require('mysql2');

const Sequelize = require('sequelize');
 
module.exports = db = {};
 
 
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
     
   const pool =  mysql.createPool({ host, port, user, password });
 
     pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
 
    // connect to db
    const sequelize = new Sequelize(database, user, password, { dialect: 'mysql',
    pool: {
        max: config.pool.max,
        min: config.pool.min,
        acquire: config.pool.acquire,
        idle: config.pool.idle
     
      } }
     
    );
     
 
   db.sequelize = sequelize ;

config.js

module.exports ={
   "database":{
        "host": "localhost",
        "port": 3306,
        "user": "root",
        "password": "enter your db password here",
        "database": "hasMany_db"
   },
   "pool": {
    "max": 5,
    "min": 0,
    "acquire": 30000,
    "idle": 10000
  }
 
 
};

Note that, for more clean code in production mode, you can use the initialize() async function to write the queries to create the database connect it. If you want to see how to do that, you can check this article: Example How to use initialize() Function in Node.js/Express API.

Defining the Sequelize Models

As we already said, we need two tables to define a one-to-many relationship. That means that we need to define and initialize two models to perform the hasMany association. Sequelize will automatically create the corresponding tables in the MySQL database by calling the sequelize.sync().

In our database, we will have two tables, Employee and Company, so we will define two models Employee and Company.

So inside your project folder, please create a new folder and name it models. Then inside this folder, create Two new files (employee.js and company.js), one for each model.

To define a schema for the two tables in the MySQL Database, we use the define() method on the sequelize instance created already. To know more about the define() method and how to define a model for MySQL table, please go HERE.

employee.js

const { Sequelize, DataTypes } = require("sequelize");
 
const db = require('../db.js');
 
 
const  sequelize = db.sequelize;
 
const Employee = sequelize.define("Employee", {
 
 id: {
 
   type: DataTypes.INTEGER,
   autoIncrement: true,
   allowNull: false,
   primaryKey: true,
 
 },
 
 name: {
 
   type: DataTypes.STRING,
   allowNull: false,
 
 },
position: {
 
   type: DataTypes.STRING,
   allowNull: false,
 
 },
 
 email: {
 
   type: DataTypes.STRING,
   allowNull: false,
 
 },
wage: {
 
   type: DataTypes.INTEGER,
   allowNull: false,
 
 },
 
});
 
 
 
 
module.exports = Employee;

company.js

const { Sequelize, DataTypes } = require("sequelize");
 
const db = require('../db.js');
 
 
const  sequelize = db.sequelize;
 
const Company = sequelize.define("Company", {
 
 id: {
 
   type: DataTypes.INTEGER,
   autoIncrement: true,
   allowNull: false,
   primaryKey: true,
 
 },
 
 name: {
 
   type: DataTypes.STRING,
   allowNull: false,
 
 }


 
});
 
 
 
module.exports = Company;

Convert Sequelize Models to SQL Tables

Now it’s time to import our Employee and Company models and then initialize and attach them to the db object. Then call the sequelize.sync() to convert the two models to tables in the MySQL database.

db.js

const config = require('./config.js');
const mysql = require('mysql2');
const Sequelize = require('sequelize');
 
module.exports = db = {};
 
 
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
    const pool =  mysql.createPool({ host, port, user, password });
     pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
 
    // connect to db
    const sequelize = new Sequelize(database, user, password, { dialect: 'mysql',
    pool: {
        max: config.pool.max,
        min: config.pool.min,
        acquire: config.pool.acquire,
        idle: config.pool.idle
     
      } }
     
    );
     
    db.sequelize = sequelize ;
 
    // init the Employee and Company models and add them to the exported db object
   
   const Employee = require('./models/employee');
  
    const Company = require('./models/company');

    db.Employee = Employee;
  
    db.Company = Company;
 
    // sync all models with database
     sequelize.sync();

server.js

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

Now, all you have to do is start your server, and you will see in your terminal that the database and the tables have been created. For confirmation, you can use the Workbench interface.

After that, go ahead and insert data into your Employees and Companies tables. You can do that manually by using the Workbench interface or by 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?

For our demonstration, we will use the data in the example given in the section above.

Defining the One-To-Many association using hasMany() Sequelize method

Before adding any code, it’s good to mention that at this current level, if you already run your code by starting your server for verification, that means your database is already created. So to validate any addition (like adding relationships between tables), you need to drop the existing tables and then recreate them with the new information by passing an object inside the sync() method and setting the parameter force to true. Note that this is not recommended for production-level software.

 
 sequelize.sync({force: true});

db.js

const config = require('./config.js');
const mysql = require('mysql2');
const Sequelize = require('sequelize');
 
module.exports = db = {};
 
 
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
    const pool =  mysql.createPool({ host, port, user, password });
     pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
 
    // connect to db
    const sequelize = new Sequelize(database, user, password, { dialect: 'mysql',
    pool: {
        max: config.pool.max,
        min: config.pool.min,
        acquire: config.pool.acquire,
        idle: config.pool.idle
     
      } }
     
    );
     
    db.sequelize = sequelize ;
 
    // init the Employee and Company models and add them to the exported db object
   
   const Employee = require('./models/employee');
  
    const Company = require('./models/company');

    db.Employee = Employee;
  
    db.Company = Company;
 
    // sync all models with database
    
  sequelize.sync();

To define the relationship between the Company table and Employee table, we need to make sequelize know about this relation by using the hasMany() method:

 
 Company.hasMany(Employee);

This means a Company can have many Employees, and an Employee belongs to one and only one Company.

In order to define a relation between two SQL tables, we need a foreign key. So when sequelize knows that the Company and Employee tables are related with one-to-many relation, it will automatically create a foreign key called CompanyId inside your Employee table. You can check that by using MySQL workbench.

But in case you need to associate two tables based on a different column, you may use the sourceKey and targetKey options as follows:

   
 Company.hasMany(Employee, {foreignKey: 'columnName', sourceKey: 'companyId'});

 Employee.belongsTo(Company, {foreignKey: 'columnName', targetKey: 'companyId'});

db.js

const config = require('./config.js');
const mysql = require('mysql2');
const Sequelize = require('sequelize');
 
module.exports = db = {};
 
 
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
    const pool =  mysql.createPool({ host, port, user, password });
     pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
 
    // connect to db
    const sequelize = new Sequelize(database, user, password, { dialect: 'mysql',
    pool: {
        max: config.pool.max,
        min: config.pool.min,
        acquire: config.pool.acquire,
        idle: config.pool.idle
     
      } }
     
    );
     
    db.sequelize = sequelize ;

  
 
  // init the Employee and Company models and add them to the exported db object
   
   const Employee = require('./models/employee');
  
    const Company = require('./models/company');

    db.Employee = Employee;
  
    db.Company = Company;

    db.Company.hasMany(Employee, {foreignKey: 'companyId'});

    db.Employee.belongsTo(Company, {foreignKey: 'companyId'});

 
    // sync all models with database
    
  sequelize.sync({force: true});

Creating Controllers using the One-To-Many association and Sequelize async/await

Once that is done, inside the project folder, create a new file and call it controller.js. We will define our controllers for the one-to-many association as async functions in controller.js. Then we will export these functions in order to call them in our router.

For more details about async/await functions and promises, go ahead and check these articles:

In the following, we will define two functions in our controller.js file. The first one selects a company including all the employees working for this specific company and the second one is to get all companies with their employees. To do that, we use the findByPk() and findAll() methods on the Company table with an include clause for employees.

controller.js

const db = require('./db');
  
const Employee = db.Employee;
const Company = db.Company;
 module.exports ={
 
  getAllCompaniesEmployees,
   getOneCompanyEmployees
 
 };
 
 
  async function getOneCompanyEmployees(id) {
 
  const company = await Company.findByPk(id, { include: [Employee] });
    
   return company;
 
 
  }
 
 
 
 
 async function getAllCompaniesEmployees() {
 
  const companies = await Company.findAll({ include: [Employee] });
    
   return companies;
 
 
  }

Adding API Routes using the One-To-Many association

After defining and exporting the necessary functions, now we are going to use them in our routes. For that, you need to create a new file named apiRouter.js inside your project directory. This apiRouter will serve as the starting point for all your API routes.

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

 
  const  {getAllCompaniesEmployees, getOneCompanyEmployees }= require(‘./controller.js’);

apiRouter.js

//apiRouter.js
 
const express =require('express');
const apiRouter = express.Router();
const  {getAllCompaniesEmployees, getOneCompanyEmployees }= require('./controller.js');
 
 
 
  // Get all companies with employees
 
apiRouter.get('/', async (req, res, next)=>{
   try {
       const companies = await getAllCompaniesEmployees();
       res.status(200).json({ companies: companies});
   } catch(e) {
       console.log(e);
       res.sendStatus(500);
   }
});
 
apiRouter.param('companyId', async (req, res, next, companyId)=> {
   try{
       const company = await getOneCompanyEmployees(companyId);
       req.company = company;
       next(); // go to apiRouter.get('/:companyId')
   } catch(e) {
       console.log(e);
       res.sendStatus(404);
   }
});
 
 
 
 
// Get company with its employees
 
apiRouter.get('/:companyId',  (req, res, next)=>{
   res.status(200).json({company: req.company});
});
 

 

 
 
module.exports = apiRouter;

Now it’s time to mount the existing apiRouter into your server.js.

   require("dotenv").config();
const express =require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const apiRouter = require('./apiRouter');
 
const db = require('./db');
 
 
 const  sequelize = db.sequelize;
 
  
const app = express();
  
const PORT= process.env.APP_PORT;
  
app.use(bodyParser.json());
app.use(cors());
 
 
app.use('/apiRouter',apiRouter);
 
 
 
sequelize.authenticate()
.then(() => {
  console.log('Connection has been established successfully.');
})
.catch(err => {
  console.error('Unable to connect to the database:', err);
});
 
 
 
app.listen(PORT, ()=>{
    console.log(`server is listening  on ${PORT}`);
});
  
 
 
 
 
module.exports = app;

Testing our API routes with Postman

Before you start testing the API with Postman, you need first to start your node.js app.

1- Get a company with its employees.

To get a company with id: 1 from the Company table, 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- Get all Companies with their employees.

To get all companies from the Company table, use the following setting in your Postman:

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

Conclusion

The One-To-Many association is one of the essential Relationships in a database structure. This article intends to show you how to define and use this association in a MySQL database using Sequelize async/await. 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 see the rest of the CRUD API and how to write the controller using Sequelize async/await, read this article: How to use Sequelize async/await to interact with MySQL database in Node.js.

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.