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.

 

Hello Migration

Suppose you have a minimal Opa app that uses the database:

    import stdlib.themes.bootstrap

    database mydb {
        int /counter
    }

    function page(){
        <h1 id="msg">Hello</h1>
        <a onclick={ function(_) {
            /mydb/counter++;
            #msg = <div>Thank you, user number {/mydb/counter}</div>
        }}>Click me</a>
    }

    Server.start(
        Server.http,
        {~page, title: "Database counter"}
    )

By default, when you generate the Node.js app with

opa counter.opa

and launch it with

./counter.js

it will run on MongoDB and displays a counter which increments itself when clicked.

To use Postgres as database backend instead of MongoDB, you just need to edit the database declaration to specify to the Opa compiler to use Postgres:

database mydb @postgres {
  int /counter
}

The app does not yet generate the ‘mydb’ database automatically, so before running the application, you need to create the database

psql -c "CREATE DATABASE mydb"

Then just run your app normally:

./counter.js

To specify database credentials you can run instead

./counter.js --postgres-auth:mydb user:password[a] --postgres-default-host:mydb locahost:5432

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:

database mydb[b][c] @postgres {
  {string page, int counter, Date.date last} /counters[{page}]
}

In the above snippet, we declare a database set, for which page is the primary key. Switching from MongoDB to Postgres is again just about adding the @postgres keyword.

The next is to update the view, according to the new database declaration. The database update code becomes:

{ page: name, counter++, last: Date.now() };

that naturally sets the page name, with last seen updated to the current time and increments the page views. The database update is performed with a single request. The view itself is:

function page(name) {
  <h1 id="msg">Page {name}</h1>
  <a onclick={function(_) {
    /mydb/counters[page == name] <- { page: name, counter++, last: Date.now() };
    #msg = <div>{ /mydb/counters[page == name]/counter } page views</div>
  }}>Click me</a>
}

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:

function stats() {
  iter = DbSet.iterator(/mydb/counters[counter > 5; order +last])
  Iter.fold(function(item, acc) {
    acc <+> 
    <div><span>{item.page}</span> <span>{item.counter}</span></div>
    <div>Last update:  {Date.to_string(item.last)}</div>
  }, iter, <></>)
}

Finally, we write the controller using an URL dispatch:

function dispatch(url) {
  match (url) {
  case {path: {nil} ... } :
    { Resource.page("Stats", stats()) };
  case {path: path ...} :
    title = String.concat("::", path)
    { Resource.page(title, page(title)) };
  }
}
Server.start(
  Server.http,
  {~dispatch}
)

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:

database mydb @postgres {
  {string page, int counter, Date.date last} /counters[{page}]
}

generates for Postgres a SQL table named counters that contains 3 columns, where the primary key is page:

CREATE TABLE counters(page TEXT, counter INT8, last INT8,  PRIMARY KEY(page))

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.