Micro languages in Clojure - part 2
In the previous article we constructed a tiny DSL for generating SQL statements using a more Clojure like syntax. It works, but it has a couple of problems: It doesn’t parameterize SQL variables, and nesting doesn’t work properly. In this article we’ll rectify those issues, and add a bit of addition functionality.
So to parameterize the SQL statements we obviously need to extract the values from our generated SQL and replace them with question marks.
Needs to become this:
The library we are using expects parameterized SQL in the following form:
That should be the eventual result of our SQL generator.
For our simple DSL we need to accumulate the string portion of the statement, and the variables for binding. Whenever I need to accumulate more then one thing in Clojure, I tend to lean on maps. You may know them by different names, perhaps dictionary or hash table, etc. They are a very useful data structure, and I tend to use them quite frequently. Perhaps too much, but that’s another story for another day.
Let’s create a simple helper function for making maps of SQL fragments:
This function takes multiple arguments and checks to see of the last one is a vector. If the last one is a vector, it’s assumed to be a variable to be bound to the SQL. It returns a map with the :stmt key bound to the SQL string, and the :vars key bound to a list of the variables.
Now we need another little helper function to selectively generate paramatized statements.
Let’s modify the sql-exp function to call paramatize:
A call to sql-exp will now return a map resembling this:
Our little SQL DSL is now using bound variables. The other issue to be dealt with is the nesting of SQL expressions. To tackle that issue we’ll create another helper function:
All this function does is surround the supplied statement with parentheses. So nesting should now work correctly. Albeit at the cost of one superfluous pair of parentheses.
All the maps get merged together using merge-with concat. Which does exactly what it says, merge the maps by concatenating the values with matching keys. All that’s left is to take the resulting map and convert it to a form that clojure.contrib.sql can digest. So one more helper function:
Let’s give it a test run:
Looks good, bound variables are working and nesting is handled correctly now. Let’s see how much work it would take to add sorting and result set paging.
For sorting we’ll use our macro/let binding trick again to create functions asc and desc.
Paging turns out to be dead simple:
Let’s test and make sure it works as expected:
Looks good, though the nesting is getting a bit extreme. As luck would have it, Clojure offers a great solution for over nestification. Say hello to the -» macro. With the “threading” macro, we can rewrite the above as:
Much more readable. Now our tiny DSL meets almost all of its users’ (yours truly) needs. In the future we might revisit how the SQL is generated to enhance composibility, and perhaps provide a more terse syntax for specifying table columns.
As always the complete source is available on Github.