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 endeavorer 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

Author

Posted by Brett Lawson, Principal Software Engineer, Couchbase

Brett Lawson is a Principal Software Engineer at Couchbase. Brett is responsible for the design and development of the Couchbase Node.js and PHP clients as well as playing a role in the design and development of the C library, libcouchbase.

All Posts

4 Comments

  1. anon January 28, 2014 at 5:01 pm

    Very excited to see work on this

  2. Justin September 29, 2014 at 4:34 pm

    I fear that examples using string concatenation to build up these queries will nudge people towards \”n1ql-injection\” vulnerabilities. In fact, it would probably be safer (and maybe it works this way, I haven\’t checked) to allow variable replacement in the query (e.g. \”%d\” or \”$1\” or something), so that you can ensure that values are escaped.

    1. Brett Lawson September 29, 2014 at 4:54 pm

      Hey Justin,
      I completely agree with you, and in a later version, we introduced built in parameterization and substitution (though this is currently unavailable for the 2.0 SDK). This is something we hope to be creating an elegant solution for soon. Definitely before N1QL is GA! Thanks for the input!
      Cheers, Brett

Leave a reply