Node MySql Callback for Multiple Queries

Question

I ran into an issue whilst attempting to create the logic to add rows to a new table I made on my MySql database. When adding a row I need to query the database 4 times to check other rows and to then add the correct value to the new row. I am using node.js and the mysql module to accomplish this. While coding I ran into a snag, the code does not wait for the 4 queries to finish before inserting the new row, this then gives the values being found a value of 0 every time. After some research I realize a callback function would be in order, looking something like this:

var n = 0;
connection.query("select...", function(err, rows){
    if(err) throw err;
    else{
        if(rows.length === 1) ++n;
    }
    callback();
});

function callback(){
    connection.query("insert...", function(err){
         if(err) throw err;
    });
}

Note: The select queries can only return one item so the if condition should not effect this issue.

A callback function with only one query to wait on is clear to me, but I become a bit lost for multiple queries to wait on. The only idea that I had would be to create another variable that increments before the callback is called, and is then passed in the callback function's arguments. Then inside the callback the query could be encapsulated by an if statement with a condition of this being the variable equaling the number of queries that need to be called, 4 for my purposes here. I could see this working but wasn't sure if this sort of situation already has a built in solution or if there are other, better, solutions already developed.

Gepser Hoil · Accepted Answer · 2015-07-21 05:10:57Z

5

You need async (https://github.com/caolan/async). You can do a very complex logic with this module.

var data = {} //You can do this in many ways but one way is defining a global object so you can add things to this object and every function can see it

firstQueryFunction(callback){
    //do your stuff with mysql
    data.stuff = rows[0].stuff; //you can store stuff inside your data object
    callback(null); 
}

secondQueryFunction(callback){
    //do your stuff with mysql
    callback(null);
}

thirdQueryFunction(callback){
    //do your stuff with mysql
    callback(null);
}

fourthQueryFunction(callback){
    //do your stuff with mysql
    callback(null);
}

//This functions will be executed at the same time
async.parallel([
    firstQueryFunction,
    secondQueryFunction,
    thirdQueryFunction,
    fourthQueryFunction
], function (err, result) {
     //This code will be executed after all previous queries are done (the order doesn't matter).
     //For example you can do another query that depends of the result of all the previous queries.
});

edited Jul 21, 2015 at 5:10

answered Jul 21, 2015 at 4:45

Gepser Hoil

4,2765 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

yanman1234 Over a year ago

I understand how this would solve my problem with the query executing before the others, but wouldn't this be overall slower than a method such as I described? Correct me if I'm wrong, but wouldn't the asynchronous calling of the queries and checking if any more need to be called be faster than calling the queries synchronously? I may have been unclear but the 4 select queries need not have any order to them

Gepser Hoil Over a year ago

I understand, I didn´t get the question then but with this information I am editing my answer ;)

Gepser Hoil Over a year ago

@yanman1234 there you go, I just edited my answer in order to run the 4 queries at the same time and wait for all of them to run the last function.

yanman1234 Over a year ago

Thank you for your help. Async does look like what will help me solve this issue and I'll begin looking into it.

Sean · Accepted Answer · 2015-07-21 04:58:45Z

As per Gesper's answer I'd recommend the async library, however, I would probably recommend running in parallel (unless the result of the 1st query is used as input to the 2nd query).

var async = require('async');

function runQueries(param1, param2, callback) {

    async.parallel([query1, query2, query3(param1, param2), query4],
        function(err, results) {
            if(err) {
                callback(err);
                return;
            }

            var combinedResult = {};
            for(var i = 0; i < results.length; i++) {
                combinedResult.query1 = combinedResult.query1 || result[i].query1;
                combinedResult.query2 = combinedResult.query2 || result[i].query2;
                combinedResult.query3 = combinedResult.query3 || result[i].query3;
                combinedResult.query4 = combinedResult.query4 || result[i].query4;
            }
            callback(null, combinedResult);
        });
}


function query1(callback) {
    dataResource.Query(function(err, result) {
        var interimResult = {};
        interimResult.query1 = result;
        callback(null, interimResult);
    });
}

function query2(callback) {
    dataResource.Query(function(err, result) {
        var interimResult = {};
        interimResult.query2 = result;
        callback(null, interimResult);
    });
}

function query3(param1, param2) {
    return function(callback) {
        dataResource.Query(param1, param2, function(err, result) {
            var interimResult = {};
            interimResult.query3 = result;
            callback(null, interimResult);
        });
    }
}

function query4(callback) {
    dataResource.Query(function(err, result) {
        var interimResult = {};
        interimResult.query4 = result;
        callback(null, interimResult);
    });
}

Query3 shows the use of parameters being 'passed through' to the query function.

I'm sure someone can show me a much better way of combining the result, but that is the best I have come up with so far. The reason for the use of the interim object, is that the "results" parameter passed to your callback is an array of results, and it can be difficult to determine which result is for which query.

Good luck.

I like your answer but I am not very sure if you can pass parameters like that. Personally I had use async.apply(query3,param1,param2)
I didn't know about async.apply, it looks as though that is the correct way to pass extra parameters and that my way is a hack.

Collectives™ on Stack Overflow

Node MySql Callback for Multiple Queries

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related