To SQL From NoSQL

NoSQL databases are increasingly popular and when we decided which database to bind first in the Opa framework, we chose the famous NoSQL MongoDB database, that some say is “the greatest mug company ever”. But at the same time, we kept having lots of request about the support of more classical SQL databases.

Opa just reached 1.1.1 today, and the little version increase brings in support for the Postgres database. This article explains how to move back from NoSQL to SQL.

Digging into Complex Stuff

In our former example case, the database is limited to a single integer… But things get nicer when we store more complex datastructures such as lists, maps (the dictionaries in Python), records or any combination of them. Let’s generalize the previous example to have one counter per page, and create a homepage that aggregates a few statistics.

Let’s first redefine the database declaration to have one counter by page:

Let’s add another view to display a few stats. Basically, we want to show all pages sorted by last update where the counter is greater than 5. To specify the query, we use Opa’s comprehensive data syntax:

In this example app, migrating from MongoDB to Postgres (or the contrary) requires almost no code change. If you want to come back to MongoDb just edit the database declaration by removing @postgres annotations or replace by @mongo. Even better, you can mix both database engines in the same application if you want.

In further releases of Opa, we will probably take runtime definitions into separate definitions and support a few platforms-as-a-service straight out of the box.

How it works: DbGen

The magic behind the previous example has a name: The “DbGen” automation layer. Unlike most ORM layers, DbGen as its name suggest is basically a code generation layer. DbGen generates queries statically, in a safe way that prevents code injections, among other runtime errors.

As Opa is a strongly statically typed technology, DbGen uses a database schema which includes type information from the program to validate queries statically. The information-rich schema is used to create the NoSQL data structure or the corresponding SQL schema. In our previous example, the schema declaration:

Note that the generated SQL code is clean and the database structure can be easily used by humans, for instance in conjunction with admin tools. Opa also generates stubs to access the database. For example, the following data access

/mydb/counters[page == name]

is compiled as a pre-compiled Postgres query:

SELECT * FROM counters
WHERE page == $1

It’s efficient, and clean.

Wrap Up

Try it for yourself! Opa 1.1.1 which now supports both MongoDB and Postgres is available from the Opa portal. The source is available on GitHub.

All resources to get you started are available from the portal, and ask your questions on StackOverflow or our own forum. The best reference on Opa is the O’Reilly book, available from Amazon or directly from O’Reilly.