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?

You can write second query in first's callback and return then — abdulbarik, Oct 06 '16 at 10:33

score 2 · Accepted Answer · edited Oct 07 '16 at 09:17

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.

Nivesh, Verified to be correct answer. Made some edits to your code which has some syntax error. Please approve. Thanks. — guagay_wk, Oct 07 '16 at 09:17
I would suggest, rather that creating connection every time you call mySQLQuery function, you should try implementing connection [pooling](https://www.npmjs.com/package/mysql#pooling-connections). Check out how I structured the code while using PostgreSQL [myRepo](https://github.com/nivesh2/DaysNote/blob/master/server/functions/database/dbAccess.js). — Nivesh, Oct 07 '16 at 09:24
May I ask what is the advantage and disadvantage, if any, of using connection pooling versus creating connection every time MySQLQuery function is called? — guagay_wk, Oct 07 '16 at 09:26
Connecting and disconnecting from a database is an expensive operation. Thus, connection pools are used to enhance the performance of executing commands on a database. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database. You get more about it by googling. — Nivesh, Oct 07 '16 at 09:36

abdulbarik · Answer 2 · 2016-10-06T10:51:25.987

Try like this

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, rows1, fields) {
                if (err) {
                    return reject(err);
                } else {
                    var mysql_query2 = 'DELETE FROM table2 WHERE user_id = ? and door_id = ?';
                    var mysql_query2_placeholder_arr = [PointObj.user_id, door_id];
                    connection.query(mysql_query, mysql_query_placeholder_arr, function(err, rows2, fields) {
                        if (err) {
                            return reject(err);
                        } else {
                            resolve({
                                rows1: rows1,
                                rows2: rows2
                            });
                        }
                    });

                }
            });
        } catch (err) {
            reject(err);
        }
    });
}

Suggestion

It's not the better approach to writing multiple queries in a callback.

To resolve such type of case use async module with parallel or waterfall methods based on scenario

Thanks. Upvoted. There is a high possibility that this function would grow to cover several queries. I am afraid that the code can grow to become quite ugly if the function needs to handle, say, 5 queries. — guagay_wk, Oct 06 '16 at 10:38
I recommended to use `async` module which has better way to resolve such type of case https://github.com/caolan/async — abdulbarik, Oct 06 '16 at 10:40

Run multiple MySQL queries in this node.js function

2 Answers2