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

ONE-TO-ONE relationship is one of the Sequelize standard associations. It is a combination of two fundamental relationships hasOne and belongsTo between two models related by a single foreign key.

This article will show you how to represent and implement a One-To-One association in a relational database using Sequelize async/await to interact with MySQL database in a Node.js API. To do that, we will follow the same steps in the previous articles dedicated to one-to-many and many-to-many associations:

This article will cover:

What is a ONE-TO-ONE relationship in a Relational database?
The One-To-One association in Sequelize
Implementation of ONE-TO-ONE association using Sequelize async/await in Node.js.
MySQL database connection using Sequelize ORM
Defining the One-To-One association using Sequelize
Controllers for the One-To-One association using Sequelize async/await
Adding Routes to our API using async functions
Testing our API routes with Postman
Conclusion
You might also like:

What is a ONE-TO-ONE relationship in a Relational database?

One-To-One relationship is the least common association in data modeling because this type of relationship is rare in real life. It is generally used when dealing with sensitive data or information related to security that needs to be stored separately.

We say that a one-to-one relationship exists between two tables(A and B) if a single record in table A is related to one and only one record in table B. And a single record in table B is related to one and only one record in table A. We relate these two tables by adding a single foreign key to one of them.

This post will explain this association by presenting a simple example of the one-to-one relationship between two tables, Employee and Employee_settings.

The employee table contains the employee information, such as name, email, position, and wage. The Employee_settings table contains the employee settings for our application, such as the color theme or auto-login.

Employees Table:

employeeId	name	position	email	wage
1	employee1	manager	[email protected]	8000
2	employee2	engineer	[email protected]	6000
3	employee3	designer	[email protected]	5000
4	employee4	architect	[email protected]	6000
5	employee5	engineer	[email protected]	6000
6	employee6	designer	[email protected]	5000

Settings:

employeeId	theme	autoLogin
1	dark	true
4	light	true

You can tell me that we can combine the two tables by adding this information to the Employee table and avoiding the Settings table. But, if most employees prefer to keep the default settings, we will have many empty fields in the Employee table. So splitting the employee data into two tables will simplify the Employee table management and allow us to store the optional data in a separated table Settings.

employeeId	name	position	email	wage	theme	autoLogin
1	employee1	manager	[email protected]	8000	light	true
2	employee2	engineer	[email protected]	6000
3	employee3	designer	[email protected]	5000
4	employee4	architect	[email protected]	6000	dark	true
5	employee5	engineer	[email protected]	6000
6	employee6	designer	[email protected]	5000

The One-To-One association in Sequelize

As we said in the introduction, the ONE-TO-ONE relationship is one of the Sequelize standard associations, and it is a combination of two basic relationships hasOne and belongsTo between two models related by a single foreign key.

Defining a one-to-one association between two models means adding a foreign key to the table containing the optional data by defining this table as the source model in the belongsTo relationship and the target model in the hasOne association.

So to represent the relationship between the two models Employee and Setting presented in the section above, we use the following associations:

  
  const Employee = sequelize.define('Employee', {/* ... */})
  const  Setting = sequelize.define('Setting', {/* ... */})

  Employee.hasOne(Setting);
  Setting.belongsTo(Employee);

To use a different name for the foreign key, we add the foreignKey option to one of the two associations hasOne or belongsTo. This foreignKey can be a string or an object in case we want to use a standard definition for the foreign key column.

Using a string for the foreignKey:

  
  const Employee = sequelize.define('Employee', {/* ... */})
  const  Setting = sequelize.define('Setting', {/* ... */})
 
 // first option
  Employee.hasOne(Setting, {
  foreignKey: 'new_name'
});
 Setting.belongsTo(Employee);

// OR second option
  
  Employee.hasOne(Setting);
  Settings.belongsTo(Employee, {
  foreignKey: 'new_name'
});

Using an object for the foreignKey:

  const Employee = sequelize.define('Employee', {/* ... */})
  const  Setting = sequelize.define('Setting', {/* ... */})
 
 // first option
  Employee.hasOne(Setting, {
  foreignKey: {
    name: 'new_name',
   type: DataTypes.UUID,
   allowNull: false
  }
});
 Setting.belongsTo(Employee);

// OR second option
  
  Employee.hasOne(Setting);
  Setting.belongsTo(Employee, {
  foreignKey: {
    name: 'new_name',
   type: DataTypes.UUID,
   allowNull: false
  }
});

Implementation of ONE-TO-ONE association using Sequelize async/await in Node.js.

In the rest of this article, we will implement the example given in the previous section and define the one-to-one association between the two models Employee Settings in a Node.js and MySQL boilerplate API. We will use Sequelize ORM to connect the database, define the database tables, and interact with the database tables.

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:

