Not supporting SQL transactions opens the door to a lot of bugs that can easily be avoided otherwise. Plus, operations take more time as one needs to make several requests instead of just one.
Why does Retool supports writing SQL directly but not transactions?
Let me take stab at this, if I am incorrect or inaccurate, hopefully the Retool Gurus can correct me.
I am assuming this is because:
Each query opens and closes a connection to the database and transactions cannot be held open across connections.
The nature of parameterized queries does not allow multiple queries at a time. No reason to have transactions if you are only running single query.
So I think the limitations is with SQL itself when in a distributed environment (which is where it almost always lives) and database engine rather than with Retool.
One way around this which I have used myself is to create a stored procedure to wrap all the queries in your transaction. This of course only works if you have the right access levels to your database.
Now, if I am wrong about #1 above and a connection can he held open, then it may be theoretically possible for Retool to enable transactions.
I do believe this would be a mistake however. My philosophy states that if you need multiple queries for a business process (this defines most scenarios where a transaction is required) , they should be as "close to the metal" as possible. Retool adds layers of connections and abstractions between the client and the server, greater distance let's call it. This increases the latency and reduces the performance and reliability of your query stack.
This is assuming you want transactions across multiple different retool queries. Sometimes you just want multiple SQL queries within a single transaction in a single retool query.
That should be possible without needing to write a stored procedure to handle (which is the current case).
And also, why not a single transaction for perhaps a retool code query/js block? That would be easier to implement within the scope of a single connection, and cover most use cases.
Hey All, just wanted to chime in. @bradlymathews has laid out a lot of the reasons here, thanks for your reply!
You can use transactions for multiple SQL queries in a single Retool query, as long as those are
non-parameterized queries. In practice, I understand that those queries aren't really that common or helpful in a Retool app, but they do work.
Spoke with an engineer on the team responsible for our SQL integrations, and it looks like support for transactions in a single Retool query with parameters may be possible, but isn't high on the roadmap right now. Support for transactions over multiple Retool queries is likely not possible. I did link this thread to that internal ticket, and can update this thread as I get any more information to share. Thank you!