Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2022-11-17

Channels

#honeysql
<
2022-11-17
>
Ben Lieberman15:11:11

First time using HoneySQL this morning and this error is a bit confusing to me: 

Execution error (ExceptionInfo) at honey.sql/format-values (sql.cljc:794).
; :values expects sequences or maps
I worked around it by calling (into []) , but I would like to understand what I'm doing wrong. 
(-> (h/insert-into :posts) 
                    (h/values
                     (->Post 1 "testing a new post" (LocalDateTime/now) 1)) 
                    sql/format)
What is my Post record instance here if not a map?

seancorfield15:11:32

You are trying to pass a single hash map. :values expects a sequence of hash maps (or a sequence of column values -- a sequence of sequences).

seancorfield15:11:18

Looking at it, I can see how many folks would expect to be able to pass one or more hash maps to the values helper and for it to "do the right thing"... so I'll create a ticket for that.

Ben Lieberman15:11:55

Thanks, Sean. I think the "or" is what put me off the scent there.

seancorfield15:11:12

The problem is that :values sees [(map-entry ..) (map-entry ..) (map-entry ..)] and says "Hey, I'm expecting sequences or maps but I got a MapEntry!"

seancorfield15:11:25

I can make the error message better as well 🙂

seancorfield15:11:59

I'm changing it to 

:values expects a sequence of rows (maps) or column values (sequences)

seancorfield16:11:28

FWIW, the docstring of values says: 

Accepts a single argument: a collection of row values.
  Each row value can be either a sequence of column values
  or a hash map of column name/column value pairs.
I can fairly easily make it take one or more hash maps or two or more "sequence of column values" but determining whether a single argument of [[..] ..] or [{..} ..] is a single row with compound data values or a sequence of row values is much tougher, so I'll have to give that some thought so I don't break any existing code...

Ben Lieberman16:11:48

Duly noted re: values docstring. Given my incomplete understanding of seqs, I am curious why calling values on a single hashmap/record like I am doing here works? The exception only happens when I pass it to format.

Ben Lieberman16:11:32

I suppose that I expect the exception to be thrown in values and not in format

Ben Lieberman16:11:04

On the other hand, my use case is so trivial I can see why this behavior would never be encountered by anyone else.

seancorfield16:11:59

values only constructs data. format turns data into SQL. format is where all the checks are.

👍 1
seancorfield16:11:04

It looks like this is going to be harder to fix than I thought (without breaking backward compatibility). I'll have to give it some thought.

Ben Lieberman16:11:22

Not a problem. Now that I know how this works as-is, I think I'll be alright. Thanks as always.

seancorfield16:11:33

(because all the helpers are variadic and can optionally take a DSL hash map as their first argument)

seancorfield16:11:32

so (values {:insert-into :posts} {:foo 42}) could reasonably mean either "add :values [{:foo 42}] to the DSL" or "build an initial DSL with two rows for :values" 🙂 Even tho' the column value is funky there (but a string would be legal as a table name!).

Ben Lieberman16:11:11

Huh, I'll have to give that some thought myself as DSLs are relatively new to me in general. I'm wondering whether I'm maybe trying to do too little with HoneySQL anyway :thinking_face: my queries are so simple I might be better off just passing them as strings to postgres if that's possible.

seancorfield17:11:02

String-bashing is never nice, so HoneySQL helps even in fairly simple cases. The data DSL behind HoneySQL is mostly fairly straightforward but the helpers introduce both convenience and some confusion -- some people love them, some people hate them. I tend to use a mixture of helpers and raw data (but mostly helpers). The key with the helpers is that you can thread them in any order so 

(-> (h/insert-info :posts) (h/values [(->Post ..)]) ..)
and 
(-> (h/values [(->Post ..)]) (h/insert-info :posts) ..)
generate identical data (or at least should generate identical data!), which is what allows users to build the SQL statement up across multiple functions and via conditional logic. But that does tend to make the helper functions a little odd/complex at times because the data DSL is a hash map and all helpers have essentially two arities: one with the DSL as the first argument (to allow threading) and one without the DSL as the first argument -- which means that (some-helper my-hash-map another-hash-map) is ambiguous if some-helper could also be called as (some-helper the-dsl my-hash-map another-hash-map) And given that some users build up that data DSL using regular Clojure code without helpers, it can be hard to tell the difference 🙂

Eric Honsey19:11:11

I found an interesting unexpected behavior where honeysql is passing invalid column names when the table name is wrapped in a vector. It took a non-trivial amount of time to debug. Did I miss something in the docs? Does anyone know if there is an open issue on this? 

; DEPENDENCY => [com.github.seancorfield/honeysql "2.4.947"]

;; VALID
(-> (honey.sql.helpers/insert-into :my_pg_table)
    (honey.sql.helpers/values [{:foo "Always look..." :bar "on the bright side of life"}])
    (honey.sql/format))
;=> ["INSERT INTO my_pg_table (foo, bar) VALUES (?, ?)" "Always look..." "on the bright side of life"]

;; ERROR => note the declaration "MY_PG_TABLE() (foo, bar)" in the output block
(-> (honey.sql.helpers/insert-into [:my_pg_table])  ;; Vector Wrapper
    (honey.sql.helpers/values [{:foo "Always look..." :bar "on the bright side of life"}])
    (honey.sql/format))
;=> ["INSERT INTO MY_PG_TABLE() (foo, bar) VALUES (?, ?)" "Always look..." "on the bright side of life"]

Eric Honsey19:11:31

Or maybe just missing the best practice on using positional params vs vectors in the DSL functions?

valtteri19:11:11

A keyword inside a vector gets treated as a function https://github.com/seancorfield/honeysql#functions

💯 1
Eric Honsey19:11:35

Ah. Thank you so much 🙏

valtteri19:11:26

No problem. 🙂 I think honeysql is working correctly because at least theoretically table name from clause could be resolved by a function

Eric Honsey19:11:23

I agree with that given the docs. No need to open an issue

👍 1
seancorfield19:11:14

Specifically, this is legal: 

user=> (sql/format {:insert-into [:table :alternative] :columns [:foo :bar] :values [[1 2]]})
["INSERT INTO table AS alternative (foo, bar) VALUES (?, ?)" 1 2]
user=>

👍 1
seancorfield19:11:40

So HoneySQL accepts generalized expressions there to make life easier.

seancorfield19:11:18

(I think it reuses part of the formatter from :select so that aliasing works the same way -- and the [:table] ;=> TABLE() is just an artifact of that)

Eric Honsey21:11:03

Of course it was a gap in understanding the meaning of the vector structure. Need to spend some time with the DSL to understand the particulars. I really like the expressiveness though. Someday we’ll play Conway’s Game of [Honey]SQL 😁

Eric Honsey21:11:46

Or I suppose it will be Corfield’s Game of HoneySQL

seancorfield21:11:29

SQL is just so... gnarly...

Eric Honsey22:11:46

Yeah. It’s the 21st century. Let’s start modeling relational databases on general data structures and algebraic data types. I’ll bash out shell scripts like the rest of us, but at some point a string-driven interface becomes too much overhead. Same goes for SQL