node.js / Postgres : Cannot assign value in scope

Question

In my node.js app, I have the following code to get the last mark of a given user.

 app.get('/user/:token/mark/last', function(req, res){
    var user_token   = req.params.id;

    // Mark to be returned
    var last_mark = 0;

    // Get user
    var get_user = client.query("SELECT id FROM user where user_token = $1", [user_token]);

    // Get mark: handle sensor retrieved
    get_user.on('row', function(row) {
      get_mark = client.query("SELECT * FROM mark WHERE user_id = $1 order by date desc limit 1", [row.id]);


      get_mark.on('row', function(mark) {
        last_mark = mark.value;
        console.log("LAST MARK:" + last_mark);    // OK IT DISPLAYS "LAST MARK:16"
      });

      // Error checking
      get_mark.on("error", function (err) {
        console.log("ERROR");
      });
    });

    // Check database errors
    get_user.on("error", function (err) {
      console.log("NO SUCH USER");
    });

    // Finalize
    get_user.on("end", function () {
      console.log("LAST MARK:" + last_mark);   // KO IT DISPLAYS "LAST MARK: 0"
    });
 })

The problem is I do not get the correct "last_mark" (I get 0), as if the assignement was not done.

Do you have any idea ?

Answer 1

This isn't my best answer ever but you could replace your two queries with one that does a JOIN:

select m.value
from mark m join user u on (m.user_id = u.id)
where user_token = $1
order by m.date desc
limit 1

That should make the problem go away and let you get on with your work. As Linus said:

No problem is too big it can't be run away from.

I still think there is some sort of timing issue with when the inner "row is ready" event is fired and the outer "query is finished" event is fired. I would expect the outer "end" event to be fired before the inner "row is ready" event (but, admittedly, I'm not familiar with the node.js database interface). You could also push the "deal with last_mark" logic into the "row is ready" callback:

get_mark.on('row', function(mark) {
    // Call yet another callback to send `mark.value`
    // into the outside world.
});

That would introduce more steps in the callback chain but this sort of thing is par for the course when dealing with asynchronous APIs.

Answer 2

Be careful with declaring variables, 'last_mark' appears to be in global scope (or at least declared outside of your posted code), this can cause unexpected behavior due to the asynchronous nature of the callbacks since there is potentially a "race condition" between multiple clients which could be clearing and setting the same global variable rather than their own local instances.

// Mark to be returned
var last_mark = 0;