CODEWITHSUNDEEP
javascriptmysqlnode.js
luso
luso

Reputation: 3000

Approach to multiple MySQL queries with Node.js

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?

Upvotes: 26

Views: 56162

Answers (4)

programaths
programaths

Reputation: 891

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"
    }
}

Upvotes: 44

Hafiz Arslan
Hafiz Arslan

Reputation: 473

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).

Upvotes: 17

David
David

Reputation: 3184

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)

Upvotes: 4

Andrey Sidorov
Andrey Sidorov

Reputation: 25446

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);
    });
}

Upvotes: 11

Related Questions