By Rick


2011-07-06 13:49:59 8 Comments

I have a Node.js/Express app that queries a MySQL db within the route and displays the result to the user. My problem is how do I run the queries and block until both queries are done before redirecting the user to the page they requested?

In my example I have 2 queries that need to finish before I render the page. I can get the queries to run synchronously if i nest query 2 inside the 'result' callback of query 1. This however will become very convoluted when the number of queries increase.

How do I go about running multiple (in this case 2) database queries synchronously without nesting the subsequent query in the prior query's 'result' callback?

I've looked at the 'Flow control / Async goodies' in the Node modules and tried flow-js but I can't get it to work with the async queries.

Listed below are the 2 queries that I'm attempting to execute from the '/home' route. Can the Node experts explain the 'right' way to do this.

app.get('/home', function (req,res) {
    var user_array = [];
    var title_array = [];

    // first query
    var sql = 'select user_name from users';
    db.execute(sql)
        .addListener('row', function(r) {
            user_array.push( { user_name: r.user_name } );
        })
        .addListener('result', function(r) {
            req.session.user_array = user_array;
        });

    // second query
    var sql = 'select title from code_samples';
    db.execute(sql)
        .addListener('row', function(r) {
            title_array.push( { title: r.title } );
        })
        .addListener('result', function(r) {
            req.session.title_array = title_array;
        });

        // because the queries are async no data is returned to the user
        res.render('home.ejs', {layout: false, locals: { user_name: user_array, title: title_array }});
});

6 comments

@EscapeNetscape 2016-10-29 23:15:40

There are some solutions here, but in my opinion the best solution is to make the code synchronously in a very easy way.

You could use the "synchonize" package.

Just

npm install synchronize

Then var sync = require(synchronize);

Put logic which should be synchronous into a fiber by using

