UniQL: One Query, Any Database

Use a simple query language to build trees that can compile to any database syntax.

Here at Hone we are building a new SDK and we needed a way to let people write queries without requiring them to know about our underlying databases. We looked around at some of the options out there and the closest thing we could find was a neat library that compiled a simple query language into ElasticSearch query objects: FiltrES. It was close to what we needed, but not quite. Inspired by the work that Abe Haskins had built on top of Filtrex (Joe Walnes), we built UniQL.

So what is UniQL?

UniQL parses a simple query language that looks like this:

( height >= 20 and height <= 40) and firstname ~= "o.+"

… and turns it into a query tree that looks like this:

{ type: '&&',
  arguments: 
   [ { type: 'EXPRESSION',
       arguments: 
        [ { type: '&&',
            arguments: 
             [ { type: '>=',
                 arguments: 
                  [ { type: 'SYMBOL', arguments: [ 'height' ] },
                    { type: 'NUMBER', arguments: [ '20' ] } ] },
               { type: '<=',
                 arguments: 
                  [ { type: 'SYMBOL', arguments: [ 'height' ] },
                    { type: 'NUMBER', arguments: [ '40' ] } ] } ] } ] },
     { type: 'MATCH',
       arguments: 
        [ { type: 'SYMBOL', arguments: [ 'firstname' ] },
          { type: 'STRING', arguments: [ 'o.+' ] } ] } ] }

That looks kind of daunting, but it’s just a representation of the query in a tree that allows us to then compile a query for a specific database. This is similar to how ASTs work for programming languages being compiled to machine code.

To give you a concrete example, we wrote a MongoDB compiler that we can use to transform any UniQL query tree into a MongoDB query very simply:

var parse = require( 'uniql' );
var mongoCompile = require( 'uniql-mongodb' );

var tree = parse( '( height >= 20 and height <= 40) and firstname ~= "o.+"' );
var mongoQuery = mongoCompile( tree );

The mongoQuery ends up being a standard MongoDB query:

{ '$and': 
   [ { '$and': [ 
      { height: { '$gte': 20 } },
      { height: { '$lte': 40 } }
     ] },
     { firstname: { '$regex': 'o.+' } } ] }

But we could also compile the same query for ElasticSearch. Or we could compile it to Javascript. And if you have a database you’d like to write a driver for, we’d be more than happy to point to it.

Why does it matter?

UniQL lets your developers or users write queries without needing to know what kind of database that query will be running against. It also allows you to run the same query against multiple databases.

For example, let’s say you’re creating a blogging service. You use MongoDB to store your users and their blog posts, but you also put the posts into ElasticSearch for fast text searching. You could let people write UniQL queries to search either users or their blog posts. When they’re searching the posts, you could compile the query to ElasticSearch, but compile to MongoDB when searching users.

And no one needs to know how your backend works or where the queries run. All they need to know is how to write in a simple query language.

Can I use this in production?

Probably not… yet. We’re just starting to work with it and we’ll continue to improve and update it as we exercise it on our own databases. But we’d welcome any help improving the existing code or writing new drivers for more databases. (Who wants to write a SQL compiler? You know you want to.)

Conclusion

UniQL was a fun project that solves a specific problem for us: letting users write queries against our data without needing to know about our backend. Hopefully you might find it useful, or even contribute a driver of your own.