Node.js and N1QL

This post was originally written by Brett Lawson.

So, recently I added support to our Node.js client for executing N1QL queries against your cluster, providing you are running an instance of the N1QL engine (to get a hold of the updated version of the Node.js client with this support, point npm to our github master branch at https://github.com/couchbase/couchnode).  When I implemented it, I didn’t have very much to test against at the time, so I figured it would be a interesting endeavor to see how nice the Node.js’s beer-sample example would look if we used entirely N1QL queries rather than using any views.

I first started by converting over the basic queries which simply selected all beers or breweries from the sample data, and then moved on to converting the live-search querying to use N1QL as well.  I figured I would write a little blog post on the conversions and make some remarks about what I noticed along the way.

Here is our first query:

var q = {
  limit : ENTRIES_PER_PAGE,
  stale : false
};
db.view( "beer", "by_name", q).query(function(err, values) {
  var keys = _.pluck(values, 'id');
  db.getMulti( keys, null, function(err, results) {
    var beers = _.map(results, function(v, k) {
      v.value.id = k;
      return v.value;
    });
    res.render('beer/index', {'beers':beers});
  })
});

and the converted version:

db.query(
    "SELECT META().id AS id, * FROM beer-sample WHERE type='beer' LIMIT " + ENTRIES_PER_PAGE,
    function(err, beers) {
  res.render('beer/index', {'beers':beers});
});

As you can see, we no longer need to do two separate operations to retrieve the list.  We can execute our N1QL query which will returns all the information that we need, and formats it appropriately; rather than needing to reformat the data and add our id values, we can simply select it as part of the result set.  I find the N1QL version here is much more concise and appreciate how simple it was to construct the query.

I then converted the brewery listing function following a similar path, and here is what I ended up with, as you can see, it is similarly beautiful and concise:

db.query(
    "SELECT META().id AS id, name FROM beer-sample WHERE type='brewery' LIMIT " + ENTRIES_PER_PAGE,
    function(err, breweries) {
  res.render('brewery/index', {'breweries':breweries});
});

Next I converted the searching methods.  These were a bit more of a challenge as looking at the original code directly, without thinking about what it was trying to achieve, the semantics were not immediately obvious, here is a look at what it looked like:

var q = { startkey : value,
  endkey : value + JSON.parse('"\u0FFF"'),
  stale : false,
  limit : ENTRIES_PER_PAGE }
db.view( "beer", "by_name", q).query(function(err, values) {
  var keys = _.pluck(values, 'id');
  db.getMulti( keys, null, function(err, results) {
    var beers = [];
    for(var k in results) {
      beers.push({
        'id': k,
        'name': results[k].value.name,
        'brewery_id': results[k].value.brewery_id
      });
    }
    res.send(beers);
  });
});

Again, we have quite a bit of code to achieve something which you should expect to be quite simple.  In case you can’t tell, the map/reduce query above retrieves a listing of beers whose names begin with the value entered by the user.  We are going to convert this to a N1QL LIKE clause, and as an added bonus, we will allow the search term to appear anywhere in the string, instead of requiring it at the beginning:

db.query(
    "SELECT META().id, name, brewery_id FROM beer-sample WHERE type='beer' AND LOWER(name) LIKE '%" + term + "%' LIMIT " + ENTRIES_PER_PAGE,
    function(err, beers) {
  res.send(beers);
});

We have again collapsed a large amount of vaguely understandable code down to a simple and concise query.  I believe this begins to show the power of N1QL and why I am personally so excited to see N1QL.  There is however one caveat I noticed while doing this, and this is that similar to SQL, you need to be careful about what kind of user-data you are passing into your queries.  I wrote a simple cleaning function to try and prevent any malicious intent (though N1QL is currently read-only anyways), but my cleaning code is by no means extensive.  Another issue I noticed is that our second query with the LIKE clause executed significantly slower as a N1QL query then it did when using map/reduce.  I believe this is simply a result of N1QL still being developer preview, and there is lots of optimizations left to be done by the N1QL team.

If you want to see the fully converted source code, take a look at the n1ql branch of the beersample-node repository available here, https://github.com/couchbaselabs/beersample-node/tree/n1ql.  

Thanks! Brett