Retool supports writing to most SQL databases via a GUI mode. This mode has two main advantages: it provides an easier-to-use interface for constructing queries if you're not super familiar with writing raw SQL, and it helps avoid accidental destructive actions, like dropping a table. The GUI mode supports most writing operations: inserting rows, updating them, deleting them, and so on (even in bulk!).
Once you've configured an SQL resource, GUI mode will be available via a dropdown inside of the query editor:
Why is GUI mode disabled?
If GUI mode is disabled in the selector, it's probably because your database user does not have write permissions. Try returning to your resource settings and inputting a database user that has both read and write permissions.
After selecting GUI mode, you can then use the GUI to construct the query:
Now, when the query is manually triggered (e.g. when you click a button), the query will run and modify your existing database.
Restricting resource access to only SQL or GUI mode
It's sometimes preferable to restrict a SQL resource to only use GUI mode in the query editor. This can be particularly useful if you don't want users to run raw SQL write operations.
To enable this, check the “show write GUI mode only” option on the resource configuration page. Note that your database user credentials still need to have both read and write permissions, even if this setting is enabled.
As a complement, you can create an entirely separate resource for the same database, but with a database user that has only read permissions. Without write permissions, only the SQL mode will be enabled in the query editor. This protects the resource user from writing any destructive SQL statements, like
When evaluating a SQL
delete, you'll first want to filter out the rows the query will affect by looking at the "filter by" section:
If the results of the
filter by contain more than one row, Retool will notify you and refuse to run the query. If you intended to update multiple rows, though, you can check the "allow this query to modify multiple rows" checkbox:
If it's an
upsert, this setting will enable you to update multiple rows. If you're deleting rows, Retool only lets you delete up to 200 rows in one go.
If you'd like to make a bulk update to your database, you can pass in an array of records to update. An example of this is shown below:
In this example, Retool will first ensure that the
ProductID column is a unique key in the database table. If it is a unique key, Retool will iterate through the array and, for each member, merge the new values with the existing database record. In this case, it will update the product with
id = 680 to have the new color of
Red and update the product with
706 to a new color of
- The entire statement runs in a single transaction. If an error occurs in any of the updates, the transaction is rolled back with no effect on the database
- The query will timeout automatically after thirty seconds to prevent long running transactions from interfering with other database queries
This works just like "bulk updates via a primary key" above, but allows you to insert new records at the same time by including objects with new primary keys in the "array of records to update" field.
Updated about a month ago