Fork me on GitHub
#sql
<
2019-05-26
>
souenzzo01:05:06

I'm using jdbc.next When I run a simple query, it returns with nil values. Something happened after this? http://corfield.org/blog/2018/12/06/null-nilable-optionality/

seancorfield01:05:10

Yeah, I gave that a lot of thought and decided, for the first stable release, to keep nil values in hash maps, based on a lot of feedback I got on Slack, Zulip, and elsewhere.

👍 4
seancorfield01:05:46

What I will probably do is add a "builder" function for hash maps that omits nil values, so folks can opt into this behavior.

seancorfield01:05:38

You could create a variant of MapResultSetBuilder with this function altered:

(with-column [this row i]
    (assoc! row
            (nth cols (dec i))
            (read-column-by-index (.getObject rs ^Integer i) rsmeta i)))
so that it called (.getObject ..) but just returned row untouched if that was nil.

seancorfield01:05:57

@souenzzo Would you prefer to get nil back or for the keys to be omitted for nil values?

seancorfield01:05:19

Reasons not to do it by default include: it doesn't work at all for the as-arrays still builder; it won't necessarily work if you try to update! based on the result of an execute-one! / query (the missing columns would not be updated, whereas with nil they would explicitly be set to NULL)...

souenzzo01:05:27

As a #datomic user, nil in database is a strange thing to me. But in SQL, nil in database is a thing I do not have enough experience to prefer anything ATM

seancorfield01:05:57

Yeah, a lot of people argued that NULL was a thing in SQL and needed to be surfaced in a lot of cases.

seancorfield01:05:56

I'm comfortable with the extensibility of next.jdbc that adding new behaviors via builders is a good way to go. The tests include a builder that produces records for rows, for example.

👍 4
souenzzo02:05:53

I'm doing something wrong? [next.jdbc :as jdbc]

(let [db (jdbc/get-datasource db)
      db* (jdbc/get-connection db {})
      tx (jdbc/prepare db* ["SELECT authed FROM app_session"]
                       {:builder-fn next.jdbc.result-set/as-unqualified-maps})]
  (jdbc/execute! tx))
=> [#:app_session{:authed nil} #:app_session{:authed nil}]

souenzzo02:05:23

using jdbc/execute! works! It's a prepare bug

souenzzo02:05:43

opts from get-connection is undocumented is it the right way to use?

(let [db (jdbc/get-datasource db)
      db* (jdbc/get-connection db {:builder-fn rs/as-unqualified-maps})]
  (jdbc/execute! db* ["SELECT authed FROM app_session"]))
If is, it's not working

seancorfield02:05:18

prepare doesn't create a result set so :builder-fn makes no sense there.

seancorfield02:05:33

execute! does create a result set, so :builder-fn does make sense there.

seancorfield02:05:04

Not sure what you're asking about get-connection? The options that make sense for get-connection are documented here https://cljdoc.org/d/seancorfield/next.jdbc/1.0.0-beta1/doc/all-the-options

seancorfield02:05:48

Remember that get-connection produces a java.sql.Connection object -- so only options that can affect that object make sense.

seancorfield02:05:42

What you want is

(let [db (jdbc/get-datasource db)
      db* (jdbc/get-connection db {})
      tx (jdbc/prepare db* ["SELECT authed FROM app_session"])]
  (jdbc/execute! tx {:builder-fn next.jdbc.result-set/as-unqualified-maps}))
=> [{:authed nil} {:authed nil}]

souenzzo11:05:18

I was browsing the wiki and the code, the answer was in the cljdoc 😅 cljdoc looks awesome! Thks! But for me, choose a builder-fn in get-connection to do not care about it in the execute! may be a nice feature

seancorfield02:05:42

What you want is

(let [db (jdbc/get-datasource db)
      db* (jdbc/get-connection db {})
      tx (jdbc/prepare db* ["SELECT authed FROM app_session"])]
  (jdbc/execute! tx {:builder-fn next.jdbc.result-set/as-unqualified-maps}))
=> [{:authed nil} {:authed nil}]

seancorfield02:05:40

@souenzzo Remember that: • get-datasource produces a Java object, javax.sql.DataSourceget-connection produces a Java object, java.sql.Connectionprepare produces a Java object, java.sql.PreparedStatement And none of those will carry options through the to "next" call you make.

seancorfield03:05:20

@souenzzo Did that help you?

👍 4
tianshu04:05:33

@seancorfield I have a question for next.jdbc. looks like I can only give sql-params to next.jdbc/plan. but there are functions in next.jdbc.sql. why not these functions return sql-params that can be used in either next.jdbc/execute! or next.jdbc/plan?

seancorfield04:05:34

@doglooksgood Because the sql+params they build is consumed by the API-level functions in that namespace -- they wouldn't be useful outside that namespace.

seancorfield04:05:47

That whole namespace is merely a convenience for a handful of common operations. They're not the primary API. And if you want to build sql+params, use something like HoneySQL...

seancorfield04:05:22

plan, execute-one!, and execute! all take sql+params, as does prepare.

seancorfield04:05:20

HoneySQL (and similar DSLs) produce sql+params. Several clojure.java.jdbc functions accept that. next.jdbc's primary API accepts that.

seancorfield05:05:07

@doglooksgood Did that answer your question?

dangercoder21:05:26

is there any managed service for postgres that anyone can recommend? I am currently on datomic-cloud for my dev-environment but I am not too sure about it anymore since i know it won't play nice with GDPR in Europe.

dangercoder21:05:51

I am on AWS so I guess https://aws.amazon.com/rds/postgresql/ would be the obvious choice.

lilactown22:05:19

yep RDS is good, would recommend before any others if you’re already drinking the AWS kool-aid

lilactown22:05:02

you might also explore how datomic cloud can support GDPR. it seems like excision would allow you to comply? not an expert in either Datomic or GDPR

kulminaator22:05:30

might be sounding like an AA meeting but "i'm martin and i've been using rds for a while now". not as flexible as your own setup but dead easy to get up and running.