How to use Sequelize async/await to interact with MySQL database in Node.js

Writing SQL queries to interact with large SQL databases can often be challenging to manage, but using ORMs (Object Relational Mapping ) can make these tasks easier and faster. ORMs are libraries that map software objects to database tables and convert all database queries into easy-to-use javascript objects so developers can interact with objects instead of writing any database queries. 

Another advantage of ORMs is their ability to make switching databases easy, smooth, and done by changing a value or two in the configuration file. (migration)

Sequelize is one of the popular ORMs created for Node.js; it is used to do all the heavy work in the background so you don t have to write raw SQL database queries and focus more on your node code. 

This article will show you how to use Sequelize async/await to interact with MySQL Database in Node.js by building a Node.js Restful CRUD API using Express, Sequelize, and MySQL server.

Related Articles:

• Best practices for optimizing MySQL database performance.
• ONE-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
• 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 ?

What is Sequelize?

Sequelize is a promise-based Node.js Object-Relational Mapper which supports all the popular SQL dialects: PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. It is essentially used to interact with MySQL databases in node.js and manage asynchronous functions and exceptions.

Sequelize supports an extensive range of data types; some of them are optional. The table below represents the most used; for a complete list, see Sequelize datatypes.

Note: In the rest of this article:

• We assume 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
• To have an idea on how to write MySQL queries using async/await, check this article: How to interact with MySQL database using async/await promises in node.js.

Creating and developing the Node.js API

To develop our app project named “sequelize async/await”, we will write our code in JavaScript for Node.js and use Express as the webserver for the API. In addition to that, we will use Sequelize ORM to connect to MySQL, define the database schemas and read/write data.

1- Setting Up Express.js Web Server

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

to avoid restarting your project every time you make changes into your project, install nodemon package and add it as a dev dependency:

Note: remember that installing nodemon is optional. You can skip this step if you want to.

 npm install --save-dev nodemon

Then add this line of code to your package.json; check the package.json file below to see where to add it exactly:

 
"start": "nodemon server.js "

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;

You have to 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": "sequelize async/await",
  "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",
    "nodemon": "^2.0.4",
    "save": "^2.4.0"
  }
}

Configuring MySQL Database & Initializing Sequelize

Configuration is a crucial step in the development process. To set up the configuration of our database and initialize the Sequelize ORM, we need to follow these steps:

Step1: Create our MySQL database

To use a database, you first need to create it then establish a connection with it. You have two options to create your database:

• Manually by using MySQL workbench interface 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 “sequelize async/await”, create a new JavaScript file and name it db.js.

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

 npm install --save mysql2

–> –> Then write SQL query to create our MySQL database named “Sequilize_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": "sequelize-db"
   }
  


};

Note: In a production mode, you can use the initialize() function to write the query to create the database because this function is executed only once on an API which means that the query to create our database will be executed only once. 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 .

Step2: Connection to the database and Initializing Sequelize

To connect to the Mysql database using Sequelize and mysql2 client, we need first to install the sequelize package.

 npm install --save sequelize

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

 
 const Sequelize = require('sequelize');

Next, we create a new instance of this class to connect the database by passing certain parameters and options.

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": "sequelize-db"
   },
   "pool": {
    "max": 5,
    "min": 0,
    "acquire": 30000,
    "idle": 10000
  }


};

The first parameter here is the name of the database you need to connect to. The second is the user name which is here root, and the third parameter is the password.

The fourth parameter is optional, but you can pass a JavaScript object here, and with this object, you can define certain parameters. For example, we use the dialect parameter to specify that we will use MySQL to interact with the database.

The second parameter is the host: you need to define your host if you are using a different host than the localhost because the default value is localhost.

To ease the asynchronous interactions with the database, we will use a connection pool to connect our database. If you want to know more about connection pooling and how it works, check this article: Why is Connection Pooling better than Single Connection?.

If you want to know more about all the sequelize available options and pool parameters, check HERE. 

