[snorremd]

My best experience integrating with a Postgres database was using Clojure with the Honey SQL library [1]. Essentially Honey SQL operated solely as a query builder library allowing me to express queries as regular Clojure EDN maps and vectors (comparable to objects and arrays in JavaScript), or nested function calls returning the same kind of data. In essence SQL queries were expressed in a Clojure native data format which finally could be transformed into SQL syntax.

The huge win was that I could use the Clojure REPL to run my SQL-generating functions and see that the SQL actually matched what I was aiming for. Caching the query-building was as simple as memoizing the query building functions as queries are parameterised in any case, so can be reused.

There is something really nice about having the full power of SQL at your fingertips. The only drawback compared to ORMs of course is that it outputs the resulting query result in a flat structure. So any aggregation you'd need to handle in code or use something like Postgres JSON aggregation.

[1] https://github.com/seancorfield/honeysql