Using Q with Node-Mysql

Question

I am relatively new to Node but I have had good success with porting over RESTful API that was previously done in PHP. There is quite a bit of database interaction and as a result I have found myself getting into what I believe many people call "the pyramid of doom" due to the async nature of Node.

As a result I am taking a stab at implementing promises using the Q library but I haven't had much success yet and am still getting null result sets back when I believe I should be getting data. Below is my current structure before adding Q, if anyone could advise on how to correctly implement Q for this I would be able to run with that example and convert the rest of the database/memcached calls over to that model.

// helper function to get a company row
getRow = function(lookUp, callback) {
    var query = db.query('SELECT * FROM table WHERE lookUp = ?', lookUp, function(err, result) {
        var count = Object.keys(result).length;

        if(count == 0) {
            return;
        } else {
            callback(null, result);
        }
    });
}

// function that uses the above helper method
insertItem = function(request, response) {
    var data = JSON.parse(request.body.data);
    var message = data.message;
    var lookUp = data.lookUp;

    security.verifyToken(lookUp, function (lookUpError) {
        if (lookUpError) {
            var errorResult = { "response": "error", "msg": lookUpError };
            response.json(errorResult);
            response.end();
        } else {
            getRow(lookUp, function (companyError, row) {
                var companyId = row[0].id;

                var res = helper.insertFeedItem(companyId, message, function (insertError, insertResult) {
                    var result = (feedError) ? { "response": "error", "msg": insertError} : insertResult;
                    response.json(result);
                    response.end();
                });
            });
        }
    });
}

What I would like to accomplish is being able to do something like:

var result = getCompanyRow(lookUp);
companyId = result.company_id;

Again, any insight into how to best implement Q (or just promises in general) for this case would be extremely appreciated.

* EDIT:

Here is what I have tried thus far to implementing Q, but as I said I am getting nothing back.

function getRow(id) {
  var dfd = Q.defer();
  var query = db.query('SELECT * FROM table WHERE lookUp = ?', id, function(err, result) {
    if(err) { dfd.reject(err); }
    else { dfd.resolve(result); }
  });
  return dfd.promise;
}

The above is not working at all when called as result = getRow(id); I tried using Q.all and binding the function to that but I also got nothing back when trying that approach. I wasn't sure what to include in my call to .then() but I tried a number of things, none were successful.

A `getRow` that maybe never call its `callback` is a bad idea even without promises. At least do a `callback(nothingFoundError)` — Bergi, Jan 22 '14 at 18:57
Could you please also show us your approach of using promises? — Bergi, Jan 22 '14 at 19:03
@Bergi Added. Very crude I know but it is all I have left after a few rounds of trial and error. — niczak, Jan 22 '14 at 19:13
That `getRow` function looks quite good - `result` will be the promise object. How have you used it in `insertItem`? — Bergi, Jan 22 '14 at 19:21
@Bergi I think the problem is how I an trying to use it in insertItem. I wanted to do something like var result = getRow(id); Result is an object but not the object I am expecting. How do I go about using that object? Do I need to use .then() in the call inside of insertItem()? — niczak, Jan 22 '14 at 19:30
Yes, `result` is the promise object on which you would call the `.then()` method. The callback that `then` takes will be given the object with which you `resolve()`d your deferred. — Bergi, Jan 22 '14 at 20:00
@Bergi Unfortunately I am not getting to where I had hoped and it appears as though it is impossible. I don't see any benefit to using .then(function ...) because it still requires nesting just like a callback would. — niczak, Jan 22 '14 at 20:30

Bergi · Accepted Answer · 2014-01-22T21:07:01.453

Your getRow promise function looks promising :-) It can be further simplified by using the node adapter methods from Q:

function getRow(id) {
    return Q.nfcall(db.query,    'SELECT * FROM table WHERE lookUp = ?', id);
    //      ^^^^^^^^^^^^^^^^^^^^
    // or   .ninvoke(db, "query", … if it must be called as a method
}
// or even just
var getRow = Q.nbind(db.query, db, 'SELECT * FROM table WHERE lookUp = ?');

I don't see any benefit to using .then(function ...) because it still requires nesting just like a callback would.

The benefit (apart from easier error handling) comes from chaining multiple tasks, i.e. when also your security.verifyToken and helper.insertFeedItem methods would return promises. If they don't (and you can't modify them) you still can use Q.nfcall as in the example above. Assuming they did, your code can be simplified to

function insertItem(request, response) {
    var data = JSON.parse(request.body.data);

    security.verifyToken(data.lookUp).then(function(/* no lookupError */) {
        return getRow(data.lookUp); // potentially catch SQL errors here
    }).then(function(row) {
        return helper.insertFeedItem(row[0].id, data.message);
        // What was the `res` it had returned before?
    }).catch(function(someError) { // lookUpError, companyError, insertError
        return { "response": "error", "msg": someError };
    }).done(function(result) {
        response.json(result);
        response.end();
    });
}

Using Q with Node-Mysql

1 Answers1