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

6 min readJan 4, 2021

Async functions are one of the most important features of JavaScript. It allows multiple asynchronous operations to run concurrently. Writing asynchronous code using callback functions can be hard and complex (causes callback hell issue). That is why promises are introduced to reduce this complexity. Even so, when performing multiple asynchronous operations in a sequence, promises should be nested or composed by chaining multiple .then() methods. To avoid this nesting problem and as an alternative to the chaining, the async/await syntax is introduced in ES6 to allow multiple promises to be initiated and then resolved for values when required during the execution of the program. So async functions offer a novel way to write more readable and scalable code to handle promises.

The combination async/await syntax, introduced in node.js version 8, and promises makes writing MySQL queries in node.js easier with a better maintainability. This article intent is to show you how to use this combination to interact with MySQL database in node.js in a practical way.

This article will cover:

Creating Connection Pool

We are using a connection pool to connect our database. A Connection pool can manage multiple connections which will help us and ease the asynchronous interaction with the database. If you want to know more about connection pooling and how it works you can check this article: Why is Connection Pooling better than Single Connection?.

ql = require('mysql');
 
const pool = mysql.createPool({
    connectionLimit: 10,    
    password: “yourDBpassword”,
    user: “yourusername”,
    database: “yourDBname”,
    host: “locahost”,
    port: “yourDBport”});

How to use promises with MySQL queries

Using asynchronous code to Interact with a MySQL database makes the process of accessing data in your database more natural and easier for humans to understand. That means you can send multiple queries to the database in parallel and while waiting for the results of your queries, some synchronous tasks can be executed such as loading DOM elements and more.

To create a promise, we use the “new” constructor keyword. The promise object has two parameters, one for success and one for fail: (resolve, reject). It returns a value with resolve callback and an error object with the reject.

Promises have three states:

Pending: This is the initial state of the promise before the operation begins
Resolved or Fulfilled: The operation was completed
Rejected: The operation failed and throws an error

Example of writing a SELECT query using promise

To write our SELECT query that selects all elements from a table named DB_table in your MySQL database, we use an arrow function that returns a promise object. we will use this function later in the async function as the returned promise.

The promise will be in pending mode until the execution of the query. When the query execution is done and we get the DB_table elements, the Promise will be resolved to a success. But if there is an error in the query and we don’t get the data, then the Promise will be in the rejected state and return an error object.

const mysql = require('mysql');
 
const pool = mysql.createPool({
    connectionLimit: 10,    
    password: “yourDBpassword”,
    user: “yourusername”,
    database: “yourDBname”,
    host: “locahost”,
    port: “yourDBport”
 
}); 
SelectAllElements = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT * FROM DB_table ',  (error, elements)=>{
            if(error){
                return reject(error);
            }
            return resolve(elements);
        });
    });};

How to use async/await promises to interact with MySQL in node.js

An asynchronous JavaScript function is created with the “async” keyword before the function name, or before () when using an arrow function. An async function always returns a promise: If the async function returns a value, then this promise is resolved with the same value, but if the async function throws an error, the promise is rejected with the thrown error as value.

What makes async functions special and more useful is the use of wait keyword. The wait operator is only available inside async function. It is used to ensure that all promises returned in the async function are synchronized and wait for each other to execute.

Node.js is an asynchronous environment, so most Input/Output operations are asynchronous and handled in a non-blocking way. That means database access is also an asynchronous action, which allows other operations to execute while waiting for the queries results.

NOTE: in this article we show you a simple example of async/wait function, but it is good to know that wait is more useful when you have to run multiple queries and they must be serialized and executed in a sequential flow.

//Example of queries in a sequential flow
queryPromise1 = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('query1',  (error, results)=>{
            if(error){
                return reject(error);
            }
            return resolve(results);
        });
    });
};
queryPromise2 = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('query2',  (error, results)=>{
            if(error){
                return reject(error);
            }
            return resolve(results);
        });
    });
};
queryPromise3 = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('query3',  (error, results)=>{
            if(error){
                return reject(error);
            }
            return resolve(results);
        });
    });
};
async function sequentialQueries () {
try{
const result1 = await queryPormise1();
const result2 = await queryPormise2();
const result2 = await queryPormise3();
// here you can do something with the three results
} catch(error){
console.log(error)
}}

In case you want to execute your queries in parallel so you can use with await the promise returned by Promise.all (Promise.all function takes a list of promises in a pre-defined order and returns another promise).

//Example of queries in parallel
queryPromise1 = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('query1',  (error, results)=>{
            if(error){
                return reject(error);
            }
            return resolve(results);
        });
    });
};
queryPromise2 = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('query2',  (error, results)=>{
            if(error){
                return reject(error);
            }
            return resolve(results);
        });
    });
};
queryPromise3 = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('query3',  (error, results)=>{
            if(error){
                return reject(error);
            }
            return resolve(results);
        });
    });
};
async function sequentialQueries () {
const promise1= queryPromise1();
const promise2= queryPromise2();
const promise3= queryPromise3();
const promises =[promise1, promise2, promise3];
try{
const result = await Promise.all(promises);
// you can do something with the result
} catch(error){
console.log(error)
}}

Example of writing a GET route using async/await promises

To write a GET route, we define the callback function as an async function to let JavaScript knows we are using async function and be able to use wait operator later. We use the wait operator to make sure that program execution waits until the promise is resolved or rejected.

If the promise is resolved, DB_table elements are returned as the promise value. To extract the query results, we store the promise value in a local variable named results.

If the promise is rejected, then an error is thrown. To catch this error and handle the promise rejection, we use the try…catch block.

NOTE: In async functions, try…catch block works perfectly and it is used to handle shared error.

//apiRouter.js
 
const express =require('express');
Const apiRouter = express.Router();
 const mysql = require('mysql');
 
const pool = mysql.createPool({
    connectionLimit: 10,    
    password: “yourDBpassword”,
    user: “yourusername”,
    database: “yourDBname”,
    host: “locahost”,
    port: “yourDBport”
 
}); 
SelectAllElements = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT * FROM DB_table ',  (error, elements)=>{
            if(error){
                return reject(error);
            }
            return resolve(elements);
        });
    });
};
apiRouter.get('/', async (req, res, next)=>{
    try {
        const resultElements = await getAllElements();
        res.status(200).json({elements: resultElements}); // send a json response
    } catch(e) {
        console.log(e); // console log the error so we can see it in the console
        res.sendStatus(500);    }});

To see the INSERT, UPDATE and DELETE corresponding promises click Here. And go Here if you want an example of post, put and delete routes.

Conclusion

Hope this article was useful and helped you to understand how to use async/wait function with promises to interact with your MySQL database and showed you how they work together.

You may also like to see a complete example of an API in which we use async/await promises to interact with MySQL database: Building a complete RESTful API with node.js.