handling transactions

Not immediately obvious from the docs: how to execute a transaction with oksql/query?

Hm yeah, that is something I didn't account for 😅

I did go on to write some other stuff on top of this thing for coast on clojure

At first I tried clojure.java.jdbc/execute! for migrations and dropping/creating databases, it didn't work out for the dropping/creating part, so then I found something like this:

(defn exec [db sql]
  (jdbc/with-db-connection [conn db]
    (with-open [s (.createStatement (jdbc/db-connection conn))]
      (.addBatch s sql)
      (seq (.executeBatch s)))))

Which works create for migrations and dropping/creating databases, unsure if it's overkill. Also unsure if that's what you meant by transactions, or if you just meant execute the sql statement in a transaction so you can roll it back

I meant running the SQL statement in a transaction, actually. But thanks for reminding me of execute!, can come in handy in testing.

Well in that case there isn’t a way currently 😭 PR opportunity!

surely!

as soon as I get a time window :(

Looking at both hugsql and yesql (see the very last example under that link), they wrap the calls to their functions in jdbc/with-db-transaction when they want to execute something within a transaction.

I take it oksql can do the same or you can try to "cook" that in, and maybe wrap the call to jdbc/with-db-connection

oksql/src/oksql/core.clj

Line 67 in 9f1b429

(jdbc/with-db-connection [conn db]

with jdbc/with-db-transaction in db-query function?

Let's say that the call to jdbc/with-db-connection is wrapped in jdbc/with-db-transaction, looking at the documentation it seems that nested transactions would be wrapped into the outer one. So if somebody wants to then use oksql functions and wrap them all in jdbc/with-db-transaction, there should be no issue.

If you're happy with this approach, I'll do the PR, because I quite like oksql. But what I'm a bit uncertain of is how to test transaction atomicity. What would a good test look like? I know that it should be something that demonstrates the atomic SQL query, but how exactly to do that in a test scenario?

Or should oksql be left as is and whoever needs to use a transaction, they can just use the same approach that hugsql & yesql are using?

Um I think it should be left as is, since yeah you can just call with-db-transaction and it'll use the transaction