We’re a medium sized Clojure shop which builds and maintains a ride- and car-sharing platform called GoMore/Amovens in several European countries.
We write a lot of our SQL by hand which gives us great performance but sometimes we do miss a consistent and convenient way of getting data into our application.
We’ve experimented with different approaches but we don’t feel like we’ve found the perfect fit. We’re very interested in hearing what others use and why it works for their product.
I am not quite sure what you mean by consistent and convenient. Which approaches have you tried so far?
Where I work we use HugSQL functions exclusively. Each module of the codebase has its own SQL file. Modules might span across database schemas and tables, but functionality is typically isolated, which makes this approach work quite well for us.
I do not think this approach has any inherent benefits over constructing SQL queries dynamically with e.g. HoneySQL, but we find using SQL directly mostly pleasant, and it allows for easy access to any Postgresql-specific features we might need.
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__id, 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.
Something like this https://dhall-lang.org might be useful for your requirements, there’s also a library to go from it to Clojure and viceversa https://github.com/f-f/dhall-clj
Have you looked at HoneySQL? It’s a composable DSL for SQL that would let you define your select of columns from user and reuse it elsewhere, combining it into other selects etc.
We use it very heavily at work for constructing complex queries on-the-fly from smaller, standard pieces. You can compose selects, from/joins, wheres etc. Sounds exactly what you are looking for, if I’m understanding your problem description correctly?
Walkable’s author here. Can you elaborate more on the “verbose syntax”? I often hear people complain that Walkable query language (EQL) is too terse for them 
Anyway Walkable tries to use plain data as much as possible, so I guess throwing some def or defn to avoiding repeating yourself is encouraged. Actually in the Fulcro/Om.next tradition, you don’t write the whole query but compose them from sub components, such as:
(def User {:query [:user/name :user/age]})
(def Post {:query [:post/title :post/url {:post/author (:query User)}]})Thanks for your input everyone! I’m going to be offline for a few days but will pick this up as soon as I’m back.
Have you looked into the advanced uses of HugSQL, like snippets and inline clojure? It seems like that would fit the requirements. Maybe that could help in building something that fits your needs, without having to switch tools.
Co-worker from GoMore here now that @chopmo is out
I think there were two things that we found difficult in using Walkable:
If I remember correctly it was mainly the deep nesting that became an issue for us. I wish we had posted this question as soon as we’d tried out Walkable (among others) so it was fresh in memory. Sorry 
We’re very interested in hearing if anyone out there are using Walkable for a large application and what their experiences are.
@mdiin: we do use snippets and inline Clojure but we’re not overly happy with the result. It’s still better than mashing raw strings together but only just 
Thanks for suggesting HoneySQL. We did look through the documentation but it looked like a very thin wrapper around just writing raw SQL. After having evaluated it and tried it out today on one of our hairier endpoints I can see that it does open up for some further, more sophisticated ways of constructing queries.
I think we’ll continue experimenting with it. As always it gives us more confidence that the library is being used in a large codebase.
Also, apologies for the multiple responses. Clojureverse has very helpfully explained to me that it is poor form 
I see. I have mainly used those features to DRY up SQL, and to keep focus on the changing parts of a set of nearly identical queries. It was more a guess that you could maybe bend them to your needs.
I’m back online now and started experimenting with HoneySQL together with Thomas. Looks quite promising so far, thanks for suggesting it @seancorfield!
I think what we’d do is things like: given a user, give us all his bookings, for each ride booking, give us all the rides that those bookings were for, and given those rides, give us the user who drove.
So there’s a chain from one entry in a user table to another entry in the same table.
That sounds like what Walkable was meant to solve, something like:
{[:user/by-id 1]
:user/username
:user/full-name
{:user/bookings [:booking/created-at
{:booking/rides [:ride/cost
:ride/created-at
{:ride/driver [:user/id
:user/name
:user/full-name]}]}]}}
assumed (part of) the floor-plan:
{:joins {:user/bookings [:booking/created-by :user/id]
:booking/rides [:booking/ride-id :ride/id]
:ride/driver [:ride/driver-id :user/id]}}
Correct me if your table design is completely different.
Thanks a lot for your responses! Right now we’re trying out few different ideas with HoneySQL but we’ll definitely keep your feedback in mind for later.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.