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

ONE-TO-ONE relationship is one of the standard associations in a relational database. MySQL does not have predefined options to represent the one-to-one relationship. Still, we can implement this association by adding the primary Key from one of the tables as a foreign key into the other table.

This article will show you how to represent and implement a One-To-One association in a MySQL database using async/await requests to interact with the relational database in a Node.js API by following the same steps in the previous articles dedicated to one-to-many and many-to-many associations.

Related Articles:

This article will cover:

How to define a One-To-One association in MYSQL?
Implementation of our MySQL/Node.js boilerplate API
Implementation of ONE-TO-ONE association in MySQL using async/await
Writing the One-To-One association queries using promises
Adding API Routes into apiRouter.js
Testing our API routes with Postman
1- Route for Getting all employees with settings
2- Route for Getting an employee settings
Conclusion
You might also like:

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

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 primary key in each one of two tables, then
Adding the primary key from the first table into the second table as a single unique foreign key.

In this post, we 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, position, and wage. The Employee_settings table contains the employee settings for our application, such as the color theme or auto-login.

Employee Table:

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

Employee_settings table:

id	employeeId	theme	autoLogin
1	1	dark	0
2	4	light	1

You can tell 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	wage	theme	autoLogin
1	employee1	manager	8000	light	0 (0 for false)
2	employee2	engineer	6000
3	employee3	designer	5000
4	employee4	architect	6000	dark	1 (1 for true)
5	employee5	engineer	6000
6	employee6	designer	5000

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

Implementation of our MySQL/Node.js boilerplate API

This section will implement the example given in the previous section and define the one-to-one association between the two tables Employee and Emplyee_settings in a Node.js and MySQL boilerplate API named “oneToOne-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": "oneToOne-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 “oneToOneDB” with two tables named “Employee“ and “Employee_settings”.

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-ONE association in MySQL using async/await

To implement a one-to-one relationship, we need two tables. That means we need to create two tables Employee and Employee_settings. We add the employee_id as a foreign key to the Employee_settings 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)' , function (err, result) {
     if (err) throw err;
     console.log("Employee table created");
   }
);

Query to create Employee_settings table :

 
//create Employee_settings table
pool.query('create table if not exists Employee_settings (' +
'id int auto_increment primary key,' +
'theme varchar(255) not null,' +
'autoLogin int default 0,'+
  'employee_id int not null,'+
'foreign key (employee_id) references  Employee(id))' , function (err, result) {
    if (err) throw err;
    console.log("Employee_settings table created");
  }
);

Writing the One-To-One association queries using promises

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

The following function AllEmployeesSettings() selects all employees with their settings from Employee_settings table.

 
db.AllEmployeesSettings= ()=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT * FROM Employee JOIN  Employee_settings ON Employee.id = Employee_settings.employee_id', (error, employees)=>{
            if(error){
                return reject(error);
            }
            return resolve(employees);
 
        })
    })
}

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

  db.employeeSettings= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Employee.id, Employee.name, Employee_settings.theme, employee_settings.autoLogin FROM Employee JOIN  Employee_settings ON Employee.id = Employee_settings.employee_id WHERE Employee.id= ? ', [id], (error, employeeSettings)=>{
            if(error){
                return reject(error);
            }
            return resolve(employeeSettings);
 
        })
    })
}

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 Employee_settings table
pool.query('create table if not exists Employee_settings (' +
'id int auto_increment primary key,' +
'theme varchar(255) not null,' +
'autoLogin int default 0,'+
  'employee_id int not null,'+
'foreign key (employee_id) references  Employee(id))' , function (err, result) {
    if (err) throw err;
    console.log("Employee_settings table created");
  }
);




   



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

db.AllEmployeesSettings= ()=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT * FROM Employee JOIN  Employee_settings ON Employee.id = Employee_settings.employee_id', (error, employees)=>{
            if(error){
                return reject(error);
            }
            return resolve(employees);
 
        })
    })
}







db.employeeSettings= (id)=>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT Employee.id, Employee.name, Employee_settings.theme, employee_settings.autoLogin FROM Employee JOIN  Employee_settings ON Employee.id = Employee_settings.employee_id WHERE Employee.id= ? ', [id], (error, employeeSettings)=>{
            if(error){
                return reject(error);
            }
            return resolve(employeeSettings);
 
        })
    })
}














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');
 

 


// Get all employees with settings.
   
apiRouter.get('/employees-settings', async (req, res, next)=>{
    try {
        const employees = await db.AllEmployeesSettings();
        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 employeeSettings = await db.employeeSettings(employeeId);
        req.employeeSettings = employeeSettings;
        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({employeeSettings: req.employeeSettings});
 });
   



 
 
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 data in the example above. 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 ?

1- Route for Getting all employees with settings

To get all employees with settings, use the following setting in your Postman:

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

2- Route for Getting an employee settings

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

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

Conclusion

This article showed you how to represent and implement a One-To-One 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 many-to-many associations, go ahead and check these two articles: