Asynchronous Database Problems with Node.js

Question

I am trying to query a database, running on a node server without express using sqlite3, to check if an element is in the database. Upon finding if this element exists, it will then go on to access a different table with this information.

var username;
checkSessionID(usersSessionID, function(temp) {
    username = temp[0].username;
});
var db = new sql.Database("Pete's FCRs.db");
var ps = db.prepare("select * from users where username = ?", errorFunc);
ps.all(username, function(err, rows) {
    if (err) throw err;
    console.log(rows);
});
ps.finalize();
db.close();

function checkSessionID(sessionID, callback) {
var db = new sql.Database("Pete's FCRs.db");
var ps = db.prepare("select * from sessionIDs where sessionID = ?", errorFunc);
ps.all(sessionID, function(err, rows) {
    if (err) throw err;
    callback(rows);
});
ps.finalize();
db.close();
}

("test: " + sessionDetails);

1) I run checkSessionID to check if the sessionID is in the database, in the sessionIDs table. 2) Next, it calls the callback function, to store the username associated with the session ID.

However, due to the fact that callbacks are asynchronous, I am accessing the database prior to "username" being updated.

To attempt to solve this, it is not possible to move the database queries inside the callback function, as that causes an error, as the database is locked.

Many thanks for any help.

Edit

Managed to solve it, by declaring db as a global variable and closing it after all process have completed.

Answer 1

Generally when writing asynchronous code you need to move code inside the callback, otherwise it fires immediately:

var db = new sql.Database("Pete's FCRs.db");
var ps = db.prepare("select * from users where username = ?", errorFunc);

ps.all(username, function(err, rows) {
  if (err) throw err;
  console.log(rows);

  ps.finalize();
  db.close();
});

Remember that your code is executing out of order, not one line to the next.

I've found using a promise library like Bluebird helps simplify this code considerably and makes it easier to follow. Promises can take a moment to absorb, they're a new concept, but once you know how they work things chain a lot better.

A promise-version of this code would look roughly like:

ps.all(username)
  .then(function(rows) {
     console.log(rows);
  })
  .then(function() {
    return ps.finalize();
  })
  .then(function() {
    return db.close();
  })

Error catching is done automatically and it's possible to add special handlers if so desired.