Mysql serial execution nodejs

Question

mysqldb.js

const mysqlDatabasePool = mysql.createPool({
    connectionLimit: 50,
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USERNAME,
    password: process.env.MYSQL_PASSWORD,
    port: process.env.MYSQL_PORT,
    database: process.env.MYSQL_DATABASE
});

mysqlDatabasePool.getConnection({
  //Basic sanity checking code here
});

mysqlDatabasePool.query = util.promisify(mysqlDatabasePool.query);

module.exports = mysqlDatabasePool;

employeeController.js

var pool = require("mysqldb");

pool.query("SELECT CategoryID, CategoryIcon FROM Categories WHERE CategoryName = ?", [categoryName], function(error, results, fields) {
    if(results !== null && results.length > 0) {

        for(var i = 0; i < results.length; i++) {
             outputData[i] = results[i];

             var subCategories = pool.query("SELECT SubcategoryID FROM Subcategories WHERE CategoryID = ?", [results[i].CategoryID], function(error, results, fields) {
                 return results;
             });

             if(subCategories.length > 0) 
                  outputData[i]["Subcategories"] = subCategories;
             else
                  outputData[i]["Subcategories"] = null;
        }
    }
});

The content of outputData[i]["Subcategories"] always turns out to be null while it should have collection of Subcategories since it is always available. I tried implementing await/async by placing await ahead of the second pool.query and making the first pool.query closure function async.

How should this be handled?

Answer 1

The function passed as parameter to pool.query is a callback. That means you cannot know when it will be executed.

You can use async/await to make your call block until pool.query gives you a result.

You may need to use util.promisify on pool.query to make it return a Promise.

try {
   const results = await pool.query("SELECT CategoryID, CategoryIcon FROM Categories WHERE CategoryName = ?", [categoryName]);
   if(results !== null && results.length > 0) {

        for(var i = 0; i < results.length; i++) {
             outputData[i] = results[i];

             const subCategories = await pool.query("SELECT SubcategoryID FROM Subcategories WHERE CategoryID = ?", [results[i].CategoryID]);

             if(subCategories.length > 0) 
                  outputData[i]["Subcategories"] = subCategories;
             else
                  outputData[i]["Subcategories"] = null;
        }
    }
} catch(error) {
  console.log(error)
  // Deal with the error
}

Above en example of how to use async/await syntax. Remember to use the keyword await, the surrounding function must be marked as async.