sync.fiber(function() { //put your logic here }

An example for two mysql queries:

var express = require('express');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var sync = require('synchronize');

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

db.connect(function(err) {
    if (err) {
        console.error('error connecting: ' + err.stack);
        return;
    }
});

function saveSomething() {
    var post  = {id: newId};
    //no callback here; the result is in "query"
    var query = sync.await(db.query('INSERT INTO mainTable SET ?', post, sync.defer()));
    var newId = query.insertId;
    post  = {foreignKey: newId};
    //this query can be async, because it doesn't matter in this case
    db.query('INSERT INTO subTable SET ?', post, function(err, result) {
        if (err) throw err;
    });
}

When "saveSomething()" is called, it inserts a row in a main table and receives the last inserted id. After that the code below will be executed. No need for nesting promises or stuff like that.

@Alex Craft 2012-05-11 07:48:02

You can use fibers to write pseudo-synchronous code with Node.JS take a look at these tests for DB https://github.com/alexeypetrushin/mongo-lite/blob/master/test/collection.coffee they are asynchronous but looks like synchronous, more details http://alexeypetrushin.github.com/synchronize

@Raynos 2011-07-08 04:51:01

Here's a really easy trick to handle multiple callbacks.

var after = function _after(count, f) {
  var c = 0, results = [];
  return function _callback() {
    switch (arguments.length) {
      case 0: results.push(null); break;
      case 1: results.push(arguments[0]); break;
      default: results.push(Array.prototype.slice.call(arguments)); break;
    }
    if (++c === count) {
      f.apply(this, results);
    }
  };
};

Example

Usage:

var handleDatabase = after(2, function (res1, res2) {
  res.render('home.ejs', { locals: { r1: res1, r2: res2 }):
})

db.execute(sql1).on('result', handleDatabase);
db.execute(sql2).on('result', handleDatabase);

So basically you need reference counting. This is the standard approach in these situations. I actually use this small utility function instead of flow control.

If you want a full blown flow control solution I would recommend futuresJS

@DigviJay Patil 2019-12-09 07:46:06

Please remove FutureJs link ! It is directing to wrong place.

@loganfsmyth 2011-07-11 02:43:21

I find that the async library is the best for things like this. https://github.com/caolan/async#parallel

I can't test this or anything, so forgive me if there are some typos. I refactored your query function to be reusable. So, calling queryRows will return a function that matches the format of the async module's parallel callback functions. After both queries are complete, it will call the last function and pass the result of the two queries as an argument, which you can read to pass to your template.

function queryRows(col, table) {
  return function(cb) {
    var rows = [];
    db.execute('SELECT ' + col + ' FROM ' + table)
      .on('row', function(r) {
        rows.push(r)        
      })
      .on('result', function() {
        cb(rows);
      });
  }
}

app.get('/home', function(req, res) {
  async.parallel({
    users: queryRow('user_name', 'users'),
    titles: queryRow('title', 'code_samples')
  },
  function(result) {
    res.render('home.ejs', { 
      layout: false,
      locals: {user_name: result.users, title: result.titles} 
    });
  });
});

@Rick 2011-07-11 21:08:19

I like this. The benefit to this answer is it doesn't need any reference counting. I'll definitely give it a try but probably move all the queries to stored procedures so that there is no need for building complex dynamic sql statements.

@loganfsmyth 2011-07-12 17:50:24

Letting a helper library implement all of the fancy asynchronous stuff like this is much easier. The library probable does either reference counting or fancy callback management, but you never have to worry about it.

@aliopi 2015-09-01 09:54:53

The answer I came to SO for. This should rank higher IMO, a solution to a common JS problem that many ppl. look for and instead receive walls of text on the philosophy of the callback.

@loganfsmyth 2015-09-01 15:06:00

These days I'd probably use Promises anyway now that the've been incorporated into ES6.

@Conrad Damon 2017-11-10 00:20:20

I'm using sync-mysql for a hobby project. The advantage is that the programming model is much simpler and cleaner. But it's much slower. That's fine; I'm not worried about performance, so not looking into pooling or anything like that. I also implemented it using the mysql package and Promises. That works and runs much faster, but the code is much heavier. I wound up leaning on Promise.all().

@jslatts 2011-07-06 22:58:24

The goal with node is not to care what order things happen in. This can complicate some scenarios. There is no shame in nesting callbacks. Once you are used to how it looks, you may find that you actually prefer that style. I do; it is very clear what order callbacks will fire. You can forgo the anonymous functions to make it less verbose if you have to.

If you are willing to restructure your code a bit, you can use the "typical" nested callback method. If you want to avoid callbacks, there are numerous async frameworks that will try and help you do this. One that you might want to check out is async.js (https://github.com/fjakobs/async.js). Example of each:

app.get('/home', function (req,res) {
    var lock = 2;
    var result = {};
    result.user_array = [];
    result.title_array = [];

    var finishRequest = function(result) {
        req.session.title_array = result.title_array;
        req.session.user_array = result.user_array;
        res.render('home.ejs', {layout: false, locals: { user_name: result.user_array, title: result.title_array }});
    };

    // first query
    var q1 = function(fn) {
      var sql = 'select user_name from users';
      db.execute(sql)
          .addListener('row', function(r) {
              result.user_array.push( { user_name: r.user_name } );
          })
          .addListener('result', function(r) {
              return fn && fn(null, result);
        });
    };

    // second query
    var q2 = function(fn) {
      var sql = 'select title from code_samples';
      db.execute(sql)
          .addListener('row', function(r) {
              result.title_array.push( { title: r.title } );
          })
          .addListener('result', function(r) {
              return fn && fn(null, result);
          });
    }

    //Standard nested callbacks
    q1(function (err, result) {
      if (err) { return; //do something}

      q2(function (err, result) {
        if (err) { return; //do something}

        finishRequest(result);
      });
    });

    //Using async.js
    async.list([
        q1,
        q2,
    ]).call().end(function(err, result) {
      finishRequest(result);
    });

});

For a one-off, I would probably just use a reference counting type approach. Simply keep track of how many queries you want to execute and render the response when they have all finished.

app.get('/home', function (req,res) {
    var lock = 2;
    var user_array = [];
    var title_array = [];

    var finishRequest = function() {
        res.render('home.ejs', {layout: false, locals: { user_name: user_array, title: title_array }});
    }

    // first query
    var sql = 'select user_name from users';
    db.execute(sql)
        .addListener('row', function(r) {
            user_array.push( { user_name: r.user_name } );
        })
        .addListener('result', function(r) {
            req.session.user_array = user_array;
            lock -= 1;

            if (lock === 0) {
              finishRequest();
            }
        });

    // second query
    var sql = 'select title from code_samples';
    db.execute(sql)
        .addListener('row', function(r) {
            title_array.push( { title: r.title } );
        })
        .addListener('result', function(r) {
            req.session.title_array = title_array;
            lock -= 1;

            if (lock === 0) {
              finishRequest();
            }
        });
});

An even nicer approach would be to simply call finishRequest() in each 'result' callback an check for non-empty arrays before you render the response. Whether that will work in your case depends on your requirements.

@Rick 2011-07-07 14:00:49

I always assumed that reference counting like this was a "hack" but it seems valid when callbacks are involved. Thanks for the example.

@jslatts 2011-07-07 14:53:15

It is a bit hacky and I would certainly invest in a more robust method if I was going to be writing that all over my code. If you are willing to restructure a bit, you can avoid that. I updated my answer with more examples.

@Rick 2011-07-07 16:08:21

Thanks for the second example with async.js It's definitely a cleaner pattern. Thanks!

@andyengle 2012-01-19 23:21:48

Thanks very much for your helpful explanation! I ended up using caolan's async library, but your explanation got me started in the right direction. Thanks!

@Andrey Sidorov 2011-07-06 22:41:17

option one: if all your queries related to each other, create stored procedure, put all your data logic into it and have a single db.execute

option two: if your db uses one connection then commands a guaranteed to be executed serially and you can use this as async helper

db.execute(sql1).on('row', function(r) {
   req.session.user_array.push(r.user);
});
db.execute(sql2)
.on('row', function(r) {
   req.session.title_array.push(r.title);
})
.on('end'), function() {
   // render data from req.session
});

@Rick 2011-07-07 13:08:21

So does that mean if I use database connection pooling with the async helper the queries could still run in parallel?

@Andrey Sidorov 2011-07-08 00:31:19

yes. If two queries went to two different connections you cant predict which one is finished first. On MySql side each connection is processed in separate thread. Simple example: pool.query('select sleep (2)'); pool.query('select sleep(1)') - most likely second query will go to separate link and most likely it is finished one second before first query. If you send queries using one link they are processed serially (and probably in the same thread on mysql side) one after another.

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] Check synchronously if file/directory exists in Node.js

  • 2010-12-19 11:19:43
  • Ragnis
  • 781794 View
  • 1247 Score
  • 15 Answer
  • Tags:   node.js fs

17 Answered Questions

[SOLVED] Writing files in Node.js

19 Answered Questions

[SOLVED] How to exit in Node.js

  • 2011-03-10 21:30:05
  • Bryan Field
  • 931315 View
  • 1905 Score
  • 19 Answer
  • Tags:   node.js

32 Answered Questions

39 Answered Questions

[SOLVED] How do I debug Node.js applications?

26 Answered Questions

[SOLVED] How to get GET (query string) variables in Express.js on Node.js?

6 Answered Questions

[SOLVED] Read environment variables in Node.js

17 Answered Questions

[SOLVED] How to decide when to use Node.js?

Sponsored Content