Perform two or more queries in one request using node-mysql and ExpressJS

Question

tl;dr: What is the correct way to handle two or more asynchronous queries to a MySQL database using node-mysql with ExpressJS?

I am using ExpressJS with node-mysql to perform two separate, unrelated database queries on a MySQL database. Since the responses are asynchronous I am nesting the queries which means they end up happening one after the other.

This seems like an ugly, slow and generally bad approach, especially if I were to add a third or fourth query.

var mysql      = require('mysql');
var credentials = {...}

router.get('/api/url/', function (req, res) {
    return_data = {}
    var connection = mysql.createConnection(credentials);
    query1 = "SELECT column1 FROM table1 WHERE column2 = 'foo'";
    query2 = "SELECT column1 FROM table2 WHERE column2 = 'bar'";
    connection.query(query1, {}, function(err, results) {
        return_data.table1 = results;
        connection.query(query2, {}, function(err, results) {
            return_data.table2 = results;
            connection.end();
            res.send(return_data);
        });
    });
});

Answer 1

This is a great candidate for using async. Here's one way you could refactor this using it:

var mysql      = require('mysql');
var async      = require('async');
var credentials = {...}

router.get('/api/url/', function (req, res) {
    var connection = mysql.createConnection(credentials);
    var query1 = "SELECT column1 FROM table1 WHERE column2 = 'foo'";
    var query2 = "SELECT column1 FROM table2 WHERE column2 = 'bar'";

    var return_data = {};

    async.parallel([
       function(parallel_done) {
           connection.query(query1, {}, function(err, results) {
               if (err) return parallel_done(err);
               return_data.table1 = results;
               parallel_done();
           });
       },
       function(parallel_done) {
           connection.query(query2, {}, function(err, results) {
               if (err) return parallel_done(err);
               return_data.table2 = results;
               parallel_done();
           });
       }
    ], function(err) {
         if (err) console.log(err);
         connection.end();
         res.send(return_data);
    });
});

Obviously there are more elegant ways to do this, but this demonstrates the concepts nicely. The two queries are done in parallel, then once they have both completed we call the last function to close the connection and return the data.

If there is an error, we will immediately jump to the last function and call it, which will probably result in strange behavior when we send return_data anyway, so I wouldn't recommend using this code as-is.

If you'd like to learn more, check out the async documentation.