how do you guys log the queries in dev envs?

@seancorfield does honeysql support case when... then..?

found only this: https://github.com/jkk/honeysql/blob/5176b576547457f7d7bea50e07d459b9c42b53d2/test/honeysql/core_test.cljc#L149

@mping As that test shows, you can use sql/call to create a case/`when` but there's nothing "built-in" beyond that.

seeking for advice here on the insert multi

i'm trying to insert multiple rows into database at once but i'm failing bad 😞

my code has 4 rows as maps and i handle them like this :

(let [row-keys (keys (first rows))
          rows-values (map vals rows)]
      (println row-keys)
      (println rows-values)
      (j/insert-multi! db table row-keys rows-values {})
      )

code output is as follows :

(id name nickname retired)
((1 bob the builder nil nil) (2 robby smith nil) (3 alice nil true) (4 jim jimmy jimmy nil))

but postgresql raw log says

2018-11-26 20:48:19.010 EET [12614] [email protected]_test_target_db LOG:  execute S_2: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.010 EET [12614] [email protected]_test_target_db DETAIL:  parameters: $1 = '1', $2 = 'bob the builder', $3 = NULL, $4 = NULL
2018-11-26 20:48:19.010 EET [12614] [email protected]_test_target_db LOG:  execute S_3: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.010 EET [12614] [email protected]_test_target_db DETAIL:  parameters: $1 = '2', $2 = 'robby', $3 = 'smith', $4 = NULL
2018-11-26 20:48:19.011 EET [12614] [email protected]_test_target_db LOG:  execute S_4: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.011 EET [12614] [email protected]_test_target_db DETAIL:  parameters: $1 = '3', $2 = 'alice', $3 = NULL, $4 = 't'
2018-11-26 20:48:19.011 EET [12614] [email protected]_test_target_db LOG:  execute S_4: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.011 EET [12614] [email protected]_test_target_db DETAIL:  parameters: $1 = '4', $2 = 'jim', $3 = 'jimmy jimmy', $4 = NULL

and that doesn't look like one insert statement to me 😞

not sure if i'm missing something in the docs here http://clojure.github.io/java.jdbc/#clojure.java.jdbc/insert-multi!

can it be that postgresql jdbc driver is tricking me ?

no, it actually still seems to be the clojure.java.jdbc itself ... i was expecting it to do the insert into table (cols) values (valueset1), (valueset2), (valueset3) kind of syntax , but that's not what is happening there

i guess i could achieve this with the reWriteBatchedInserts option described in https://jdbc.postgresql.org/documentation/94/connect.html

added this to the db connection map :

:reWriteBatchedInserts true

from postgres logs

2018-11-26 21:45:11.706 EET [15239] [email protected]_test_target_db LOG:  execute <unnamed>: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 ),( $5, $6, $7, $8 ),( $9, $10, $11, $12 ),( $13, $14, $15, $16 )
2018-11-26 21:45:11.706 EET [15239] [email protected]_test_target_db DETAIL:  parameters: $1 = '1', $2 = 'bob the builder', $3 = NULL, $4 = NULL, $5 = '2', $6 = 'robby', $7 = 'smith', $8 = NULL, $9 = '3', $10 = 'alice', $11 = NULL, $12 = 't', $13 = '4', $14 = 'jim', $15 = 'jimmy jimmy', $16 = NULL

Ah, good to know that it's a DB-specific issue there! I've opened https://dev.clojure.org/jira/browse/JDBC-174 to remind me to update either the docstring/docs page or the code to set expectations appropriately.

:thumbsup:

@seancorfield what is the best way to create a db connection and reuse it? For instance, if I run (query db ["select * from table"]) multiple times, I think its creating a new connection every time right? I've looked at get-connection and with-db-connection but I can't figure out how to use it, could you please give me an example?

would something like

(sql/with-db-connection [con db]
  (sql/query con ["select * from table"])
  ...
  (sql/query con ["select * from table"])
  )

@michaellindon i think that is fine for simple cases like that. otherwise, have you seen this? https://github.com/tomekw/hikari-cp#postgresql-example

I don't understand why I need it, I'd just like to persist a connection across multiple queries

with-db-connection will do that

that is what it is for

but most people use a connection pool like hikari or c3p0, so they never both with with-db-connection

there is also, I think it is just called db-connection, which will return an unscoped db connection

If you’re interested in having a stable basis for your queries, you might want to use perform your queries on a transaction using with-db-transaction (for a definition of stable implied by the transaction isolation level).