Writing to SQL

insert, update, upsert, and delete. Even in bulk!

Overview

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!).

Getting Started

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:

Updating the status for a record in the orders table by looking it up by id.

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 DROP TABLE.

Bulk updates

When evaluating a SQL update, upsert, or delete, you'll first want to filter out the rows the query will affect by looking at the "filter by" section:

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 update or 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.

Bulk updates via a primary key

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 id of 706 to a new color of Green.

Notes

  • 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

Bulk upserts via a primary key

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 9 months ago


Writing to SQL


insert, update, upsert, and delete. Even in bulk!

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.