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).
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.
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.
When evaluating a SQL
delete, we first filter out the rows the query could affect by looking at the "filter by" section:
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
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.
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
706 to the 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 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.