Chained MariaDB queries using promises & async-await in Node.js

Intro

Imagine you are building a web application in React.js with a dashboard that displays user information, such as the nickname of the logged in user and a list of his/her/them registered friends. This information would be stored in two tables (users & friends) in a MariaDB database.

Common problem

As a newbie to Node.js you might stumble upon the problem that your axios.get() request to retrieve data has to execute multiple chained queries, which depend on the output of the previous query.



For more experienced developers it is well known that Node.js code runs on a single thread architecture using an event loop which allows to execute one operation at a time. To prevent code blocking issues the execution thread does not wait for an operation to complete before moving on to the next piece of code. Before Node version 7.6 callbacks were the only way to run consecutive operations. A callback is a function passed as an argument inside another function, which can invoke the outer function to complete an action. Using a nested chain of callbacks cannot only get messy, it can easily cause issues to subsequent operations.


To find a way out of the commonly called Callback hell the async-await concept was introduced. A function with the async keyword guarantees that its returned object is a promise. The await keyword suspends the execution of code until the awaited promise is resolved. You might now understand the possibility of an asynchronous function but wrapping your head around its implementation can be still baffling.

How did I retrieve my dashboard data?

The backend server was set up on port 3001 using the Node.js/Express framework. As mentioned before an axios.get() method was used to make the HTTP request to the backend server, passing the email address of the user as URL parameter.

const getDashboardInfo = () => {
axios.get("http://localhost:3001/dashboard", {
            params: {
                user: user_email,
            }
      })
      .then(response => {
            setFriendList(response.data.friendList);
            setUserNickname(response.data.user[0].nickname);
      })
};

An asynchronous function with three promises was used to i) get information, e.g. ID, of logged in user from the table users, ii) get all IDs of friends for logged in user from the table friends, and iii) get information of all friends, e.g. names, from the table users. The list of friends with their user data and the user nickname was passed as response to the Frontend. If a user doesn’t have friends (yet), a Boolean object with the value false was passed.

const getDashboardData = (req, res) => {
const queryObject = url.parse(req.url, true).query;
(async () => {
        // Call 1st function to get information for logged in user
        const user = await getUser(queryObject.user);

        // Call 2nd function to get user’s friends id
        const friendIdList = await getFriendsId(user[0].id);

        // Formatting friendIdList-array
        let formattedIdList = [];
        friendIdList.forEach((item, index) => {
            formattedIdList = [...formattedIdList, item.friend_id]
        });

        if (formattedIdList.length > 0) {
            // Call 3rd function to get data of user’s friends
                    const friendList = await getFriendsList(formattedIdList);
                    res.send({friendList: friendList, user: user});
        } else {
                    res.send({friendList: false, user: user});
        }
})()
};

A promise can take one of three states: pending, fulfilled or rejected. The initial state is pending until the resolve or reject methods are invoked. If an error occurs, a rejected promise would be returned with the thrown error. If a promise is resolved then its state becomes fulfilled, and the resolved value is treated as the return value of the await expression.

// 1st function to query information for logged in user 
const getUser = (user) => {
return new Promise((resolve, reject) => {
            dbConnection.query('SELECT * FROM users WHERE email = ?', user,
            (err, result) => {
                    return err ? reject(err) : resolve(result)
            })
})
};

// 2nd function to query user’s friends id
const getFriendsId = (id) => {
return new Promise((resolve, reject) => {
            dbConnection.query('SELECT * FROM friends WHERE user_id = (?)',[id],
            (err, result) => {
                    return err ? reject(err) : resolve(result)
            })
})
};

// 3rd function to query data of user’s friends
const getFriendsList = (idList) => {
return new Promise((resolve, reject) => {
            dbConnection.query('SELECT * FROM users WHERE id IN (?)', [idList],
            (err, result) => {
                    return err ? reject(err) : resolve(result)
            })
})
};

The above solution worked for me. How would you solve it?