Hi Matthias, and thanks for chiming in!
In fact HugSQL is what we use as well, and we’re quite happy with it as a library. And we also have roughly one SQL file per namespace.
I’ll try to illustrate with an example.
Let’s say that we have a
users table with a lot of columns on it (unsurprisingly, we do…). Now, whenever we need information about a user, we have to enumerate all the columns we’re interested in. Not just in one SQL file but anywhere where we load user information, typically by joining it onto other tables.
Of course we don’t need all the columns all the time, but a long list is repeated in many places. And in each case, we carefully follow aliasing conventions for the column names, like
user__first_name etc. so that we can extract the “user parts” in Clojure afterwards. This is tedious and also quite error prone because it is easy to misspell an alias.
So what we’re looking for is a way to abstact out the concept of “loading a user”, possibly as part of loading data from a different table. Also, we often join together tables in certain patterns which are also repeated across many SQL files. Again, it is easy to miss an important join condition because of this repetition.
However, we are not looking for an ORM-like library that automatically loads data from associations as they are being accessed. We would like to have something that is simple and explicit, but still at a higher level of abstraction than raw SQL.
We’ve experimented with Toucan and Walkable. Toucan didn’t support joins, making it usable only for the trivial cases. Walkable is intesting but we weren’t too happy with the rather verbose syntax.