Hey there,
Any chance we will get transaction block support in the future? It'd be helpful to input and store that information in Retool so it's clear for other developers to see how resource queries affect the backend.
Accord to this previous post in 2021, this wasn't available. Hoping there's a chance it gets allowed in the future: Running Multiple Queries in Transaction Lock
Thanks all
Hi @shu—at the moment, there's nothing planned for this on our roadmap, but I'll keep this thread updated!
Hey @victoria, thanks for the response.
Do you have any suggestions / alternatives for handling rollback?
I know I can use event handlers to trigger other queries (e.g., one resource query does a bulk update via primary key, then triggers on success a stored procedure in a different resource query), however I'd like to rollback a previous query in the event a downstream one fails.
Is there a JS query solution that would allow for this?
Thanks.
We don't have anything to natively support handling rollback, but you can always run a query to undo the action you just did On Failure of the query. Would something like that work for you?
You can also write a JS query to use query.trigger and the onFailure parameter. Example here: https://docs.retool.com/docs/scripting-retool#4-write-the-javascript-query
Let me know if either of those routes sound possibly helpful and I'd be happy to help guide you down them!
@victoria is right, you would need to make a query to undo your earlier changes and that can all get pretty hairy if you are doing more than a couple queries.
What I do in those situations, and I realize this is not an option for everyone, is to create a stored procedure on the database to handle all of the queries.
I also wonder (never tried it) if you could disable converting queries to prepared statements and then run multiple SQL queries at a time all wrapped in a transaction block?
A tad risky maybe: make a special resource for doing such things and leave it on for your other queries.