Write data to SQL databases
Learn how to construct queries to safely write data to SQL databases and similar data stores.
You can create SQL queries using a visual interface (GUI mode) to safely write data to a connected database resource, such as a PostgreSQL or a MySQL database. This method helps prevent against destructive actions that malformed SQL statements could cause, such as dropping a table or overwriting existing data with incorrect values.
- Web or mobile app
- Workflow
1. Add a query
- Web or mobile app
- Workflow
- Navigate to the Code tab in the IDE.
- Click + to add a new query.
- Select the resource.
- Either:
- Right-click on the canvas and select the Resource query block.
- Click ⊕ in the left toolbar to open the Blocks tab, then select Resource query block.
- Click-and-drag from an existing block to create a new, connected block.
- Select the resource.
There are two modes from which to select when writing an SQL query. The mode you choose depends on whether you need to read or write data:
- SQL: Read data using an SQL statement.
- GUI: Write data using a graphical query editor.
Select GUI mode.
2. Select the action type
- Web or mobile app
- Workflow
Retool supports the following action types for writing data to an SQL database. Actions can require a Changeset (values to modify) or Filter options (to determine which records to update), or both.
Name | Description | Changeset | Filter |
---|---|---|---|
Insert a record | Create a new record using the provided changeset values. | ||
Update an existing record | Update an existing record with the provided changeset. | ||
Update a record, or create a new record if it doesn't exist | Update an existing record with the provided changeset. If the specified record doesn't exist, the query creates a new one. | ||
Delete a record | Delete the specified record. | ||
Bulk insert records | Create multiple records at once with the provided changeset. | ||
Bulk update via a primary key | Update multiple records with the provided changeset. | ||
Bulk upsert via a primary key | Update multiple records with the provided changeset. If any records do not already exist, create new ones. |
Retool supports the following action types for writing data to an SQL database. Actions can require a Changeset (values to modify) or Filter options (to determine which records to update), or both.
Name | Description | Changeset | Filter |
---|---|---|---|
Insert a record | Create a new record using the provided changeset values. | ||
Update an existing record | Update an existing record with the provided changeset. | ||
Update a record, or create a new record if it doesn't exist | Update an existing record with the provided changeset. If the specified record doesn't exist, the query creates a new one. | ||
Delete a record | Delete the specified record. | ||
Bulk insert records | Create multiple records at once with the provided changeset. | ||
Bulk update via a primary key | Update multiple records with the provided changeset. | ||
Bulk upsert via a primary key | Update multiple records with the provided changeset. If any records do not already exist, create new ones. |
Select a table and the desired Action type for your query.
3. Specify a changeset
- Web or mobile app
- Workflow
You specify the Changeset for all actions except Delete a record. For queries that modify a single record, this can be either:
- A manually defined set of key-value pairs, where each key corresponds to a column.
- An object with key-value pairs, such as a Form component's
data
value.
Configure the changeset as needed. You use {{ }}
embedded expressions to reference other values within the query.
For bulk operations, the changeset must be an array of records.
You specify the Changeset for all actions except Delete a record. For queries that modify a single record, this can be either:
- A manually defined set of key-value pairs, where each key corresponds to a column.
- An object with key-value pairs, such as a Form component's
data
value.
Configure the changeset as needed. You use {{ }}
embedded expressions to reference other values within the query.
For bulk operations, the changeset must be an array of records.
4. Specify filters and primary key
- Web or mobile app
- Workflow
Query actions that modify existing data require you to configure Filter by options—the equivalent of a WHERE
clause—to determine which records to update.
These filters are a set of key-value pairs, where each key corresponds to a column, along with an operator.
Operator | Description |
---|---|
= | Where the selected column value equals the provided value. |
!= | Where the selected column value does not equal the provided value. |
> | Where the selected column value is greater than the provided value. |
< | Where the selected column value is less than the provided value. |
IS | Where the selected column value is the provided value. |
IS NOT | Where the selected column value is not the provided value. |
IN | Where the selected column value is one of the provided values. |
NOT IN | Where the selected column value is not one of the provided values. |
If required, configure the Filter by options for the query. For example, you may want to filter records where id
matches the corresponding value from a selected table row.
Column | Operator | Value |
---|---|---|
id | = | {{ table1.selectedRow.id }} |
The Bulk update via a primary key and Bulk upsert via a primary key actions use a specified Primary key column to identify which records to update. For example, you may want to specify id
as the primary key so that the query updates each record based on the id
values provided in the changeset array.
Query actions that modify existing data require you to configure Filter by options—the equivalent of a WHERE
clause—to determine which records to update.
These filters are a set of key-value pairs, where each key corresponds to a column, along with an operator.
Operator | Description |
---|---|
= | Where the selected column value equals the provided value. |
!= | Where the selected column value does not equal the provided value. |
> | Where the selected column value is greater than the provided value. |
< | Where the selected column value is less than the provided value. |
IS | Where the selected column value is the provided value. |
IS NOT | Where the selected column value is not the provided value. |
IN | Where the selected column value is one of the provided values. |
NOT IN | Where the selected column value is not one of the provided values. |
If required, configure the Filter by options for the query. or example, you may want to filter records where id
matches the corresponding value from data received in a webhook event.
Column | Operator | Value |
---|---|---|
id | = | {{ startTrigger.data.id }} |
The Bulk update via a primary key and Bulk upsert via a primary key actions use a specified Primary key column to identify which records to update. For example, you may want to specify id
as the primary key so that the query updates each record based on the id
values provided in the changeset array.
5. Run the query
- Web or mobile app
- Workflow
Click Save & Run to save the query and then execute it. The query results then appear in the Output tab.
You can reference the query output elsewhere in the app using the query's data
property. This contains an object with key names that correspond to the column names. Each key contains an array of values.
Use SQL statements to write or modify data
Retool recommends you only use GUI mode to write or modify data. If necessary, you can use SQL mode and write SQL statements to perform these actions.
Users with sufficient permissions to modify resources (e.g., admins) can restrict write operations to GUI mode only. Enable Show write GUI mode only in a resource's settings to prevent write operations using SQL statements.