Forrest Wilbur Forrest Wilbur - 1 year ago 41
Node.js Question

In nodejs, how to run SQL queries so data is ready before page is rendered?

I'm trying to run three SQL queries to get data to push to the browser. I'm new to nodejs and Express (less than a week), so I might not have some basic concepts down as to how to approach problems like these. The solution is probably something really basic.

Anyway, I'll share my code, describe what I'm trying to have happen, and then describe what actually happens with a few notes about where I think I've gone wrong.

app.get("/", function(request, response) {
var rock_query = "SELECT COUNT(*) AS number FROM Votes WHERE shape_id = 1;";
var paper_query = "SELECT COUNT(*) AS number FROM Votes WHERE shape_id = 2;";
var scissors_query = "SELECT COUNT(*) AS number FROM Votes WHERE shape_id = 3;";
pool.query(rock_query, function(err, res) {
if(err) {
return console.error('error running query', err);
}
rock = res.rows[0].number;
console.log("Rock votes: " + rock);
});
pool.query(paper_query, function(err, res) {
if(err) {
return console.error('error running query', err);
}
paper = res.rows[0].number;
console.log("Paper votes: " + paper);
});
pool.query(scissors_query, function(err, res) {
if(err) {
return console.error('error running query', err);
}
scissors = res.rows[0].number;
console.log("Scissors votes: " + scissors);
});
response.render("home", {
rock: rock,
paper: paper,
scissors: scissors,
});
});


I'm trying to make it so when I visit the home page, the queries run to count the number of votes for each shape, write the count for each shape to the console, push the data so when the page renders it displays the number of votes.

When I go to the home page, it displays all zeroes for each shape when the page renders. The console shows the counted votes for each shape. I've figured out that because code in nodejs runs asynchronously, the page renders before the queries have finished.

I'm not sure how I should rewrite the above code to finish the queries before rendering the page. Help?

Answer Source

Using pg-promise, this can be done as follows:

app.get("/", function (request, response) {
    db.task(function (t) {
        var query = 'SELECT count(*) FROM Votes WHERE shape_id = $1';
        return t.batch([
            t.one(query, 1, a => +a.count),
            t.one(query, 2, a => +a.count),
            t.one(query, 3, a => +a.count)
        ]);
    })
        .then(function (data) {
            response.render('home', {
                rock: data[0],
                paper: data[1],
                scissors: data[2],
            });
        })
        .catch(function (error) {
            // handle the error here
        });
});

For more examples see Designing a RESTful API With Node and Postgres by Michael Herman.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download