sql 2021-05-21 | Slack Archive

seancorfield01:05:12

I’ve had a few requests for the ability to log the SQL and parameters in next.jdbc so I’ve added an experimental feature on develop if folks want to try it out and provide feedback.

seancorfield01:05:50

I will probably change the first argument to the logger function to be the fully-qualified symbol, but I’m open to any and all feedback on other aspects of how you might want logging to work.

seancorfield01:05:53

Automatically logging ResultSet data has all sorts of complications around it (size, plan doesn’t even produce a data structure to log, etc). However, having a second optional logging function that is called on the results of execute! and execute-one! is obviously possible if enough folks think it would be worth doing? There’s also the possibility of handling timing if the logging functions could coordinate. One possibility there is that the result of calling the first function on the sql-params could be passed as an argument to the second function (you wouldn’t be able to time plan reduction or operations performed on a PreparedStatement since those are not encapsulated within the library.

seancorfield01:05:25

Along these lines:

(let [state (sql-logger `execute! sql-params)]
  (result-logger `execute! state (execute! connectable sql-params opts)))

seancorfield01:05:15

An updated version with both sql and result logging, as an example:

dev=> (def lds (jdbc/with-logging ds 
 #_=>            (fn [sym sql-params] 
 #_=>              (prn sym sql-params) 
 #_=>              (System/nanoTime)) 
 #_=>            (fn [sym state result] 
 #_=>              (prn sym 
 #_=>                   (- (System/nanoTime) state) 
 #_=>                   (if (map? result) result (count result))))))
#'dev/lds
dev=> (sql/find-by-keys lds :foo {:name "Piyer"})
next.jdbc/execute! ["SELECT * FROM foo WHERE name = ?" "Piyer"]
next.jdbc/execute! 813049 1
[#:FOO{:NAME "Piyer"}]
dev=>

thumbnail05:05:13

Very interesting! At work we use a custom datomic DB in development which taps> each query, result and execution time. We were wondering if we could do something similar to next.jdbc. I'll take a look at it today and see if it fits the bill.

seancorfield05:05:42

@jeroen.dejong Cool. Since you just pass in two “logging” functions, you should be able to do whatever you need in that respect.

seancorfield05:05:49

(you can use the symbol passed in to determine whether or not the “result” function will be called)

thumbnail13:05:20

Thanks for the pointers! It works really well. Nice to have this one back in the toolchain 🙂 (even if it's just experimental for now)

thumbnail13:05:46

For datomic we actually allowed to pass a 'wrapper' fn; i.e. it worked like this:

(fn [f]
  (fn [arg-map]
    (println "before" arg-map)
    (let [res (f arg-map)]
      (println "after" res)
      res))

It allows for a little more freedom but the two fns are easier to work with 🙂

seancorfield16:05:35

That’s a good structure for being able to transform the result — but I don’t want to put a burden on users to correctly return the result set structure, just because they’re doing logging. I want them to be able to pass in simple functions (like prn) and not have it break.

➕ 2

dakra15:05:15

How can I tell next.jdbc to escape keywords when inserting? E.g.

(jdbc/execute! ds ["
create table address (
  id int auto_increment primary key,
  `key` varchar(32),
  email varchar(255)
)"])

(next.jdbc.sql/insert! ds :address {:key "fail" :email ""})

fails with sql syntax error in MariaDB.

dharrigan15:05:29

Maybe this might help?

dakra15:05:51

Oh, wow, thanks. I swear I read 10 times already over this page but didn't see it :face_palm: passing :column-fn next.jdbc.quoted/mysql works 🙂

dharrigan15:05:59

:thumbsup:

2021-05-21

Channels