Write data to SQL databases
Learn how to use GUI mode to safely write data to SQL databases.
Retool supports writing to most SQL databases using a GUI mode. This mode has two main advantages: it provides a way to write queries that doesn't require a lot of experience with writing raw SQL, and it helps prevent destructive actions, like dropping a table. The GUI mode supports most writing operations: inserting rows, updating rows, deleting rows, bulk updates, etc.
Get started
After configuring a SQL resource, GUI mode is available inside of the query editor:
If GUI mode is disabled, open your resource's settings and make sure the database user has both read and write permissions.
After selecting GUI mode, you can configure the query.
When the query is manually triggered (e.g., when you click a button), the query runs and modifies your existing database.
Restrict 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 is particularly useful if you don't want users to run raw SQL write operations.
To enable this, select 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.
You can also create a separate resource for the same database, but with a database user that has only read permissions. Without write permissions, only SQL mode is enabled in the query editor. This protects the resource user from writing any destructive SQL statements, like DROP TABLE.
Bulk updates
When creating an update, upsert, or delete query, filter out the rows the query affects by filling out the Filter by section.
If the filters results in more than one row, Retool notifies you and blocks the query from running. If you need to update multiple rows, select the Allow this query to modify multiple rows in the database? option.
For update or upsert queries, this setting enables you to update multiple rows. If you're deleting rows, Retool only allows you to delete up to 200 rows in a single query run.
Bulk updates using a primary key
If you want to make bulk updates to your database, you can pass in an array of records to update.
In this example, Retool ensures that the ProductID column is a unique key in the database table. If it is a unique key, Retool iterates through the array and merges the new values with the existing database record. In this case, it updates the product with id = 680 to have the color Red, and updates the product with id of 706 to the color Green.
Bulk updates runs in a single transaction. If an error occurs in any of the updates, the transaction rolls back with no effect on the database. The query timeouts automatically after thirty seconds to prevent long running transactions from interfering with other database queries
Bulk upserts using a primary key
Bulk upserts work the same as bulk updates but they allow you to insert new records at the same time by including objects with new primary keys in the Array of records to update field.
