Run multiple MySQL queries in this node.js function

Question

I have this node.js function which returns a Promise after executing a single MySQL query.

function deletePoint(PointObj, door_id) {

return new Promise(function(resolve, reject) {
    try {
        var connection = jonMySQL.getMySQL_connection();

        var mysql_query = '`DELETE FROM table1 WHERE user_id=? and door_id=?`';

        var mysql_query_placeholder_arr = [PointObj.user_id, door_id];

        connection.query(mysql_query, mysql_query_placeholder_arr, function(err, rows, fields) {
            if (err) {
                return reject(err);
            } else {
                resolve(rows);
            }
        });
    } catch (err) {
        reject(err);
    }
});

} The above function works fine. However, what if I would like the function to finish running 2 MYSQL queries?

The second query would look something like this;

var mysql_query2 = '`DELETE FROM table2 WHERE user_id=? and door_id=?`';
var mysql_query2_placeholder_arr = [PointObj.user_id, door_id];

How do I integrate this second MySQL query into the function such that the Promise will return only after both queries have been executed?

EDIT: It would be preferable if the answer provided can handle up to several queries (say, up to 5) without callback hell. Can someone provide an answer using async module?

Answer 1

I would suggest to create a generalized method to execute queries, you can modify it as per your requirement.

Note: running sequentially you have to manage the row that you are returning in resolve(rows). In parallel all the rows gets produced in final successCallback result parameter as array of objects.

function deletePoint(PointObj, door_id){  
  var query = {
    text : '`DELETE FROM table1 WHERE user_id=? and door_id=?`',
    placeholder_arr : [PointObj.user_id, door_id],

  };
  var query2 = {
    text : '`DELETE FROM table2 WHERE user_id=? and door_id=?`',
    placeholder_arr : [PointObj.user_id, door_id],

  };

  //do this if you want to execute the queries sequentially 
  mySQLQuery(query).then(mySQLQuery(query2)).then(successCallback).catch(errorCallback);

  //do this if you want to execute the queries parallely
  var query_arr = [mySQLQuery(query),mySQLQuery(query2),...];                   
  Promise.all(query_arr).then(successCallback,errorCallback)


  function successCallback(result){
    console.log('done',result);
  }
  function errorCallback(err){
    console.log('Error while executing SQL Query',err);
  }

}


function mySQLQuery(query) {

var connection = jonMySQL.getMySQL_connection();
return new Promise(function(resolve, reject) {
    try {
      connection.query(query.text, query.placeholder_arr, function(err, rows, fields) {
            if (err) {
                return reject(err);
            } else {
                return resolve(rows);
            }
        });
    } catch (err) {
        return reject(err);
    }
});

Besides, you can always use async module,

• async.parallel for parallel execution,
• async.waterfall for sequential execution with values passed from one function to other or
• async.series if no values need to be passed between sequential functions.