Query Anatomy

Running queries to power your Retool apps

Queries are the bridge between your data and your Retool apps. Here’s an overview in our docs that walks through reading and writing data. In this unit, we’re going to take a deep dive into the anatomy of the query to learn about all Retool queries have to offer.

NOTE: This guide excludes JS queries. They have different anatomy and functionality than all other Retool queries, so go ahead and read the Reschool section on JS queries.

These are the 3 tabs in a query, let’s learn about them!

General Tab

Here is where you’ll spend most of your time. You can choose your resource, write the body of the query and tell it what to do if it fails. Let’s examine each section of the General tab!

A—Resource

Select the resource you’d like to query. Resources are setup from your Retool org’s home page and are the first step in connecting Retool to your data. More on our resources here.

B—Run query…

Queries can either be run automatically when inputs change or when manually triggered.

An automatically running query will run if an input changes or recalculates. An “input” is a value inside of a {{ }} tag anywhere inside the query panel, such as a new toggle value, new selectedRow of a table or any Retool value that the query is using.

A manually triggered query will run when manually triggered, via a button press, toggle action or something similar, which is separate from a watched value changing.

Automatically when inputs changeManually triggered
Automatically runs on page load🚫
Can be triggered from a button
Can be set to show a confirmation modal before running
(Advanced Tab)
🚫
Can be set to run on page load** (Advanced Tab)🚫 (default behavior)
Can fire confetti on finish
(Advanced Tab)
🚫

** 💡 As a general rule of thumb, limit queries that run on page load. Retool allows you to run queries on page load, and add an optional delay. While this can be helpful to make sure your dashboards are always carrying fresh data, it can overload your app when it starts up. Limit the number and size of queries you run on page load, and consider having them run manually or when specified inputs change.

C—Query Body

Depending on the resource type and action (reading from Postgres vs writing to Google Sheets), the fields you see here will vary and so will the ways in which you’ll interact with your data. The doc linked here and in the intro is a good starting point, but in general, your query type will fall into 2 main query modes:

  1. SQL mode. SQL mode allows you to write SQL statements and are primarily used to read data. More on reading from SQL here.
  2. GUI mode. GUI mode provides a form-like UI with a dropdown of available actions and input fields for data to be passed back to the database. Some resource types (like Google Sheets or Rest API) use this UI for all queries since they aren’t relational databases that are queryable with SQL. More on writing to SQL here.

D—Transformers

Often transformers are used to change the results of a query into a different format. If you find yourself doing this, you can attach a transformer directly to the query. This changes the value of the query everywhere, so that when you use query1.data, you will get the results of the query after the transformer has been applied.

Docs on query transformers here, and some examples below.

Narrow down your results to only include products with high ratingsNarrow down your results to only include products with high ratings

Narrow down your results to only include products with high ratings

Capitalize all the names in your data before displaying them in a tableCapitalize all the names in your data before displaying them in a table

Capitalize all the names in your data before displaying them in a table

E—After this query runs…

You'll often want to trigger more queries when a query finishes/succeeds.

For example, after you change something in a database table, you probably want to refresh the getUsers query that pulls in all your users. So updateUsername should cause your users query to refresh.

💡 Beware of long, convoluted query chains here! If getUsers runs on input change/on page load, then the queries that run on success of getUsers will also run.

You can also trigger a query if a query fails.

F—Preview vs Run

Clicking the "preview" button doesn't actually run the query and update the query.data value. It just shows up in the preview pane as a sample of what values it would return, with one small exception!

Previewing a REST API query will run the query and hit your API endpoint, so there’s no difference between Preview and Run.

G—Schema Browser

You can toggle the schema browser on to view the tables, columns and column types of your selected resource! You can also collapse it for more room to type in your query.

Response Tab

You can use “failure conditions” in queries to mark your queries as failing. Docs on Failure conditions here.

The keys (first input field) are the conditions. If any of these conditions “come true”, i.e. evaluate as truthy, Retool will mark the query as failing and display the error message specified in the value.

Failure conditions are especially useful to set when your resource always returns a success. For example, most GraphQL APIs always return a 200 success code even when there are errors contained in the response.

Let’s walk through a concrete use-case. If you want to throw an error if your API returns fewer results than you want, you can set a failure condition specifying {{ data.results.length < # of ideal results }}.

This query will fail when the number of countries returned is fewer than 251This query will fail when the number of countries returned is fewer than 251

This query will fail when the number of countries returned is fewer than 251

Advanced Tab

The settings available in the advanced tab will vary depending on how you’re running your query: automatically or manually.

Settings for “Run query automatically when inputs change”Settings for “Run query automatically when inputs change”

Settings for “Run query automatically when inputs change”

Additional settings available for queries set to “Run query only when manually triggered”Additional settings available for queries set to “Run query only when manually triggered”

Additional settings available for queries set to “Run query only when manually triggered”

H—Timeout after (ms)

Specify the number of milliseconds to wait before timing the query out. Queries that take longer than 120 seconds are automatically timed out.

I–Run triggered queries after (ms)

Specify the number of milliseconds to wait before running the query once triggered.

J—Delay between runs (ms)

Specify the number of milliseconds to wait in between query runs.

K—Page load delay (ms)

Specify the number of milliseconds to wait before running on page load.

L—Run this query periodically?

If yes, you can specify the frequency at which to refresh this query in milliseconds.

M—Disable logging for

Specify parameters to remove from audit logs.

N—Disable query

Disable the query when expression passed in (between {{ }}) is true.

O—Watched inputs

Choose which parameters should be watched by the query and update in reaction to. For manually triggered queries, you can specify inputs to watch and trigger based on. This allows you to “selectively-automatically” trigger queries.

P—Cache the results of this query

Check this box and then provide the number of seconds you would like this query's results to be cached for.

To invalidate or remove the cache, simply click the Invalidate cache text shown above. At this time, there is no way to programmatically invalidate the cache.

💡 Use query caching to your advantage. Retool allows you to cache query results. Just click on the “advanced” tab in your query editor for a given query and select the checkbox. You can also cache queries across users in your organization’s advanced settings page (/settings/advanced). If you’re running large analytical queries, or in general don’t need constantly up to date figures, consider using caching to help improve performance.

More on caching in Retool here.

Q—Show a confirmation modal before running [Manually triggered only]

R—Fire confetti when the query finishes 🎉 [Manually triggered only]


What’s Next

Wow! You’ve officially completed the 3rd section and are on your way to start building. You have 1 final, very important section remaining. Head back to the main page to learn more.

Did this page help you?