Why doesn't Retool support SQL transactions?

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:

  1. Each query opens and closes a connection to the database and transactions cannot be held open across connections.

  2. 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.