Fork me on GitHub
Slack Archive

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

2022-05-20

Channels

#honeysql
<
2022-05-20
>
rschmukler15:05:01

Does honeysql support anything for generating a batch values insert statement as expected by jdbc-next? ie. rather than generating INSERT INTO table (id, col) VALUES (?, ?), (?, ?), (?, ?) when doing (sql/format {:insert-into :table :values [{:id 1, :col "a"}, {:id 2, :col "b"}, {:id 3, :col "c"}) it does INSERT INTO table (id, col) VALUES (?, ?)

seancorfield16:05:17

I assume you mean jdbc/execute-batch! there? Feel free to open an issue and I'll give it some thought. The machinery that does that formatting is deep inside the formatter and it would affect a lot of how parameters are handled -- because anything could be an expression and have parameters lifted out of it.

seancorfield16:05:59

An easier "win" would be to enhance https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.780/api/next.jdbc.sql#insert-multi! so it could accept a sequence of hash maps and validate/split them into cols and rows.

rschmukler16:05:40

If I'm understanding the docs correctly insert-multi! will still not use batch statements (which makes sense as default). I'm specifically trying to get to batch statements (because my JDBC driver warns me when I don't use them 😛)

seancorfield16:05:05

@UEC8W94AE It's a tricky area, since both PG and MySQL need (different) JDBC URL parameters to perform the statement rewriting necessary to actually batch things properly...

seancorfield16:05:14

...but changing insert-multi! to conditionally use execute-batch! would be "relatively straightforward" and would be my preference, rather than trying to conditionally change HoneySQL's :insert-into behavior (since it is actually :values that would be affected -- and there's an interaction with :columns as well).

rschmukler16:05:45

That all makes sense @U04V70XH6 - I'll look at extending insert-multi! to support the hash maps + add an optional :batch option that converts it to using execute-batch! instead of execute! - thanks for the help!

seancorfield16:05:46

Thanks! That's definitely something I'd consider a PR for -- with multi-database tests and documentation, please -- or just create an issue as a placeholder and provide your thoughts.

rschmukler16:05:58

Let me know if you want me to just close https://github.com/seancorfield/honeysql/issues/408 with some of your thoughts (in case somebody else goes looking on GH)

seancorfield16:05:10

That code would fail if inserting composite values since it doesn't account for nested parens in the generated SQL.

seancorfield16:05:09

(feel free to close it -- the actual changes needed are almost certainly much more complex than they might appear)

rschmukler16:05:42

Closed! Thanks again for the input

rschmukler19:05:52

Thanks for the quick publish!

seancorfield19:05:47

Thanks for the high quality contribution!

rschmukler21:05:27

Confirmed that it's working for me chef_kiss

rschmukler15:05:10

If not, would you be open to a PR?

rschmukler15:05:27

Something like: 

(let [[sql & groups] (sql/format {:insert-into :table :values val-seq} {:batch true})]
  (jdbc/insert-batch! connectable sql groups))

rschmukler16:05:44

What I currently have (and works fine - but just wondering if this is something honeysql would like): 

(defn- -format-batch-insert
  "Convert a multi-parameterized sql insert map into a statement suitable for batch use.
  Returns a sequence that can be destructured as `[sql & param-groups]`"
  [{:keys [insert-into values]}]
  (let [key-count              (-> values first keys count)
        [raw-sql & raw-params] (sql/format {:insert-into insert-into :values values})
        sql                    (->> (str/split raw-sql ")")
                                    (take 2)
                                    (str/join ")")
                                    (#(str % ")")))]
    (concat
      [sql]
      (partition key-count raw-params))))
Obviously the impl could be cleaner inside of honeysql itself