Run two or more queries in a single query using node-mysql and ExpressJS

Question

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

I am using ExpressJS with node-mysql to execute two separate, unrelated database queries in a MySQL database. Since the answers are asynchronous, I insert the requests, which means they end one by one.

This seems like an ugly, slow and generally bad approach, especially if I have 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

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

Remyp's answer needs to be changed as follows:

 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 do not have a representative to do this, but he was sent as a response.

Answer 2

This is a great candidate for using async . Here you can reorganize 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 it perfectly demonstrates the concepts. Two requests are executed in parallel, and then, once they are completed, we call the last function to close the connection and return the data.

If there is an error, we will immediately go to the last function and call it, which will probably lead to strange behavior when we send return_data anyway, so I would not recommend using this code as is.

If you want to know more, check out the asynchronous documentation .

Answer 3

From in this article :

For security reasons, the execution of multiple operator requests is disabled by default. To use multiple operator queries, you must first enable it when creating a connection, as shown below.

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

Once it is turned on, you can execute several operator 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 } });