Wait until DB query is done

Question

I'm pretty sure my issue is not directly related to the use of Koa or SQLite3, but more to the general use of JavaScript.

I have the following server.js:

var http = require('http');
var koa = require('koa');
var route = require('koa-route');
var app = koa();
var sqlite3 = require('sqlite3').verbose();
var dbFile = 'coc.db';
var db = new sqlite3.Database(dbFile);

var members = [];

var printMembers = function(name){

        db.each(query, [name], function(err, row){
            members.push(row);
        });

    return members;

}


app.use(route.get('/', index));


function *index() {

    this.body = printMembers("name");

}


app.listen(8008);
console.log('Koa listening on port 8008');

When I start the server and visit localhost:8008 for the first time, I only get an empty Array ([]) as a response. This is the content of the variable members. When I then reload I get, as expected, my SQLite query results.

The reason is probably because my query takes longer than the script is executed. Of course, the second time members is filled. But with the results of the query of the first time!
Also var members = [] should be declared within printMembers.

In my opinion I need something to make sure, that index() is waiting until db.each() is done. But how can I achieve this?

Answer 1

(Update: These days you'd use Koa 2.x which uses promises and first-class async/await in place of the co/yield example in this answer.)

Since the core abstraction in Koa 1.x is to use generators for control flow in routes, you must use yield to wait for something to finish.

Koa uses https://github.com/tj/co under the hood, so when trying to interop with libraries that aren't built for Koa, as a Koa developer you basically just need to figure out how you can modify/wrap asynchronous libraries so that you can yield them inside your route.

You can basically yield generators and promises.

The most general-purpose tool for the job is to wrap a function with a Promise. When creating a Promise (new Promise(fn)), you must pass it a function fn that takes two functions as arguments: resolve and reject.

You can often find Co-specific libraries where someone has already wrapped a popular library in a way that lets you yield its functions/methods.

For example, check out https://www.npmjs.com/package/co-sqlite3. (I have no idea how good it is, but fortunately it's easy to wrap stuff yourself if it turns out to be no good)

Alternatively, there are libraries that can take a callback-style module (i.e. most Node modules) and wrap it so that it returns promises which you can yield.

For example, bluebird has a promisifyAll function that does this, though to be honest I've never used bluebird.

// (Untested)
var Promise = require('bluebird');
var sqlite3 = require('sqlite3').verbose();
var db = Promise.promisifyAll(new sqlite3.Database(':memory:'));

function* printMembers(name) {
  var query = '...';
  return yield db.allAsync(query, [name]);
}

function* index() {
  // promisifyAll adds an {fnName}Async version of all the functions
  // to the module you call it on, so now we can call `allAsync`.

  var members = yield db.allAsync(query, ['name']);
  // or
  var members = yield printMembers('name');

  this.body = members;
}

But I'll give you some Promise-wrapping examples since it's such an essential tool to have in your kit.

Here I rewrite your printMembers function to return a Promise which you can then yield from your route:

var printMembers = function(name) {
  var query = '...';
  return new Promise(function(resolve, reject) {
    var rows = [];
    db.each(query, [name], function(err, row){
      if (err) return reject(err);
      rows.push(row);
    });
    resolve(rows);
  });
}

Though note that sqlite3 has a Database#all function that returns all the rows to you at once so you don't have to manually build an array:

var printMembers = function(name) {
  var query = '...';
  return new Promise(function(resolve, reject) {
    db.all(query, [name], function(err, rows){
      if (err) return reject(err);
      resolve(rows);
    });
  });
}

Now, here's what your route would look like:

function *index() {
  var members = yield printMembers('name');
  this.body = members;
}

If the promise hits that reject(err) path, then the promise is put in a rejected state and will throw the error which you can try/catch if you want where you yield the promise.

If the promise hits that resolve(members) path, then that's what's yielded and assigned to the members variable.