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.

Clicking Preview on a query in SQL mode runs the query. For example, previewing a query in SQL mode that drops a table will in reality drop the table. Retool strongly recommends using GUI mode only when performing potentially destructive actions. Refer to Previewing queries for more information.

Web or mobile app

Workflow

1. Add a query

Web or mobile app

1. Navigate to the Code tab in the IDE.
2. Click + to add a new query.
3. Select the resource.

Add a query

Workflow

1. Either:
   1. Right-click on the canvas and select the Resource query block.
   2. Click ⊕ in the left toolbar to open the Blocks tab, then select Resource query block.
   3. Click-and-drag from an existing block to create a new, connected block.
2. Select the resource.

Add a query

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

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.

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.

Select a table and the desired Action type for your query.

3. Specify a changeset

Web or mobile app

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 {{ }} to reference other values within the query.

Configure the changeset

For bulk operations, the changeset must be an array of records.

Configure the changeset

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 {{ }} to reference other values within the query.

Configure the changeset

For bulk operations, the changeset must be an array of records.

Configure the changeset

4. Specify filters and primary key

Web or mobile app

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 }}

Specify filter options

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.

Set the primary key

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. 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 }}

Specify filter options

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.

Set the primary key

5. Run the query

Web or mobile app

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.

Workflow

Click ▶︎ to run the block. The query results then appear in the Data and JSON tabs.

You can reference the query output further down the control flow 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.

Clicking Preview on a query in SQL mode runs the query. For example, previewing a query in SQL mode that drops a table will in reality drop the table. Retool strongly recommends using GUI mode only when performing potentially destructive 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.