On several occasions I've been taken aback by Retool not supporting things like transactions or even simply returning the ids of the inserted rows after a bulk insert.
Let's allow Retool to support dynamic SQL generation with prepared statements enabled.
How I imagine it might work:
The query's SQL mode will support entering '?' wherever an input is needed. The query will have one additional field that's going to be an array where all the inputs will be given. Retool can then create a prepared statement and use the inputs from this array.
For example:
Let's say I want to write a query that bulk inserts several users and returns their ids (currently not supported):
insert into users (name) values ? returning id`
Then I set the Inputs field (the new field) to {{ [['John'], ['Richard']] }}.
Having this lower-level approach will open the doors to support any possible SQL construct without the Retool team having to do anything for each specific use case.