2023-02-08 hugsql | Clojure Slack Archive

hugsql

Zeniten 2023-02-08T12:48:49.291609Z

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;

dharrigan 2023-02-08T14:37:24.228369Z

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

serioga 2023-02-08T14:54:57.249959Z

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

Zeniten 2023-02-08T15:10:46.134479Z

@dharrigan: 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.

serioga 2023-02-08T15:13:19.588499Z

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.

Zeniten 2023-02-08T15:20:22.537259Z

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;

Zeniten 2023-02-08T15:21:13.430849Z

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

serioga 2023-02-08T15:23:11.041519Z

> 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?

Zeniten 2023-02-08T15:25:53.942489Z

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

Zeniten 2023-02-08T15:34:14.450529Z

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!))

Zeniten 2023-02-08T15:34:20.517649Z

Is that correct?

serioga 2023-02-08T15:35:03.131089Z

Test it in REPL 🙂

Zeniten 2023-02-08T15:35:54.356689Z

😊 👍

seancorfield 2023-02-08T17:07:41.211579Z

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 #sql stuff and specifically c.j.j and next.jdbc if you get stuck.

Zeniten 2023-02-09T08:45:17.785929Z

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

Clojurians Log v2

hugsql