Fork me on GitHub
#hugsql
<
2023-02-08
>
Lars Kristian Maron Telle12:02:49

I want to use Postgres transactions with HugSQL, and I'm pretty new to this. I saw in the https://www.postgresql.org/docs/current/tutorial-transactions.html that you set up transactions using BEGIN and COMMIT commands, like so:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;
Then I came across transactions in the https://www.hugsql.org/using-hugsql/transactions, e.g.,:
(clojure.java.jdbc/with-db-transaction [tx db]
  (characters/insert-character tx
   {:name "Miracle Max", :specialty "miracles"})
  (characters/insert-character tx
   {:name "Valerie", :specialty "speech interpreter"}))
Do these usages of "transaction" refer to the same concept, so if I want to use transactions in HugSQL, this is the way to do it? Or do I have to do something like this?
-- :name transaction :? :*
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

dharrigan14:02:24

Hi! When you say you are new to this, do you mean clojure+jdbc in general, or hugsql specifically?

serioga14:02:57

transactions in SQL are transactions around wrapped queries transactions in code can rollback on runtime errors in your code including DB query exceptions so both have their use cases

Lars Kristian Maron Telle15:02:46

@U11EL3P9U: Both, but I guess I know the least about jdbc (using clojure.java.jdbc, btw). I've been working on this project. I used Luminus to set it up. So far I haven't had the need to understand jdbc much, as everything just works, so I haven't really looked at it. I know how to work with basic HugSQL, i.e., creating new functions and using them. The problem that I want to solve: I want to update multiple rows in different tables in the database. I want all the updates to be applied succesfully, or none of them. My understanding is that one should use transactions for this. Assuming this is correct, I'd like to use transactions with HugSQL, but I don't know how to do it, and I'm a bit confused.

serioga15:02:19

if you can write your update as single HugSQL query then you can use BEGIN/COMMIT otherwise transactions in Clojure code will be more clear.

Lars Kristian Maron Telle15:02:22

Okay, so something like this as the HugSQL query would work fine?

-- :name make-transfer! :! :n
-- :doc updates accounts
BEGIN
UPDATE accounts SET balance = balance - 100.00
  WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
  WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
  WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
  WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
COMMIT;

Lars Kristian Maron Telle15:02:13

And then I could use make-transfer! in my request handler, for example?

serioga15:02:11

> Okay, so something like this as the HugSQL query would work fine? Looks OK, but I don't see where it can fail Are there DB constrains on negative balance?

Lars Kristian Maron Telle15:02:53

Nice. 😄 I just tried to convert the example from the Postgres documentation, so no constraints to worry about.

Lars Kristian Maron Telle15:02:14

So, if I understood you correctly, I could also achieve the same doing something like this:

;; HugSQL queries
-- :name update-alice-account!
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';

-- :name update-alice-branch!
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');

-- :name update-bob-account!
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Bob';

-- :name update-bob-branch!
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');


;; Usage:
(clojure.java.jdbc/with-db-transaction [tx db]
  (db/update-alice-account!)
  (db/update-alice-branch!)
  (db/update-bob-account!)
  (db/update-bob-branch!))

serioga15:02:03

Test it in REPL 🙂

seancorfield17:02:41

Just a note on clojure.java.jdbc -- it's old and no longer getting updates and the documentation is pretty poor. next.jdbc is the next generation of that library, has a better API, and has lots of documentation (and HugSQL has an adapter for it). I can't help with HugSQL (because I don't use it) but I can help with general #C1Q164V29 stuff and specifically c.j.j and next.jdbc if you get stuck.

Lars Kristian Maron Telle08:02:17

Right. I appreciate that. 🙂 Thank you all for your help.