I have a question regarding transactions in doobie. I have looked over the documentation here and it looks like it can only handle one query at a time?
Is it possible to have something like this
sql'''
begin;
select * from table where id=1 for update;
update table set id=2 where tabletest=2;
commit'''
any ideas or more examples/documentations anyone can point me to will be much appreciated! thank you!!
You are running things in transaction when you apply the Transactor
query.transact(transactor)
but that doesn't mean that you have to run whole transaction in a single line:
val operations = for {
myClass <- sql"""SELECT a, b, c, FROM table_x WHERE ...""".query[MyClass].to[List]
updatedRows <- sql"""UPDATE table_x SET ... WHERE""".update.run
} yield someResult
operations.transact(transctor)
Basically you turn each query/update into ConnectionIO which is a monad - you can use all monadic/applicative/functor operations from Cats there - flatMap, map, mapN, tupled, etc - to combine these ConnectionIOs into bigger ConnectionIOs and once you are done run them (you have been building queries but haven't been executing them so far!) with .transact(transactor) into the actual result computed in a transaction. BEGIN-COMMIT-or-ROLLBACK is handled by that .transact(transactor) which is why you don't write it yourself.
See Doobie documentation with this FAQ (How do I do several things in the same transaction?) in particular.