26

I'm a newbie both on event/callback style programming and NodeJS. I'm trying to implement a little http server which serves ddbb data using node-mysql module.

My problems comes from queries structuration. Since there are often queries that require results from previous queries to run, I am not able to run all them simultaneously (asynchronously) and I am forced to wait some results.

My first approach was to run all the non-dependant queries at the same time and then loop until all of them have set a flag up saying I'm done so I can continue with the dependant (synchronized) ones, but I don't know if this is the correct approach.

Something like this:

function x(){
    var result_for_asynch_query_1 = null
    var result_for_asynch_query_2 = null

    mainLoop(){
        // call non-dependant query 1
        // call non-dependant query 2

        // loop until vars are != null

        // continue with queries that require data from the first ones
    }
}

//for each browser request
httpServer{
     call_to_x();
}.listen();

This way I can save some time in the final result since I don't wait all responses in a serial way but just to the longest one.

Is there a common way to do this? Any design pattern I'm not following?

Improve this question

4 Answers 4

Reset to default
44

One should avoid the pyramid of doom:

var express = require('express');
var Q = require('Q');
var app = express();

app.get('/',function(req,res){
    var mysql      = require('mysql');

    var connection = mysql.createConnection({
        host     : 'localhost',
        user     : 'root',
        password : ''
    });

    connection.connect();

    function doQuery1(){
        var defered = Q.defer();
        connection.query('SELECT 1 AS solution',defered.makeNodeResolver());
        return defered.promise;
    }

    function doQuery2(){
        var defered = Q.defer();
        connection.query('SELECT 2 AS solution',defered.makeNodeResolver());
        return defered.promise;
    }

    Q.all([doQuery1(),doQuery2()]).then(function(results){
        res.send(JSON.stringify(results[0][0][0].solution+results[1][0][0].solution));
        // Hint : your third query would go here
    });

    connection.end();

});

app.listen(80);
console.log('Listening on port 80');

This sample show a result which depend of 2 independent computed values. Each of these values a queried in doQuery1 and doQuery2. They are executed in sequence, but asynchronously.

Next you can see Q.all(... which basically call the "then" callback on success. Within that callback, the calculation is done.

Using promises (details : Github Q: promise for Javascript and wikipedia ) permit to make your code cleaner, separate computation and handling of results and move things arround.

Look at how easy it would be to add "doQuery3" as prerequisit for your calculation !

And bellow the "package.json" bellonging to the sample code:

{
    "name": "hello-world",
    "description": "hello world test app",
    "version": "0.0.1",
    "private": true,
    "dependencies": {
        "express": "3.2.0",
        "q": "0.9.3",
        "mysql":"2.0.0-alpha7"
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, I could not for the life of me figure out the syntax to handle the control flow of multiple mysql nodejs calls!
I don't believe those queries would be executed in parallel.
In fact, documentation clearly says they aren't: "Every method you invoke on a connection is queued and executed in sequence." npmjs.com/package/mysql#introduction
In fact, this solution clearly works in more generic cases. And in fact, it avoid the pyramid of doom or passing through englobing scope for data sharing.
ES6 has promises built-in now
17

Another solution is to concatenate all statements, ending each with a semicolon. For example, to select from multiple tables you could use this query:

var sql = 'select * from user; select * from admin;'

Then, you can use only one connection to execute the multiple statements:

var connection = mysql.createConnection({multipleStatements: true}) connection.query(sql)

Note: Multiple statements is disabled by default to prevent SQL injection. Be sure to properly escape all values (see docs).

Improve this answer

Comments

11

try to think other way (there is good introduction on async flow howtonode.org)

var db = get_link_or_pool();

do_queries( callback ) {
    db.query(sql1, function(err, res1) {
        if (err) {
             callback(err);
             return;
        }
        // use res1 ...
        db.query(sql2, function(err, res2) {
             if (err) {
                 callback(err);
                 return;
             }
             callback(null, res2); // think 'return'
        }
    });
}

request_handler(req) {
    do_queries( function(err, result) {
        if(err)
            report_error(err);
        else
            write_result(req, result);
    });
}
Improve this answer

8 Comments

And if you have 100 db queries, I'd suppose you'd nest 100 funcs? Nasty and ugly as all hell. If there is no better answer, then the answer is, you shouldn't (rather than you can't).
if you have 100 queries there is something wrong in your db design
no, unfortunately to actually run queries in parallel you need to run each in its own connection. The way mysql protocol designed is you need to wait result from previous command before you are allowed to send next one. When you do db.query(sql1); db.query(sql2); sql2 is in the commands queue until sql1 complete
@AndreySidorov, if you have 100 queries there is something wrong in your db design. Apparently, you have never been involved in huge projects.
@Green I'm not talking about "100 different queries in a project", I'm talking about "100 queries where each next depend on a result from previous one"
|
4

I found the below very helpful in getting over this problem:

Taken from http://book.mixu.net/node/ch7.html - Lots of other great examples here!! 

function async(arg, callback) {
  console.log('do something with \''+arg+'\', return 1 sec later');
  //replace setTimeout with database query  
  setTimeout(function() { callback(arg * 2); }, 1000);
}
// Final task (return data / perform further operations)
function final() { console.log('Done', results); }

// A simple async series:
var items = [ 1, 2, 3, 4, 5, 6 ];
var results = [];
function series(item) {
  if(item) {
    async( item, function(result) {
      results.push(result);
      return series(items.shift());
    });
  } else {
    return final();
  }
}
series(items.shift());

"Take a set of items and call the series control flow function with the first item. The series launches one async() operation, and passes a callback to it. The callback pushes the result into the results array and then calls series with the next item in the items array. When the items array is empty, we call the final() function." (from http://book.mixu.net/node/ch7.html)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.