Fork me on GitHub
#honeysql
<
2021-04-10
>
seancorfield07:04:01

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

🎉 3
dharrigan07:04:21

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

seancorfield07:04:09

At least a couple of weeks, yes.

seancorfield07:04:45

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.

seancorfield07:04:12

(I’ve already found and pushed a bunch of small doc fixes since Beta 1 hit http://cljdoc.org!)

dharrigan07:04:48

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

seancorfield07:04:20

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.

seancorfield07:04:22

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

dharrigan07:04:08

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]
  (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)))
I wouldn't feel half as bad about it if it didn't use eval thoughts?

seancorfield16:04:10

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

seancorfield16:04:38

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

seancorfield18:04:13

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.

seancorfield18:04:24

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.

seancorfield18:04:08

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.

seancorfield18:04:17

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

👀 3
Ben Sless18:04:21

That's pretty complicated

seancorfield19:04:36

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

seancorfield19:04:19

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

seancorfield19:04:01

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

seancorfield19:04:59

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

seancorfield19:04:43

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

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 🙂

seancorfield17:04:48

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]]}?

seancorfield18:04:17

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.

seancorfield18:04:00

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?