Clojurians Log v2

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 https://github.com/seancorfield/honeysql/releases/tag/v2.0.0-beta1 for details of this release. I consider this stable for production usage (we’re already using Alpha 3 at work).

w00t!

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 http://cljdoc.org!)

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

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]
  (clojure.walk/postwalk
   (fn [e]
     (if (and (symbol? e)
              (contains? params e))
       `(sql/param ~(keyword e))
       e))
   form))

(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)))

@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 = ?.

https://github.com/seancorfield/honeysql/issues/320

Can you please tag my GH user, @bsless ?

Figured it wasn't going to be this easy 🙂

Tagged!

thank you 🙂

I have a tentative solution for https://github.com/seancorfield/honeysql/issues/315 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?

> 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: https://github.com/seancorfield/honeysql/blob/v2/src/honey/sql.cljc#L923-L951

That's pretty complicated

SQL is complicated, man 🙂

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…

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 🙂

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 https://github.com/bsless/contextual

Interesting.

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