TDN169 TDN169 - 1 month ago 10
MySQL Question

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

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

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.

Comments