Result from sqlite-3 DB does not push to Javascript array

Question

I am working with a NodeJS application which fetches film names and description from an Sqlite-3 file when user send a GET to /films. There seems to be a thing which I am missing while pushing the object to an array. I don't get what I am missing. The object does not get pushed to the array and always shows empty [] when I res.json() it back as response.

app.get('/films', (req, res) => {
    let db = new sqlite3.Database('./data.db', sqlite3.OPEN_READWRITE,  err => {
        if (err) return console.error(err.message)
        console.log('DB connected')
    })
    
    var films = []
    db.serialize(() => {
        db.each('select * from film', (err, row) => {
            if (err) return console.log(err.message)
            // console.log(row.name + '\t' + row.description)
            films.push({
                "name": row.name,
                "description": row.description
            })
        })
    })
    res.json(films)
    
    db.close(err => {
        if (err) return console.error(err.message)
        console.log('DB coonnection closed')
    })
})

Answer 1

Your issue is that Express returns response object before the database retrieves any values. To fix this you’d normally move it inside the callback function.

In your case though db.each() method actually accepts 2 functions. The 1st one is callback which runs after each value is retrieved and wouldn’t be any help at all. The 2nd one is called complete in documentation and does exactly what you need.

Here’s the full reference in docs for db.each() from node-sqlite3 wiki.

With that you could write your code this way:

db.each(
  'select * from film',
  (err, row) => { /* Does normal stuff */ }),
  (err, num) => { /* Sends response to client */
    res.json(films);
    console.log(`Retrieved ${num} films`); // (Just to show what the 2nd argument does)
  }
);

Keep in mind that if your film database is not particularly huge docs actually recommend to use db.all method instead.