Our boilerplate for the node.js server uses express as a web server, and we name our project “oneToOne-example”. 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 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}`);
});

Inside the project directory oneToOne-example, create a .env file to save all the environment variables related to your API.

.env

 
 APP_PORT = 3000

package.json

  //package.json
    
{
  "name": "oneToOne-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"
  }
}

MySQL database connection using Sequelize ORM

To create our MySQL database and establish a connection with it by initializing the Sequelize ORM, we need to follow these steps:

1- Create a MySQL Database

To create our database, we write a query in the db.js file, but first, we need to create this file:

In your project folder “oneToOne-example”, create a new JavaScript file and name it db.js. Then, install the mysql2 package inside the project directory and import it into db.js. Make sure it’s Mysql2 and not mysql.

  npm install --save mysql2

Next,open your db.js file and write the following SQL query to create the database named “oneToOne_db“.

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}\`;`);

Please don’t forget to save the configuration data and database connection options for the MySQL database and 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": "oneToOne_db"
   }
     
   
   
};

Notes:

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?.
If you’re going to create the database manually using the MySQL workbench interface, check HERE or go Here if you are a mac user.

2- Connecting MySQL database using Sequelize

To connect the database created in the previous step, we use Sequelize and mysql2. To do that, we have to install and import the sequelize package.

  npm install --save sequelize

In the db.js file, we require the Sequelize class from the sequelize package, and 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": "oneToOne_db"
   },
   "pool": {
    "max": 5,
    "min": 0,
    "acquire": 30000,
    "idle": 10000
  }
   
   
};

For a more clean code in production mode, you can use the initialize() async function to write the queries and connect the database. 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.

3- Sequelize models definition: Employee and Setting

To implement our example and perform the one-to-one association, we need to define and initialize the two models: Employee and Setting. Then, Sequelize will automatically create the corresponding tables in the MySQL database by calling the sequelize.sync().

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 setting.js), one for each model.

We will use the define() method on the sequelize instance created already to define the two tables in the MySQL Database. If you want 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;

setting.js

  
 const { Sequelize, DataTypes } = require("sequelize");
   
const db = require('../db.js');
   
   
const  sequelize = db.sequelize;
   
const Setting = sequelize.define("Setting", {
   
  id: {

    type: DataTypes.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true,
 
  },

 theme: {
   
   type: DataTypes.STRING,
   allowNull: false,
   
 },
autoLogin: {
   
   type: DataTypes.BOOLEAN,
   allowNull: false,
   
 }
   
 
   
});
   
   
   
   
module.exports = Setting;

4- Synchronize the Sequelize Models using the sequelize.sync()

We use the sequelize.sync() to convert the two models to tables in the MySQL database. To do that, we need first to import our Employee and Setting models and then initialize and attach them to the db object and then call the sequelize.sync().

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 Setting models and add them to the exported db object
     
   const Employee = require('./models/employee');
    
    const Setting = require('./models/setting');


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

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.

We need to populate our tables, Employees, and Settings to continue our demonstration. 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?.

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;

Defining the One-To-One association using Sequelize

To let Sequelize know about the one-to-one association between the two models, we define the hasOne and belongsTo relationships between these tables. Then Sequelize will automatically create a foreignKey in the Employee_settings table. But if you want to customize the foreign key, check the section above to see the different options.

 
  Employee.hasOne(Setting);
  Setting.belongsTo(Employee);

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 Settings models and add them to the exported db object
     
   const Employee = require('./models/employee');
    
    const Setting = require('./models/setting');


  Employee.hasOne(Setting);
  Setting.belongsTo(Employee);

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

Note: To validate any addition (like adding relationships between tables), we pass an object inside the sync() method and set the parameter force to true.

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

This will drop the existing tables and then recreate them with the new information. Note that this is not recommended for production mode.

The database in the Workbench: you can see that Sequelize created a foreign key EmployeeId in the Settings.

Controllers for the One-To-One association using Sequelize async/await

We will write our controllers for the one-to-one relationship inside a separate file called controller.js. So inside the project folder, please create a new file and name it controller.js. We will define our controllers as async functions. Then we will export these functions in order to call them in our router later.

For more details about async/await functions and promises, use these links:

In the controller.js, we will define the following functions:

The first ones ( insertEmployee(), insertEmployeeSettings() ) insert data to the Employee and Settings tables by using the create() method.

 async function  insertEmployee(name, position, email, wage) {
  
  await Employee.create({name, position, email, wage});
  
 }
 
 
async function  insertEmployeeSettings(theme, autoLogin, EmployeeId) {
  
  await Setting.create({ theme, autoLogin, EmployeeId});
  
}

The following function findAllEmployees() is to get all the employees from the Employee table and settings from the Settings table.

  
  sync function findAllEmployees(){
 
  const employees= await Employee.findAll({
    
    include: [ 
      {
        model: Setting,
         attributes: ['theme', 'autoLogin'],
         
   
      },
    ]
  });
   
  return employees;
   
   
   
  }

The findEmployeeSettings() takes the EmployeeId as a parameter and returns the employee with all his settings.

 
  sync function findEmployeeSettings(id){
 
    const employee= await Employee.findByPk(id, {
      include: [ 
        {
          model: Setting ,
           
          attributes: ["theme", "autoLogin"],
          
     
        },
      ],
    });
     
    return employee;
     
     
     
    }

controller.js

  
  const db = require('./db');
   
