node.js and express passing sqlite data to one of my views

Question

In my app.js I have the following to try to retrieve data from a sqlite database and pass it to one of my views:

app.get("/dynamic", function(req, res) {
    var db = new sqlite3.Database(mainDatabase)
    var posts = []

    db.serialize(function() {
        db.each("SELECT * FROM blog_posts", function(err, row) {
            posts.push({title: row.post_title, date: row.post_date, text: row.post_text})
        })
    })

    res.render("dynamic", {title: "Dynamic", posts: posts})
})

Can someone tell me what I am doing wrong here. The posts array seems to stay empty nomatter what.

EDIT I was following a tutorial that explained that though the plugin has async, this method is not asynchronous

Here is a quote from the tutorial

Despite the callbacks and asynchronous nature of Node.js, these transactions will run in series, allowing us to create, insert, and query knowing that the statement prior will run before the current one does. However, sqlite3 provides a "parallel" wrapper with the same interface, but runs all the transactions in parallel. It just all depends on your current circumstances.

Answer 1

the db calls are likely asynchronous. Which means you are rendering before they return with their data.

You need to figure out how to get one callback from your query, and render your template in that callback.

It looks like you want a second complete callback passed to db.each() (Thanks, Jonathan Lonowski, for the tip!)

var posts = [];
db.serialize(function() {
    db.each("SELECT * FROM blog_posts", function(err, row) {
        posts.push({title: row.post_title, date: row.post_date, text: row.post_text})
    }, function() {
        // All done fetching records, render response
        res.render("dynamic", {title: "Dynamic", posts: posts})
    })
})

The idea is the render in the last callback of any asynchronous code, that way you have everything you need.

---