Querying your Cloudant database with SQL

Updating the silverlining Node.js library to support the basics of SQL

Cloudant and its Apache CouchDB stable-mate are “NoSQL” databases — that is, they are schemaless JSON document stores. Unlike a traditional relational database, you don’t need to define your schema before writing data to the database. Just post your JSON to the database and change your mind as often as you like!

One of the appealing things about relational databases is the query language. Structured Query Language or SQL was developed by IBM in the 1970s and was widely adopted across a host of databases ever since. In its simplest form, SQL reads like a sentence:

SELECT name, colour, price 
  FROM animalsdb
  WHERE type='cat' OR (price > 500 AND price < 1000) 
  LIMIT 50

This statement translates to:

“Fetch me the name, colour and price from the animals database, but only the rows that are cats, or ones which are more expensive than 500 but cheaper than 1000. And I only want a maximum of 50 rows returned.”

It is a convenient way of expressing the fields you want to fetch, the filter you wish to apply to the data, and the maximum number of rows you want in reply.

Many databases can store BLOB types, but this isn’t one of those kinds of blobs. Image credit: mark du toit.

Unfortunately, NoSQL databases don’t generally support the SQL language. Cloudant and Apache CouchDB™ have their own form of query language where the query is expressed as a JSON object: “Cloudant Query” (CQ) and “Mango,” in their respective contexts. The CQ or Mango equivalent of the above SQL statement is:

It’s a world of curly brackets! If you’re happier expressing your query in SQL, then there is a way.

Silverlining + SQL

The latest version of the silverlining Node.js library can now accept SQL queries. It will convert the SQL into a Cloudant Query and deliver the results.

Editor’s note: In February 2018, the silverlining library has been deprecated and replaced with cloudant-quickstart: https://www.npmjs.com/package/cloudant-quickstart

Simply install the Silverlining library:

npm install -s silverlining

And add it to your Node.js app by passing your Cloudant URL to the library:

var db = require('silverlining')('https://USER:PASS@HOST.cloudant.com/animalsdb');

We can then start querying our database with an SQL statement:

db.query('SELECT name FROM animalsdb').then(function(data) {
    // data!
  });

Here are some other sample queries:

Silverlining achieves this by converting your SQL query into the equivalent Cloudant Query object. If you’d like to see that data yourself, then call the explain function instead of query to be returned by the query that would have been used:

Limitations

Before we get carried away, this feature doesn’t suddenly make Cloudant support joins, unions, transactions, stored procedures etc. It’s just a translation from SQL to Cloudant Query.

It doesn’t support aggregations or grouping either, but you can use Silverlining’s count, sum, and stats functions to generate performant grouped aggregation without any fuss.

This feature simply makes it easier to explore data sets if you already have SQL language experience.

If you enjoyed this article, please ♡ it to recommend it to other Medium readers.