const Employee = db.Employee;
const Setting = db.Setting;
 

 
 module.exports ={
  
   insertEmployee,
   insertEmployeeSettings,
   findAllEmployees,
   findEmployeeSettings
   
  
 };
  
 
 
 
 async function  insertEmployee(name, position, email, wage) {
  
  await Employee.create({name, position, email, wage});
  
 }
 
 
async function  insertEmployeeSettings(theme, autoLogin, EmployeeId) {
  
  await Setting.create({ theme, autoLogin, EmployeeId});
  
}
     





async function findAllEmployees(){
 
  const employees= await Employee.findAll({
    
    include: [ 
      {
        model: Setting,
         attributes: ['theme', 'autoLogin'],
         
   
      },
    ]
  });
   
  return employees;
   
   
   
  }





  async function findEmployeeSettings(id){
 
    const employee= await Employee.findByPk(id, {
      include: [ 
        {
          model: Setting ,
           
          attributes: ["theme", "autoLogin"],
          
     
        },
      ],
    });
     
    return employee;
     
     
     
    }

Adding Routes to our API using async functions

In this section, we will use the async functions we have just defined in the controller.js to write our routes. But first, we need to create a new file named apiRouter.js inside the project directory. This apiRouter will serve as the starting point for all your API routes.

To pull the async functions from controller.js, we add this line of code to our apiRouter.js.

  

const  { insertEmployee, insertEmployeeSettings,  findAllEmployees,  findEmployeeSettings }= require('./controller.js');

apiRouter.js

 

   //apiRouter.js
 
const express =require('express');
const apiRouter = express.Router();
const  { insertEmployee,
    insertEmployeeSettings,
    findAllEmployees,
    findEmployeeSettings
     }= require('./controller.js');
 
 
 
 
    // Create an employee
 
apiRouter.post('/employee',  async (req, res, next)=>{
    try{
        const name = req.body.employee.name;
        const position = req.body.employee.position;
        const email = req.body.employee.email;
        const wage = req.body.employee.wage;
        console.log(name);
              if (!name || !position || !wage) {
                return res.sendStatus(400);
             }
  
        const employee =  await insertEmployee(name, position, email, wage).then(() => res.json({ message: 'Employee created.' }));
         
         
  
    } catch(e){
        console.log(e);
        res.sendStatus(400);
    }
 });








 // add an employee settings
 
apiRouter.post('/employee-settings',  async (req, res, next)=>{
    try{
        const EmployeeId = req.body.settings.employeeId;
        const theme = req.body.settings.theme;
        const autoLogin = req.body.settings.autoLogin;
        
        
              if (!EmployeeId || !theme || !autoLogin) {
                return res.sendStatus(400);
             }
  
        const settings =  await insertEmployeeSettings( theme, autoLogin, EmployeeId).then(() => res.json({ message: 'settings created.' }));
         
         
  
    } catch(e){
        console.log(e);
        res.sendStatus(400);
    }
 });

 




 
   
  
 
   // Get all employees including settings.
  
 apiRouter.get('/employee-settings', async (req, res, next)=>{
    try {
        const employees = await findAllEmployees();
        res.status(200).json({employees: employees});
    } catch(e) {
        console.log(e);
        res.sendStatus(500);
    }
 });
 



// Get an employee settings

 apiRouter.param('employeeId', async (req, res, next, employeeId)=> {
    try{
        const employee = await findEmployeeSettings(employeeId);
        console.log(employee);
        req.employee = employee;
        next(); // go to apiRouter.get('/employee-settings/:employeeId')
    } catch(e) {
        console.log(e);
        res.sendStatus(404);
    }
 });
  
  
  
  
 apiRouter.get('/employee-settings/:employeeId',  (req, res, next)=>{
    res.status(200).json({employee: req.employee});
 });






 
 
module.exports = apiRouter;

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 change the sequelize.sync({force: true}) to sequelize.sync() in the db.js file, then start your node.js app and insert the following data into the database tables. If you want to see how to add routes to your API to do that, you can check here: How to add Routes to insert data into MySQL database-related tables in Node.js API?.

1- Create an employee.

To create and insert an employee to Employees table, use the following setting in your Postman:

Path: apiRouter/employee
Method: POST
URL: http://localhost:3000/apiRouter/employee
Change the body type to “raw”
Change the format to “JSON”
The json object: {“employee” : {“name”: “employee1″,”position”: “manager”,”email”: “[email protected]”,”wage” : 8000}}

2- Insert an Employee settings.

To add an employee settings to the Settings table, use the following setting in your Postman:

Path: apiRouter/employee-settings
Method: POST
URL: http://localhost:3000/apiRouter/employee-settings
Change the body type to “raw”
Change the format to “JSON”
The json object: {“settings” : { “theme”: “light”,”autoLogin”: true,”employeeId”: 4}}

3- Get all employees including settings

To Get all employees including the settings, use the following setting in your Postman:

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

4- Get an employee settings

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

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

Conclusion

This article was a follow-up of two previous articles dedicated to one-to-many and many-to-many associations. We learned how to represent and implement a One-To-One association in a relational database using Sequelize async/await to interact with MySQL database in a Node.js API.

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.