express.js with MySQL

Question

I just started learning node.js... Here is an example of my code. In this example everything works.

But, I have a question. How to make several SQL queries and send results to template?

At the moment I can only do this for one query...

Thanks.

 //connection database
var connection = mysql.createConnection({
host     : 'localhost',
user     : 'root',
password : 'password',
database : 'test'
});

connection.connect(function (err){
 if (err) throw err; 
    console.log('Database connected . . . \n\n');

});

router.get('/', function(req, res, next) {
  var sql = 'SELECT * FROM `test`';
   connection.query(sql, function(err, rows, field){
     if (err) throw err; 
     res.render('index', {
        data: rows
      })
   });
 });

You will want to declare an array that you can call `allResults = []`. Then, you do each of your queries either one after the other or in parallel (with promises or async.js library) and push the acquired rows in the 'allResults' variable like so `allResults.push(...rows)`. — Azami, Feb 21 '19 at 14:53
Sure, just put two snippets showcasing both uses (promises and async.js). — Azami, Feb 22 '19 at 01:52

Azami · Accepted Answer · 2019-02-22T03:05:37.160

Here is an answer following my comment since you mentioned you couldn't figure it out on your own.

First snippet uses promises, a quick helper function, but no external library. Second snippet uses the external async.js library and is a bit more callback-heavy. Both of them tackle the problem assuming we want the queries to be executed in parallel.

With promises

router.get('/', async function(req, res, next) {
    var queries = ['SELECT * FROM `test`', 
                   'SELECT * FROM `test2`', 
                   'SELECT * FROM `test3`'];

    var allResults  = [];
    /*transform our `query` array into an array of promises, then 
      await the parallel resolution of all the promises*/
    var allQueryRows = await Promise.all(queries.map(query => promiseQuery(query))); 

    /*'allQueryRows' is an array of rows, so we push each of those
       into our results*/
    allQueryRows.forEach(function(rows){
        allResults.push(...rows);
    });

    res.render('index', {
        data: allResults
    })
});

function promiseQuery(sqlQuery){
    return new Promise((resolve, reject) => {
        connection.query(sqlQuery, function(err, rows, field){
            if(err) 
                return reject(err);
            resolve(rows);
        })
    })
}

With callbacks and async.js

const async = require('async');

router.get('/', function(req, res, next) {
    var queries = ['SELECT * FROM `test`', 
                   'SELECT * FROM `test2`', 
                   'SELECT * FROM `test3`'];

    var allResults  = [];

    async.each(queries, function(sqlQuery, callback){
        connection.query(sqlQuery, function(err, rows, field){
            if(err)
                throw err;
            allResults.push(...rows);
            callback();
        });
    }, function(){
        res.render('index', {
            data: allResults
        });     
    });
});

express.js with MySQL

1 Answers1

With promises

With callbacks and async.js