Queries and Resources FAQ

đź’¬ Queries and Resources
1

Queries and Resources FAQ

Welcome to the Queries & Resources section of the Retool Community :waving_hand:
This space is dedicated to questions about connecting to data, writing queries, and troubleshooting integrations in Retool.

If you’re stuck on a query, seeing an error you don’t recognize, or trying to model data cleanly for your app, you’re in the right place.

Common Questions

My query works in staging but fails in production (or vice versa) - why?

Common causes could include: different credentials or permissions, network access (VPC / firewall / IP allowlists), or timeouts under production load.

Compare your resource configurations for each environment and validate that they have the correct settings.

Why is my query slow?

Things to check:

  • Missing indexes
  • SELECT * on large tables
  • Large JSON payloads
  • No pagination or limits
  • Running queries on component change instead of user action

Retool executes queries exactly as written and database performance rules will still be a factor.

I’m getting a connection refused / timeout error

This is almost always a networking issue, not a query issue.

Check:

  • Hostname and port
  • Firewall rules / security groups
  • IP allowlists
  • Whether the resource requires SSH tunneling or VPC access

Posting the exact error text helps a lot here.

Why does my query re-run more often than I expect?

Common reasons:

  • The query is set to Run automatically
  • It’s triggered by component change (e.g. table selection, input value)
  • It’s referenced inside a transformer that re-evaluates

Tip:
Disable auto-run and trigger queries explicitly when possible.

Should I put business logic in SQL or in transformers?

It depends, SQL is better for:

  • Filtering, joins, aggregation
  • Reducing data size before it reaches Retool
  • Performance-critical logic

Transformers are better for:

  • UI-specific formatting
  • Conditional display logic
  • Combining multiple query results

Avoid duplicating complex business logic in multiple places.

How do I debug a failing REST API query?

Checklist:

  • Confirm method (GET vs POST)
  • Check headers (ex: Content-Type and Authorization)
  • Inspect the raw response body
  • Verify parameter interpolation ({{ }})

If possible, test the request outside Retool (curl, Postman) and compare.

Why am I seeing an error for converting a query to a prepared statement?

By default, Retool allows dynamic values in SQL queries, but not dynamic table names or actions. This is to prevent SQL injection attacks (ex: Hi my name is “Robert; DROP TABLE students;--”). You can turn this setting off in your resource config under Disable converting queries to prepared statements if you would like to use javascript to generate more dynamic SQL statements. It is recommended only in use cases where users couldn’t input values into these SQL statements.

In some scenarios, two otherwise identical resource configurations could be used to allow dynamic names and actions on only some use cases, and not allow it with user-facing interfaces.

Caching

How does caching work in Retool?

When enabled, queries are cached server-side inside of the Retool database connector. Whenever a query is run, Retool checks the inputs and cache duration lifespan to determine whether to return cached results or route the query to your database.

If the exact same query is run using the same inputs within the Cache duration lifespan, the cached result is returned rather than querying the resource. If the cache lifespan for the specific query and input combination has expired or the cache has been invalidated, the query is routed to the resource.

You can cache queries across all users or on a per-user basis. See the docs below for details:

Query caching | Retool Docs

Should I cache this query?

Good candidates for caching:

  • Lookup tables (roles, statuses, countries)
  • Configuration data
  • User lists that rarely change
  • Reference data used across many apps

Poor candidates:

  • Frequently updated records
  • User-specific data that changes often
  • Anything used immediately after a mutation
Can caching cause permission issues?

Caching does not bypass database permissions, but it can be confusing if different users expect different results or the query includes user-specific filters. Best practice would be to avoid caching queries that depend on current_user or user context, and instead cache shared/global data.

1 Like