Getting Started with Querly: A Simple CouchDB Query Engine

About a month ago, I introduced a simple project that allows limited T-SQL style queries against a CouchDB database.  The codename for that project is Querly.  Today, I'm going to talk a little about what you need to get started with Querly.

The other posts can be found in the following locations:

- Querly - A Query Engine for CouchDB  
- Setting Up Querly

Loading Up A Test Database

To get started with Querly, make sure that you have followed the instructions outlined in Setting Up Querly.  If all the tests run, you are ready to get started with Querly.  Press [Enter] to get back to the Erlang prompt.  In order to load up a database with several documents that we can play with, type  querly_load_tests:load_10000_people(). and press [Enter] (Note: This will take a little while to finish).

Writing Our First Query

Once the "person" database is loaded, we can start Querly and begin writing simple queries.

To start querly, type querly:start().

Here are a few samples of queries that can now be run (Note: The first query may take a few seconds as the documents are being loaded into an ETS table):

querly_client:sql_query("select * from person where Idno = 1").

querly_client:sql_query("select * from person where FirstName = TestFirst2").

querly_client:sql_query("select * from person where Dob= 08/28/1977 and FirstName = TestFirst3").

Querying Other Databases

In order to write T-SQL style queries against a database, a record must be defined in the src/record_definitions.hrl file.  An example for the person table is as follows:

-record(person, {'Idno', 'FirstName', 'LastName', 'Dob', 'Ssn'}). 

-define(personFields, record_info(fields, person)).

This definition makes it possible to use specific field names in the TSQL style queries.

Limitations

There are a number of known limitations in this simple query engine. 

The most notable limitations are as follows:

- The T-SQL style query functionality is limited to simple "where" clauses.  

- A record must be predefined in order to query a database.

Conclusion

Querly allows simple T-SQL style queries against a CouchDB database.  While Querly has a number of known limitations, it hopefully serves as a simple PoC and/or starting point for a CouchDB query engine.