Fork me on GitHub

com.github.seancorfield/honeysql {:mvn/version "2.0.0-beta1"} — HoneySQL V2 moves out of Alpha! At this point I consider the documentation “complete” (it will continue to grow but I believe it now covers all V2 functionality) and the APIs are all stable. Only accretive/fixative changes will be made from this point on. See for details of this release. I consider this stable for production usage (we’re already using Alpha 3 at work).

🎉 3

Will you let it bed in for a couple of weeks before promoting to release?


At least a couple of weeks, yes.


I’m hoping for wider usage/testing now it’s “stable” (out of alpha) and therefore more feedback on the docs and some of the specific functionality.


(I’ve already found and pushed a bunch of small doc fixes since Beta 1 hit!)


I'm writing a new backend service, using v2, nothing fancy, nothing pushing the boundaries, but will keep an eye on it 🙂


Of the open issues, I definitely want to get #308 and #310 done for an RC, maybe #315 as well. I still need to figure out what to do about #146 (Spec) and I need to come up with a suitable extension hook for Gungnir #276.


But those are all accretive at this point. The rest will slip to 2.1 or later.


All excellent news. It's a great library

Ben Sless08:04:20

Hi, something I've been wondering about regarding honeysql for a while is if there's any way to render a string in advance and pass it parameters as symbols. From what I've seen there's no way to avoid calling format every time, but I could have missed it. What I had in mind can best be illustrated by this little macro I wrote:

(defn parametrize-form
  [form params]
   (fn [e]
     (if (and (symbol? e)
              (contains? params e))
       `(sql/param ~(keyword e))

(defn format
  [args query]
  (let [form (parametrize-form query (set args))]
    (sql/format (eval form) (zipmap (map keyword args) args))))

(defmacro defsql
  [name args query]
  `(defn ~name [~@args]
     ~(format args query)))
I wouldn't feel half as bad about it if it didn't use eval thoughts?


@ben.sless yeah, this is something I've thought about often but haven't yet come up with a good way to handle in the context of HoneySQL. It's definitely still on my radar.


I’ll create a GH issue for tracking. It definitely feels like something worth doing. It’s not easy though: there are some constructs that don’t lend themselves to it in all cases — the two that spring to mind immediately are [:in :col some-vals] where the generated string depends on the number of elements in some-vals and even something as simple as [:= :col foo] because the generated string is different for foo being nil, vs foo being non-`nil`: col IS NULL vs col = ?.

Ben Sless17:04:24

Figured it wasn't going to be this easy 🙂

Ben Sless18:04:49

> produce strings with special placeholders so that only those pieces of the overall string needed to be generated for a final format Wonder if it's possible to return a closure and delay the building Emit something like:

(fn [foo]
  (let [rendered-foo (if (nil? foo) " IS NULL" " = ?")]
    (let [sb (StringBuilder.)]
      (.append sb "SELECT * WHERE col")
      (.append sb rendered-foo))))


Right now, the entire formatting system expects to use str to bash pieces together under the hood. I suspect I’d need to change every piece of code that does that to allow for a sequence of strings-or-thunks and then have just the top-level format actually bash everything together — but that would break the public API functions format-expr and format-dsl, as well as making it much harder for anyone to extend HoneySQL for their own syntax.


One “easy” workaround is to build the sql+params once with dummy values and just reuse that sql string with different sets of values — but that doesn’t account for IN or the NULL stuff.


Another option is to produce a Selmer-style template that could just be formatted with the final set of parameters — but that’s still going to potentially do a bunch of string-bashing each time anyway.


As an example of the complexity of logic needed to “do the right thing”, look at how much conditional logic is in format-in to take into account empty collections and nil values:

👀 3
Ben Sless18:04:21

That's pretty complicated


SQL is complicated, man 🙂

😄 3
Ben Sless19:04:00

I think it was Rich in one of his talks who commented on how silly SQL was, instead of being a protocol/specification first it was a natural language first and doomed us forever to string-manipulation-purgatory


Yeah, it’s been eye-opening trying to implement a much large subset of SQL for V2 than V1 supported — and the vendor-specific extensions are often completely arbitrary as far as syntax goes (in my opinion).


Every year I continue to work with relational databases is a year that I get ever closer to wanting something more like Datalog instead 🙂


That said, HoneySQL has definitely made our lives easier at World Singles Networks, where we programmatically compose some very gnarly reporting queries full of conditionals…

Ben Sless19:04:44

Working with data is certainly better than working with strings, don't get me wrong, but at the bottom of the stack stands some poor soul who has to write a compiler for that mess


As someone who started their career in IT writing compilers, yes, I feel that pain 🙂

🙂 3
Ben Sless19:04:44

And speaking of bashing strings, I don't know if you'll find this useful, but I wrote a templating engine which bashes strings relatively fast because I needed one. Was somewhat inspired by Metosin's approach

Ben Sless19:04:45

Returning types which implement the desired behavior at run/compile/render time can speed you up significantly over runtime interpretation. Another thing I did because I was already used to that technique was unroll loops where possible, which is why I have ugly macros emitting defrecord s with 20 arities

Ben Sless17:04:59

Can you please tag my GH user, @bsless ?

Ben Sless17:04:12

thank you 🙂


I have a tentative solution for but I’d like some input: if you effectively say {:where [:in :id []]} what would you expect to get? Also {:where [:in :id [nil]]}?


Since id IN () is not legal SQL and no IDs are in an empty set, I would lean toward producing WHERE FALSE for that case and WHERE id IS NULL for the second case.


And, just as a sanity check, do folks think adding an explicit OR id IS NULL is the correct way to handle this in the first place?