Multi-Query Transactions in Workflows

Workflows
1

Hi Retool Team,

I am building an inventory management app that records inventory and transactions. Each transaction transforms parent assets into new child assets. Whenever a new transaction is created, the app records the new transaction as well as the mapping from parent assets to child assets. This requires inserting items into 3 separate tables in a Postgres DB in a specific order, then updating quantities of the parent assets.

It is important to wrap these 4 atomic operations into a single transaction. Previously I did this by allowing JS scripting inside DB queries and implementing the transaction in a single query. This was pretty messy and unsafe but got the job done. Now I am moving this functionality to a workflow and I'd like to separate each atomic operation into its own query, and exit differently depending on which query throws an error. I tried issuing seperate queries manually with BEGIN, ROLLBACK and COMMIT but the ROLLBACK doesn't work. I assume this is because every query is wrapped in its own transaction. Is there any way to disable this behavior and do a multi-query transaction currently? I am running Retool 3.12.4 self hosted.

If multi-query transactions in workflows aren't supported, I think they should be. The modular structure of workflows, and the fact that they run in the backend, would be perfect for this approach.

Thanks,
Sam

2

Bumping.

3

Hi @sshersh,

To my knowledge Retool does not currently support multi-query transactions in workflows.

In the meantime, there are a few workarounds that you can use to achieve the same result:

  1. Use a single query with JS scripting. This is the approach that you were using before, and it is still the most reliable way to implement a multi-query transaction.
  2. Use a stored procedure. Stored procedures allow you to group multiple SQL statements into a single unit. This can be a good option if you want to encapsulate your transaction logic and make it reusable.
  3. Use a third-party transaction library. There are a number of third-party libraries available that can help you to implement multi-query transactions in Retool.

Now, Retool is working on a new feature called "transaction blocks" which will allow you to group multiple queries into a single transaction. This feature is expected to be released in early 2024 I think.

Hope this helps.

:grinning:

Patrick

4

Hi Patrick,

Thanks for the response. Looking forward to that transacstion blocks feature rolling out!

Best,
Sam

1 Like
5

Same here!

:grinning:

Patrick

6

Hey @PatrickMast @sshersh, transaction blocks are not on the current roadmap. We do have a feature request for supporting transactions, and I've linked this topic to that internal ticket. We can update you here when there is any additional information to share. Thanks!