Fork me on GitHub
#sql
<
2017-06-29
>
mattly00:06:48

for the curious, this is going in my sql-utils.clj:

mattly00:06:01

it's a starting point

noisesmith00:06:26

tiny style thing, you can replace #(vector %1 %2) with vector, or even list

mattly00:06:34

derp right

seancorfield00:06:03

Something else to look at, from a debugging p.o.v.: https://github.com/ttddyy/datasource-proxy

bja01:06:09

if you happen to use honeysql, I use a utility to EXPLAIN ANALYZE

(require '[clojure.java.jdbc :as j])

  (defn explain-analyze
    [postgres q]
    (->> (update-in (honeysql.core/format q) [0] (fn [x] (str "EXPLAIN ANALYZE " x)))
         (j/query postgres)
         (mapv #(get % (keyword "query plan")))
         (str/join "\n")
         println))

(comment
  (explain-analyze (:postgres sys)
                   (bulk-action-counts-all-time actions contents)))

seancorfield01:06:27

@bja did you know that you could specify :explain? "EXPLAIN ANALYZE" as an option on java.jdbc/query?

seancorfield01:06:34

By default :explain? true uses just plain old "EXPLAIN" but you can also specify a string and it will use that instead. You can also specify :explain-fn which will do whatever you want with the output of the EXPLAIN SELECT... — by default it’s println.

seancorfield01:06:03

If you want all your SQL queries explained, you can just add :explain? to your db-spec map (you can set any default options there and they apply to all calls).

seancorfield01:06:03

(and, er, yeah, I know this isn’t documented anywhere yet, except in the change log for java.jdbcahem working on that… honest!)

bja02:06:29

I've had this functionality sitting in a scratch namespace for awhile

bja02:06:39

I don't suppose java.jdbc happens to do statement logging too?

bja02:06:14

I hacked that into my company's curiosity.components.jdbc such that I can add (with-query-logged log-level ...) or (with-honey-map-logged log-level ...) to anything and get a spy of sql generated or the honeysql map, but at least the sql generation part is probably generic enough to belong in java.jdbc

seancorfield02:06:16

No, but it’s easy with that datasource-proxy library I linked to above.

seancorfield02:06:53

I’d be interested to see how you added query logging on a per-query basis tho’…

bja02:06:22

tldr; dynamic var sitting inside my facade to jdbc/query

bja02:06:37

with-query-logged just changes the value inside the dynamic scope

seancorfield02:06:37

Right, but what I meant was, how exactly do you log each query?

seancorfield02:06:51

Thanks… reading…

bja02:06:57

we've been meaning to getting around to open sourcing that anyway

bja02:06:10

I actually have approval to do so, just a matter of finding time at a startup

bja02:06:49

most of our usage is something along the lines of (query-runner db-spec honeysql-map)

bja02:06:07

log/spy is taoensso.timbre/spy

seancorfield02:06:42

So you just log the vector of SQL + params that is going into the JDBC driver?

bja02:06:45

technically speaking you don't have to force the query, but we do because we know our queries are small

bja02:06:02

it would be easy to just log the query if that was a concern for security

bja02:06:23

but it's super handy to be able to (with-query-logged ...) something when you get confused/curious

seancorfield02:06:51

I tried out that datasource-proxy above — it was a simple change to the one line where we construct our (pooled) datasource — and it logs everything:

17-06-29 00:51:36 sean-corfields-imac.local INFO [net.ttddyy.dsproxy.listener.logging.SLF4JQueryLoggingListener:20] - Name:, Time:1, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["SELECT code FROM countryFeatured WHERE siteId = ?"], Params:[(45)]
17-06-29 00:51:37 sean-corfields-imac.local INFO [net.ttddyy.dsproxy.listener.logging.SLF4JQueryLoggingListener:20] - Name:, Time:2, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["SELECT ISO, Country FROM countryInfo ORDER BY Country ASC"], Params:[()]

seancorfield02:06:10

(deliberately picking some not very interesting queries from our app 🙂 )

bja02:06:18

I could log everything via setting that var at startup

bja02:06:25

but we....don't ever do that

seancorfield02:06:18

I’m thinking that a :logging? option could be added to java.jdbc and :logging-fn option (defaulting to println) could allow for any logging function to be passed in as desired.

bja02:06:26

that might be handy

seancorfield02:06:31

Then it could be built-in to the library…

bja02:06:54

when I gave a short talk on honeysql, the feedback I got was that my utility functions were the most useful part

seancorfield02:06:56

Logging result sets is a lot more problematic (because of their size)…

bja02:06:03

and most of the util functions were just stuff that belonged in java.jdbc or honeysql

seancorfield02:06:15

OK, dinner time here. Thanks for sharing that!

seancorfield18:06:26

I’m looking for feedback on Clojure version support for the next release of clojure.java.jdbc — if you use the library, could you please complete this very brief survey? https://www.surveymonkey.com/r/MR2HRFD Thank you!