Note that we save all the parameters values in the config.js file.

Sequelize Model Definition

Now that we have established a connection with our database, the next step is to create our models. In our database, we will have only one table Employee that means we will have one model Employee.

It s a good practice to create a separate file for each Model and save them in a models folder inside your project. So inside your project folder, please create a new folder and name it models. Then inside this folder, create a new file, employee.js, to define the Employee model.

The Employee table will have specific columns, and we need to define these columns as fields in the models, and each field must have a datatype supported by sequelize. 

 To define a schema for the Employee table in the MySQL Database, we use the define() method on the sequelize instance created in the db.js file. This will give us full access to perform CRUD (create, read, update, delete) operations on Employees in MySQL.

The define() method takes two parameters:

• The first one is the name of the table, which is “Employee” and
• The second one is an object describing each of the table columns. These columns will be generated automatically.

Our Employee table has an id, a name, position, email, and wage columns. The id in the Employee model will be the employee id the table, and it has the type of integer. We can define the datatype just by calling the data type on the sequelize class, and we can also provide some other information. For example, if I want my id to be an auto-incremented field and I won’t allow null values, I want to make it the primary key of the Employee table.

Let’s move to the name field: it’s going to have the type of string, and we are not going to allow null values as well and the same with position, email, and wage fields.

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;

Now we have our complete Employee model, so we would like to export it in order to initialize it in the db.js and use it in our controllers.

Creating the Employee object and defining all the parameters and fields will give you a clear vision and a better understanding of the actual ORM functionalities. We are literally mapping javascript objects into SQL tables.

Initializing the Sequelize Model and Convert it to SQL Table

To initialize the Employee model in the db.js file, we need first to import it from the Employee.js and define it as a method of the db object. Then use the sync() method provided by sequelize to sync our javascript model to the actual SQL table. The sequelize.sync() is used to synchronize all models at once automatically.

When we call the sync() method on the sequelize object, will actually create the table Employee for you because when we defined our model, we used the same sequelize object with the define() method so the sequelize object knows all your models and all your relations if any and will translate the Employee model into a table.

Note: If you want to drop a table that exists already and then recreate it with the new information, you need to force sync by passing an object inside the sync() method and setting the parameter force to true. But it 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 model and add it to the exported db object
    db.Employee = require('./models/employee');

    // sync all models with database
     sequelize.sync();

Finally, to test the connection and check if the database responds correctly, we use the sequelize.authenticate() method, then start our server from the terminal by running: npm start.

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





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;

We can use Workbench to confirm that we created our database and the Employee Table:

Creating Controller for CRUD functions using Sequelize async/await in node.js

Sequelize uses Promises extensively by chaining multiple .then() methods which can create a nesting problem. To avoid this issue and as an alternative to the chaining, we can use async/await syntax for all asynchronous calls made with Sequelize.

The async/await syntax is introduced in ES6 to allow multiple promises to be initiated and then resolved for values when required during the program’s execution. So async functions offer a novel way to write more readable and scalable code to handle promises.

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

Inside the project folder, create a new file and call it controller.js, and we will define all our controllers for the CRUD functions as async functions in controller.js because we can only use await inside an async function. Then we will export these functions in order to call them in our routers.

The following table presents the CRUD functions and the corresponding methods provided by Sequelize.

controller.js

 const db = require('./db');
 
const Employee = db.Employee;
 module.exports ={

  insertEmployee,
  updateEmployee,
  getOneEmployee,
  getAllEmployees,
  deleteEmployee

 };


 async function  insertEmployee(name, position, email, wage) {

  await Employee.create({name, position, email, wage});

}


 
  async function updateEmployee (name, position, email, wage, id){
  
    await Employee.update({name, position, email, wage}, { where: {id: id}});
  
 }

 
  async function getOneEmployee(id) {

  const employee = await Employee.findByPk(id);
   
   return employee;


  }




 async function getAllEmployees() {

  const employees = await Employee.findAll();
   
   return employees;


  }

  


   async function deleteEmployee(id) {

  const employee = await getOneEmployee(id);
   
   await employee.destroy();


  }

  

