Fork me on GitHub
#sql
<
2020-09-16
>
valtteri05:09:28

Yep, the sample code in the docs is demonstrating a "Clojure in - Clojure out" use-case. However I think adding the when guard to the example would make sense to avoid confusion if for any reason there's a JSON null in the database. It's cool to see this kind of edge-cases that I've never personally encountered. 🙂

valtteri05:09:02

Will you @seancorfield update the docs? If you wish, I can also do that later today.

seancorfield05:09:27

I'll do it right now, while it's fresh in my mind!

👍 3
valtteri05:09:36

Cool, thanks!

seancorfield05:09:03

Update pushed. It'll get to http://cljdoc.org when I make a new release (which likely won't be for a while).

🚀 3
Test This12:09:27

Thank you so much @seancorfield and @valtteri. I will try out the new example.

practicalli-john14:09:34

Hello, looking for an example of using next.jdbc snake-kebab-opts to convert hash-maps with kebab-case keys to a snake_case as part of the next.jdbc.sql/insert! function. I've tried different combinations to adding an options map to the insert! function, but haven't found the right syntax yet. https://cljdoc.org/d/seancorfield/next.jdbc/1.1.588/doc/getting-started#options--result-set-builders I added https://clj-commons.org/camel-snake-kebab/ so the docs suggest I dont need to provide a function, next.jdbc should convert it given the right option map, at least that's what I understood from the docs (which could be wrong) I have a specification that generates a hash-map with kebab-case keywords

(practicalli.specifications-banking/mock-data-account-holder)
;; => #:practicalli.specifications-banking{:account-holder-id
;;                                         #uuid "57e23f2a-e395-4af3-ba17-25bd741fe4ad",
;;                                         :first-name "59Z3vCS1L0pbhe17aF",
;;                                         :last-name "fKuQwcI1c7Ox2N2f1",
;;                                         :email-address
;;                                         "0x7jNK5sBrg52NPYt1pk8i3DqF",
;;                                         :residential-address "71DWd6kaN6DGy",
;;                                         :social-security-number
;;                                         "7g06UJEvyTaV6Pg2w694AsV42Yy"}
I can insert! using a hash-map
{:account_holder_id      (java.util.UUID/randomUUID)
 :first_name             "Rachel"
 :last_name              "Rocketpack"
 :email_address          ""
 :residential_address    "1 Ultimate Question Lane, Altar IV"
 :social_security_number "BB104312D"} 
However, using this function call to convert the keys from the spec to snake_case and insert...
(jdbc-sql/insert! db-specification-dev
                  :public.account_holders
                  (practicalli.specifications-banking/mock-data-account-holder)
                  {:column-fn next.jdbc/unqualified-snake-kebab-opts})
I get a H2 syntax error...
1. Unhandled org.h2.jdbc.JdbcSQLSyntaxErrorException
   Syntax error in SQL statement "INSERT INTO PUBLIC.ACCOUNT_HOLDERS (,[*] , , ,
   , ) VALUES (?, ?, ?, ?, ?, ?)"; expected "identifier"; SQL statement: INSERT
   INTO public.account_holders (, , , , , ) VALUES (?, ?, ?, ?, ?, ?)
   [42001-200]
An internet search seems to suggest that at least one of the column names is incorrect

dpsutton15:09:27

user> next.jdbc/unqualified-snake-kebab-opts
{:qualifier-fn #'camel-snake-kebab.core/->kebab-case,
 :label-fn #'camel-snake-kebab.core/->kebab-case,
 :builder-fn #'next.jdbc.result-set/as-unqualified-kebab-maps,
 :column-fn #'camel-snake-kebab.core/->snake_case,
 :table-fn #'camel-snake-kebab.core/->snake_case}

dpsutton15:09:59

i think you are passing a {:column-fn {:column-fn ...}} map. just use next.jdbc/unqualified-snake-kebab-opts as the options

practicalli-john15:09:55

@dpsutton Ah yes, blindingly obvious now you show me... it works. Thank you.

(jdbc-sql/insert! connection table record-data jdbc/unqualified-snake-kebab-opts)

seancorfield16:09:17

And if you work with a datasource as your primary DB "thing", you can use jdbc/with-options to annotate the datasource with those snake-kebab-opts and then you have a (wrapped) datasource that you can execute/insert/update/etc and the options get automatically applied.

practicalli-john19:09:14

Hmm, I'll need to think what that means, but sounds useful. I've been using with-open with get-connection and with-transaction so will be good to read up on the unwrapping caveats in the docs. Will be adding connection pooling next and then seeing how much change is needed to get everything to work with postgresql. Lots of next.jdbc docs still to read. Thank you.

seancorfield20:09:03

My recommendation is to create a pooled datasource (with HikariCP or c3p0) and just use that everywhere and only use get-connection if it is critical that a single connection is reused across multiple requests (unlikely, in my experience) and only use with-transaction where you absolutely need a transaction (also fairly rare, IME). That way you only have to worry about the unwrapping inside TX blocks.

seancorfield16:09:51

But read the caveats in the docs about with-options and when things get unwrapped, e.g., get-connection and with-transaction

schmee10:09:39

what do you think about adding the note about this in the Getting Started doc to the with-options docs? I was just about to ask this very question and I happened to see that you’ve already answered it 😄

seancorfield18:09:34

@U3L6TFEJF It's explained in the Getting Started doc already: > Note: Because get-datasource and get-connection return plain JDBC objects (`javax.sql.DataSource` and java.sql.Connection respectively), next.jdbc/with-options cannot flow options across those calls, so if you are explicitly managing connections or transactions as above, you would need to have local bindings for the wrapped versions:

seancorfield18:09:59

I guess I can add a note after the first mention to go read that section below...

schmee19:09:41

@seancorfield yes, I’ve seen the note, what I mean is adding that “note” part to the with-options docstring :thumbsup:

schmee19:09:57

the note explained it perfectly, I just couldn’t find it 😄

seancorfield19:09:27

Ah, OK. I added this note to Getting Started to link the two sets of examples https://github.com/seancorfield/next-jdbc/commit/ef96e04cb6bc4aa5a187f2f2ac5d21bef646d216

seancorfield19:09:59

I'll take a look at the docstring and see what I do. I do sort of assume folks read the whole the Getting Started guide before they, you know, get started 🙂

schmee19:09:44

haha, that is certainly a fair assumption, just making a suggestion on behalf of all us speedreaders out there 😁 :thumbsup:

schmee19:09:51

sweet, thank you! 🙂