NodeJS mysql sync query

Question

I want to run sync query in nodejs like this...

for (var i in data){

conn.query("Select 1 from user where userid="+data[i].id,function(err,row){
     Rows.push(row);
});

}

console.log(Rows);

In this code block my rows all time going to null... I want to run sync query

Answer 1

You may not be able to use such a function synchronously in the thread-blocking sense (nor should you!) but you may get close to that if you use a promise version of the database connection (using Bluebird's promisifyAll or a specific promise version of mysql driver available on npm) and the new async/await syntax (or generator-based coroutines for platforms older than Node 7.x where async/await is not available - another option is to use Babel for transpilation).

Example - you would be able to use a code like this:

for (var i in data) {
  let row = await conn.query("Select 1 from user where 
  userid="+data[i].id);
  Rows.push(row);
}
console.log(Rows);

But if it can be run in parallel then something like this would be more efficient and shorter:

let Rows = await Promise.all(data.map(item =>
  conn.query("Select 1 from user where userid=" + item.id));
console.log(Rows);

For more details on that topic, see:

• Do async in a blocking program language way?
• Using async/await + Bluebird to promisifyAll
• try/catch blocks with async/await
• Using acyns/await in Node 6 with Babel
• jQuery: Return data after ajax call success

Note - it can be use only inside of functions declared with the async keyword.

Word of warning: your code may be vulnerable to SQL injection attacks. You should use placeholders instead of strings concatenations in your SQL. I didn't fix that aspect of your code - see those answers for more details:

• cannot use backtick when using nodejs 7.3.0
• Node.js - The multi part could not be bound
• How to escape mysql special characters with sockets.io/node.js/javascript
• Node js - Promise Rejection Warning when process a lot of data
• Is it possible to listen for object instantiation in Node.js?

Answer 2

You should use bluebird npm module to solve this. use npm install bluebird to install bluebird

    var Promise = require('bluebird');
    var Rows= [];
    data.forEach(function (obj) {
       conn.query("Select 1 from user where userid="+obj.id,function(err,row){
               Rows.push(row);
       });
      });
       return Promise.all(Rows);
    })

Answer 3

You can also use async module.

var async= require('async')
async.eachSeries(arr, function(index, callback){
  conn.query("Select 1 from user where userid="+index, 
    function(err,res) {
      if (err) {
        return console.error('error running query', err);
      }else{
       Row.push(res);
      }
    callback();
});
},function(err){
  if(err)
   return err;
   console.log("all queries executed")
});

Answer 4

You can make a wrapper that returns a Promise. Using await you can make it sync:

function promiseWrapper() {
    return new Promise((resolve, reject) => {
        conn.query("SQL_QUERY", queryCallback(resolve, reject);
    }
}

function queryCallback(err, row){
    return (resolve, reject) {
        resolve(row);
    }
}