Adding Routes for handling CRUD functions using Sequelize async/await calls

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 async functions, so it doesn’t block the processing of the code below it.

 
const  { insertEmployee, updateEmployee, getOneEmployee, getAllEmployee, deleteEmployee}= require(‘./controller.js’);

apiRouter.js

  
  //apiRouter.js

const express =require('express');
const apiRouter = express.Router();
const  { insertEmployee, updateEmployee, getOneEmployee, getAllEmployees, deleteEmployee}= require('./controller.js');





// Get all employees

apiRouter.get('/', async (req, res, next)=>{
   try {
       const employees = await getAllEmployees();
       res.status(200).json({employees: employees});
   } catch(e) {
       console.log(e);
       res.sendStatus(500);
   }
});

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




// Get an employee

apiRouter.get('/:employeeId',  (req, res, next)=>{
   res.status(200).json({employee: req.employee});
});





// Create an employee

apiRouter.post('/',  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);
   }
});





// Update an employee

apiRouter.put('/:employeeId',  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;
       const employeeId= req.params.employeeId;
 
             if (!name || !position || !wage) {
               return res.sendStatus(400);
            }

       const employee =  await updateEmployee(name, position, email, wage, employeeId).then(()=>{return getOneEmployee(employeeId);});
       res.json({employee: employee});
       
   } catch(e){
       console.log(e);
       res.sendStatus(400);
   }
});




// Delete an employee

apiRouter.delete('/:employeeId', async (req, res, next)=>{
   try{
       const employeeId = req.params.employeeId;
       const response = await deleteEmployee(employeeId);
       return res.sendStatus(204);

   } catch(e){
       console.log(e);
   }
})



module.exports = apiRouter;

Don’t forget to mount the existing apiRouter into your server.js.

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;

In VS Code, Your project will look like:

Testing our API with Postman

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

1. Create an employee

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

• Path: apiRouter/
• Method: POST
• URL: http://localhost:3000/apiRouter/
• Change the body type to “raw”
• Change the format to “JSON”

2. Get an employee

To get an employee with id: 13 from the Employee table, use the following setting in your Postman:

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

3. Get all employee

To get all employees from the Employee table, use the following setting in your Postman:

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

4. Update an employee

To update an employee with an id:13 in the Employee table, use the following setting in your Postman:

• Path: apiRouter/:employeeId
• Method: PUT
• URL: http://localhost:3000/apiRouter/13
• Change the body type to “raw”
• Change the format to “JSON”

5. Delete an employee

To delete an employee with an id: 13 from the Employee table, use the following setting in your Postman:

• Path: apiRouter/:employeeId
• Method: DELETE
• URL: http://localhost:3000/apiRouter/13
• Change the body type to “raw”
• Change the format to “JSON”

Conclusion

I hope this article was useful and helped you understand how to use Sequelize to interact with your MySQL database and write the Sequelize calls using async/wait functions and showed you how they work together.

To see how to use the initialize() function to connect MySQL server, connect a database with Sequelize ORM and initialize the models, and attache them to the exported db object, go here: Example How to use initialize() Function in Node.js/Express API .

You might also like:

Best practices for optimizing MySQL database performance.

How to interact with MySQL database using async/await promises in node.js ?

How to Build a Complete API for User Login and Authentication using MySQL and Node.js.

Example How to use initialize() Function in Node.js/Express API .

Why is Connection Pooling better than Single Connection?.

How to create MySQL database using node.js.

How to store Session in MySQL Database using express-mysql-session.

How to Manage Session in Nodejs using Redis Store.

Complete Guide to Build a RESTful API with Node.js and Express.

Node.js + Redis – Complete API for Authentication, Registration and User Management.