A few weeks ago I blogged about the HTTP JSON api in Drizzle. (See also a small demo app using it.) In this post I want to elaborate a little on the design decisions taken. (One reason to do this is to provide a foundation for future work, especially in the form of a GSoC project.)

Looking around: MongoDB, CouchDB, Metabase

Designing a HTTP JSON API is of course inspired by the fact that there already are some NoSQL databases that do that. So it makes sense to look more closely into what they do exactly, learn from them, and possible even try to be compatible. When designing the JSON key value interface to Drizzle json_server plugin, I looked at the following:

MongoDB

The MongoDB manual, especially the chapter on Querying.
Mongo Wire Protocol (no, it's not HTTP)
Comparing MongoDB and CouchDB

I also found these posts from Anders Karlsson useful, perhaps since we share the same background with MySQL:
NoSQL for us RDBMS folks - MongoDB 101
MongoDB for MySQL folks - part 2
MongoDB for MySQL folks part 3 - More on queries and indexes

CouchDB

CouchDB Guide, in particular Getting Started page.

MQL

Metaweb Query Language is used by the Freebase online database. It is another JSON based query language but there isn't really a database product you would use it with, it is only used by Freebase. I came to know about MQL mainly through Ronald Bouman's MQL to SQL project, which is a proxy that converts HTTP JSON queries to SQL against a MySQL database. I used Roland's documentation as my primary reference to MQL - see in particular the Samle MQL Queries page. (Roland has also provided many helpful insights in tweets and blog comments.)

Choosing a protocol

I was surprised to learn MongoDB doesn't have a HTTP API rather uses its own binary protocol for which you need specific MongoDB client libraries. Very similar to the SQL world. Supposedly, this gives MongoDB (and SQL databases) more performance.

Even so, this choice is easy. We want to choose HTTP. If nothing else, because that's what Stewart already implemented in 0.1 version. And also, because then we can access Drizzle over HTTP without any client libraries.

Choosing the query "language"

As we settled for HTTP, it then feels natural to focus more on how at least CouchDB does things, since there is an opportunity to become compatible with it.

CouchDB implements a very pure REST design and this leads to a strong key-value emphasis. Basically you end up accessing records via URLs like:


GET https://servername:5984/databasename/documentid


As you can see, there is no obvious way to express something like filtering on another field, let alone a secondary index. For anything that is more complex than a key-value fetch, you will have to write a JavaScript function that will scan through your documents key-by-key. Sounds both complex and inefficient.

Note that even if the Drizzle HTTP JSON api currently only supports key-value operations, I wanted to select a design that will nicely support also more than that.

Btw, note at this point that CouchDB stores the keys under a json key "_id" and they have values that look like "6e1295ed6c29495e54cc05947f18c8af". A complete CouchDB document looks like:


{"_id":"6e1295ed6c29495e54cc05947f18c8af","_rev":"1-2902191555","title":"There is Nothing Left to Lose","artist":"Foo Fighters"}


Ok, giving up on CouchDB, I looked at MQL. With MQL, you specify a query document in JSON:


{
"type": "/sakila/film",
"film_id": 1,
"title": null
}


This query document asks to get all records where film_id equals 1. The title field is null, this means that it acts as a placeholder: we want the database to add the value of the film title, then return the document to us. The table may contain other fields, but we will only get "title" in return, because that's what we asked for.

The field name "type" is special: It basically denotes the schema and table name where to query from. Even if this is schemaless databases we are talking about, it seems we are supposed to assume that records with the same type are going to be similar.

Note that except for "type", there is no "_id" or other special fieldnames. In this case I know the sakila film database and know that film_id is a primary key, but it could have any name.

MQL also supports more complex operations. For instance, this query object maps to an IN (1, 2, 3) query in SQL:


[{
"type": "/sakila/film",
"film_id|=": [1,2,3],
"title": null
}]


Ok, I kind of like the basic approach of using a query document. If there is a value, then this field is used as a parameter. (WHERE, in SQL) If there is a field name but it has a null value, then it means the database should return that value. This much I like.

I don't like the type field being part of the record (or document, if you want to use schemaless terminology) itself. Records should be inside the table, the table(name) should not be inside the record. For instance, this means when returning results, you'd have to insert the table name into every single record you return. Or store the table name into the table itself, which is probably even worse.

I also don't like the more advanced operators like "film_id|=" being a way to express "film_id IN".

So what about MongoDB?

It is a little bit of both. It also has query documents, so you'll do this to find people called "Smith":


db.users.find({'last_name': 'Smith'})


If you only want to know the first name, and not get the full record back, you'll specify it like this:


db.users.find({last_name: 'Smith'}, {'first_name': 1});


Hmm... Okay. I liked the MQL way better.

MongoDB also stores the key in a field called "_id", but it is of a Mongo-proprietary ObjectId() type. Looks like this:


{ "_id" : ObjectId("4eb0634807b16556bf46b214"), "c1" : 1 }


A more complex Mongo query looks like this:


db.updates.find( { user : { $in : [1, 2, 5] } } ).sort( { ts : -1 } ).limit(10)


Notice the "$in" keyname, it is again an attempt to express the SQL IN() operator by overloading the meaning of a field name. I find that MongoDB's use of dollarsign+name works slightly better for me than the magic operators appended by MQL, but it still looks hacky.

You'll also notice there's a call to separate .sort() and .limit() methods, the meaning of which seems obvious. But this is now in the specialy MongoDB query language. So it is not something we can use as such for Drizzle which is purely HTTP and JSON.

The Drizzle implementation

In summary I ended up choosing a mix of the above. I like the MQL approach where a query object is basically a template: If I provided a field with a value, then please query for records where these fields match the given values. If I provided an empty field, it means please give me the value of this field back. This is elegant and intuitive.

The Drizzle implementation actually returns all records currently. But returning only the requested records could be implemented with the chosen design.

The Drizzle implementation uses a field/key called "_id" for the primary key. It takes integer values, so it is not strictly identical to either Couch or Mongo, but at least it looks familiar.

I obviously did not take the special "type" field from MQL. Instead you give the schema name and table name on the query string part of the URL. (In the future, the DBA could configure default values and allow users to omit them.)

Drizzle currently does not support any kind of filtering or secondary indexes in the JSON interface. I haven't completely decided how to express those. I didn't quite like any of the above examples. Doing a for loop in JavaScript is out of the question I suppose :-) I don't like inserting | or $ or any other special character into places where I expect to see only field names. This seems hackish.

But of the above two the MongoDB approach seems more usable, so unless someone has a better idea, it will probably come to that.

It should be noted that even if I chose the "query object" approach, in the end I also implemented a way where you can just append the key value to the URI too and omit the query object. It was easy to implement and makes life easy if you want to fetch some records with curl or wget. Also it might be useful is you want to shard your Drizzle JSON database, because you could use a HTTP proxy to direct queries to the correct shard by looking at the key value given in the URI.