sql

All things SQL and JDBC...
practicalli-john 2020-09-04T17:47:54.184200Z

I am assuming using lazy functions like clojure.core/map shouldn't be used inside next.jdbc transactions (as I type this, it seems more obvious that they should not) I'm working through the excellent next.jdbc doc and started using with-transaction to execute three SQL statements, each creating a table in the database (H2). I created a simple helper function as follows:

(defn create-tables
  "Establish a connection to the data source and create all tables within a transaction.
  Close the database connection.
  Arguments:
  - table-schemas: a vector of sql statements, each creating a table"
  [table-schemas data-spec]

  (with-open [con (jdbc/get-connection data-spec)]
    (jdbc/with-transaction [tx con]
      (map #(jdbc/execute! tx %) table-schemas ))))
If I step through the create-tables function with the CIDER debugger it works, although calling it without the debugger gives an error: "transaction is already closed". Replacing this with doseq works correctly.
(with-open [connection (jdbc/get-connection data-spec)]
    (jdbc/with-transaction [transaction connection]
      (doseq [sql-statement table-schemas]
        (jdbc/execute! transaction sql-statement) )))
Is there something in next.jdbc that would be a better approach to executing multiple queries from a data set when creating tables (and before I get to migratus).

iarenaza 2020-09-04T18:36:42.184800Z

You could also use mapv, which is eager.

seancorfield 2020-09-04T19:06:36.185100Z

run! or doseq would be the better choices.

👍 1
practicalli-john 2020-09-04T19:40:45.186800Z

I like the sound of using run! aesthetically, thanks for reminding me about that. I always feel a little unclean using mapv just to get rid of lazyness 🙂