Node js mysql stored procedure call in a for loop

Question

So, I'm currently using mysql npm package https://www.npmjs.com/package/mysql. I have a requirement where I'll have to call a stored procedure multiple times with the caveat that subsequent stored procedure calls depend on the previous call. Pseudo code will be as follows:

let mysql = require("mysql");

let mysqlPoolConnection = mysql.createPool({
      connectionLimit: 20,
      host: '0.0.0.0',
      port: '3306',
      user: 'user1',
      password: 'pwd',
      database: 'mysql_db'
});


for (let index = 0; index < params.length; index++) {
    let sp_ProcedureCall = "CALL sp_StoredProcedure(?, ?, ?, ?)";
    let sp_ProcedureParams = [params[index].firstParam, params[index].secondParam, params[index].thirdParam, params[index].fourthParam];
    // This is where the issue is. I'd like to call the stored procedure once and then once I get a response from it then make subsequent calls. Basically, depending on the previous stored procedure result, I decide whether I continue with subsequent calls or not.
    mysqlPoolConnection.query(sp_ProcedureCall, sp_ProcedureParams, (errorObj, responseObj, fieldsObj) => {

     }
}

Answer 1

NodeJS is asynchronous, meaning your for-loop iterates without waiting for the result of the call. You need to control the flow of your program if you want to "wait" for previous results.

Look at a library like async to enabled that type of control flow. From your description, eachSeries() might be a good fit - to run a function for each value in an array, and only run one at a time. (Or reduce, depending what you need - there are other options too)