Queries
Queries let your components read or write data from various datasources. Retool supports querying most databases, as well as any API.
Your components then connect to queries using their properties. For example, you can set a Table component's data to be {{query1.data}}, and the table would then show data from query1. Similarly, you can change the onClick property of a Button component to trigger a {{resetPassword}} function, which could fire an API request.
Overview
Queries that don't mutate data (e.g., read-only SQL statements, or GET API requests) are automatically run on page load. Then they populate their .data field, which you can access using queryName.data. When their parameters change (e.g., select * from users where id = {{textinput1.value}}), read-only queries automatically re-run.
Queries that mutate data are only run when you explicitly trigger them. For example, if you have a table of users, and you have a POST request that resets the selected user's password, the POST query won't automatically run when you change rows. Instead, POST and other write requests must be run explicitly, generally using a Button component. That way, Retool won't write data back accidentally.
Run and refresh queries
Queries that read data (SQL statements, GET API requests, etc.) automatically refresh when their parameters change. For example, the following query updates every time textinput.value changes:
select * from users where customer_id = {{textinput.value}}
Queries that write data don't automatically run when their parameters change.
Queries that write data (SQL actions, HTTP methods excluding GET) don't automatically refresh when their parameters change. If you want them to change, you can select the Run query automatically when inputs change in the dropdown at the top of the query.
Use caution when enabling write queries to update when inputs change. For example, if your query pulls in the value from a Text Input component, every time you enter a keystroke in the component the query triggers. So if you typed mary into the component, rows for m, a, r, and y are written to your data source. Retool generally suggests adding a button that triggers write queries instead.
Trigger queries on success or failure
It's common to trigger more queries when a query finishes. For example, after you update a database table, you probably want to refresh the query that pulls in all your data. To do that, add a success event handler to trigger the query that retrieves your table's data.
Response options
By default, queries show notifications in the center of the app screen on any failures. JavaScript queries also show notification messages on success. You can modify the messages, adjust the notification duration, and disable these success or failure notifications from the Response tab of the query editor.
Prevent query variable spoofing
To prevent a user from manipulating the network request to pass in arbitrary values to the prepared statement, you can enable the Prevent query variable spoofing feature in your Beta settings. When enabled, Retool rejects network requests that refer to current_user properties (e.g., current_user.email) which don’t match the expected values for the user making the query. If you include additional logic in your expression (e.g., current_user.groups.map(group => group.name)), query spoofing prevention is not available.
Advanced options
The following options are available in the query editor's Advanced tab.
Require confirmation before running
If you don't want a query to run automatically (whether because parameters change or because you triggered it), you can require confirmation before running. Before the query runs, a modal is displayed (the message can be dynamic), asking whether you want to run the query.
Run queries periodically
Retool also provides the mechanism to periodically run a query in a user specified period.
Disable queries dynamically
On more complex pages with many queries, it is sometimes useful to stop queries from running if they are not immediately needed. For example, if you are using a tabbed container, you might only want a query to run when the user switches the active tab to the second tab. To do this, you could write something like the following.
{{ tabbedContainer1.currentViewKey != "View 2" }} disables the query when "View 2" in the Tabbed Container is not selected.
Hide parameters from audit logs
Run queries and their parameters are logged in the audit logs. You should hide sensitive parameters, such as API keys, from your audit logs. Add sensitive parameters to the Disable logging for field on the Advanced tab in the query editor. In the audit logs, these parameter values display as --blacklisted-by-developer--.
Query throttling
If you find that Retool performance is slow, it might be because your queries are refreshing too frequently (eg. on every keystroke). To solve that, you can throttle query such that it runs less frequently. The default is 750ms.
Watch inputs
If the query uses a dynamic input--for example, using a text input component to supply an ID parameter--and is triggered manually, you can set the query to watch and update in reaction to these inputs. In the Advanced tab under Advanced options, add any inputs to watch to the Watched inputs dropdown.
In the example below, the query watches the selected row in the table. When a different row is selected, it triggers the query.
Timing options
You can control when queries run using options in the Timing section of the Advanced tab in the query editor.
Query timeout
Retool automatically times out queries that run for longer than 10 seconds (10000ms). You can adjust the Timeout after duration for queries on an individual basis in the Advanced tab of the query editor. For Retool-hosted users, the maximum duration before a query times out is 120 seconds (120000ms). Self-hosted users can set the DBCONNECTOR_QUERY_TIMEOUT_MS environment variable to change the maximum timeout value.
Delay between query runs
To update the amount of time to wait between query runs, in the Advanced tab, update the Delay between runs field. This field specifies the number of milliseconds to wait in between runs of the same query and defaults to 750 milliseconds.
Delay page load
If the query runs on page load, you can set a delay by updating the Page load delay (ms) field in the Advanced tab.