Documentation

Welcome to Retool! We're a fast way to build custom internal software.

You'll find the 5 minute demo, quickstart guide, and documentation for each of our connectors and components here. If you've got any questions -- chat with us on the bottom right!

Get Started    Guides

Writing to SQL

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

Overview

We support writing to most SQL databases with a GUI. We give you a GUI since we don't want you to accidentally drop your tables. The GUI lets you insert rows, update rows, delete rows, etc., and all in bulk too, if you're into that sort of thing.

To enable write access, you'll need to set up a database user with write permission, and add it to Retool with the "allow this connection to write back to database" option.

Use a separate resource for writes

When you enable the "allow this connection to write back to database" option, the resource will only show the GUI, which means you can't use the same resource for reading and writing. You should have a separate resource for reading data. Ideally, the credentials are read-only.

(Currently - if the credentials support writing, you're able to write raw SQL statements - including ones that affect the database. This will probably change in future versions of Retool.)

Enabling writes on a specific datasource will show a write-only GUI (see next screenshot).

Enabling writes on a specific datasource will show a write-only GUI (see next screenshot).

Then, when you want to write back to the database, make sure to select the correct datasource (the datasource for writing to a database is different from the one reading from it), and use our GUI to construct the query:

Updating the rating for a record in the `purchases` table by looking it up by `id`, and refreshing `purchases` once complete.

Updating the rating for a record in the purchases table by looking it up by id, and refreshing purchases once complete.

Write queries can't be previewed

We don't let you preview write queries by default. But if you really want, you can hook up a button to run and preview it. Watch out - writes are dangerous!

Now, when the query is fired (eg. when you click a button), the query will run and modify your existing database.

Bulk updates

When evaluating a SQL update, upsert, or delete, we first filter out the rows the query could affect by looking at the "filter by" section:

The `filter by` section.

The filter by section.

If the results of the filter by contain more than one row, we'll let you know 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'll let you update multiple rows. If you're deleting rows, we'll only let you delete up to 200 in one go.

Bulk updates via a primary key

If you would like to make a bulk update to your database, (e.g., you have a CSV that contains the new name for a set of users) - you can use Retool to perform the update for you. 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, then it will iterate through the array and for each member, it will 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 the 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 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 with" field.

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.