Express/NodeJS multiple mysql queries for the same route

Question

In a blog made in express/nodejs I am trying to display in the single article page both the article (working fine) and a set of 2 recommended articles. Unfortunately as you can see in the commented bit of the code it doesn't work (can't render the same template twice)

What would be the best approach in this case?

<!-- language: lang-js -->
router.get('/*', function(req, res, next) {

var slug=req.url.replace(/^\//,'').replace(/\/$/,'');
var bg = getRandomInt(5);

if(slug==''){
    connection.query('SELECT * FROM `pages` WHERE NOT slug = "about"', function (error, results, fields) {
res.render('index', { title: title, year: year, bg:bg, pages: results });
    });

}else{
    connection.query('SELECT * FROM `pages` where slug=?', [slug], function (error, results, fields)
             {
        if(results.length){ 

            res.render('page', { title: results[0].title, page:results[0] });
        }else{
            console.log(req);
            res.render('error',{url: 'http://'+req.headers.host+req.url});
        }
    });
    /* not working apparently you can't send the header of the template twice umhh
    connection.query('SELECT * FROM `pages` ORDER by RAND () LIMIT 2',  function (error, random, fields)
             {
                res.render('page', { pages:random}); 

             });
    */

}
 });

Answer 1

You can't render a page twice, otherwise you'll get Error: Can't set headers after they are sent to the client

What you need to do, is fetch the current article and the recommended pages, and render the page once you have the results from both queries.

In order to achieve that I used: Promise.all, and then performed a single res.render

router.get('/*', async (req, res, next) => {

    const slug = req.url.replace(/^\//, '').replace(/\/$/, '');
    const bg = getRandomInt(5);

    if (slug == '') {
        const results = await query('SELECT * FROM `pages` WHERE NOT slug = "about"');

        return res.render('index', {
            title: title,
            year: year,
            bg: bg,
            pages: results
        });

    }

    // Get current article and recommended pages
    // Promise.all returns an array where each entry has
    // the resolved value of the promise passed at that index
    const [article, recommended] = await Promise.all([
        query('SELECT * FROM `pages` where slug=?', [slug]),
        query('SELECT * FROM `pages` ORDER by RAND () LIMIT 2')
    ]);


    if (article.length) {

        // Render the article & recommended pages at once.
        res.render('page', {
            title: article[0].title,
            page: article[0],
            pages: recommended 
        });

    } else {
        console.log(req);
        res.render('error', {
            url: 'http://' + req.headers.host + req.url
        });
    }

});

// Query helper
// You can use promisify...
function query(statement, placeholders = []) {

    return new Promise((resolve, reject) => {

        connection.query(query, placeholders, (error, results) => {

            if(err)
                return reject(err);

            resolve(results);

        });
    });

}

Answer 2

The way you have it now

• Both queries will complete (and call their callback) at unrelated times
• res.render will be called multiple times, which does not work because it assumes all of the data is being sent in a single call. So it sends the HTTP headers, which cannot be sent twice.

Updated according to what it looks like you intended. Note this makes the order of the queries sequential, which may not be desirable. You'll want to use the async lib to help manage running them both at the same time and still consolidate the results:

router.get('/*', (req, res, next) => {
  const slug = req.url.replace(/^\//, '').replace(/\/$/, '');
  const bg = getRandomInt(5);

  if (slug == '') {
    return connection.query('SELECT * FROM `pages` WHERE NOT slug = "about"', (error, results, fields) => {
      res.render('index', { title: title, year: year, bg: bg, pages: results });
    });
  } else {
    return connection.query('SELECT * FROM `pages` where slug=?', [slug], (error, results, fields) => {
      if (results.length) {
        return connection.query('SELECT * FROM `pages` ORDER by RAND () LIMIT 2', (error, random, fields) => {
          if (error) {
            // handle error
          }
          // consolidate renders into a single call
          // adjust the template file accordingly
          return res.render('page', { title: results[0].title, page: results[0], pages: random });
        });
      } else {
        console.log(req);
        return res.render('error', { url: 'http://' + req.headers.host + req.url });
      }
    });
  }
});

Alternatively, consider using bluebird & async/await, this is just another style - to give you options that are new based on node 8+. In this one the queries are kicked off at the same time again.

const bluebird = require('bluebird');

router.get('/*', async (req, res, next) => {
  try {
    const slug = req.url.replace(/^\//, '').replace(/\/$/, '');
    const bg = getRandomInt(5);
    if (slug == '') {
      const results = await bluebird.fromCallback(cb => connection.query('SELECT * FROM `pages` WHERE NOT slug = "about"', cb));
      return res.render('index', { title: title, year: year, bg: bg, pages: results });
    } else {
      const [results, random] = await Promise.all([
        bluebird.fromCallback(cb => connection.query('SELECT * FROM `pages` where slug=?', [slug], cb)),
        bluebird.fromCallback(cb => connection.query('SELECT * FROM `pages` ORDER by RAND () LIMIT 2', cb))
      ]);
      if (results && results.length) {
        return res.render('page', { title: results[0].title, page: results[0], pages: random });
      } else {
        return res.render('error', { url: 'http://' + req.headers.host + req.url });
      }
    }
  } catch (e) {
    return res.render('error', { url: 'http://' + req.headers.host + req.url });
  }
});