JavaScript nodejs mysql with queries in a loop

Question

I feel a bit dumbstruck right now. I am fairly new to nodejs and javaScript and can't figure this one out. I guess it is because of the async nature of queries to mysql...

I made an example that shows my problem. I just want to cycle over a number of sql queries and do stuff with the results. for the sake of the example I just print out stuff. I know that I could use a single sql query like this "SELECT id, name FROM player WHERE id IN (1,2,3,4,5)" but this is not possible in the real application I am trying to write.

this is the relevant part of my nodejs app.js

var mysql = require("mysql");
var mysqlPool = mysql.createPool(conf.mysqlArbData);

for (var i = 0; i<5; i++){

    mysqlPool.getConnection(function(err, connection) {

        var detailSql = "SELECT id, name FROM player " +
            "WHERE id = "+i;
        if (err){
            throw err;
        }
        connection.query(detailSql, function(err, detailRows, fields) {
            connection.end();
            console.log("detailSql="+detailSql);
            if (err){
                console.log("can't run query=" + detailSql +"\n Error="+err);
            }
            else{

                console.log(detailRows[0].id + " " +detailRows[0].name);

            }

        });
    });

};

Now the output:

web server listening on port 3000 in development mode
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla

My question is, why do I get only the result for database entry with id=5? What needs to be changed in order to receive each individual result in the callback?

Answer 1

For node, you can use let statement. It limit i scope in for loop.

for (let i = 0; i<5; i++) {
  mysqlPool.getConnection(function(err, connection) {
    console.log(i);
  });
};

Answer 2

This is how I've been doing it. It's worked for UPDATE AND INSERT statements with 50,000+ items.

const mysql = mysql.createConnection({
  host     :   'HOST',
  user     :   'USER',
  password :   'PASSWORD',
  database :   'DATABASE_NAME',
  port     :   PORT,
  ssl      :   { ca : fs.readFileSync(process.cwd() + '/ssl/certs/ca-certificate.crt') },
});


async function processRequest(itemArray) {

  //pre-processing of array (if needed...)

  // initialize sql connection
  mysql.connect();

  const startProcess = await processItems(itemArray)
    .catch(e => {
      // end sql connection if there was an error running startProcess
      mysql.end();
      throw new Error(`Error @ startProcess: ${e}`);
    })

  // end sql connection after startProcess is finished
  mysql.end();
  
  // optional return of startProcess. This occurs after processItems is done.
  try {
  
    if (startProcess.status == 'success') {
      return {status: 'success', msg: 'function startProcess has compelted.';
    }

  }
  
  catch (e) {
      return {status: 'error', msg: 'function startProcess did not complete.', error: e;
  }


  function processItems(array){

    return new Promise((resolve,reject) => {

      for (let i in array) {

        // perform DB operations inside loop
        mysql.query(`UPDATE db SET ?`, array, (err, results, fields) => {
          // exit if there's any sort of error
          if (err) reject(err)
        })

      }

    // resolve promise after loop completes
    resolve({status: 'success'});

    })

  };

}

Here's the workflow:

1. mysql connection is opened (via mysql.connect())
2. processRequest is called (in my case, it's called from a Route)
3. the processItems function is called asynchronously
4. processItems returns a promise
5. processItems finishes where it either resolves the promise or rejects it
6. mysql connection is closed (via mysql.end())