Rendering view after multiple SELECT queries in Express

Question

I'm a bit new in Node.JS and Express framework and I have a great problem with the code below:

app.get('/student', function(req, res) {
    var dbRequest = 'SELECT * FROM Students WHERE IDCard = \'' + req.query['id'] + '\'';
    db.all(dbRequest, function(error, rows) {
        if(rows.length !== 0) {
            /* Save data. */
        }   
        else
            res.render('incorrect_student'); /* Render the error page. */
    });

    dbRequest = 'SELECT * FROM Groups WHERE Name = \'' + req.query['group'] + '\'';
        db.all(dbRequest, function(error, rows) {
            /* Add selected data to previous saved data. */
        }
    });
    res.render('student', {data: /* data from both queries above */});
});

As I have written in comment blocks, I would like to: execute first select query, save data from rows object, execute second query, again save received data in other object and then finally render the page passing data from both queries. My question is, what is the best way to do that?

I know that there is a problem caused by anonymous function. I have tried to fix the problem for over five hours as follows:

1. Clone rows object to another in anonymous function and then pass it to res.render. This solution dosen't work, because values of copied object are not visible (undefined) outside this function - only inside it.
2. Render the student page twice - it was really naive of course.
3. Change db.all command to db.prepare and then db.run - it wasn't working too.
4. Return object by the anonymous function and then assign it to external object defined between app.get and var dbRequest. The result was as described in 1st point.

I have also an idea to create "subpages" containig parts of student page, which need variables from only one query. The other idea is to use some other functions of db, req, res or app objects. But, as I said before, I'm new in Express and I don't know how to realize my above ideas.

Please note that it is impossible to join tables - in fact, I want to make 4-5 queries and then render my view. I'm using SQLite3 database.

Thank you very much for your help! I hope that you'll help me to solve my problem.

Answer 1

In your situation, I would split up the database calls into separate calls, and make use of the next middleware function.

It would looks something like:

function findStudent(req, res, next) {
    var dbRequest = 'SELECT * FROM Students WHERE IDCard = \'' + req.query['id'] + '\'';
    db.all(dbRequest, function(error, rows) {
        if(rows.length !== 0) {
            req.students = rows;
            return next();
        }

        res.render('incorrect_student'); /* Render the error page. */            
    });
}

function findGroups(req, res, next) {
    dbRequest = 'SELECT * FROM Groups WHERE Name = \'' + req.query['group'] + '\'';
        db.all(dbRequest, function(error, rows) {
            /* Add selected data to previous saved data. */
            req.groups = rows;
            next();
        }
    });
}

function renderStudentsPage(req, res) {
    res.render('student', {
        students: req.students,
        groups: req.groups
    });
}

app.get('/student', findStudent, findGroups,  renderStudentsPage);

When you GET /student, you first call findStudent. Once the db call is finished, it will either render an error page, or call next(). Calling next goes to the next function, findGroups, which will then call renderStudentsPage. You can store the data on the req object as you move down the line of functions.

Hope this helps, and here is more info: http://expressjs.com/guide/using-middleware.html

---

edit/note:

I did not mention it earlier, but if you pass in an argument when calling next(), you will trigger the error handling state. Convention dictates next() is left parameter-less unless you have met an error instance.

You want to separate out the UI rendering aspect from the database call, so going further, your code could look like:

function findStudent(req, res, next) {
    var dbRequest = 'SELECT * FROM Students WHERE IDCard = \'' + req.query['id'] + '\'';
    db.all(dbRequest, function(error, rows) {

        if (error || !rows.length) {
            return next(error);
        }

        req.students = rows;
        return next();
    });
}

And then elsewhere in your code you can handle rendering error pages.

Answer 2

I know this is an old question, but for anybody still having problems and using MongoDB instead this is what worked for me.

//index.js    
const express = require('express');
const router = express.Router();

function getData (req, res, next) {
  var db = req.db;
  var collection = db.get('usercollection');

  collection.find({}, {}, function(e, docs) {
    req.data = docs;
    return next();
  });
}

function getVendor (req, res, next) {
  var db = req.db;
  var collection = db.get('usercollection');

  collection.distinct("vendor", function(e, docs) {
    req.vendor = docs
    next();
  });
}

function getType (req, res, next) {
  var db = req.db;
  var collection = db.get('usercollection');

  collection.distinct("type", function(e, docs) {
    req.type = docs
    next();
  });
}

function renderData(req, res) {
    res.render('index', {
        data: req.data,
        vendor: req.vendor,
        type: req.type
    });
}

/* GET home page. */
router.get('/', getData, getVendor, getType, renderData);


module.exports = router;

Then inside your ejs file

//index.ejs 
<body>
<div id="app">
<h1>Choose a Vendor</h1>
<template v-for="vendor in values.vendor">
  <label :for="vendor">{{ vendor }}</label>
  <input type="radio" :value="vendor" v-model="flagpole.vendor">
</template>

<div>
  <template v-for="type in values.type">
    <label :for="type">{{ type }}</label>
    <input type="radio" :value="type" v-model="flagpole.type">
  </template>
</div>

</div>

<script type="text/javascript">
var vendor = <%- JSON.stringify(vendor) %>
var type = <%- JSON.stringify(type) %>

var vm = new Vue({
  el: '#app',
  data: {
    values: {
      vendor: vendor,
      type: type
    },
    flagpole: {
      vendor: '',
      type: ''
    }
  },