The Problem: ORMs and Static String Queries

Dealing with static strings when writing queries for a database is annoying. Historically, people have reached for ORMs to handle this problem. ORMs can be very convenient. However, in my experience, there is always a mismatch between the ORM and DSL. You must drop back into the DSL when you want to do something that the ORM API doesn’t support.

Over time, I’ve come to embrace the DSL. If I’m using Postgres, then I’m writing Postgres SQL. If I’m writing SQLite, then I’m writing SQLite SQL. If I’m writing neo4j, then I’m writing Cypher. You get the idea.

Unfortunately, this brings us back to static strings inside your code. It probably looks like this:

const sql = "SELECT name, description, body FROM todos " +
            "INNER JOIN users ON todos.user_id = users.id " +
            "WHERE name LIKE $1 "
            "ORDER BY timestamp DESC";

In Erlang, we can get away with something like this:

L = [<<"SELECT name, description, body FROM todos ">>,
     <<"INNER JOIN users ON todos.user_id = users.id ">>,
     <<"WHERE name LIKE $1 ">>,
     <<"ORDER BY timestamp DESC"],
Query = list_to_binary(L).

The initial example and the Erlang one are pretty similar. The examples both suffer from the issue of misformatting a string. They both involve extra string delimiters and some mechanism to concatenate these strings.

The Solution: erlquery and rebar compiler plugins

Okay. We don’t want to use ORM. Static strings are error-prone. There has got to be a better way!

My first taste of freedom from this conundrum was HugSQL[https://www.hugsql.org/]. This library is what opened my eyes to how embracing the DSL can increase productivity!

HugSQL works with a macro that runs compile time to parse a file with a list of queries demarcated by special comments.

 --:name get-todos :? :*
 select * from todos

The query and comment are parsed and generate a get-todos function to query the database. It worked with multiple SQL drivers.

erlquery draws a lot of inspiration from hugSQL but takes its own path. Some of the differences are simply because of technical differences between Clojure and Erlang. For one, Erlang’s macros are not the same as Clojure’s macros. The most significant difference is erlquery can work for any database type, not just SQL. erlquery also doesn’t require a driver. All erlquery expects is a function that will take the query string and a list of arguments.

In Erlang, we can achieve a similar effect to Clojure’s compile-time macros by using Erlang’s built-in compiler API. Combine the compiler with rebar3’s pre-compiler plugin hooks, and you can add on top of Erlang whatever DSL you want! The project that inspired me to do this was rebar3_erlydtl_plugin. That plugin works by generating compiled beam modules from templates. Erlang’s compiler will include any .beam files found in the release build environment. This property makes it great for deployment. As far as Erlang is concerned, these exotic DSLs or templates are Erlang code.

It took a few steps to get there, but we achieved the benefit of Clojure’s macros for hugSQL in Erlang.

erlquery compiler

The erlquery compiler parses all files with the .erlq extension and generates a module from that file. It made sense to make erlquery look like Erlang code.

Here is an example:

-module(todo_queries).

-query(pgo:query/2).

hello_todos ->
  SELECT * FROM todos.

If you are familiar with Erlang syntax, this should feel right at home. Let’s break it down further.

The first line defines the module name for the Erlang module. Easy. The following expression declares what function will execute the queries. We’ll see what this looks like when I get to code generation. Note that all functions specified in the query section must only accept two arguments.

The following expression is an actual query. This expression looks like an erlang function. Here we have a query named hello_todos that, when executed, will select all from the todos table.

The generated Erlang code for hello_todos looks like this:

hello_todos() ->
  hello_todos([]).

hello_todos(Args) ->
  pgo:query(<<"SELECT * FROM todos">>, Args).

For each query, there are two functions generated. The first one is to make the ‘Args’ argument optional. The function that does accept the Args parameter combines the specified query function, the query string, and the args parameter. Note that the args parameter expects a list.

That’s it! That is how the erlquery compiler works.

In Practice

The first step is to have the erlquery compiler parse erlquery files and generate erlang code. The next step is actually to use it in an erlang project. To generate the erlang modules at compile time, we must integrate the erlquery compiler into rebar with a plugin. The rebar plugin for erlquery is rebar3_erlquery. This plugin is on Hex and is installed by adding the snippet below to your rebar.config:

{project_plugins, [rebar3_erlquery]}.

{provider_hooks, [{pre, [{compile, {erlquery, compile}}]}]}.

Now all you have to do is make a .erlq file in your project, and away you go!