Synchronous database queries with Node.js

Question

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

Answer 1

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.