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.

Answer 2

Keep in mind that in order for the queries to run in parallel, you will have to leverage connection pooling. Only a single query can be run at a time on a mysql connection. See https://github.com/felixge/node-mysql/#pooling-connections for examples.

Remyp's answer would need to be modified to the following:

var mysql      = require('mysql');
var async      = require('async');
var credentials = {connectionLimit: 10,...}

router.get('/api/url/', function (req, res) {
    var pool = mysql.createPool(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) {
           pool.query(query1, {}, function(err, results) {
               if (err) return parallel_done(err);
               return_data.table1 = results;
               parallel_done();
           });
       },
       function(parallel_done) {
           pool.query(query2, {}, function(err, results) {
               if (err) return parallel_done(err);
               return_data.table2 = results;
               parallel_done();
           });
       }
    ], function(err) {
         if (err) console.log(err);
         pool.end();
         res.send(return_data);
    });
});

I would comment on his post, but I don't have the rep to do so yet so posted as an answer.

Answer 3

From this article:

For security purpose, by default, executing multiple statement queries is disabled. To use multiple statement queries, you should first enable it while creating a connection as shown below.

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

Once it is enabled, you can execute multiple statement queries as shown below in your connection.query.

connection.query('select column1; select column2; select column3;',    
function(err, result){
  if(err){
      throw err;
  }else{
      console.log(result[0]);       // Column1 as a result
      console.log(result[1]);       // Column2 as a result
      console.log(result[2]);       // Column3 as a result
  }
});