# Otherwise...
redis-cli -h  --tls -a  -p 
```

3. Once in the Redis CLI, run some `get` and `set` commands to verify expected behavior. Running the `INFO` command displays stats for the Redis cluster.

---

## JavaScript

When building apps in Retool, anything inside `{{ }}` is JavaScript. You can use it to access variables custom to your app, as well as globals provided by Retool. You can also write custom JavaScript to manipulate data.

For security purposes, all JavaScript runs in a separate iframe, on a different domain. This is to prevent JavaScript injection, such as cross-site scripting (XSS) attacks.

## Common Use Cases

### Manipulating Data

Because anything inside of `{{ }}` is just JavaScript, you can manipulate the variables in Retool. Let's say you want to make a string lowercase, just write `{{ textinput1.value.toLowerCase() }}`. Adding 100 to a value would just be: `{{ textinput1.value + 100 }}`. Making a date show up in the format you want using `moment` would just be: `{{ moment(table1.selectedRow.date_column).format('YYYY-MM-DD') }}` .You can even do more complex things, like map over an array of objects, and pick out one of the fields: `{{ query1.data.map(i => i.name) }}`.

Because it's all JavaScript, Retool can handle any JSON shape returned by databases and APIs. Just write a quick 1-liner to transform data.

### Complex JavaScript

If you want to write multiple lines of code we suggest using [Transformers](../guides/transformers.mdx) rather than writing them in-line in `{{ }}`. This is useful if you want to do things like filter, transform, and even join different data sets. Here's an example transformer that takes the results of a database query, filters out for approved users, and returns an array with their `credit_amount` multiplied by 10:

```javascript
var users = {{ query1.data }};
var approvedOnly = users.filter(i => i.approved === true);
var amountsTimesTen = approvedOnly.map(i => i.credit_amount * 10);
return amountsTimesTen;
```

### Run JS Code (Perform custom behavior using JavaScript)

You can also manipulate the components in your Retool app programmatically, by [Scripting Retool](../guides/javascript/index.mdx). Rather than doing this in Transformers, you should create queries of type `Run JS Code`.

For example if you want to iterate over a CSV file and call an API for every row in the CSV, you could do something like this:

```javascript
// map through every row of a csv file and call an API
return Promise.all(
  filepicker1.parsedValue.map((row) => {
    return apiQuery.trigger({
      additionalScope: {
        id: row.id,
      },
    });
  }),
).then((results) => {
  return results;
});
```

## Variables

All of the components and queries have names: for example, `textinput1`, or `query1`. And all of them are globally available on the page you're in, so you can refer to them elsewhere on the page: for example, `{{ textinput1.value }}` or `{{ query1.data }}`.

For example, set the `value` of a `Text` component to be `{{ table.selectedRow.first_name }}`, and the `Text` component will change based on the selected row of the `table`.

But we give you other variables too, including libraries like like [Lodash](https://lodash.com/) (`_`), and [Moment](https://momentjs.com/) (`moment`), as well as variables that represent the email of the currently logged in user (`current_user`), the current URL and its parameters (`url`), and the browser's localStorage.

## Exploring objects using the State window

If you want to see a list of all available variables on a page, open the **State** window from the bottom left panel. From there, you can view all the components, queries, and other global variables available. Anything you can see there can be accessed in Retool.

| Section         | Description                                                                                                                               |
| :-------------- | :---------------------------------------------------------------------------------------------------------------------------------------- |
| Temporary state | This section lists the variables used in your app and the associated details. Variables enable you to temporarily store your app's state. |
| Components      | This section lists the components used in your app layout, and the associated component details.                                          |
| Queries         | This section lists the queries used in your app and the associated query details.                                                         |
| Globals         | This section lists the global variables used in your app and the associated variable details.                                             |

:::tip Debugging

If code within `{{ }}` isn't working, try using the **State** window to debug the issue.

:::

---

## Query performance

:::tip Query timeline
You can also use the **Query Timeline** in the [debug toolbar](../../apps/concepts/debug-tools.mdx) to visualize and identify long query run times, duplicate queries, and chained queries.
:::

Complex queries or those retrieving large amounts of data can affect the performance of apps and workflows. Retool provides details about the performance of queries and breaks down these measurements between the backend and frontend. 

Queries display the total runtime in the query editor once they complete. Place the cursor over the runtime to display a popover menu with a breakdown of performance.

The lifecycle of a query is shown in the diagram below.

* **Transfer data**: The latency between the client browser and the Retool DBConnector service. 
* **Execute resource**: The latency between the Retool DBConnector service and the end-user resource. 

## Total time

The total time for the query to execute and receive a complete set of results.

## Prepare query

The time taken to calculate all inputs to a query in the Retool frontend, format the request, and then send it to the Retool backend to make a request to the resource.

## Backend

The time taken by the Retool backend to execute the query with the resource, and transfer data to and from the Retool backend. It includes the following measurements.

| Measurement          | Description                                                                                                                                                                                                                                                                                                                                                       |
| -------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Execute resource** | The time taken by the Retool backend to prepare the query, execute it with the resource, and receive the response. Network latency between Retool and the resource, along with the time taken by the resource to perform the request, can impact the time taken. Larger queries and responses require more bandwidth and can be more affected by network quality. |
| **Transfer data**    | The time taken to transfer the query and results between the Retool backend and frontend.                                                                                                                                                                                                                                                                         |

## Frontend

The time taken by the Retool frontend processing the query results. It includes the following measurements.

| Measurement         | Description                                                                                                                                                                                                                                                                                                                                                                                      |
| ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **Handle response** | The time taken to receive the query response in the Retool frontend and populate the query's `data` property in the app's data model                                                                                                                                                                                                                                                             |
| **Transformer**     | The time taken processing the query's original `data` property using your attached transformer and returning a result. This statistic only appears if the query has a query transformer attached and enabled. More complex transformers with many recursive iterations or additional data references take longer to process.                                                          |
| **Post-processing** | The time taken to update all dependencies inside of the Retool app that reference properties of this query. For example, a [**Table**](https://retool.com/components/table) component that displays `{{query2.data}}` or a [Text Input](https://retool.com/components/text-input) component that displays `{{ query2.data[0]name }}`. The more dependencies to update, the longer this can take. |

## Response size

The total size of the payload received. Larger payloads result in longer processing times in both the Retool backend and frontend.

---

## Prepared statements

Retool converts SQL queries into prepared statements. This makes queries run more efficiently and prevents SQL injection attacks. Since most databases do not support dynamic column or table names in prepared statements, you generally cannot use `{{ }}` embedded expressions to dynamically specify columns or tables.

## How it works

A prepared statement separates the query code from the values it references. This allows the query to be reused with different values without the database needing to recompile the query each time. Instead, the database can use the same pre-compiled query and use different values whenever it's run. 

In addition, this prevents any code, malicious or accidental, from performing destructive actions, such as `DROP TABLE`. 

## Dynamic values

The following example uses a `{{ }}` embedded expression to reference a value when retrieving a list of users. As a prepared statement, the query successfully runs with the dynamic value.

```sql
SELECT id, name, subscription
FROM sample_users
WHERE expiration 

```sql
select id, first_name, last_name
from users u
where u.id = {{ numberInput1.value }};
```

```sql
select id, first_name, last_name
from users u
where u.id = '{{ numberInput1.value }}'
```

Consult your database documentation on prepared statements to confirm expected usage.

---

## Query Library

The Query Library lets you create, run, and share queries across your organization and between Retool apps. Queries in the library are similar to functions in JavaScript—you can define variables in them and they return a result. You can then “call” this query inside and across multiple Retool apps, so that you’re not copy pasting the same query everywhere.

## Features and use cases

Query Library supports:

- Writing a query for any resource (SQL, Firebase, REST, etc.)
- Sharing your queries with the rest of your organization
- Defining variables for your query that you can use when importing it in apps
- Maintaining and rolling back to a previous version of your query
- Running queries in [different environments](../../apps/concepts/debug-tools.mdx#query-library)
- Downloading the results of a query as JSON or CSV

Example use cases:

- Writing a SQL statement and sharing the link with your coworker
- Writing a query that’s used across multiple Retool apps
- Checking out an older version of a complicated SQL query
- Storing a SQL snippet for later reference

## Permissions

Retool admins can configure who has access to the Query Library using permission groups. Note that currently, anyone given access to the Query Library has read and write access. Navigate to the permission group in Retool and select the **Additional** tab to set these permissions.

All queries are private when created. While private, they can only be read and updated by the creator. Users can choose to Share a query, which makes it accessible to the entire organization. You can only delete private queries, and after sharing a query, you can't make it private again.

:::note

Users only have access to queries if they have access to the resource used in the query. This holds true for shared queries as well.

:::

## Version history and source control

Every time you save a query, Retool stores a snapshot, similar to a commit. You can click the **Saved on** text in the bottom right corner to view the history, see the differences between saves, and revert to a specific version. Both SQL-based and non-SQL resources have version history, but only SQL resources show a line by line diff of the changes.

:::note

When you select a specific version of an imported query in your Retool apps, you can use this history view to verify the differences.

:::

If you use Source Control, you can also [protect queries](../../source-control/guides/protect/queries.mdx) to track changes and share queries across Retool instances.

## Reusable queries

You can import shared queries from the Query Library into your Retool apps. This lets you avoid repetition and maintains a single source of truth for your organization. For example, instead of copy pasting the same query on monthly revenue into each app, you can maintain one in the Library.

By default, when you import a shared query into an app, it syncs with the latest version. You also have the option to choose a specific version instead.

For more details on reusing queries, [check out this guide](../guides/query-library.mdx).

## Variables

Variables let you avoid hardcoding pieces of your queries, the same way you can inside a Retool app. In your app you might use `select * from users where id = {{ textinput1.value }}`, but in the Library, there are no other objects to reference. So, the Query library extracts unknown words inside `{{ }}`. In this case, you might write `select * from users where id = {{ user_id }}`, and the Library would pick out `user_id` and display it on the right.

Note that like in the regular Retool app, you can use JavaScript inside `{{ }}`.
For example, you can:

- Write expressions like `{{ parseInt(user_id) }}`
- Write queries like `select * from users where status = {{ age > 65 ? 'retired' : 'working' }}`
- Use libraries like `moment` or `numbro` inside `{{ }}`

For details on how variables work with Reusable queries, check out this guide on [reusing queries](../guides/query-library.mdx).

---

## Row-level security

Row-level security in Retool lets you manage access to specific rows within database tables. It ensures that users can only see and interact with data that they are authorized to access. In Retool, you can implement row-level security by referencing properties of the current user accessing the app in your Resource queries.

## Implement row-level security

Below is an example for how you can implement row-level security by referencing the Retool user's email in queries against your resources. This example assumes you maintain a mapping table from user emails to groups.

The `orders` table below contains order information for different clients (Retool users). The `group_id` designates the client assigned to each row.

| id  | group_id | description |
| --- | -------- | ----------- |
| 1   | 1        | order 1     |
| 2   | 1        | order 2     |
| 3   | 2        | order 3     |
| 4   | 2        | order 4     |
| 5   | 3        | order 5     |
| 6   | 3        | order 6     |

The `user_groups` table maps Retool users to groups. Within a Retool app, when Alice selects data from the `orders` table, Alice should only see the first two rows.

| user_email        | group_id | name  |
| ----------------- | -------- | ----- |
| alice@example.com | 1        | Alice |
| bob@example.com   | 2        | Bob   |
| jane@example.com  | 3        | Jane  |

To achieve this, you can apply a filter using an inner join.

```sql
SELECT        *
FROM          orders as a
INNER JOIN    (SELECT group_id from user_groups where user_email = {{ current_user.email }}) as b
ON            a.group_id = b.group_id
```

The request passes the request from the client to Retool's server, and the query is evaluated with prepared statements on Retool's backend before being executed on the database. The result is returned along the same path. 

To ensure that the client cannot intercept the initial request and insert a different user’s email, [prevent query variable spoofing](../quickstart.mdx#query-variable-spoofing-prevention) is on by default. This ensures that the parameterized statement that arrives at the server contains the same `current_user.email` as the client that's logged in.

## Use user attributes

Rather than maintaining a mapping table, you can use [user attributes](../../org-users/guides/user-management/user-attributes.mdx) to store foreign keys directly on the current user. This prevents the need for additional `JOIN`s, which improves query performance and maintainability while keeping your data secure.

## Additional security

To ensure the security of the request passing from the server to the database, you can take several approaches. Deploying the server and the database within the same virtual private cloud (VPC) avoids exposing requests to the public internet. You can also enable encryption communication between the server and the database using SSL certs.

This architecture has two advantages:

- Because the database filters the result, unfiltered data isn't sent over the network. This minimizes the amount of data exposed over the network, so if there is a security breach, less data is at risk.
- Latency is also reduced because the data transmitted over the network is minimized.

---

## Queries and code concept guides

import Concept from '/docs/_partials/_doctypes/_concept.mdx';

---

## Advanced query options

The following options are available in the query editor's **Advanced** tab. Many query options can help you improve your [app's performance](../../apps/concepts/best-practices.mdx). Read about [best practices](../concepts/best-practices.mdx) to learn how to optimize queries.

## Exclude parameters from audit logs

Queries and their parameters are logged in the [audit logs](../../org-users/guides/monitoring/audit-logs.mdx) when they're run. 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 app performance is slow, it might be because your queries are refreshing too frequently (e.g., on every keystroke). To solve this, you can throttle queries so they runs less frequently. The default is `750ms`.

## 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](../../self-hosted/reference/environment-variables/index.mdx#property-DBCONNECTOR_QUERY_TIMEOUT_MS) to change the maximum timeout value.

## Configure query-specific notifications

There are a few notification settings you can set at the [global level](../../apps/concepts/notifications.mdx#global-notification-settings), but you can also configure notification settings on individual queries. This includes toggling success and failure notifications, but also duration and success messages. You configure these settings from the **Response** tab after selecting a query.

## Keep variable references in sync

:::warning

This setting may introduce performance issues and may not work well with transformers, so use it with caution.

:::

If your query calls a component's `setValue` method, you can ensure future references to the component use the updated value by toggling on the **Keep variable references inside the query with your app** setting. This can be useful if you need to set and use a value in the same query.

## Allow only select users to run resource queries

The **Access controls** settings enable you to restrict resource query usage to specific [permission groups](../../permissions/guides/configure-permission-groups.mdx). If configured, any users who are not a member of a listed group cannot run or trigger the resource query. Click the **Groups that can run this query** and then select the permission groups for whom you want to restrict access.

Restrict access with permission groups.

---

## Retool AI chat actions

import Connectsvg from "/img/icon-packs/bold/interface-geometric-circle-alternate.svg";

You can use the **Retool AI** resource to generate chat responses based on the provided instructions and message history. You can provide text directly in the query, reference existing data from elsewhere (e.g., another query), or use data stored in Retool Vectors.

## Get started

To get started with chat actions for apps, add a new [LLM Chat component](../../../apps/reference/components/special-inputs/llm-chat.mdx). For workflows, add a new [AI Action block](../../../workflows/guides/blocks/ai-action.mdx).

Adding a LLM Chat component automatically creates an AI Action query with **Generate chat response** selected and prefills the input and message history.

1. Add an LLM Chat component to the canvas.
2. Find the newly-created Retool AI query.
3. Optionally, enable **Use Retool Vectors to provide more context to your query**, and choose the vector you want to use. Incorporating [Vectors](../../../data-sources/quickstarts/retool-vectors.mdx) enables the chatbot to generate responses specific to your needs.
4. Select the AI model to use (e.g., **GPT-4**).
5. Click **Save** and then **Run** to perform the action.

The specified AI model then generates a response based on the previous messages and additional input provided.

export function ArcadeEmbed() {
  return (
    
      
    
  )
}

Like all components, you can access LLM Chat [component properties](../../../apps/reference/components/special-inputs/llm-chat.mdx) using JavaScript. For example, LLM Chat components have a `messageHistory` property that contains all messages in the chat log. You can reference specific messages using `{{ chat1.messageHistory[0].content }}`.

Refer to the [LLM Chat guide](../../../apps/guides/forms-inputs/chats/llm-chat.mdx) for more information about this component.

1. Click and drag  to create a new, connected block. 
2. Select **AI Action**.
3. Optionally, enable **Use Retool Vectors to provide more context to your query**, and choose the vector you want to use. Incorporating [Vectors](../../../data-sources/quickstarts/retool-vectors.mdx) enables the chatbot to generate responses specific to your needs.
4. Select the AI model to use (e.g., **GPT-4**).

---

## Retool AI document actions

You can use the **Retool AI** resource to parse PDF documents and extract the text for use elsewhere. Unlike other AI actions, Retool parses the PDF directly—no files or text are sent to an AI model.

## Get started

To get started with AI actions for apps, add a new **AI Action** query.

## Convert document to text

Use the **Convert document to text** action to extract the text content from a PDF file and return it as text. This is useful if you need to include content from PDF documents for use in other AI actions.

You specify an input component for files, such as [File Input](https://retool.com/components/file-input) or [File Button](https://retool.com/components/file-button), or reference a file from another source using `{{ }}` notation (e.g., a file retrieved from an Amazon S3 bucket).

1. Select the **Convert document to text** action.
2. Specify the file to use in the **File source** field.
3. Click **Save** and then **Run** to perform the action.

Retool parses the PDF file and returns the extracted text. The response is available at `{{ query.data }}`.

---

## Retool AI image actions

You can use the **Retool AI** resource to write queries that leverage AI models to generate images. The AI model returns a Base64-encoded string of the image.

As with all queries, the response is available at `{{ query.data }}`. You can display base64-encoded images in any Retool component that support images, such as the [Image](https://retool.com/components/images) or [Avatar](https://retool.com/components/avatar) components.

## Get started

import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

import Getstarted from "./_getstarted.mdx";

## Generate an image

Use the **Generate image** action to generate an image based on the instructions you provide.

1. Select the **Generate image** action.
2. Enter some instructions in the **Input** field.
3. Select the AI model to use, (e.g., **dall-e-2**).
4. Click **Save** and then **Run** to perform the action.

The query output then displays a preview of the base64-encoded image that the AI model returns.

## Image constraints

AI models typically generate `image/png` images with a maximum resolution (e.g., 1024x1024px for **dall-e-2**). You can specify custom dimensions but they cannot exceed the maximum limit.

---

## Retool AI text actions

You can use the Retool AI resource to write queries that leverage AI models to generate and analyze text content. You can provide text directly in the query, reference existing data from elsewhere (for example, another query), or use data stored in Retool Vectors.

As with all queries, the response is available at `{{ query.data }}`. If you reference the query data in a component, such as a [Text Area](https://retool.com/components/text-area) component, Retool streams the response so it appears in real time.

## Add an AI Action

import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

import Getstarted from "./_getstarted.mdx";

The Retool AI text actions are: **Generate text**, **Summarize text**, **Classify text**, and **Extract entity from text**. 

:::info
For all text actions, the **Fx** button next to the **Model** field allows you to dynamically select different AI models within your apps or workflows. Refer to the [Dynamic AI model selection](#dynamic-ai-model-selection) section for more information.
:::

## Generate text

Use the **Generate text** action to generate text content based on the instructions you provide.

1. Select the **Generate text** action.
2. Enter some instructions in the **Input** field.
3. Select the AI model to use (for example, **GPT-4**).
4. Click **Save** and then **Run** to perform the action.

The specified AI model then generates the requested text.

export function ArcadeEmbed_generate() {
  return (
    
      
    
  )
}

### Include context from Retool Vectors

import Vectors from "./_vectors.mdx";

### Dynamic AI model selection

Combine the **Fx** button with an [Option list](https://docs.retool.com/apps/guides/forms-inputs/option-lists) in a [Select](https://docs.retool.com/apps/reference/components/select) component to change the AI model dynamically. 

1. Create a new [Generate text](#generate-text) query. 
2. Open the **Components** panel and drag a **Select** component onto the canvas. 
3. In the **Inspector**, select **Mapped**.
4. Set the **Data source** to the query. 
5. Switch the **Mode** to **Manual** and click on the **Option** labels to edit them.
6. In your query, select the **Fx** button next to the **Model** field and copy the model string.
7. Paste the string into the **Value** field of the **Edit option** box. 
8. Add a **Label** 
9. Repeat Steps 6-8 as needed for additional AI model options.
10. Change the **Model** value in your query to `{{ select1.value }}`
11. Click **Save**.
12. Choose one of the AI model options from the **Select** component dropdown list. 
13. **Run** the query. 

The model listed in the **Output** metadata should match the **Model** selected from the dropdown. 

export function ArcadeEmbed_fx() {
  return (
    
      
    
  )
}

## Summarize text

Use the **Summarize text** action to generate a summary of the provided text.

1. Select the **Summarize text** action.
2. Enter a passage of text in the **Input** field.
3. Select the AI model to use (for example, **GPT-4**).
4. Click **Save** and then **Run** to perform the action. 

The specified AI model then generates a summary of the provided text.

export function ArcadeEmbed_summarize() {
  return (
    
      
    
  )
}

## Classify text

Use the **Classify text** action to analyze text and categorize it with one of the specified labels. The AI model determines what each label should be for and decides which label most appropriately reflects the text. This is useful for performing customer support or sentiment analysis.

1. Select the **Classify text** action.
2. Enter a passage of text in the **Input** field.
3. Specify the **Classification labels** to use.
4. Select the AI model to use (for example, **GPT-4**).

:::info
`{{ query.data }}` contains a `category` property with a value that corresponds to the chosen label.
:::

export function ArcadeEmbed_classify() {
  return (
    
      
    
  )
}

## Extract entity from text

The **Extract entity from text** action analyzes the input text and identifies information based on the specified entities.

1. Select the **Extract entity from text** action.
2. Enter a passage of text in the **Input** field.
3. Specify the **Entities to extract** (for example, `product name` or `address`).
4. Select the AI model to use (for example, **GPT-4**).

:::info
`{{ query.data }}` contains an array of key-value pairs for each entity the AI model has identified.
:::

export function ArcadeEmbed_extract() {
  return (
    
      
    
  )
}

---

## Collect app usage analytics

Retool’s analytics feature allows you to collect analytics from your apps, and it supports integrations with Segment and Google Analytics.

:::note

Your organization must be on the Enterprise plan to use analytics features.

:::

## Get started

There's an **Analytics** section on the **Settings** > **Advanced** tab in Retool. From there, you can connect to Segment and Google Analytics. You can also log to standard out. After connecting, events are sent to all enabled integrations.

## Configuration

After connecting, you can create **Analytics tracking** queries in the app IDE.

Each analytics event has three configurable sections: when to trigger the query, what conditions have to be met, and the fields to include in the payload.

This example triggers when the `finished` property of `query1` changes, and only runs if the query returns more than 10 items. The payload includes the current time, the user that triggered the query, how long the query took to run, and the value of the `textInput1` component.

## Triggering

The instrumentation event triggers when any of the listed properties change. If you want to limit when the payload is sent, enable a condition. The condition can access properties outside of your **Trigger On Property Change** clauses.

---

## Query GraphQL APIs

:::tip Introduction to GraphQL

If you're unfamiliar with GraphQL, check out the [Introduction to GraphQL](https://graphql.org/learn/) guide by the [GraphQL Foundation](https://graphql.org/foundation/) to learn more about its fundamental concepts.

:::

import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

You can write queries to interact with an existing [GraphQL resource](../../../data-sources/guides/integrations/api/graphql.mdx) in your Retool organization. Retool can read GraphQL schema from the API's introspection endpoint, which enables autocomplete and linting of GraphQL queries.

To interact with a GraphQL API that is not configured as a resource, select the **GraphQL** resource from the **Resource** field in the query editor. You manually define the API endpoint and other parameters in the available fields.

:::warning

Clicking **Test** on a GraphQL query runs the query with your latest changes. Retool recommends testing your query on non-production data to avoid making unintended changes to your resource. Refer to [Testing queries](../../quickstart.mdx#testing-queries) for more information.

:::

The following example is a GraphQL query that retrieves a list of three SpaceX launches. 

  

  

  
    

```graphql title="SpaceX launch history"

query Launches($limit: Int) {
  histories(limit: $limit) {
    details
    title
    event_date_utc
    id
  }
}

```

```json title="Response"

{
  "data": {
    "histories": [
      {
        "details": "Falcon 1 becomes the first privately developed liquid-fuel rocket to reach Earth orbit.",
        "title": "Falcon reaches Earth orbit",
        "event_date_utc": "2008-09-28T23:15:00Z",
        "id": "5f6fb2cfdcfdf403df37971e"
      },
      ...
    ]
  }
}

```

## Reference values in GraphQL variables

Unlike other query types, you use [variables](https://graphql.org/learn/queries/#variables) to reference values within Retool in the query body. You specify the variables to use in the query editor and then reference those within the GraphQL query body.

GraphQL variables begin with the `$` character. In the previous example, `$limit` references the **limit** variable from the query editor.

In certain situations you may need to use JavaScript directly in the query body (e.g., to dynamically specify a query field, rather than a value). You can use `{{ }}` expressions in the query body. 

## Fetch and mutate data

GraphQL queries that only fetch data can run automatically. GraphQL queries that modify data must contain a `mutation` field. Similar to **POST** REST API requests, mutation queries cannot run automatically and must be triggered by an event handler. 

## Paginate GraphQL data

The [Table](https://retool.com/components/table) component supports [GraphQL Relay-based cursor pagination](https://relay.dev/graphql/connections.htm). You can enable this from the **Add-ons** section of the Inspector. 

1. Click **+** and select **Pagination**.
2. Click the **Pagination** add-on and toggle **Enable server-side pagination**.
3. Set the **Pagination type** to **GraphQL Relay Cursor based**.

Refer to the [server-side pagination](../../../apps/guides/data/table/pagination.mdx) guide to learn more about paginating data.

---

## Query REST APIs

You can make requests to any REST or [SOAP](../../../data-sources/guides/integrations/api/soap.mdx) API. You can also use [custom authentication](../../../data-sources/guides/authentication/custom.mdx) to interact with private REST APIs.

## Connect to a REST API

You can connect to a REST API in two ways:

- [Create a resource](../../../data-sources/guides/integrations/api/rest.mdx) to connect the API to Retool. This allows you to configure the API once so it's available as a resource anywhere in Retool. You can also configure additional [authentication options](../../../data-sources/guides/authentication/api.mdx), if required.
- Use the built-in **REST API** resource and manually construct API requests. This is useful if you only need to query an API temporarily and won't use it elsewhere. You must manually define API endpoints, authentication options, and any other parameters in each query.

## Query a REST API

You can select a REST API resource in your Retool organization from the **Resource** field in the query editor. After selecting the resource, the configured API endpoint appears in the **URL** field. You can then specify the parameters and other options in the available fields.

To interact with a REST API that is not configured as a resource, select the **REST API** resource from the **Resource** field in the query editor. You manually define the API endpoint and other parameters in the available fields.

:::warning

Clicking **Test** on a REST API query runs the query with your latest changes. Retool recommends testing your query on non-production data to avoid making unintended changes to your resource. Refer to [Testing queries](../../quickstart.mdx#testing-queries) for more information.

:::

### Action types

Retool supports the following request methods for REST APIs:

- **GET**
- **POST**
- **PUT**
- **PATCH**
- **DELETE**

You specify the method to use by selecting an option in the **Action type** dropdown.

Queries using **GET** methods automatically run whenever any of their parameters change by default (e.g., input field value changes). Queries using **POST**, **PUT**, **PATCH**, and **DELETE** methods must be manually triggered by default. You can choose whether a query should run automatically or manually in the query editor; select **Run query only when manually triggered** or **Run query automatically when inputs change** in the dropdown menu.

### Path and URL parameters

You can include additional path and URL parameters to any API request in the query editor. Path parameters can be included directly in your URL, and URL parameters can be added in the **URL parameters** section. Path and URL parameters can also read from values within curly braces (`{{ }}`).

When you specify URL parameters, Retool automatically appends them to the endpoint URL. Conversely, Retool detects any URL parameters added to the endpoint URL and populates the URL parameter fields.

The following example shows a **GET** request which reads the currently selected row of a [Table component](https://retool.com/components/table) and passes it as a URL parameter with a key of `id`.

#### Disable URL parameter sorting

URL parameters are automatically sorted alphabetically. To disable sorting, navigate to **Settings** > **Beta** and turn on the **Disable URL Params Sorting** setting.

### Request headers

For REST APIs configured as a resource, you can set request headers on the resource's configuration page. Headers set on resource configuration pages are always included on any requests using that resource.

You can also add headers to any request using the **Headers** fields in the query editor. These headers are included in addition to any already configured for the resource.

Retool sanitizes any headers with keys that contain `authorization`, `key`, or `password`.

:::note

Retool passes through all headers by default, even if they are duplicates. To exclude duplicate headers, navigate to **Settings** > **Beta**, and toggle on the **Remove duplicate headers (case-insensitive)** settting.

:::

### Request bodies

You can send requests with **JSON**, **raw**, **x-www-form-urlencoded**, **Form data**, or **binary** bodies.

#### JSON

To construct a JSON request body, select **JSON** in the **Body** section of the query editor. You can then construct a JSON object using the key-value fields. A `Content-Type: application/json` request header is automatically added to requests using **JSON** body types. The JSON body also supports nested objects within a single key.

#### Raw

To send a request body without modifications, select **Raw** in the **Body** section of the query editor. For example, if you use the **Raw** body type and manually add a `Content-Type: application/json` header, you can send more complex JSON objects.

You can include JSON in raw request bodies with some additional formatting. You can either wrap the entire body in curly braces to construct the object dynamically or manually format values in the following ways:

- **String values**: Wrap values in curly braces `{{ }}` with double quotes.
- **Boolean or number values**: Do not wrap curly braces `{{ }}` with double quotes.
- **Objects and arrays**: Use`JSON.stringify()` to convert to string values.

#### x-www-form-urlencoded

To send URL-encoded data, select **x-www-form-urlencoded** in the **Body** section of the query editor. Retool adds `Content-Type: application/x-www-form-urlencoded` to the request header.

#### Form data

:::tip

See the guide to [handling files in Retool](../files.mdx) to learn how Retool represents files.

:::

To send form data in your request, select **Form data** in the **Body** section of the query editor. A `Content-Type: multipart/form-data` header is added to the request.

To upload a file using form data, set a **key** to `file`, select the **File** upload type, and set the **value** to the file to upload. The `.value` property on [File Input](https://retool.com/components/file-input), [File Button](https://retool.com/components/file-button), and [File Dropzone](https://retool.com/components/file-dropzone) components has the correct schema for file uploads. For example, set **value** to `{{ fileInput.value }}` to upload a file as form data.

#### Binary

To send data that can't be formatted as form data—for example, an image, audio, or video—select **binary** in the **Body** section of the query editor.

The following examples passes the [File button](https://retool.com/components/file-button) upload value as binary data.

### Cookies

You can configure REST API resources to use [cookies-based authentication](../../../data-sources/guides/authentication/api.mdx#cookie-based-apis) for REST API resource support cookies for API authentication. You can also add cookie headers to API requests using the **Cookies** key-value field in the query editor.

---

## App functions

_App functions_ enable you to write reusable logic with dynamic parameters. Use functions when you want to perform the same actions on different pages, with different parameters, and return different data.

Functions run only when they are called, and they are stateless—you can't reference a `.data` or `.value` property as the source for component data or another value. Functions also replace most usages of [app-level preloaded custom JavaScript](./javascript/custom.mdx) and the **Additional Scope** setting in resource queries.

This page covers only app functions, which differ in some ways from [workflow functions](../../workflows/guides/functions.mdx).

## Types of app functions

App functions are one of the many tools that you can use to accomplish your goal. Use this section to identify which use case they are best- suited for.

There are two types of app functions. They work similarly, but are used for different purposes.

### Sync functions

_Sync functions_ are a mechanism that enables you to reuse JavaScript code. They run synchronously and are helpful tools for formatting or processing different sets of data in the same way.

They can be called from any JavaScript in your app, including in embedded expressions `{{ }}`.

For example, you could have a table showing the inventory of all your products. The manufacturer uses one part number, but your business uses a custom part number that combines the manufacturer number and the color, and you want to display your custom part number in a table.

You would create a sync function with two parameters, `manufacturer` and `color`. In the **JavaScript code** input, write `return manufacturer + "-" + color`. Test out your function with example parameters.

export function CreateSync() {
  return (
    
      
    
  )
}

Next, you would create a new column in your Table for your custom part number. Set the value of the column to `{{ function1({manufacturer: currentSourceRow.partNumber, color: currentSourceRow.color}) }}`, which dynamically provides parameters to the function using the current source row.

export function UseSync() {
  return (
    
      
    
  )
}

#### Sync functions vs other JavaScript logic

Retool provides a variety of options for writing functional code, each with a distinct purpose:

- Use a sync function if you want to reuse/parameterize simple JavaScript code that has no side effects. You can think of a function as a parameterized, anonymous version of a transformer, whose result is returned directly to the caller rather than being stored in the app state. You can also think of a function as a “pure function” in JS.
- Use a [JavaScript query](../quickstart.mdx#code-queries) to write JavaScript code that handles app orchestratation (such as control a component or trigger another query), or whose return value should be persisted in app state and be referenceable by other parts of your app.
- Use a [transformer](../quickstart.mdx#transformers) to transform data and store the transformed result in app state so it can be referenced by other parts of your app. You can think of a transformer like a [selector in React](https://redux.js.org/usage/deriving-data-selectors).
- Use a [variable](../quickstart.mdx#variables) to store some state and explicitly update its value from other parts of your app (such as from an event handler).

### Resource functions

_Resource functions_ are a mechanism that enables you to reuse resource queries. Resource functions are helpful tools for performing tasks that don't require a persistent state, such as updating or deleting an entry. Resource functions eliminate the need for the **Additional Scope** setting in resource queries.

Resource functions run asynchronously.

They can be called from a JavaScript query or a Function Call query, but they cannot be called from embedded expressions.

For example, you could use a resource function that utilizes a REST API to delete a set of customers. Using a function in this scenario would be ideal if you might need to delete customers from various different pages.

Create a resource function that accesses an your REST API of customers with one parameter, `customer`. Update the request URL to include your parameter.

export function ResourceFunction() {
  return (
    
      
    
  )
}

To connect this function to a [Button](../../apps/reference/components/buttons/button.mdx), you first need to [create a Function Call query](#function-call-query). Then, use the Button's event handler to trigger the Function Call. You can also select a `Run Script` event handler and call the function from the script.

## Calling functions

There are several ways to trigger app functions.

### Function Call query

You can use the _Function Call_ query type to trigger your function. Function Call has a state, which means that you can use the output of this query as a static and referenceable value. You must use a Function Call to trigger a Resource Function run from an event handler.

Add a Function Call query in the **Code** menu. Select the function you want to call, and define the parameters.

export function FunctionCall() {
  return (
    
      
    
  )
}

Similar to other types of queries, you can also configure event handlers and run behavior for a Function Call.

### Inline functions

You can also call functions directly from JavaScript code. Use the following guidelines when calling functions:

- Sync functions can be called anywhere that you can use [embedded expressions](../quickstart.mdx#embedded-expressions), or from JavaScript queries.
- Resource functions can only be called from Javascript queries.
- When calling functions from JavaScript queries, you do not need to surround them with brackets.
- Call functions with either positional parameters or named parameters:
  - Named parameters: `functionName({param1: value, param2: value})`. Use named parameters when your function takes a large number of parameters or has optional parameters.
  - Positional parameters: `functionName(value, value)`. Use positional parameters when your function only has a few parameters.

---

## Write queries and code with Ask AI

import Default from "/docs/_shared/_ai-default-model.mdx"

Ask AI enables you to leverage AI when writing JavaScript, SQL, or GraphQL queries. You can instruct Ask AI to:

- **Generate** a new query using natural language instructions.
- **Edit** an existing query using natural language instructions.
- **Explain** an existing query and add comments.
- **Fix** errors in an existing query.

## Generate a query

When your query is empty, the AI operates in generate mode and can write you a query using plain English.

Click **Ask AI** to open a text field and make your request.

## Edit a query

If you have a query already, Retool's AI operates in edit mode and can modify the existing query. Click the **Ask AI** icon to open a text field and make your request.

## Explain a query

For existing queries, you can also set Retool's AI to operate in explain mode, which describes the functionality of the existing query by adding a comment.

Click the **Ask AI** icon and select **Edit** > **Explain** to add a comment to the query.

## Fix a query

If a query fails to run, click **Run error detected. Debug?** to have Ask AI debug the query and suggest a fix. Ask AI uses context about the query, schema, and the error message. It can then explain the problem and provide a possible fix.

---

## Dynamically query resources

Using dynamic queries, you can use a single query to access multiple different resources of the same type. You can use components to enable a user to select which resource to query. Dynamic queries decrease the maintenance burden for apps that need to query various similar resources.

Some example use cases for dynamic queries include:

- An app for your support team to interact with customer data, where every customer’s data is hosted on a standalone database with unique credentials.
- An admin panel to measure the health of customers’ deploys, where every customer has their own tenant.
- A generic budget dashboard for client projects, where every client has their own database.

:::note

Dynamic queries are available in Retool Apps only. This feature is not available in Retool Workflows.

:::

This guide includes instructions for creating a simple dynamic query that retrieves a set of users from one of three different REST APIs.

## Find resource ID

To create a dynamic query, find the ID of the resources that you want to query.

There are three methods to retrieve the resource ID:

- _Manually:_ You can find ID of a resource from the URL of the resource’s configuration page. The ID is the  character string following `resources/`.  For example, if the URL is `https://demo.retool.com/resources/b49d8ef2-a774-433f-9b5c-cadd2a60c678`, the resource ID is `b49d8ef2-a774-433f-9b5c-cadd2a60c678`.
- _Via database:_ If you are on a self-hosted instance of Retool, you can query the underlying Retool database to fetch a list of resources.
- _Via the Retool API:_ You can query the `/resources` endpoint of the [Retool API](../../api) to fetch a list of resources, filtered by the desired resource type.

## Assign ID to a selection component

Use a component to select between resource IDs. For example, use a Select, Listbox, Radio Group, or other selection input component. Create an option for each resource, with the resource ID in the `value` field.

## Create dynamic query

To create a dynamic query, add a new query from the **Code** menu. Click the **𝑓** button, and add a JavaScript snippet that represents a resource ID. For example, if you are using a Select to choose which resource to query, set the **Resource ID**.

In the following example, the `select1` component includes three different resources, with their corresponding resource IDs in the `value` field.

You can only query one type of resource in a given dynamic query. This example uses three REST API resources.

To change the resource type, toggle the **𝑓** button and change the resource type.

---

## Interact with files

You can add components that allow users to upload files in [web apps](../../apps/guides/forms-inputs/file-inputs.mdx) and [mobile apps](../../mobile/guides/forms-inputs/files.mdx). You can also query data stores, such as Retool Storage or [Amazon S3](../../data-sources/guides/integrations/object-file-store/amazon-s3.mdx), to save or retrieve files.

import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

## Reference user-uploaded files in apps

How you reference user-uploaded files differs depending on whether you're using web or mobile apps.

Files that users upload are Base64-encoded. A [File Input](../../apps/reference/components/special-inputs/file-input.mdx), [File Dropzone](../../apps/reference/components/special-inputs/file-dropzone.mdx), or [File Button](../../apps/reference/components/special-inputs/file-button.mdx) component's value property contains an array of objects that correspond to uploaded files.

`value` is always an array, regardless of whether a user uploads a single file or multiple files. Single-file uploads are accessible using `value[0]`.

Uploaded files are Base64-encoded. The component's `value` property contains a Base64-encoded string of the file. You can reference Base64-encoded values anywhere in Retool that accepts a string value, including URLs, using `{{ query.data.base64data }}`.

For component properties without built-in image fields, such as the **Prefix image** of a [List View component](../../apps/reference/components/list-view), use the `data:` prefix and include the MIME type. For example:

```
data:image/png;base64,{{uploadedImageQuery.data.base64Data}}
```

:::tip Convert blobs to Base64-encoded strings

Mobile apps use blobs to temporarily store files. Use [utils.getDataByObjectURL()](../reference/libraries/utils.mdx#utilsgetdatabyobjecturl) to convert blobs into a Base64-encoded string to use with queries for other data sources.

:::

You use either the File Input component or the Image Input component to upload files or images. These components are functionally similar but have some notable differences.

### File Input

Files that users upload are Base64-encoded. The File Input component's `value` property contains a Base64-encoded string of the file. You can reference Base64-encoded values anywhere in Retool that accepts a string value, including URLs. For example, you can set the URL of an Image component to `{{ fileInput1.value }}` to display an uploaded image.

The [file](../../mobile/reference/components/forms/file-input.mdx#property-file) property is an object with more information about the uploaded file.

### Image Input

The [Image Input](../../mobile/reference/components/media/image-input.mdx) component temporarily saves captured data as blobs with `blob:` or `file://` URI strings. You can reference this blob elsewhere in your app (e.g., to preview in an [Image](../../mobile/reference/components/media/image.mdx) component) or upload captured images to a data store.

The Image Input component's `value` contains an array of `blob:` or `file://` values, as the component supports multiple selections. The `files` property is an object with more information about the uploaded images.

## Interact with files on data stores

You can create, retrieve, and modify files on [Retool Storage](../../data-sources/quickstarts/retool-storage.mdx) or any other connected data store resource, such as [Amazon S3](../../data-sources/guides/integrations/object-file-store/amazon-s3.mdx).

You can write queries to save and retrieve files on Retool Storage.

### Save files to Retool Storage

:::tip Save files directly to Retool Storage

Enable the **Upload file to Retool Storage** option for supported upload components, such as [File Button](https://retool.com/components/file-button), to automatically store the file in Retool Storage without the need for a separate query.

:::

Use the **Upload file** action to upload files to Retool Storage. If your app has a supported component for uploads, you can select it from the **File source** dropdown. Retool then saves the uploaded file.

In some cases, you may need to reference Base64-encoded data instead of a component, such as data returned by another query.

import FxSvg from '/img/icons/fx.svg';

Click the  button to enable more granular options. You can then specify the **File content**, a Base64-encoded string, and the file name to use.

In [Retool Workflows](../../workflows/index.mdx), use the **File content** field to set your file content, and give the file a unique name. You can also use JavaScript to dynamically set the file name—for example, `{{ 'report-' + new Date().toDateString() + '.csv' }}`.

### List all files on Retool Storage

Use the **Get a list of file metadata** action type to retrieve metadata about the files your organization has uploaded to Retool Storage. The list includes file names, IDs, sizes, types, URLs, and created and modified dates.

:::note

To improve the performance of this action, you can choose to paginate the results that Retool returns. Navigate to **Settings** > **Beta**, and toggle on the **Retool Storage: Paginate list actions** setting.

:::

### Retrieve a file from Retool Storage

Query the **Retool Storage** resource to retrieve saved files as an array of [Retool File](../../data-sources/reference/objects/file.mdx) objects. Set the **Action type** to **Get contents of a file** and select the file to retrieve.

Some components, such as [Image](https://retool.com/components/image) and [PDF](https://retool.com/components/pdf), have built-in support for files on Retool Storage. Select **Storage** as the **Source** and then select the file to use.

### Download files from Retool Storage

In web and mobile apps, write queries using the **Download a file** action type to download a file to the user's browser. You cannot download files in Retool Workflows.

### Delete files on Retool Storage

Delete files by writing queries using the **Delete a file** action type. Select a file by its name, or pass in its ID.

### Manage files on Retool Storage

To view all your uploaded files, change file access levels, and manually upload, download, move, or delete files, go to **Resources** > **Retool Storage**, or directly to `your-org.retool.com/files`. By default, uploaded files are accessible to users in the organization which hosts the file. Select **...** > **Make public** to make files publicly accessible.

You can also view file IDs and URLs from this page. You can use IDs in Retool Storage queries, and URLs in [Image](https://retool.com/components/image) and [PDF](https://retool.com/components/pdf) components in apps.

You can query any data store that has been made available as a [resource](../../data-sources/quickstarts/resources.mdx) in Retool. How you save and retrieve files, and the format in which it returns files, depends on your data source.

For example, the following query retrieves a file from Amazon S3:

| Setting         | Value                    |
| --------------- | ------------------------ |
| **Action type** | **Read a file from S3**. |
| **Bucket name** | The S3 bucket name.      |
| **S3 file key** | The S3 file path.        |

```json title="Amazon S3 example response"
{
  "Metadata": {},
  "LastModified":"2023-09-15T19:49:20.000Z",
  "ContentLength":21204,
  "ContentType":"binary/octet-stream",
  "AcceptRanges":"bytes",
  "ETag":"8522cef0882eecf42bc8093d64f2d051",
  "Key":"path/to/file.jpg",
  "Body":"UklGRsx..."
}
```
Once retrieved, `{{ query.data.Body }}` contains the Base64-encoded file data for you to reference.

## Fetch files using a URL

:::caution Remote files must be accessible

The remote file must be publicly accessible and not subject to CORS policies. If the file is stored in a remote data store to which you have access, consider creating a resource instead.

:::

In some cases, you may need to fetch a file from a remote URL, such as an image. You can write a query to retrieve the remote file and use it within your app.

1. Add a query and select the **RESTQuery** resource.
2. Select the **GET** action and provide the URL to the remote file.

If the remote file is binary data, Retool makes it available as a file object with the same schema as Retool Storage files. You can reference `{{ query.data.base64Data }}` to use the Base64-encoded data. Plain text content is not encoded.

The following example retrieves Retool's GitHub organization image.

```title="Retrieve a GitHub image"
GET https://github.com/tryretool.png
```

`{{ query.data.base64Data }}` is the Base64-encoded.

```json title="Query data"
{
  "name": "tryretool",
  "type": "image/png",
  "sizeBytes": 4551,
  "base64Data": "iVBORw0KGgoAA..."
}
```

---

## Generate PDFs

Retool provides a built-in PDF exporter to generate PDFs based on data in your Retool apps. To download the entire view of your Retool app as a PDF, you can instead use the [utils.downloadPage()](../reference/libraries/utils.mdx#utilsdownloadpage) method.

## Write queries using the PDF resource

Select the **PDF Exporter (exporter)** resource in the query editor and configure the following fields. On query success, a PDF file with the given contents is downloaded to the user's browser.

### PDF content

The **PDF content** field accepts Markdown for formatting. To convert data, such as the contents of a table, to a format usable for Markdown, use a JavaScript [transformer](transformers.mdx) or [query](javascript/index.mdx).

The following transformer constructs Markdown table formatting for `{{ table1.data }}`.

```javascript title="transformer1"
const headers = [Object.keys({{ table1.data }}[0])];
const breaks = [headers[0].map(() => "---")];
const rows = {{ table1.data }}.map((r) => Object.values(r));
const tableEntries = headers.concat(breaks).concat(rows);
return tableEntries.map((row) => "|" + row.join("|") + "|").join("\n");
```

You can then reference the transformer in the PDF Exporter resource using an `{{ }}` embedded expression, such as `{{ transformer1.value }}`.

### Filename

Use the **Filename** field to specify a PDF file name. This field accepts dynamic inputs. For example, set it to `user_{{ usersTable.selectedRow.id }}_data` to download the file as `user__data.pdf`.

---

## Preload custom JavaScript code and libraries

Retool enables you to make use of custom JavaScript and libraries in apps. You can configure this on a per-app basis or for all apps in your organization.

In general, Retool recommends you configure custom JavaScript and libraries only in apps in which it is required.

:::warning Custom JavaScript is sandboxed

For security reasons, all JavaScript runs in a sandbox. Retool executes all JavaScript in a separate `` on a different domain to prevent cross-site scripting (XSS) attacks. You cannot use jQuery or similar libraries directly in apps.

:::

## Configure custom JavaScript

import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

You can preload custom JavaScript to extend app functionality with custom logic. Once configured within an app, custom JavaScript is available only within that app. If you configure custom JavaScript across your organization, it is preloaded and available across all apps.

While you can call global JavaScript functions from anywhere in your app, [local storage](../../reference/objects/localstorage.mdx) is the recommended way to store global variables in Retool.

To add preloaded JavaScript to an app, open the **App Settings** tab in the left panel, then select **Preloaded JS**.

:::warning All apps load custom JavaScript for the organization

Add custom JavaScript for the organization only if it is needed in most apps. Custom JavaScript can affect performance, especially if you load complex JavaScript functions that aren't used in every app.

:::

Retool admins can configure custom JavaScript that loads in all apps within the organization. This is useful if there is custom JavaScript or libraries that must be available in all apps. Navigate to your organization's **Settings** at `/settings` and select the **Advanced** tab.

After you define your preloaded JavaScript, you can use your methods wherever you write JavaScript, such as:

- Within `{{ }}` embedded expressions, such as `{{ window.myCustomHelper() }}`.
- In [transformers](../transformers.mdx) and [JavaScript queries](./index.mdx).

Custom JavaScript functions use the `window` prefix.

## Configure custom libraries

:::tip Preloaded libraries

Retool automatically [imports some libraries](./index.mdx#libraries), such as `moment()`.

:::

Retool supports the use of externally-hosted JavaScript libraries that function without any external dependencies. Retool recommends using libraries that follow [UMD](https://github.com/umdjs/umd).

In addition, Retool recommends using:

- **The minified version of a library**. Minified versions are optimized to load quicker and generally end with `.min.js`.
- **Only the libraries that you require**. Every library you add to an app is loaded. The more libraries you include, the more it can impact performance.

[CDNJS](https://cdnjs.com/libraries) lists libraries you can load. Non-minified builds usually require `@require` or `import` statements. Some libraries may not be compatible.

To add libraries to an app, open the **App Settings** tab in the left panel, then select **Libraries**.

To import external libraries, add URLs to **Settings** > **Advanced** > **Libraries** in your organization's settings, or the **Libraries** section of an app's settings. This allows you to access the library wherever you write JavaScript.

Custom libraries extend app functionality further with additional features and logic. For instance, you can use the [blueimp-md5](https://cdnjs.com/libraries/blueimp-md5) library to generate an MD5 hash of an email address to [retrieve a profile image from Gravatar](../../../apps/guides/presentation-styling/avatar.mdx#obtain-profile-images-from-gravatar).

---

## Write and run JavaScript

You can write JavaScript to interact with components and queries, which is useful for adding custom logic to your apps. This guide covers common use cases of JavaScript in Retool. For more comprehensive references, visit the [JavaScript API reference](../../reference.mdx) and the [Component reference](https://retool.com/components).

To get started, open the code list and create a query. Select **Query** in the **JavaScript** section.

In the editor, you can write code to set component properties, trigger queries, and access useful JavaScript libraries.

:::note

To ensure safety and security for all users, certain interactions with the browser and context outside of Retool apps can't be run. For example, you can't access browser events or use libraries like jQuery.

:::

## Run an API request for each row in a table

This examples makes an API request for each row in a table, and then shows any errors returned in the process.

### 1. Add a table

Add a Table component to your app. Select **Use an array** in the **Data source** dropdown menu, then paste this JSON into the **Data source** attribute.

```json
[
  {
    "id": 1,
    "name": "Hanson Deck",
    "email": "hanson@deck.com",
    "sales": 37
  },
  {
    "id": 2,
    "name": "Sue Shei",
    "email": "sueshei@example.com",
    "sales": 550
  },
  {
    "id": 3,
    "name": "Jason Response",
    "email": "jason@response.com",
    "sales": 55
  },
  {
    "id": 4,
    "name": "Cher Actor",
    "email": "cher@example.com",
    "sales": 424
  },
  {
    "id": 5,
    "name": "Erica Widget",
    "email": "erica@widget.org",
    "sales": 243
  }
]
```

### 2. Create a query

Create a query using the **RestQuery (restapi)** resource. Set the **Action type** to **Post** and use this URL: `https://approvals.tryretool.com/api/users/approve?email={{table1.data[i].email}}`. The URL parameters are populated automatically.

By default, the `i` property is `0` but JavaScript in a subsequent step will increment this value so the query runs on each row.

### 3. Add a Button and Text components

Add a Button and two Text components to your app. **Status** shows the query's progress and **Errors** displays any errors while the query runs.

### 4. Write the JavaScript query

Create a JavaScript query named `query2` and add the following JavaScript.

```javascript
var rows = table1.data;
var errors = "";
var total = rows.length;

function runQuery(i) {
  // Update the Status text
  Status.setValue("Progress: " + (i.toString() + "/" + total.toString()));

  if (i >= rows.length) {
    console.log("Finished running all queries");
    return;
  }

  console.log("Running query for row", i);

  query1.trigger({
    additionalScope: { i: i }, // This is where we override the `i` variable
    // You can use the argument to get the data with the onSuccess function
    onSuccess: function (data) {
      runQuery(i + 1);
    },
    onFailure: function (error) {
      // Update the Errors text
      errors += "Found error at line " + i.toString() + ":  " + error + "\n\n";
      Errors.setValue(errors);
      runQuery(i + 1);
    },
  });
}

runQuery(0);
```

### 5. Add an event handler to your button

After saving `query2`, add an [event handler](../../../apps/guides/interaction-navigation/event-handlers.mdx) to your button that triggers the JavaScript query.

Now click the **Submit** button to test the app. As the query runs on each row, the status updates and errors are displayed. Since the API endpoint at `https://approvals.tryretool.com/api/users/approve` doesn't exist, all the requests fail.

## Clear state after running a query

You can use the following snippet to clear state after a query runs.

```javascript
userInput.setValue("");
emailInput.setValue("");
pricingTierDropdown.setValue(null);
```

## Trigger a query

This snippet programmatically triggers a query.

```javascript
query1.trigger();
```

You can also pass additional arguments to customize the behavior of a query.

```javascript
query1.trigger({
  additionalScope: {
    name: "hi",
  },
  // You can use the argument to get the data with the onSuccess function
  onSuccess: function (data) {
    console.log("Successully ran!");
  },
});
```

The `additionalScope` option allows you to pass more variables to the query that aren't defined on the global scope. In this example, `name` is now passed to `query1`, and `query1` can access `{{name}}`. The `onSuccess` or `onFailure` callback is called after the function completes.

:::note

Queries that reference values within `additionalScope` initially show an error because there's no global property of the specified name. This error resolves when you populate the **Additional Scope** field or save and run the query.

:::

Here's an example of `additionalScope` used in an app.

## Retrieve triggering components

The variable `triggeredById` returns the name of the component that triggered a query. You can reference this inside of `{{ }}` in any query to return the name of the component that triggered it. If the query is triggered by another query, `triggeredById` returns `undefined`.

```javascript
text1.setValue("I was triggered by component: " + triggeredById);
```

## Retrieve triggering component indexes

If a query is triggered by a table action or a button in a list view, the variable `i` is defined in the query and returns the component's index in that table or list view.

```javascript
text1.setValue("I was triggered by component at index: " + i);
```

## Trigger a query for each item in an array

This script triggers a query for each item in an array.

```javascript
var rows = [{ a: 1 }, { a: 2 }, { a: 3 }, { a: 4 }];

function runQuery(i) {
  if (i >= rows.length) {
    console.log("Finished running all queries");
    return;
  }
  var data = rows[i];
  console.log("Running query for row", data);

  query1.trigger({
    additionalScope: {
      data: data,
    },
    // You can use the argument to get the data with the onSuccess function
    onSuccess: function (data) {
      runQuery(i + 1);
    },
  });
}

runQuery(0);
```

## Return data

Besides manipulating components and queries, JavaScript queries can also return data. For example, this script generates a random number.

```javascript
return Math.random();
```

When this query is triggered, you can access the number generated using the `.data` property: `{{ generateRandomNumber.data }}`.

## Promises and async queries

JavaScript queries can be asynchronous. If you return a [Promise](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise), Retool waits for the promise to resolve before considering the query complete.

### Simple promise

Passing in a query to be triggered as the first argument in a `Promise.resolve()` triggers the other query, waits for it to finish, and then returns the `.data` result from the triggered query.

```javascript
return Promise.resolve(query1.trigger());
```

### Access data from an asynchronous function

Use [await](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/await) to wait for a `Promise` and access the data it returns. The following example [triggers a query](../../reference/objects/query.mdx#properties#trigger) named `getCountry` within a `for` loop. Using `await`, the loop waits for data to be returned before repeating.

```javascript
const countries = ["spain", "france", "germany"];
let allResults = [];

for (let country of countries) {
  let countryResult = await getCountry.trigger({
    additionalScope: { countryName: country },
  });
  allResults.push(countryResult);
}

return allResults;
```

### Promising an array of query returns

You can also pass in an array of items that are dependent on query triggers, and use `Promise.all()` to return all of their results in an array of the same length. In this case, you would trigger `query1` for each row in `table1`, and pass in the `id` value from each row. Using `{{id}}` inside `query1` evaluates as the provided row id when it's run.

```javascript
const promises = table1.data.map((row) => {
  return query1.trigger({
    additionalScope: {
      id: row.id,
    },
  });
});

return Promise.all(promises);
```

### Resolve and reject

If you want to return a value from the query, you can pass it in as a parameter to the Promise's `resolve` function. You can also use the `reject` function from the Promise to fail the query.

In the following example, the query takes two seconds to run, and returns the value `12345` through the resolve function.

```javascript
return new Promise((resolve, reject) => {
  setTimeout(() => {
    resolve(12345);
  }, 2000);
});
```

:::tip Ask the community about scripting

If you're running into any issues with writing JavaScript or scripting in Retool, check out some of the [answered questions on our community forums](https://community.retool.com/search?q=javascript), or [ask one of your own](https://community.retool.com/).

:::

---

## Share queries with the Query Library

You can use the same query across multiple apps by sharing them with the Query Library. Often, you want the same functionality across apps, but don’t want to copy paste the same query over and over again. Reusable queries help you avoid code duplication, version control queries, and share queries with your team.

## Write your query

Reusable queries are written in the [Query Library](https://login.retool.com/auth/login?source=docs&redirectOnLogin=querylibrary). To set up a reusable query:

1. Create and configure a query.
2. Save the query.
3. Share the query using the **Share** button. This makes your query visible and editable to every Editor in your team. You can also import the query in your apps.

:::note

By default all users have access to the Query Library. If you're an admin, you can configure which user groups have access to the Query library in your Permission settings.

:::

You can define variables for queries using curly braces `{{ }}`. Variables are dynamic inputs to your query. You can define defaults in the **Variables** section of the right hand panel. When you import a query in a Retool app, you can pass inputs to the variables you define.

:::tip
Tip: You can write JavaScript inside the `{{ }}` curly braces
:::

## Import your query

Now that your query has been shared, you can import it inside any Retool app. To do this, create a new query in your app and select **Import from Query Library** from the **Resource** dropdown.

:::note

Every time you save a query in the library Retool creates a new version. By default, when you import a shared query into an app, it will be in sync with the latest version. You also have the option to choose a specific version instead.

:::

## Using variables

You can define variables by wrapping their names in `{{ }}` curly braces within reusable queries. For example, if you write `select * from users where age > {{ min_age }} and age 

You can also write more complicated JavaScript expressions. Consider this query: `select * from users where status = {{ age > 65 ? 'retired' : 'working' }}`. In this example, you can pass in `age` as a numerical input, and Retool evaluates it as `retired` or `working` based on the value.

---

## Configure query run behavior

Web or mobile app queries can run either automatically or manually, and include additional run behavior options. These features depend on whether a query is only reading data or making changes.

| Run behavior | Actions                                 | Description                                                                                    |
| :----------- | :-------------------------------------- | :--------------------------------------------------------------------------------------------- |
| Automatic    | **SQL** mode and **GET** requests       | The query runs on page load or whenever a referenced value changes, such as a component value. |
| Manual       | **GUI** mode and all other API requests | The query runs only when triggered by an event.                                                |

Use the **Run behavior** settings in the code editor to view and switch between the available options.

## Run a query automatically

SQL mode queries for databases or GET requests for APIs can run automatically. If enabled, the query runs whenever the page loads or a referenced value changes, such as a component value. This is best suited for smaller datasets or when an app needs to reflect changes immediately.

Click **Run behavior > Automatic** to configure a query to run automatically.
 

## Run a query manually

**GUI** mode SQL queries or any other type of API request (**POST**, **PUT**, etc.) can only run manually. The query must be triggered for it to run. You can trigger queries using an event handler, such as a button click, or with JavaScript code using the `query.trigger()` method. 

You can also configure automatic queries that only read data to run manually. Retool recommends switching to manual run behavior whenever querying a large dataset. Frequent runs of these types of queries can potentially result in rate limits or app performance issues. 

Click **Run behavior > Manual** to configure a query to run manually.

## Additional run behavior options

In addition to running queries automatically or manually, you can configure additional options in the **Advanced** tab. Available options depend on the query's run behavior.

### Run a query on page load

Whenever a user launches an app, automatic queries run once the page loads. You can also configure this behavior for manual queries. To configure this option, navigate to the **Advanced** tab and enable the **Run this query on page load?** setting.

You can also configure the **Page load delay** option for queries that run on on page load. This delays the query from running until the specified time, in milliseconds, has passed since the page loaded. For example, a delay of `5000` would result in a query waiting 5 seconds after a user launches the app before running.

:::caution Avoid running queries that modify data on page load

Running a query on page load prevents users from triggering the query. If a query is not correctly configured, it could result in erroneous changes or deletion of data.

:::

### Run a query periodically

You can configure queries to run at a regular interval. This can be useful for keeping app data current without needing users to perform any action. 

Enable **Run this query periodically** and specify the interval, in milliseconds, at which the query runs again. 

### Run a query when certain values change

Both automatic and manual queries can be triggered whenever a specified value changes, such as an input field or another query. This is similar to configuring a **Change** event handler.

Select the values for which the query should monitor in the **Watched inputs** field, such as `{{ textInput1.value }}` or `{{ query1.data }}`. 

### Require confirmation before running

:::tip Markdown-formatted messages

You can use Markdown formatting in query confirmation messages. 

:::

To prevent users making accidental changes when a query runs, you can include a confirmation modal with a custom message. Users can then either confirm the query should run or cancel. 

Enable **Show a confirmation before running**, then provide a confirmation message. 

## Prevent queries from running

In more complex apps with many queries, you can prevent queries from running if they are not immediately needed. For example, a query should only run if a user selects an option in a Select component.

To prevent a query from running, specify a truthy statement or value in the **Disable query** field. For example, 

- `{{ !select1.value }}` evaluates as true if the component has no current value.
- `{{ password.value.length

---

## _mode

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 **{props.mode}** mode.

---

## Explore database schemas

Retool can retrieve database table schemas from supported data sources, such as SQL databases. You can then browse the schema, search for tables and columns, and use autocompletion in the code editor.

## View schema

In the code editor for a resource query, click **Schema** to display the schema browser for the currently selected resource. The schema browser contains a list of all tables and details about their columns, such as name and type. You can also search the schema in the app IDE to find relevant tables or columns.

## Refresh schema

Retool periodically fetches the resource's schema. Click  to manually refresh the schema to reflect the latest state.

## Autocomplete table and columns

You can autocomplete table and column names in SQL mode queries by selecting one of the presented options or pressing Tab to autocomplete the first match. In GUI mode, Retool populates column names in the provided dropdowns. 

## Schemas for large datasets

If your resource contains a large number of databases or tables, Retool performs a partial fetch of the schema to prevent performance degradation. Regardless of whether the schema is complete, you can always reference valid tables and columns when writing queries.

---

## Read data from SQL databases

You can create queries that use SQL statements to retrieve (read) data from connected database resources, such as a [PostgreSQL](../../../data-sources/guides/integrations/database/postgresql.mdx) or a [MySQL](../../../data-sources/guides/integrations/database/mysql.mdx) database.

## 1. Add a query

import AddQuery from './_add-query.mdx';

import Mode from './_mode.mdx';

## 2. Write an SQL statement to retrieve data

:::tip Create AI-generated SQL queries 

You can also use the [Ask AI](../ask.mdx) option to generate SQL queries.

:::

Write an SQL statement to retrieve data from the database. The following example retrieves the `id`, `name`, `subscription`, and `expiration` values for all records in the `sample_users` table of a database.

```sql title="getUsers"
SELECT 
  id, 
  name, 
  subscription, 
  expiration
FROM
  sample_users
```

You use `{{ }}` embedded expressions to reference other values within the SQL statement, such as components and queries.

The following example adds a `WHERE` clause to retrieve only records with an `expiration` date prior to a Date Input component value.

  

```sql title="getUsers"
SELECT 
  id,
  name, 
  subscription, 
  expiration
FROM
  sample_users
WHERE 
  expiration 
  

  
  

Write an SQL statement to retrieve data from the database. The following example retrieves the `id`, `name`, `subscription`, and `expiration` values for all records in the `sample_users` table of a database.

```sql title="getUsers"
SELECT 
  id, 
  name, 
  subscription, 
  expiration
FROM
  sample_users
```

You use `{{ }}` embedded expressions to reference other values, such as blocks or webhook payloads.

The following example adds a `WHERE` clause to retrieve only records with an `expiration` date before the `expiredBefore` value provided by the [Start](../../../workflows/guides/triggers/webhooks.mdx) block.

```sql title="getUsers"
SELECT 
  id,
  name, 
  subscription, 
  expiration
FROM
  sample_users
WHERE 
  expiration >= {{ moment() }}
AND expiration 

:::info Prepared statements

To prevent SQL injection, Retool converts SQL queries into [prepared statements](../../concepts/prepared-statements) that separate the query from values. This prevents you from writing queries that dynamically reference column or table names.

:::

## 3. Run the query

import RunQuery from './_run.mdx';

---

## Query JSON data with SQL statements

You can create queries that use SQL statements to query JSON data, such as data from other queries, using the **Query JSON with SQL** resource. Instead of referencing database tables for data, you use `{{ }}` embedded expressions to reference JSON arrays of objects.

:::info Client-side SQL

Retool uses the [AlaSQL](https://github.com/AlaSQL) JavaScript library for JSON SQL queries. Some SQL syntax may differ since queries run in the browser. For example, AlaSQL uses square brackets and backticks to enclose column names that contain whitespace, rather than double quotes. 

Refer to the [AlaSQL documentation](https://github.com/agershun/alasql/wiki/Sql) for a complete reference on supported operations.

:::

## 1. Add a query

import AddQuery from './_add-query.mdx';

## 2. Write an SQL statement

import QuerySqlJson from './_query-sql-json.mdx';

:::tip Reformat JSON data structure

The structure of the `data` property depends on the response from the data source. Use the [helper](../../reference/libraries/index.mdx) methods `formatDataAsArray()` or `formatDataAsObject()` to convert between arrays and objects.

:::

## 3. Run the query

import RunQuery from './_run.mdx';

## Reference array values in JSON SQL queries

If you need to reference an array within a query, such as a list of values for an `IN` clause, prefix the array with `@`.

```sql
SELECT 
  id, 
  name, 
  subscription, 
  expiration
FROM
  {{ formatDataAsArray(getUsers.data) }}
WHERE 
  id IN @({{ activeUsers.data }})
```

---

## Write data to SQL databases

You can create SQL queries using a visual interface (**GUI** mode) to safely write data to a connected database resource, such as a [PostgreSQL](../../../data-sources/guides/integrations/database/postgresql.mdx) or a [MySQL](../../../data-sources/guides/integrations/database/mysql.mdx) 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.

To test a SQL query, author the query in **GUI** mode and click **Test** to view a mock of the query response in the **Output**

:::warning

Clicking **Test** on a query in **SQL** mode runs the query. For example, testing 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 [Testing queries](../../quickstart.mdx#testing-queries) for more information.

:::

## 1. Add a query

import AddQuery from './_add-query.mdx';

import Mode from './_mode.mdx';

  

## 2. Select the action type

import SqlActions from '../../../_partials/_queries/_sql-actions.mdx'

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

## 3. Specify a changeset

import Changeset from './_changeset.mdx'

## 4. Specify filters and primary key

import SqlFilters from './_filter-by.mdx'

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.

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

import RunQuery from './_run.mdx';

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

:::warning

Clicking **Test** on a query in **SQL** mode runs the query. For example, testing 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.

---

## Store temporary data with variables and localStorage

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

You can store data temporarily instead of writing changes back to a data source. There are three methods available:

- Variables: Temporarily store data within the app.
- localStorage: Temporarily store data in the customer's browser.
- URL query strings: Temporarily store data in the URL.

Variables are like scratch-pads for your app. You can store data in them while your app is running. They are accessible anywhere on your app inside `{{ }}`, and they support reads (`{{ variable.value }}`), as well as writes (`{{ variable.setValue(3) }}`).

[localStorage](https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage) is a flexible solution for temporarily storing data locally to the browser. All apps in your Retool organization have the same [origin](https://developer.mozilla.org/en-US/docs/Glossary/Origin) and share access to its localStorage. Users can erase their localStorage at any time though or switch to a different browser.

URL query strings are best suited for smaller pieces of information that you need to share between apps. You can append [query strings](https://en.wikipedia.org/wiki/Query_string) to an app's URL and retrieve them using the `url` object.

## Store data using variables

:::warning

Variables are reset each time your app is loaded.

:::

Variables are useful in the following situations:

- Retaining input field values or query results that can change while a user interacts with your app.
- Storing data that is only used in the app session and doesn't need to persist.
- Performing complex logic that requires temporarily storing data first.

### Create a variable

Open the code list and click **+** to create variables.

### JavaScript methods

You can call the following methods within JavaScript queries.

#### variable.setValue(value: any)

Sets `variableName.value` to the value passed in as an argument. Returns a void Promise.

For example, to set the value of `variable1` to a string "user_123_456":

```js
await variable1.setValue("user_123_456");
```

#### variable.setIn(path, value: any)

Sets the value of `variableName.value` at a specified location. `path` accepts an array of keys or indexes to select, where each item represents a key or index in the path to the object to update. The last item of the `path` array is the key or index to update. The value is set without destroying and recreating other values.

When using `variableName.setIn`, the initial value of `variableName` must be an empty object (e.g., `{ }`).

The following examples show objects before and after calls to `variableName.setIn`.

##### Path as an array of indexes

```json
"object1": {
  "key1": [
    "value1",
    "value2"
  ],
  "key2": {
    "nestedKey1": "value3"
  }
}
```

```js
await variableName.setIn(["object1", "key1", 1], "value3")`
```

```json
"object1": {
  "key1": [
    "value1",
    "value3"
  ],
  "key2": {
    "nestedKey1": "value3"
  }
}
```

##### Path as an array of keys

```json
"object1": {
  "key1": [
    "value1",
    "value2"
  ],
  "key2": {
    "nestedKey1": "value3"
  }
}
```

```js
await tempState.setIn(["object1", "key2", "nestedKey1"], "value4")
```

```json
"object1": {
  "key1": [
    "value1",
    "value2"
  ],
  "key2": {
    "nestedKey1": "value4"
  }
}
```

## Store data using localStorage

:::warning Overwriting data in localStorage

Saving a value to localStorage using a key that currently exists will immediately overwrite it. Use a naming convention if you intend to use localStorage with multiple apps.

:::

Use [localStorage.setValue()](../reference/objects/localstorage.mdx) to save data as key-value pairs to localStorage. You can also save objects to localStorage if you want to share entire data sets rather than individual values.

```javascript
// Save to localStorage

localStorage.setValue("app1name", table1.selectedRow.name);

localStorage.setValue("app1data", table1.selectedRow);
```

You can use the [State](../../apps/concepts/ide.mdx) tab to view `localStorage` and confirm that your app data was successfully saved. Click the **Left panel** button, located above the canvas, to open the Modal Browser. Expand `localStorage` to reveal the currently stored values.

### Retrieving data

Use `localStorage.values` to retrieve saved data from localStorage:

```javascript
// Load a value stored in localStorage

{
  {
    localStorage.values.app1name
      ? localStorage.values.app1name
      : "No product submitted";
  }
}

console.log(localStorage.values.app1name);

// Load a value from an object stored in localStorage

{
  {
    localStorage.values.app1data.name
      ? localStorage.values.app1data.name
      : "No product submitted";
  }
}

console.log(localStorage.values.app1data.name);
```

### Deleting data

Use `localStorage.clear()` to clear all localStorage data from your apps. You can also clear specific values by setting an empty value for a specified key:

```javascript
// Clear localStorage

localStorage.clear();

// Set an empty value

localStorage.setValue("app1name", null);
```

Retool recommends you delete data from localStorage after retrieving it once it is longer needed.

### Considerations when using localStorage

Keep in mind the following considerations to avoid common pitfalls when implementing localStorage:

- **Prevent query loops**. Avoid queries that write to localStorage that trigger based on changes to localStorage values. This can cause an infinite loop and impact your app's performance.
- **Assume apps are open in multiple tabs**. It's common for users to have multiple apps—or multiple instances of the same app—open in different browser tabs. Consequently, an event triggered by a change to a localStorage value can cause performance issues because it occurs in every instance of the app.

## Store values in URL query strings

You can send data directly from one app to another using query strings. Query strings are best suited for smaller pieces of information that is not sensitive.

Every app has a unique URL. There are two ways to pass data from one app to another with query strings:

- Use event handlers to open an app and specify query strings to include.
- Manually construct a link to another app that includes query strings in the URL.

### Configure URL query strings with event handlers

An [event handler](../../apps/guides/interaction-navigation/event-handlers.mdx) acts in response to specified user interaction, such as a button click. The **Go to app** action opens another app and includes additional options to create query strings.

Specify a name for the query string and a reference to some data to pass. For example, a query string value of `{{table1.selectedRow.name}}` would include the value for the `name` column from this table’s selected row.

### Manually construct a link

You can also link to an app with query strings attached to the URL. This method can be helpful if you need to link to an app differently or share data outside of Retool.

Copy the URL of the app you want to send data to, and add your query strings:

```html
Open in new app
```

### Retrieve query string values

You retrieve any query strings appended to the app’s URL from the `url` object. For example, you can dynamically include the value of a particular query string or display a placeholder if the value doesn't exist:

```javascript
// Load a value from a query string

{
  {
    url.searcbParams.name ? url.searchParams.name : "No product submitted";
  }
}

console.log(url.searchParams.name);
```

---

## Transform data with JavaScript

Transformers enable you to write larger, reusable, blocks of JavaScript code that can be referenced anywhere in the app. You can use JavaScript [libraries](../reference/libraries/index.mdx) and [utility methods](../reference/libraries/utils.mdx), and Transformers are scoped to the page.

Use transformers whenever you need to reuse the same blocks of JavaScript code in an app.

## Create a transformer

In the **Code** panel, click **+** > **Transformer** to create a transformer.

Transformers are written in JavaScript. The return value at the end is what becomes the value of the transformer in the rest of the app. Here's an example of using a transformer to split out the first name from a string value in a table column.

You can use `{{ yourTransformerName.value }}` to reference the transformer's value. This is how the name is displayed in the Text component in the example above.

You can also select **Test** to check the results of a transformer, but you must **Save** the transformer to use its value in the rest of your app.

## Delete a transformer

Select the transformer you want to delete in the bottom panel. Then click **•••** > **Delete** to remove the transformer.

## Query transformers

Transformers are often used to change the results of a query into a different format. When you attach a transformer directly to a query, it changes the value of the query everywhere. This means `query1.data` uses the results of the query **after** the transformer has been applied.

Here's an example using query transformers to return a specific field from the query results.

After writing this query transformer, instead of writing `query1.data.result`, you can use `query1.data`.

Query transformers work the same way as regular transformers. In the transformer, you are provided a field `data` which contains the results of the query without any modification. The return value from the transformer you write becomes the value of `yourQuery.data` in the rest of the app.

If you want the unaltered results from the query, you can use `yourQuery.rawData` to access the results before transformation.

## Transformers are read-only

Transformers in Retool are read-only: they cannot affect the values of other components or state in Retool. They can only update _their own_ value using a `return` statement. This means that if you're writing JS in a Transformer, you **cannot**:

- Set the value of a variable
- Set the value of a component
- Trigger a query

To do any of these, you'll need to use a JS Code query.

## Examples

The following show common use cases for transformers.

### Return data

To return data from the transformer, use the `return` syntax.

```javascript
const val = {{textinput1.value}}
return parseFloat(val) * 10
```

### Include a variable from your app

Use the `{{ }}` syntax to include a variable used elsewhere on the your app. The transformer will automatically re-run when the variable value changes.

```javascript
const val = {{textinput1.value}}
return parseFloat(val) * 10
```

### Use query data

To access the query results, use `{{sqlQuery.data}}`. If you need to access the original response of the query, use `{{sqlQuery.rawData}}`.

```javascript
// Retrieves query data
{{ sqlQuery.data }}

// Retrieves the original query response
{{ sqlQuery.rawData }}
```

### Convert query data to an array

If you want to use query data as an array of objects, use [formatDataAsArray](../reference/libraries/tools.mdx#formatdataasarray).

```js
{{ formatDataAsArray(sqlQuery.data) }}
```

### Sort an API query

If your API doesn't support filtering using parameters, you can sort using the `sort()` syntax.

```javascript
const data = {{query1.data}}

return data.sort((e1, e2) => e1.fieldToSortBy - e2.fieldToSortBy)
```

### Modify query data

To modify query data, use the `map()` syntax. The following example adds `1` to every query data value.

```javascript
const data = {{query1.data}}
return data.map(x => x + 1)
```

### Filter an API query

If your API doesn't support filtering using parameters, you can filter using the `.filter()` syntax.

```javascript
const data = {{query1.data}}

return data.filter(f => f.fieldToFilterBy.toLowerCase().indexOf(textInput3.value.toLowerCase()) !== -1 )
```

### Join two queries

To join the result of a REST query with a SQL query in to one table, use the `zipWith()` syntax.

```javascript
var sqlDataSorted = {{formatDataAsArray(query1.data)}}.sort((e1, e2) =>  e1.id - e2.id)
var apiDataSorted = {{query2.data}}.sort((e1, e2) =>  e1.id - e2.id)

return _.zipWith(sqlDataSorted, apiDataSorted, (sql, api) => Object.assign({}, api, sql))
```

## Failure conditions

You can use failure conditions in queries to mark your queries as failing. In the **Response** tab of the query editor, add conditions to the **Failure conditions** section. The keys in this section are the conditions, and the values are the error messages to display. If any condition results in a truthy value, Retool shows the error message.

For example, to throw an error if your API returns fewer results than you've configured in a Text Input, set a failure condition of `{{ data.results.length 

To use similar logic outside of the **Failure conditions** section, you need to include the query name (e.g., `{{ queryName.data.results.length < textinput1.value }}`).

### Failure condition properties

Within the failure conditions inputs, you can access `.data`, `.error` and `.metadata` properties. Failure conditions are especially useful to set when your resource always returns a success. For example, most GraphQL APIs return a 200 status code, even when the response contains errors.

Use `.data` to reference the data returned by your query (rows in a database, the response of an API, etc.). The `.error` property extracts an error out of the query's response. The `.metadata` property contains metadata about your query's response, such as response headers for a REST API. Note that `.metadata` is generally only available for REST and GraphQL queries.

:::note

The green evaluation box for failure conditions autocompletes your data based on the last run of the query. For example, `{{ data.error }}` in the autocomplete shows you the `.error` field from the last query result.

:::

---

## Edit queries in VS Code

import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

The VS Code extension allows you to author complex code in the comfort of your own development environment. Enjoy custom keybindings, flexible window management, third-party extensions, and integration with your existing code repositories.

Retool's VS Code extension is currently available for editing queries within apps, including those protected with [Source Control](../../source-control/index.mdx). Editing Query Library and workflows queries is not yet supported.

## Setup

1. Ensure your Retool admin has enabled **VS Code Editing Enabled** in the **Settings** > **Beta** section on your Retool instance.
2. Download the VS Code [extension](https://marketplace.visualstudio.com/items?itemName=Retool.retool-vscode-extension) from the VS Code marketplace. If you are not running the latest version of Retool, use the changelog to choose the right extension version for your Retool instance.

## Usage

You can start building with the VS Code extension by following a deep link from the Retool web editor, or invoking a `Retool:*` command directly from VS Code.

1. Complete the setup steps above.

2. In VS Code, checkout the branch of your repository that you want to work in.

3. In the browser, while editing a Retool app that contains queries, open the **Code** tab and then click the **VS Code** icon.

4. The browser displays an authorization page for you to grant VS Code permission. You can also save your consent to skip this step in the future. After you click **Authorize**, your browser launches the VS Code app.

5. VS Code automatically downloads and displays the queries from the app you were editing. You can edit and save the query files as you would edit other code on your machine.

1. Complete the setup steps above.

2. Invoke the `Retool: Sign In...` command using the VS Code [Command Palette](https://code.visualstudio.com/docs/getstarted/userinterface#_command-palette).

3. Follow the instructions to open the authorization page on your Retool instance. After you click **Authorize**, your browser redirects back to your local VS Code application.

4. You can now invoke the **Retool: Open App** command from the [Command Palette](https://code.visualstudio.com/docs/getstarted/userinterface#_command-palette) to open the queries from any app you have access in VS Code. You can then edit and save the query files like regular code on your machine.

  

## Features

:::info

Some query types (e.g. REST queries) and code blocks do not appear in VS Code. See the [Known limitations](#gui-and-non-code-queries) section for more information.

:::

### Hot reloading
:::warning

Hot reloading is not currently available if multiplayer is enabled on your app.

:::

When you save query files in VS Code, the query content automatically syncs back to your Retool instance. Any open query editors in your browser tab automatically refresh when you refocus your mouse on the Retool editor. You can then click “run” to test your new code.

  

### Merge conflict resolution

If someone else saves a new version of a query you are editing, you will see a merge conflict resolution prompt in VS Code the next time you attempt to save. Once the merge conflict is resolved, the query body will sync over to Retool as normal.

### JS type completion

JavaScript query bodies benefit from type auto-completions in VS Code. Completion options should trigger for any variable available in the Retool browser context, including:

- Global objects (e.g. `window`, `Math`, `localStorage`)
- Built-in libraries (e.g. `lodash`)
- Widget names (e.g. `text1`, `input1`)
- Retool utility functions (e.g. `utils.confetti`, `formatDataAsArray`)

These types are auto-refreshed every 60 seconds based on your latest app save. To refresh types manually, you can invoke the **Retool: Refresh from Retool** quick command.

Type completions are also available within Retool expressions for JavaScript queries. Completions are not available for Retool expressions within other languages, such as SQL queries.

### Integration with extensions

Since query files are written to your local file system the same as any other code, the VS Code extension is compatible with other third-party extensions. For example, you can integrate query writing with [GitHub Copilot](https://marketplace.visualstudio.com/items?itemName=GitHub.copilot), [Vim](https://marketplace.visualstudio.com/items?itemName=vscodevim.vim), and [Code Spell Checker](https://marketplace.visualstudio.com/items?itemName=streetsidesoftware.code-spell-checker). Note that Retool is not responsible for the behavior of third-party plugins.

### VS Code commands

The VS Code extension adds several convenience commands that can be invoked from the [Command Palette](https://code.visualstudio.com/docs/getstarted/userinterface#_command-palette) to interact with your Retool instance. The following table lists the available commands.

| Name                        | Description                                                                                                   |
| --------------------------- | ------------------------------------------------------------------------------------------------------------- |
| Retool: Sign in...          | Initiate authentication flow with your Retool instance                                                        |
| Retool: Sign out...         | Sign out of any currently active sessions and revoke all cached authentication tokens                         |
| Retool: Who Am I            | Display the currently logged-in user, if any                                                             |
| Retool: Open App            | Open queries from a Retool app in the workspace. You must be logged in to run this command.
| Retool: Refresh from Retool | Fetch latest changes from your Retool instance for the currently open app. Your active tab must be a Retool query. |
| Retool: Open in Retool      | Open up a browser tab to the Retool app currently being edited. Your active tab must be a Retool query. |

## Known limitations

### Auto-formatting and linting

Linters like Prettier and ESLint may treat Retool expressions as JavaScript blocks and destroy them on format. For this reason, it's recommended to enable a linter to receive auto-suggestions, but disable the **Format on save** option in VS Code.

### Multiple Retool instances
Editing queries from multiple instances simultaneously is not supported at the moment; your workspace can only contain queries from a single Retool instance. If you open a deep link from a Retool instance and already have queries open from another instance, your existing VS Code folders will be closed automatically.

### GUI and non-code queries
Currently, only code-based queries can be edited in VS Code. Queries that are configured primarily via a graphical interface (e.g. GUI-mode SQL, REST, OpenAPI) will not appear in VS Code. In addition, aside from transformers, secondary code blocks such as event handlers are not editable in VS Code either. File a ticket on the [VS Code extension GitHub repository](https://github.com/tryretool/vscode-extension/issues) to surface feature requests.

### Creating, renaming, and deleting queries
Creating, renaming, and deleting queries is not supported from VS Code. To perform a CRD operation, save any pending changes in VS Code first, perform the operation from your Retool browser tab, and then use the **Retool: Refresh from Retool** quick command in VS Code to resync.

## Telemetry

Retool takes customer privacy and data transparency seriously. The VS Code extension only communicates with your Retool instance. You can monitor outbound network traffic in VS Code by enabling the **Help** > **Toggle Developer Tools** option.

The following usage statistics are collected by default:

- `Retool:*` VS Code command invocations
- Session duration
- Number of editing sessions over time
- Number of unique apps edited

If you run an air-gapped on-premise instance, these usage stats are not reported anywhere. Otherwise, the stats are reported to a central Retool analytics server.

The content of customer code opened in VS Code is never used for analytics and is never transmitted to any centralized Retool servers. In addition, Retool never collects data on non-Retool folders opened in VS Code.

## Feedback

To provide feedback, file an issue on the VS Code extension [GitHub repository](https://github.com/tryretool/vscode-extension/issues).

---

## Import and run a workflow from an app

The [Retool Workflows](../../workflows/index.mdx) query resource enables you to run a workflow from an app. This is useful for reducing complexity as it enables you to:

- Break down complex sets of queries into more manageable functions that perform specific actions (e.g., perform multiple API requests).
- Reuse the same actions across multiple apps, reducing the amount of duplication (e.g., transform and store data in [Retool Vectors](../../data-sources/guides/vectors/embeddings.mdx)).

The query triggers the specified workflow, passing the provided data for it to use. If the workflow produces data, it is returned back to the current app.

:::tip Run another workflow from a workflow

You can also use the [Workflow block](../../workflows/guides/blocks/run-workflow.mdx) within a workflow for the same functionality.

:::

## Import a workflow

Open the **Code** tab, add a new query, and select **Import a Workflow**. This populates a query with the **Retool Workflows** resource. You then select the workflow to run and specify the parameters to pass:

| Parameters | Description                                                                                                               |
| ---------- | ------------------------------------------------------------------------------------------------------------------------- |
| **Raw**    | Reference raw data. This can be any value, such as a string or all JSON data from a previous block (e.g., `query1.data`). |
| **JSON**   | Create a set of key-value pairs using input fields.                                                                       |
| **None**   | Do not pass any data.                                                                                                     |

You do not have to pass any data to the specified workflow. For example, you may want to run a workflow that retrieves data to then use in the app.

## Return data to the app

Workflows run from within an app operate in a similar way as a [webhook-triggered](../../workflows/guides/triggers/webhooks.mdx) workflow. When run, data passed from the app is made available on `startTrigger.data`. You must include a [Response](../../workflows/guides/blocks/response.mdx) block in the workflow to be run as this is how it returns data back to the app. If you want to trigger a workflow without returning data back to the app, you don't need a Response block.

---

## Queries and code how-to guides

import Howto from '/docs/_partials/_doctypes/_howto.mdx';

---

## Queries and code documentation

Write queries to interact with data, script apps to control behavior and respond to user events, and create functional logic to transform data using JavaScript. You can also extend the functionality of workflows with Python.

---

## Queries and code quickstart

import SideBySide from '@site/docs/_partials/_side-by-side.mdx';

This guide serves as an introduction to queries and code in Retool. It covers many of the concepts and terminology you can expect to use when querying and transforming data, scripting behavior, and executing code. After reading this page, you should have a good understanding of the fundamentals for writing queries and code.

## Introduction

[Web apps](../apps/index.mdx), [mobile apps](../mobile/index.mdx), and [workflows](../workflows/index.mdx) all rely on functional code with which to operate. The code you write, language you use, and where you write it depend on the context and functionality being used.

Queries are executable blocks of code that query data, control app behavior, and other functions. Queries must be triggered to run. This can be done with app event handlers or triggered as part of code elsewhere. Some queries can run automatically, such as when a referenced input changes (e.g., a text field) or as part of a workflow.

Retool performs [string interpolation](https://en.wikipedia.org/wiki/String_interpolation) and evaluates `{{ }}` embedded expressions as JavaScript. As a result, you can write JavaScript code that evaluates and returns a value synchronously—almost anywhere.

[Transformers](./quickstart.mdx?apps-workflows=apps#transformers) are reusable blocks of JavaScript code that synchronously transform data. You write JavaScript code that manipulates or reshapes data, such as query results, to meet your needs.

[Variables and localStorage](./quickstart.mdx#temporary-state) are data stores for apps that need to temporarily store state.

Store a value temporarily in a variable.
   

You can specify custom JavaScript code and libraries for use across Retool. You can also make use of built-in libraries and third-party libraries that Retool preloads.

   Using custom Python in a workflow.
   

## Queries

Queries are asynchronous blocks of code that execute on-demand. These are globally scoped and can be triggered from any part of an app or workflow. [Query objects](reference/objects/query.mdx) have state, expose properties, and can be controlled with methods.

The `data` property contains the results of the last run. You either select a query or reference it's `data` property to use its results.

Queries can also reference other values, such as components, and write them back to the data source. Queries can also perform asynchronous actions, run simultaneously, and trigger [event handlers](../apps/guides/interaction-navigation/event-handlers.mdx) upon success or failure.

### Resource queries

A resource query is a piece of code you write to interact with a resource (i.e., an API or database) and perform CRUD operations.

Resource queries are not part of an app's user interface. Users interact with queries via components. For example, a user completes and submits a form in a customer refund app. When the form is submitted, it triggers a `refundCustomer` query which performs a refund API action with Stripe.

If a resource query only reads data, such as retrieving a list of customers, it can automatically run. If a resource query writes data, it must be manually run using an event handler.

The query retrieves a list of customers from a database using an SQL statement.

Workflows use [Resource query](../workflows/guides/blocks/resource-query.mdx) blocks to interact with data sources. These are fundamentally the same as app queries. You either select a Resource query block or reference it's `data` property in another block. When you specify the resource to query, Retool presents relevant options based on the resource type.

Resource query blocks run automatically when part of the workflow's control flow or as part of a function.

The **Loop** block references the **getCustomers** query. Its then iterates on the query results to send an email.

#### API resource queries

Queries for API-based resources include relevant options for making API requests. Resources for popular service integrations, (Amazon S3, Google Sheets Twilio, etc.) include a tailored set of options. This abstracts away some of the complexities of working with APIs.

Other resources, such as GraphQL-based integrations or custom APIs, include more generic options and require further knowledge of working with the API.

#### SQL resource queries

You interact with databases and other structured data stores using SQL. Retool provides two modes with which to interact using SQL: **SQL** and **GUI**.

- SQL mode is for primarily for reading data from databases. You write (raw) SQL statements that retrieve matching records.
- GUI mode is for writing and modifying data in databases. Rather than need to write an SQL statement, GUI mode queries contain options to specify what changes to make and the values to use. 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.

#### Query variable spoofing prevention

To prevent a user from manipulating network requests to pass in arbitrary values to prepared statements, Retool enables _query spoofing_ by default. This 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.

### Code queries

You can write JavaScript code to control app behavior, transform data, set property values, and more. Workflows also supports Python code.

**JavaScript queries** are multiline blocks of JavaScript code. You can write complex JavaScript statements to perform advanced data transformations and control app behavior, such as components and other queries.

The **Loop** block references the **getCustomers** query. Its then iterates on the query results to send an email.

The following JavaScript query loops through each row of the specified table (`table1.data`) and triggers the `updateCustomer` query.

```javascript title="Loop through table rows"
var rows = table1.data;
var errors = "";
var total = rows.length;

function runQuery(i) {
  // Update the Status text
  Status.setValue("Progress: " + (i.toString() + "/" + total.toString()));

  if (i >= rows.length) {
    console.log("Finished running all queries");
    return;
  }

  console.log("Running query for row", i);

  updateCustomer.trigger({
    additionalScope: { i: i }, // This is where we override the `i` variable
    // You can use the argument to get the data with the onSuccess function
    onSuccess: function (data) {
      runQuery(i + 1);
    },
    onFailure: function (error) {
      // Update the Errors text
      errors += "Found error at line " + i.toString() + ":  " + error + "\n\n";
      Errors.setValue(errors);
      runQuery(i + 1);
    },
  });
}

runQuery(0);
```

Workflows can execute both JavaScript and Python code. You can write complex statements to perform advanced data transformations or control workflow behavior, such as triggering functions.

You can use popular [JavaScript](../workflows/guides/blocks/code/javascript.mdx#use-libraries-in-a-workflow) or [Python](../workflows/guides/blocks/code/python.mdx#available-libraries) libraries to further extend the functionality of workflows.

The **Code** block executes custom Python code that extends the functionality of a workflow.

This workflow uses a Resource query block to retrieve a list of transactions. It then uses Python to generate a bar chart that counts the total number of subscription plans. The Python code returns a base64-encoded image which can then be stored in Retool Storage or another file store resource.

```python title="Generate a chart"
def display_bar_chart():
    import matplotlib.pyplot as plt
    import base64
    import io

    plans = [customer['plan'] for customer in getCustomers.data]
    plan_counts = {plan: plans.count(plan) for plan in set(plans)}

    plt.bar(plan_counts.keys(), plan_counts.values())
    plt.xlabel('Plan Type')
    plt.ylabel('Total Counts')

    img = io.BytesIO()
    plt.savefig(img, format='png')
    img.seek(0)

    encoded_image = base64.b64encode(img.read()).decode('utf-8')
    plt.close()

    return encoded_image

return display_bar_chart()

import matplotlib.pyplot as plt
import base64
import io

def display_plan_counts_chart():
    data = getCustomers.data
    plan_counts = {}

    for customer in data:
        plan = customer['plan']
        if plan in plan_counts:
            plan_counts[plan] += 1
        else:
            plan_counts[plan] = 1

    plt.bar(plan_counts.keys(), plan_counts.values())
    plt.xlabel('Plan Type')
    plt.ylabel('Count')
    plt.title('Total Counts for Each Type of Plan')
    plt.gcf().set_size_inches(15, 8)

    buffer = io.BytesIO()
    plt.savefig(buffer, format='png')
    buffer.seek(0)
    chart_image = base64.b64encode(buffer.read()).decode('utf-8')

    return chart_image

return display_plan_counts_chart()
```

The **Code** block executes custom Python code that extends the functionality of a workflow.

The chart generated by the Python code.

### AI actions

AI actions are queries that interact with AI models. Instead of code, you write a prompt—the instructions for the AI model to follow—and include any relevant data.

The **AI action** block references the **getCustomers** query and contains instructions to generate a Markdown table.

### Analytics tracking

Retool can integrate with third-party analytics tools, such as Google Analytics and Segment. When configured, you can add **Analytics tracking** queries that send configured events when triggered.

Analytics tracking queries trigger on property change and can include conditional statements. When triggered, the query sends the configured payload to your connected analytics service.

The **Code** block executes custom Python code that extends the functionality of a workflow.

### Testing queries

When editing a query, click **Test** to test out your changes with your latest edits. Retool executes your query and displays the result in the **Output** section, but no downstream entities within Retool are updated. For example, components are not updated, and event handlers are not executed.

:::warning

In most queries, clicking **Test** fully executes the code in your query. Any changes, additions, or deletions that a query makes will in reality be completed when testing. 

To be safe, Retool recommends testing your query on non-production data.

:::

For SQL queries, clicking **Test** while using using **GUI** mode does _not_ execute the query, and outputs a mock of the SQL query. This feature helps you avoid making destructive changes.

If you want to update your app state based on the query run, click , then **Run in app**. Retool executes the query against your resource, and propagates the resulting data into your app—components update, and other queries that depend on its value will run.

Once you confirm that the query works as intended, click **Save** to finalize your changes.

## Embedded expressions

Retool performs [string interpolation](https://en.wikipedia.org/wiki/String_interpolation) and evaluates `{{ }}` embedded expressions as JavaScript. As a result, you can write JavaScript code—that evaluates and returns a value synchronously—almost anywhere. This enables you to dynamically set property values using transformations or conditional logic to build complex apps and workflows.

import Embedded from '/docs/_shared/_quickstarts/_embedded-expressions.mdx';

## Data transformations

While you can use JavaScript within `{{ }}` embedded expressions and JavaScript queries, you may need to manipulate data and implement complex logic to produce values, such as filtering or joining data sets.

### Transformers

A [transformer](./guides/transformers.mdx) is a reusable block of JavaScript. You reference property values using embedded expressions and the results of the transformation are output on the transformer's `value` property using a `return` statement.

While similar to JavaScript queries, transformers cannot control app behavior. This is because transformers are dynamically evaluated in the same way as embedded expressions. The returned value updates whenever referenced values in the transformer change.

The **Code** block executes custom Python code that extends the functionality of a workflow.

### Query transformations

If a query returns data in a format unsuitable for components or other elements in your app, you can directly transform a query's output. The return value of this transformation is then used wherever you access `query.data`.

You can also use `{{ }}` embedded expressions in query transformations. This can help improve app performance as certain actions, such as filtering, can be performed using JavaScript rather than repeatedly running a query. For example, the following snippet filters query data based on whether the first name of a customer matches the value of a Text Input component.

```js title="Query transformation"
const filteredData = formatDataAsArray(data).filter(
  obj => obj.first.includes({{ search.value }})
);
return filteredData;
```

The **Code** block executes custom Python code that extends the functionality of a workflow.

## Preloaded code and libraries

You can specify custom code and libraries to use within apps and workflows. This extends the functionality within Retool and enables more flexibility.

You can preload custom JavaScript statements and libraries on a per-app basis or for the whole organization to extend app functionality with custom logic.

The **Code** block executes custom Python code that extends the functionality of a workflow.

The **Code** block executes custom Python code that extends the functionality of a workflow.

Retool includes support for a selection of popular JavaScript libraries which you can use in a workflow. You can browse and add libraries, configure their imports, and use them in your workflow.

### Built-in libraries

import BuiltIn from "/docs/_shared/_libraries/_built-in-libraries.mdx";

### Preloaded third-party libraries

import PreloadedLibraries from '/docs/_shared/_libraries/_preloaded-libraries.mdx';

## Temporary state

Retool apps can temporarily store data using variables, localStorage, or URL query strings.

### Variables

Variables can temporarily store data for a user while the app is running. They are accessible anywhere on your app using the `value` property.

Each variable has an initial value you configure and methods with which to update it. The variable reverts back to the initial value the next time the user opens the app.

  This variable contains an array of objects as its initial value.

### localStorage

[localStorage](https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage) is a flexible solution for temporarily storing data locally to the browser. All apps in your Retool organization have the same [origin](https://developer.mozilla.org/en-US/docs/Glossary/Origin) and share access to the browser's localStorage.

Since users can erase their localStorage at any time though or switch to a different browser, localStorage should not be relied upon as a persistent data store.

  You can store data to the browser's localStorage.

### URL query and hash strings

URL query strings are best suited for smaller pieces of information that you need to share between apps. You can append [query strings](https://en.wikipedia.org/wiki/Query_string) to an app's URL and retrieve them using the `url` object.

  URL query and hash strings can be used to store values or sync state, such as the ID of a selected customer.

---

## Query and code glossary

import SharedGlossary from '../../_partials/_glossary.mdx';

---

## The File Utils JavaScript library

import Fileutils from "/docs/_shared/_libraries/_fileutils.mdx";

---

## JavaScript libraries



---

## Preloaded JavaScript libraries

import PreloadedLibraries from "/docs/_shared/_libraries/_preloaded-libraries.mdx";

import PreloadedExamples from "/docs/_shared/_libraries/_preloaded-examples.mdx";

---

## The Tools JavaScript library

import Tools from "/docs/_shared/_libraries/_tools.mdx";

---

## The Utils JavaScript Library

import Utils from "/docs/_shared/_libraries/_utils.mdx";

---

## The localStorage object

Use `localStorage` to save data locally to the browser.

## Properties

## Methods

---

## The Query object

Each Query object represents a [query](../../../queries/index.mdx) within an app, such as an SQL query or transformer. Each instance has a unique name with which to reference (e.g., `getUsers.trigger()`).

## Properties

## Methods

## Events

---

## The Variable object

Each instance has a unique name with which to reference (e.g., `myVariable1.value`).

## Properties

## Methods

---

## Queries and code reference

import Reference from '/docs/_partials/_doctypes/_reference.mdx';

---

## Insert CSV data into a database

You can use Retool's File Button and Table components to upload CSVs and insert their rows into your database.

## 1. Upload files

Start by dragging in a [File Button](https://retool.com/components/file-button) component from the right panel. Toggle on **Parse files** in the Inspector.

This button now automatically parses CSV and JSON, which you can test by uploading a file. If you don't have a CSV ready, you can [download the sample file](https://cdn.tryretool.com/docs/books.csv) used in this guide. Parsed files are stored in `fileButton1.parsedValue`, and content from a single file in `fileButton1.parsedValue[0]`. 

## 2. Render uploaded rows

You can now render your CSV rows into a [Table component](https://retool.com/components/table). Create a new Table component, then pull in data from the File Button. In the table's Inspector, change **Data source** to `{{ fileButton1.parsedValue[0] }}`.

You may need to clear out columns from the demo data before you upload your CSV. Select **...** next to **Columns** in the Inspector, then **Clear columns**.

## 3. Insert rows into your database

In this step, rows in your first table are inserted into your database when a user selects them.

First, add an **Action** button to each row, so you can choose which rows to insert. In the **Actions** section of the Inspector, select **+**. Name it **Insert into DB**.

Next, create another query and name it `insertIntoDB`. Select your database as the _Resource_ type. This example uses Retool Database to store data.

Use **GUI mode** in the query to enable safe writes to your database, and select your table. This example uses the `books` table. Set the following parameters:

- **Action type**: **Insert a record**
- **Changeset** type: **Object**
- **Changeset** value: **table1.selectedRow**

If it's safe to do so for your database, select **Save & Run** to test this query.

Finally, connect the **Insert into DB** action to the query by adding `insertIntoDB` as an event handler to the action. This inserts a record into the database based on the selected row in the uploaded CSV table. You can modify this query later to perform a bulk upload and use data from the entire table.

## 4. Verify uploaded data

To verify the data is uploaded properly, create a Table component containing its records. Drag a Table component onto the canvas.

Create a SQL query to retrieve your database records, then set this SQL query as the **Data source** for your new table.

```sql getBooks
select * from books order by id desc;
```

Finally, you can selectively disable the Upload button for rows that are already uploaded. In the **Copy to DB** action, set the **Disabled** field to `{{ table3.data.find(i => i.id === currentSourceRow.id )}}`. This disables the **Copy to DB** function for rows whose IDs are already present in the `books` database.

---

## Firebase query tutorial

Retool supports Firebase's Admin API and allows you to build CRUD apps on top of Firestore, RealtimeDB, and Auth data.

## Working with Firestore

You need to connect a Firebase resource before you can work with Firestore. Follow the [Connect to Firebase](../../../data-sources/guides/integrations/development/firebase.mdx) guide to connect a Firebase resource if you haven't yet.

:::tip Firestore admin panel template

Retool built a [template](https://retool.com/templates/firestore-admin-panel) that can help you get started with CRUD operations. To use the template, click **Create new** and then **Create from template** on your Retool homepage. Select the Firestore Admin Panel template to get started.

:::

### Query Firestore and get Collections

To query Firestore, create a new resource query and select **Firestore** from the **Service type** dropdown. Set the **Action type** to **Query Firestore**. You can also specify a collection using the dropdown, or input a custom value by clicking **Use raw id**.

Retool lets you query using a key (a document field), operators (`==`, `in`, `>=`, etc.), and a value. You can add as many statements as you need, and you can also set limits, order results by field, etc.

You can set the value of your key or value dynamically with JavaScript, which you can use to implement features like search. For example, you can use a Text Input component to pass a search term into the `value` input.

### Format Firestore query results for a Table component

You can display the query's results in a Table component to view all the documents in a collection. Either select the query in the **Data source** dropdown, or use JavaScript (`{{ query_name.data }}`).

You may need to use `lodash` or other [JavaScript libraries](../../guides/javascript/custom.mdx) to reformat your data for display in the Table component. For example, use`{{ _.values(query_name.data) }}`, or for more advanced use cases, use `.zipWith`:

```
_.zipWith(_.keys(query_name.data), _.values(query_name.data), (key, value) => Object.assign({}, { key: key}, value))
```

You can write this code in the **Data source** field of the table, apply it as a transformer in your query, or create a transformer. See the [transformer docs](../../guides/transformers.mdx) to learn more. You can also use helper functions, such as [formatDataAsArray](../../reference/libraries/tools.mdx), but you might need to do some custom formatting.

Retool allows you to query available Firestore collections (top level or under a specific document ID), which you can use to dynamically select which documents to display in the table. To get started, create a query that lists top level collections.

To query a nested collection, specify the **Parent document ID**. You can also specify the document path (e.g., `home/locations/restaurants`).

Next, display the results of this query in a Select component.

Then connect the value of the Select component to your original query (`query1` in the example) by changing the **Collection** to use a raw ID and pass the value of the Select component. Make sure that your first query is set to run when inputs change.

### Update documents in Firestore

To update a Firestore document, you need the document’s ID and the object to update it with. Create a new query, select your Firebase resource, choose **Firestore** from the **Service type** dropdown, and choose **Update Document** as the **Action type**.

Passing a partial object as the value updates only your passed fields.

There are a few ways to build a UI for the update flow:

1. Use a Form component with Text Input components for each document field you want to update.
2. Use a JSON Editor component that lets you update values directly, and submit the changed values with a Button component.
3. Make columns in the Table component editable.

You can choose whichever pattern fits your users’ needs, as long as you format the results as a JSON object and pass it in the **Value** field of your query. Both the document ID and value can be dynamic, so you can pass their values using JavaScript in `{{ }}`. For this guide, a Table component is used to make updates.

Select the table to make the columns editable. To simplify the example, a single column is made editable.

This makes the columns editable on the frontend but doesn't save the changes to your resource. To save the changes, open the query you created previously that updates a document.

For document ID, pass the ID of the edited row with `{{ table1.changesetArray['0'].id }}` (this works for updating a single row, but you'll learn how to [update multiple rows](#update-multiple-rows) in a subsequent section). Enable **Use raw id** for the collection, and pass the selected value from the dropdown.

When you edit tables, the changes are stored in the `changesetArray` property until they're saved. You can pass values from this property into queries. For this example, set the **Value** in the query to `{ "streetname": "{{ table1.changesetArray['0'].streetname }}"}`. Your query should now look something like this.

Make sure to save the query. If some of the values display an `undefined` error, this is expected if the table hasn't been edited yet. 

When a change is saved, the update query needs to run, but the table also needs to be refreshed after the update. To do this, add a **Save action** to the table, connect it to the update query, and then add a success event handler to the update query that triggers your original query. The demo below illustrates this process.

Edit the `streetname` for one of the rows and then click **Save** to test the workflow. 

#### Update multiple rows

The app currently supports updating a single row. To update multiple rows, create a JavaScript query that iterates through the table's `changesetArray` property, which contains all the changes. On each iteration, you trigger the update query for the changed row.

```javascript
const toUpdate = table1.changesetArray
toUpdate.forEach ((record) => {
  query3.trigger({
    additionalScope: {
      document_id: record.id,
      document_object: record
    }
  })
})
```

This code uses `additionalScope` to pass values from `table1.changesetArray` to `query3`, which updates each row.

To connect your JavaScript to the update query, set the **Document ID** to `{{ document_id }}` and **Value** to `{{ document_object }}`. These values are passed from `additionalScope`.

Finally, update the save action on the table to trigger the JavaScript query instead of the update query.

You can now edit multiple street names to test this functionality.

### Use references in Firestore queries

If your Firestore query needs to use [Database References](https://firebase.google.com/docs/reference/js/database.databasereference.md), you can reference them in Retool using the `$ref` identifier in the value of your query.

The syntax is similar to [MongoDB Extended JSON](https://docs.mongodb.com/manual/reference/mongodb-extended-json/) syntax, where `$ref` specifies that the string should be a ref.

### Insert a document

Create another query and select your Firebase resource. Then set:

- **Service type** to **Firestore**.
- **Action type** to **Insert document**.
- Your **Collection** value.

Inserting a document in Retool takes the same format as updating a document, but you can leave the document ID blank if you want Firestore to automatically generate one.

### Delete a document

Create another query and select your Firebase resource. Then set:

- **Service type** to **Firestore**.
- **Action type** to **Delete document**.
- Your **Collection** value.

The only parameter you need to provide is a document ID, which you can pass dynamically based on the selected row (e.g., `{{ table1.selectedRow._id }}`).

### Get document by ID

Create another query and select your Firebase resource. Then set:

- **Service type** to **Firestore**.
- **Action type** to **Get Document by ID**.
- Your **Collection** value.

The only parameter you need to provide is a document ID, which you can pass dynamically based on the selected row (e.g., `{{ table1.selectedRow._id }}`).

## Working with RealtimeDB

Retool lets you get, update, and delete records in your RealtimeDB.

:::note

You must provide the **Firebase database URL** to query Firebase Realtime Database.

:::

### Query the database

To query the RealtimeDB, create a query and select your Firebase resource. Then set:

- **Service type** to **Realtime Database**.
- **Action type** to **Query Database**.
- Your [DatabaseReference](https://firebase.google.com/docs/reference/js/database.databasereference.md).

If you enable **Use ordering**, you can access these ordering and filtering options.

- Order by child, key, or value.
- Limit to the first or last item.
- Use `equalTo`, `startAt`, and `endAt` to [filter your queries](https://firebase.google.com/docs/reference/js/database.query).

### Set and update data

The Firebase database offers [two APIs for updating data](https://medium.com/@jasonbyrne/closer-look-at-firebase-set-versus-update-eceff34d056b): `.set()` and `.update()`.

The `.set()` method _replaces_ your chosen object with the value that you provide. For example, if your initial object has five fields, and you use `.set()` to only pass three fields, the updated object only contains the three fields. To use this method in Retool, choose **Set** from the **Action type** dropdown. You need to provide a database ref and an object to set.

The `.update()` method updates fields if they exist, but doesn't replace an entire object. The query format is the same as `.set()`, except that you set the **Action type** to **Update data** and provide your database ref and update object.

:::warning You cannot set or update root level properties

You can't run `.update()` or `.set()` queries on the root of your database. If your database ref doesn't have a `/` in it (e.g., references a child), your queries fail. If you input a database ref but the query still fails, you might need to try prepending it with a slash (e.g., `/characters` instead of `characters`).

:::

### Delete fields

Retool doesn't support deleting fields or objects directly, but you can use the `set()` method to change a value to `null`. When you `.set()` a property to `null`, it is deleted from the database. Make sure to pass `null` as JavaScript in curly braces, e.g., `{{ null }}`.

## Working with Firebase Auth

Retool lets you view, update, add, and delete users from your Firebase Authentication setup.

:::tip Firestore admin panel template

If want to do basic CRUD operations on your Firebase Auth data, Retool built a template that can help you get started. [See more details](https://retool.com/templates/firebase-auth-admin-panel) or click **Create new** and then **Create from template** on your Retool homepage. Look for the Firebase Auth Admin Panel template to get started.

:::

### List users

To list your existing Firebase Auth users, create a query and select your Firebase resource. Then set:

- **Service type** to **Firebase Auth (user management)**.
- **Action type** to **List users**.

You can display the results of the query in a table by selecting the query as the table's **Data source**.

You can limit the number of users the query returns using the **Number of users to retrieve** property. You can set this value manually or use a dynamic value referencing a component's value—for example, `{{ textInput1.value }}`.

### Get a user by UID, email, or phone number

To get a user by UID, email, or phone number, create a query and select your Firebase resource. Then set:

- **Service type** to **Firebase Auth (user management)**.
- **Action type** to UID, email, or phone number.

The example query below retrieves a user by their email address, which is passed in dynamically from a Text Input component.

### Delete a user

To delete a user, create a query and select your Firebase resource. Then set:

- **Service type** to **Firebase Auth (user management)**.
- **Action type** to **Delete a user**.

You also need to pass the user's UID. In the example query below, the ID of the selected user is passed into the query.

### Update a user’s information

To update a user’s information, create a query and select your Firebase resource. Then set:

- **Service type** to **Firebase Auth (user management)**.
- **Action type** to **Update a user**.

You also need to pass the user's UUID and the update object.

You can design your UI for updating users in various ways: a form, a raw JSON object, an editable table, etc. If you want to use a table, see the section above on [updating documents](#update-documents-in-firestore) for an example.

A common use case for Firebase Auth in Retool is verifying email addresses. You can use the [update example](#update-a-users-information) for this.

### Create a new user

To create a new user, create a query and select your Firebase resource. Then set:

- **Service type** to **Firebase Auth (user management)**.
- **Action type** to **Create a user**.

You also need to provide a user object. Technically, the Firebase API doesn’t require you to include any fields, but Retool recommends passing at least an email address and password.

Remember that the user object field is dynamic, so you can pass values from other Retool components (a form, two text inputs, etc.) as you build your UI.

:::tip Ask the community about Firebase/Firestore

If you're running into any issues with Firebase/Firestore in Retool, check out some of the [answered questions on our community forums](https://community.retool.com/search?q=firebase), or [ask one of your own](https://community.retool.com/).

:::

## Use timestamps in Firestore queries

Firestore uses a custom data type for timestamps and does not support string values. To write timestamps back to Firestore, provide the timestamp directly in a query, such as `{ lastUpdatedAt: {{ moment() }} }` or `{ lastUpdatedAt: {{ new Date() }} }`. Any values provided to a query using `additionalScope` are strings and cannot be used.

---

## Google Analytics query tutorial

You can connect to [Google Analytics](../../../data-sources/guides/integrations/google/google-analytics.mdx) by creating a resource in Retool. Once complete, you can write app or workflow queries to interact with Google Analytics APIs.

To start, select the API you want to query from the **API** dropdown. Next, select the desired API operation from the **Operation** dropdown. You can also search for available operations within this field.

After you select an operation, the query editor displays additional **Path** and **Query** parameters.

:::caution 

If you connect to Google Analytics using OAuth 2.0, you need to grant access to Retool in the Google Admin console under **Security** > **Apps**. 

:::

## Build an analytics report

To build a report using the `/reports:batchGet` operation of the v4 Reporting API, review the [Google Analytics API documentation](https://developers.google.com/analytics/devguides/reporting/data/v1/rest/). Google also provides an interactive [Request Composer](https://ga-dev-tools.appspot.com/request-composer/) for building requests you can copy and paste into Retool.

## Process the report response

The `/v4/reports:batchGet` endpoint returns an array of Report objects. While the Report object contains useful metadata, you may need to manipulate the data to render its results in a chart or table. You can use the following transformer on your query to parse results into a useable table.

```javascript title="Transformer Code"
const transformedReports = data.reports.map((report) => {
  const { columnHeader } = report;
  const dimensionEntries = (columnHeader.dimensions || []).map((name) => name);
  const metricHeaderEntries = (
    columnHeader.metricHeader.metricHeaderEntries || []
  ).map((o) => ({ ...o, key: o.name }));
  const rows = report.data.rows || [];

  const formattedData = rows.reduce((arr, row) => {
    const dimensions = row.dimensions || [];
    const flatDims = dimensions.reduce((obj, value, index) => {
      const key = dimensionEntries[index];
      return { ...obj, [key]: value };
    }, {});
    const flatMetrics = row.metrics[0].values.reduce((obj, value, index) => {
      const { key } = metricHeaderEntries[index];
      return { ...obj, [key]: Number(value) };
    }, {});
    arr.push({ ...flatDims, ...flatMetrics });
    return arr;
  }, []);
  return { ...report, formattedData };
});

return { ...data, reports: transformedReports };
```

The following snippets show the response after the transformer has been applied.

import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

```json
    [ {
        "ga:landingPagePath": "/",
        "ga:users": 147825,
        "ga:goal1ConversionRate": 5.254646535406078,
        "ga:goal1Completions": 14102
      },
      {
        "ga:landingPagePath": "/contact",
        "ga:users": 32371,
        "ga:goal1ConversionRate": 0.0040,
        "ga:goal1Completions": 130
      }, {...},
]
```

```json {{gaQuery.data.reports\[0].data}}
  { "columnHeader": {
        "dimensions": ["ga:landingPagePath" ],
        "metricHeader": {
          "metricHeaderEntries": [
            { "name": "ga:users", "type": "INTEGER" },
            {  "name": "ga:goal1ConversionRate","type": "PERCENT" },
            { "name": "ga:goal1Completions",  "type": "INTEGER" }
          ]
        }
      },
      "data": {
        "rows": [
          { "dimensions": [ "/" ],
            "metrics": [
              {  "values": [ "148876", "5.270975823347485", "14265" ]  }
            ]  },
          {
            "dimensions": [ "/blog/salesforce-for-engineers/" ],
            "metrics": [
              { "values": [  "31963",  "0.0",  "0"  ] }
            ]
          }, {...}],
     ...}
```

---

## Google Sheets query tutorial

You can build Retool apps using your [Google Sheets](https://docs.google.com/spreadsheets) data, reading and writing data to Google Sheets as you would any other data source. This means you can also [query Google Sheets using SQL](../../guides/sql/query-json.mdx) and combine it with other data sources.

This guide explains how to:

- Query Google Sheet data.
- Display the data in a table.
- Use a form to add rows.
- Edit individual cells.

## Requirements

To build apps with Google Sheets, you need:

- A [Google Sheets resource](../../../data-sources/guides/integrations/google/google-sheets.mdx).
- Edit access to the Google Sheet you want to work with.

### Spreadsheet requirements

To make it easier to work with Google Sheet data, your spreadsheet should meet these requirements:

- The first row needs to contain column names that map to the data in each column.
- Column names should only use alphanumeric characters and lowercase letters, and underscores to separate words.
- There should be no empty columns between columns.
- There should be no empty rows between rows.

If your spreadsheet groups data in a different format (e.g., summary data in a standalone cell), you can instead use [A1 notation](https://developers.google.com/sheets/api/guides/concepts#a1_notation) to query data.

:::note

Retool queries a maximum of 26 columns (`A:Z`). If you have more than 26 columns in your data, you need to use [A1 notation](https://developers.google.com/sheets/api/guides/concepts#a1_notation).

:::

## 1. Create an app

If you haven't created an app yet, [log in to Retool](https://login.retool.com/auth/login) and click **Create new** -> **App**. Name the app and then click **Create app**.

## 2. Write a query to access your data

To work with Google Sheets, create a query to pull the data into your app. Click **+** > **Resource query** in the query editor.

Confirm the following query settings are correct:

- **Resource** is set to your Google Sheets resource.
- **Action type** is set to **Read data from a spreadsheet**.
- **Spreadsheet** is set to the spreadsheet that contains your data.
- If your data isn't contained on the first sheet, pass the sheet name in the **Sheet name** field.

Click **Save and run** to query your data.

If needed, you can set a **Data range** using **Limit** and **Offset**, or [A1 notation](https://developers.google.com/sheets/api/guides/concepts#a1_notation). Limit caps the number of rows returned, excluding the first row with your column names. Offset sets an integer offset on the rows returned, excluding the first row with your column names.

## 3. Display your data in a table

To display the data from your query, drag a [Table component](https://retool.com/components/table) to the canvas. Retool automatically sets the table's **Data source** to the query you created. The **Primary key** should also be set, but verify the value is correct before continuing.

## 4. Add rows

To create rows and append data to your spreadsheet, you need to make the table columns editable. With the table selected, click **•••** in the **Columns** section and select **Make all columns editable**.

Next, drag a [Form component](https://retool.com/components/form) to the canvas and click **Generate form**. Use the default **Source** of `table1`, verify the column and input types are correct, and click **Generate form**. This form is used to add rows, so it should be populated with blank values. To remove the currently selected row data, select the form and delete the **Data source** value in the Inspector.

To add rows, you need a query that writes data from the form back to your spreadsheet. Create another resource query and set:

- **Action type** to **Append data to spreadsheet**.
- **Values to append** to `{{ form1.data }}`.

Scroll to the bottom of the query editor and add a success **Event handler**. Set **Query** to the query that pulls data from your spreadsheet. This way, when you add a row, the table data in your app updates automatically.

Make sure to save the query.

The last step to add rows is to create an event handler on the form's **Submit** button. Select the button and click **+** in the **Event handlers** section. Make sure **Query** is set to the query you just created.

Test out the form by filling out the data and clicking **Submit**.

## 5. Update existing rows

When you made columns editable, it made it possible to click individual cells and edit them. To save these edits back to your spreadsheet, create another resource query and set:

- **Action type** to **Bulk update a spreadsheet by primary key**.
- **Primary key column** to the column you use as the primary key.
- **Array of rows to update** to `{{ table_name.changesetArray }}`.
- A success event handler that runs the query that reads data from your spreadsheet.z

Save the query and then select your table. Click **Save actions** under **Add-ons** in the **Inspect** tab. Create an event handler that runs the bulk update query.

Edit a few cells and click **Save** to test the edit functionality.

If the bulk update query doesn't work for your use case, you can also write a JavaScript query to iterate through rows and update them individually.

```javascript
// Get list of changes
const updates = table1.changesetArray.map((d, i) => {
  // Trigger the query that updates an individual row
  update_row.trigger({
    // Use additional scope and the i variable to pass updates to
    // for each change to the query that updates individual rows
    additionalScope: { i: i },
    onSuccess: function (data) {
      if (i == table1.changesetArray.length - 1) {
        // Refresh table data after updating
        // the last row
        getTableData.trigger();
      }
    },
  });
});

return Promise.all(updates);
```

## Data type options

By default, Retool reads Google Sheets data as formatted strings (e.g., "$1.25" or "TRUE"). This means table sorting is string-based, and column types might not match your spreadsheet exactly. You can optionally configure queries to read data as typed values instead. To enable this option, select **Read data as typed values** in the **Data types** section of the query editor.

Formatted strings work well when you:

- Display data in a table exactly as it appears in Google Sheets, and you don’t need to use any table sorting or formatting features.
- Use column formats supported by Google Sheets that are not supported by Retool.

Formatted strings do not work well when you try to apply Retool’s column formats to them. For example, a formatted boolean from Google Sheets is `"TRUE"` or `"FALSE"`, which if you used with the **Boolean** column type, would always evaluate to `true`.

If you decide to read data as typed values, uncheck **Compare formatted spreadsheet values** in queries that write data back to your spreadsheet so rows are updated correctly.

---

## Queries and code example tutorials



---

## Amazon S3 query tutorial

You can connect to [Amazon S3](../../../data-sources/guides/integrations/object-file-store/amazon-s3.mdx) or S3-compatible services by creating a resource in Retool. Once complete, you can write app or workflow queries to interact with S3 bucket data.

You select the **action type** for S3 queries that determines what action the query performs, such as listing all files or uploading data.

## List all files in a bucket

Select **List all files in a bucket** to retrieve a list of all files. This contains information about each file, such as the file key.

You can use **Prefix to filter results** so only file keys that start with the same string are returned. In general, use prefixes to filter directory structure, such as `images/`. You can enter values directly into this field or reference component values like `{{ textInput.value }}`.

## Read a file from S3

Select **Read a file from S3** to retrieve the contents of a specified file. You set the value of **S3 file key** to the corresponding key of the file to retrieve. The content of the file is Base64-encoded.

For example, you can configure a [Listbox](https://retool.com/components/listbox) component to use data from a query that lists all images in S3. The selected file is retrieved from S3 using `{{ listbox1.value }}` as the **S3 file key**, then displayed using the [Image](https://retool.com/components/image) component.

## Download a file from S3

Select **Download a file from S3** to retrieve a specified file from the S3 bucket. This action is similar to **Read a file from S3** but the file is downloaded by the browser instead of being made available by the query.

## Generate a signed URL

Select **Generate a signed URL** to retrieve a direct link to the file. You specify the **operation name** that the URL supports, such as GET or PUT, along with any **operation options** to include.

For example, you can select a file from a list and generate a signed URL for it that expires in 60 seconds. Set the **Operation name** to **Get object** and **Operation options** to the following:

    
    ```json
    {
      Key: {{ listBox1.value }},
      Expires: 60
    }
    ```
  
  
    ```json
    {
      Key: {{ listBox1.value }},
      expiresIn: 60
    }
    ```
  

The signed URL can be used by anyone to download the file, but is no longer valid after 60 seconds.

## Upload data

Select **Upload data** to upload data to the S3 bucket. You configure the following settings when writing an upload query:

| Action type          | Description                                                                                                                             |
| -------------------- | --------------------------------------------------------------------------------------------------------------------------------------- |
| **S3 Content-Type**  | The MIME type for the uploaded file. You can select a MIME type for common file types, such as CSV and JSON, or specify a custom value. |
| **Upload file name** | The file name for the uploaded data.                                                                                                    |
| **Upload data**      | The base64-encoded data to upload.                                                                                                      |

The [File Button](https://retool.com/components/file-button), [File Dropzone](https://retool.com/components/file-dropzone), and [File Input](https://retool.com/components/file-input) components for web apps return base64-encoded values and MIME types for selected files.

The [Image Input](../../../mobile/reference/components/media/image-input.mdx) component for Retool Mobile apps returns blob values and MIME types for selected files. Use [utils.getDataByObjectURL](../../reference/libraries/utils.mdx#utilsgetdatabyobjecturl) in a JavaScript query first to convert blobs into Base64-encoded values.

```js convertToBase64
return utils.getDataByObjectURL(imageInput1.value[0]);
```

## Copy a file to a new location

Select **Copy a file to a new location** to copy a file and save it to another location. You provide the key of the file to copy as **Copy source**, and the key of the file to create as **Destination file key**.

## Delete a file from S3

Select **Delete a file from S3** to delete the specified file from the S3 bucket. To prevent accidental deletion, you can enable **Show a confirmation modal before running** in the **Advanced** tab of the query. You can also reference the current file key the query would delete in the **Confirmation message**:

```
{{ deleteData.fileKey }} will be permanently deleted. Are you sure?
```

## Get, delete, or update tags on a file from S3

Select **Get tags on a file from S3** to get all tags for the specified file. Tags are returned as key-value pairs. Select **Delete tags on a file from S3** to remove all tags from the specified file.

Select **Update tags on a file from S3** to update the specified tags or add new ones. Set **Tag Set** with key-value pairs for the required tags.

---

## Salesforce query tutorial

After setting up a [Salesforce integration](../../../data-sources/guides/integrations/crm/salesforce.mdx) with Retool, your Salesforce resource is available to use in the query editor. You can then configure a query using the Salesforce Object Query Language (SOQL) or other query types to retrieve and modify data from Salesforce.

## Query types

Under **Query types**, choose the method to use to query Salesforce. **SOQL queries** are often used to read data, and **CRUD action** and **Bulk load** are often used to write data.

### SOQL queries

Select **SOQL query** to construct queries using [Salesforce Object Query Language (SOQL) syntax](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm).

For example, the following query retrieves opportunities marked "Closed Won" within the last month.

```sql
SELECT Id, Name
FROM Opportunity
WHERE StageName = 'Closed Won' and CloseDate >= {{ moment().format('YYYY-MM-01') }}
```

### CRUD action

The **CRUD Action** query type supports **Retrieve**, **Create**, **Update**, **Delete**, and **Upsert** operations. It accepts a **Resource type** and **Resource ID**. See the [Salesforce Object Reference](https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/sforce_api_objects_list.htm) for a list of standard objects.

### Bulk load

Using the **Bulk load** query type, Retool supports Bulk Insert, Bulk Update, Bulk Upsert, and Bulk Delete operations.

For example, the following query inserts Account objects.

The following query updates Account objects. The `extIdField` option specifies the external ID field name.

## Troubleshoot access tokens

If you see an `expired access/refresh token` error when running Salesforce queries, you may need to change a setting in Salesforce to allow Retool to generate valid access and refresh tokens.

- If you have **Enforce IP restrictions** set in your **IP Relaxation** settings, consider editing your policy to **Refresh token is valid until revoked**.
- Ensure you only enable the **Connect to a sandbox organization** resource setting if you are connecting to a developer sandbox organization.
- Ensure the **Timeout value** in your [organization-wide settings](https://help.salesforce.com/s/articleView?id=sf.connected_app_manage_session_policies.htm&type=5) is not too low.

---

## Shopify GrapQL query tutorial

You can connect to Shopify by creating a GraphQL or REST API resource in Retool. This guide provides examples using the GraphQL API.

## Retrieve customers

The following GraphQL query retrieves the name, email, address, and order count for the first 20 customers.

```graphql
query {
  customers(first: 20) {
    edges {
      node {
        displayName
        email
        defaultAddress {
          name
          address1
          address2
          city
          province
          country
          zip
        }
        ordersCount
      }
    }
  }
}
```

To use this data in a Table component, access the `.node` property of each result. The following example shows how to destructure the query results (here, named `getCustomers`). Use this example as the **Data source** of Table.

```
{{ getCustomers.data.customers.edges.map(customer => customer.node) }}
```

## Retrieve orders

The following GraphQL query retrieves the product names, quantity, email, and price for the first 10 orders in your Shopify store.

```graphql
query {
  orders(first: 10) {
    edges {
      node {
        email
        lineItems(first: 10) {
          edges {
            node {
              product {
                title
              }
              quantity
            }
          }
        }
        totalPriceSet {
          shopMoney {
            amount
          }
        }
      }
    }
  }
}
```

To access these results in a Table component, destructure the results by mapping the `edges` field.

```
{{ getOrders.data.orders.edges.map(order => order.node) }}
```

To display the nested values directly, rather than as objects, set the column's **Mapped value** field. For example, to use the `amount` field as a table column, set its mapped value to `{{ self.shopMoney.amount }}`.

## Server-side pagination

Shopify’s GraphQL API supports server-side pagination using GraphQL relay based cursor pagination.

### 1. Update your query

Add the `cursor` field under `edges`, and the following variables in the customers query.

`(first: $first, last: $last, after: $afterPage, before: $beforePage)`

For example, an updated `getCustomers` query might resemble the following.

```graphql
query ($first: Int, $last: Int, $afterPage: String, $beforePage: String) {
  customers(
    first: $first
    last: $last
    after: $afterPage
    before: $beforePage
  ) {
    edges {
      cursor
      node {
        displayName
        email
        defaultAddress {
          name
          address1
          address2
          city
          province
          country
          zip
        }
        ordersCount
      }
    }
  }
}
```

After you add these variables to your GraphQL query, assign values by filling in the **Variables** section in the query editor. Set the following variables, where `table1` is the name of your table.

| Key          | Value                                                                      |
| ------------ | -------------------------------------------------------------------------- |
| `first`      | `{{ table1.pagination.beforeCursor ? null : table1.pagination.pageSize }}` |
| `last`       | `{{ table1.pagination.beforeCursor ? table1.pagination.pageSize : null }}` |
| `afterPage`  | `{{ table1.pagination.afterCursor }}`                                      |
| `beforePage` | `{{ table1.pagination.beforeCursor }}`                                     |

See the guide to [server-side pagination](../../../apps/guides/data/table/pagination.mdx) for more information.

### 2. Configure table server-side pagination

In the **Add-ons** section of the table's Inspector, select **Pagination**, then **Enable server-side pagination**. Under **Pagination type**, select **GraphQL Relay cursor based**. Fill in the input fields with the following values.

| Field                | Value                                                                   |
| -------------------- | ----------------------------------------------------------------------- |
| Previous page cursor | `{{ _.first(getCustomers.data.customers.edges).cursor }}`               |
| Next page cursor     | `{{ _.last(getCustomers.data.customers.edges).cursor }}`                |
| Has more data        | `{{ _.last(getCustomers.data.customers.edges).cursor ? true : false }}` |

---

## SMTP query tutorial

If you have your own SMTP service, you can add it as resource to make it available in Retool apps and workflows. Once configured, you can send emails using your mail provider.

## 1. Create an SMTP resource

Follow the [Connect to SMTP servers](../../../data-sources/guides/integrations/messaging/smtp.mdx) guide to add your SMTP service as a resource.

## 2. Create an app

If you haven't created an app yet, [log in to Retool](https://login.retool.com/auth/login) and click **Create new** -> **App**. Name the app and then click **Create app**.

## 3. Add a Form component

Drag a Form component to the canvas, then click **Add components**. Select **Email** from the list to add an email field to the form.

## 4. Create a query

Create a resource query and select your SMTP resource. Enter a valid email address in the **From** field. You can customize the sender name using the format `"custom-name" custom-email@domain.com`. Set the **To** field to `{{email1.value}}`. This passes the email address in the Email component to the query. Then, fill out the subject and body. You can optionally use Retool's AI feature to draft the body content. Make sure to save the query.

## 5. Add an event handler

To send the email, you need to add an event handler to the **Submit** button. Select the button and then click **+** in the **Event handlers** section of the **Inspector**. The default settings should run the query you created, but make sure that:

- **Event** is set to **Submit**.
- **Action** is set to **Control query**.
- **Query** is set to the query you created.

## 6. Test the form

To test the form, enter a valid email address and click the **Submit** button. If you don't see the email in your inbox, check your spam folder.

## Add attachments to emails

Whether you use Retool Email or your own SMTP service, you can allow users to add attachments to the emails they send. To do this, add a File Button, File Dropzone, or File Input component to the canvas. Then in the query that sends the email, add the component in the **Attachment(s)** field. This allows users to upload an attachment using the app, and then automatically add it to the email.

You can also set the **Attachment(s)** field to the results of a [Retool Storage query](../../../data-sources/quickstarts/retool-storage.mdx) or any query returning a [file object](../../guides/files.mdx) in Retool.

---

## Stripe query tutorial

You can connect to [Stripe](../../../data-sources/guides/integrations/finance/stripe.mdx) by creating a resource in Retool. Once complete, you can write app or workflow queries to interact with Stripe data.

You select the **Operation** for Stripe queries to determine what action the query performs, such as retrieving a list of customers or creating a refund.

## Select an operation

:::note

Click **Stripe docs** to access Stripe's API documentation at any time.

:::

Query operations are derived from the API request methods available in [Stripe's API](https://stripe.com/docs/api). When a query is run, Retool performs the API request using your Stripe API key. When you select an operation, the query editor displays the available settings for you to configure.

For example, the **GET /v1/customers** operation performs a GET request to [retrieve a list of customers](https://stripe.com/docs/api/customers/retrieve). You can provide values for attributes like `email` and `limit` to retrieve only the customers required.

If you use Stripe Connect, you can specify the account ID of a connected account with the `Stripe-Account` field.

## Automatically paginate results

You can automatically paginate results by enabling **Auto-paginate**. This option fetches all paginated results without the need to perform [server-side pagination](../../../apps/guides/data/table/pagination.mdx).

---

## Resource query tutorial

This tutorial explains how to create resource queries that interact with connected resources. There are two types of resource query:

- **API**: Queries that use API requests to interact with API-based resources.
- **SQL**: Queries that use SQL statements to interact with SQL-based resources such as databases and similar data stores.

## Introduction

Queries for API resources include relevant options for making API requests. Resources for popular service integrations, ([Amazon S3](../../data-sources/guides/integrations/object-file-store/amazon-s3.mdx), [Google Sheets](../../data-sources/guides/integrations/google/google-sheets.mdx) [Twilio](../../data-sources/guides/integrations/messaging/twilio.mdx), etc.) include a tailored set of options. This abstracts away some of the complexities of working with APIs.

Other resources, such as [GraphQL](../guides/api/graphql.mdx)-based integrations or custom APIs, include more generic options and require further knowledge of working with the API. 

Queries for databases and similar types of structured data stores use SQL queries, such as [PostgreSQL](../../data-sources/guides/integrations/database/postgresql.mdx) or [MySQL](../../data-sources/guides/integrations/database/mysql.mdx) databases. 

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. 

## Prerequisites

This tutorial uses a demo REST API with sample customer data. You can use the **Sample API generator** to create a fully functional REST API for testing purposes that's preloaded with sample data.

Save the base URL for later, then navigate to **Apps** and click **Create new > App**.

This tutorial uses sample customer data and Retool Database, a built-in PostgreSQL database. To prepare Retool Database:

1. Download `sample_customers.csv`.
2. Navigate to **Resources** and select **Retool Database**.
3. Click **Import CSV** and select **Into a new table**.
4. Upload `sample_customers.csv` and then click **Create table**.

Once complete, navigate to **Apps** and then click **Create new > App**.

## 1. Add a resource query to read data

Click  to open the **Code** tab, then click **+** to add a new query. Retool can interact with a REST API that is not configured as a resource so you don't need to create one first. Search for **REST API** in the **Resource** field and select it.

Click  to open the **Code** tab, then click **+** to add a new query. Select **Retool Database**.

## 2. Write the query code

Update the query with the following options. 

| Option          | Value          | Description                                |
| --------------- | -------------- | ------------------------------------------ |
| **Action type** | **GET**        | Perform a GET request that retrieves data. |
| **URL**         | `YOUR_API_URL` | The URL for the sample API.                |

Click **Save & Run** to save the resource query. The results appear in the **Output** section below. 

Write an SQL statement to retrieve data from the database. The following example retrieves all data from the newly created `sample_customers` table.

```sql
SELECT 
  *
FROM
  sample_customers
```

Click **Save & Run** to save the resource query. The results appear in the **Output** section below. 

## 3. Display results in a table

With the query results available, you can now display them using a component. Right-click on the canvas, click **Add component**, and select the [Table](../../apps/reference/components/data/table.mdx) component.

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.

Tables automatically reference the most recently created query or you can change this in the **Data** setting of the Inspector. 

## 4. Edit table data

Tables are read-only by default but can be configured to allow editing. To do this, you specify which columns should be editable. 

Position the cursor over the **First** table column, click **•••**, then select **Make editable**. Repeat this process for the **Last** table column. Once done, you can click to edit cells in these columns.

Although the table data is editable, there is no query to save the edits back to the database. When you make a table editable, Retool adds a **Save action** to the table. You configure this with an [event handler](../../apps/guides/interaction-navigation/event-handlers.mdx) that triggers a query when changes are saved.

## 5. Add a resource query to write data

Navigate back to the **Code** tab and click **+** to add another REST API resource query. The action you use often depends on the API. **POST**, **PUT**, and **PATCH** actions modify data differently. Since this query modifies existing data only, select the **PATCH** action.

You provide the modified data in the _body_ of the the API request. Retool supports different types of body data, such as **JSON** and **Form Data**. Select **JSON** and set the value to `table1.changesetArray`.

Each table row corresponds to a customer object in the API. When run, this query updates each object.

Navigate back to the **Code** tab and click **+** to add another Resource query for Retool Database. As this query will write back changes, select **GUI** mode. 

GUI mode provides you with a set of inputs to configure a writable query. This is a safer alternative to using raw SQL statements. Update the query with the following options.

| Option                         | Value                           | Description                                                  |
| ------------------------------ | ------------------------------- | ------------------------------------------------------------ |
| **Table**                      | `sample_customers`              | The database table to update.                                |
| **Action type**                | **Bulk update via primary key** | Update multiple records that correspond to the primary keys. |
| **Primary key column**         | `id`                            | The primary key column to identify records.                  |
| **Array of records to update** | `table1.changesetArray`         | The edited records in the table.                             |

Each table row corresponds to a database table record. When run, this query updates each record and uses the `id` to match edited values with the correct table record.

## 6. Configure the save action

The final step is to configure the table's **Save action**. Select the table to display its settings in the Inspector, then select the **Save action**. 

Event handlers trigger queries and perform actions in response to events or user interactions, such as clicking the **Save** button. Add an event handler and select the query you just created.

## 7. Refresh data on save

Now that the save action is set up, you can edit table data and then save changes. However, the table does not immediately reflect the changes. This is because the query that retrieves data from the database needs to be run again.

Queries also support event handlers and can perform actions on success or failure. Add an event handler to the write query that triggers the read query on success. Now, whenever changes are successfully saved, the read query runs again and the table reflects the latest changes.

---

## Queries and code tutorials

import Tutorial from '/docs/_partials/_doctypes/_tutorial.mdx';

---

## Create a Resource Configuration

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Creates a new resource configuration for a non-default environment.

---

## Create a Resource

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Creates a new resource, along with a resource configuration for the default environment.

---

## Delete a Resource Configuration

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Deletes the resource configuration with the given resource configuration id.
    You cannot delete resource configurations in the default environment.

---

## Delete a Resource

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Deletes the resource with the given resource id, along with all of its configurations.

---

## Get a Resource By Id

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Returns the resource configuration with the given id.

---

## Get a Resource Configuration By Id

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Returns the resource configuration with the given resource configuration id.

---

## Get an Environment by Id

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Returns the environment with the given id.

---

## Get Environments

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Returns a list of environments.

---

## Get Resource Configurations

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Returns a paginated list of resource configurations.

---

## Get Resources

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Returns a paginated list of resources.

---

## Get the Default Environment

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Returns the default environment.

---

## Retool Developer API

import ApiLogo from "@theme/ApiLogo";
import Heading from "@theme/Heading";
import SchemaTabs from "@theme/SchemaTabs";
import TabItem from "@theme/TabItem";
import Export from "@theme/ApiExplorer/Export";

This version of the Retool API is deprecated. Please use [Retool API v2](https://docs.retool.com/api).

  
  
    

      
        
          
            
              
                Security Scheme Type:
              
                http
              
            
              
                HTTP Authorization Scheme:
              
                bearer

---

## Update a Resource Configuration

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Updates the resource configuration with the given resource configuration id.

---

## Update a Resource

import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";

  

Updates the resource with the given resource id.

---

## All Edge releases of Self-hosted Retool

import Releases from '@site/src/components/ReleaseNotesAll.js';

---

## Self-hosted Retool Edge releases



---

## Self-hosted Retool releases

You can [deploy a self-hosted instance of Retool](../self-hosted/tutorials.mdx) on your own infrastructure, such as a virtual private cloud (VPC). There are two release channels for self-hosted versions of Retool:

- [Stable](#stable): A new release every 13 weeks (quarterly).
- [Edge](#edge): A new release every week.

Unless you need access to the latest features and have the ability to upgrade frequently, Retool strongly recommends that you use the Stable channel.

:::info Legacy releases

import Legacy from "./_partials/_legacy.mdx";

:::

## Stable release channel {#stable}

import Stable from "./_partials/_stable.mdx";

## Edge release channel {#edge}

import Edge from "./_partials/_edge.mdx";

## Update to a newer release

import Upgrade from "./_partials/_upgrade.mdx";

## Release versioning

Releases use [semantic versioning](https://semver.org) for versions using the format `MAJOR.MINOR.PATCH-CHANNEL`.

- `MAJOR.MINOR` refers to the release (e.g., `3.33`).
- `MAJOR.MINOR.PATCH` refers to a specific version of the release (e.g., `3.33.1`).
- `CHANNEL` refers to the release channel. Either `stable` or `edge`.

Retool will release `PATCH` version updates (e.g., `3.47.2-stable`) if necessary. Whether you upgrade to a new release or perform an update of an existing one, always specify the full semantic version number you want to use.

---

## All Stable releases of Self-hosted Retool

import Releases from '@site/src/components/ReleaseNotesAll.js';

---

## Self-hosted Retool Stable releases



---

## System architecture of self-hosted Retool deployments

import SelfhostedLatest from "@site/src/components/SelfhostedLatest";
import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

Organizations that have on-premise requirements can deploy the Retool platform on their own infrastructure. Each deployment instance uses a distributed set of containers with services for different functions. These work together to securely run the platform within your VPN or VPC.

## Containers and services

A standard deployment instance uses the following containers and services that interact with each other. The `SERVICE_TYPE` [environment variable](../reference/environment-variables/index.mdx#service_type) values determine what services a container runs.

| Container                               | Image                   | Repository                                                                                  | Services                                                       |
| --------------------------------------- | ----------------------- | ------------------------------------------------------------------------------------------- | -------------------------------------------------------------- |
| [api](#api)                             | `backend`               | [tryretool/backend](https://hub.docker.com/r/tryretool/backend)                             | `MAIN_BACKEND``DB_CONNECTOR``DB_SSH_CONNECTOR`     |
| [jobs-runner](#jobs-runner)             | `backend`               | [tryretool/backend](https://hub.docker.com/r/tryretool/backend)                             | `JOBS_RUNNER`                                                  |
| [workflows-worker](#workflows-worker)   | `backend`               | [tryretool/backend](https://hub.docker.com/r/tryretool/backend)                             | `WORKFLOW_TEMPORAL_WORKER` (for workflows)                                    |
| [workflows-backend](#workflows-backend) | `backend`               | [tryretool/backend](https://hub.docker.com/r/tryretool/backend)                             | `WORKFLOW_BACKEND``DB_CONNECTOR``DB_SSH_CONNECTOR` |
| [agent-worker](#agent-worker)           | `backend`               | [tryretool/backend](https://hub.docker.com/r/tryretool/backend)                             | `WORKFLOW_TEMPORAL_WORKER` (for agents)                                     |
| [agent-eval-worker](#agent-eval-worker) | `backend`               | [tryretool/backend](https://hub.docker.com/r/tryretool/backend)                             | `AGENT_EVAL_TEMPORAL_WORKER`                                   |    
| [code-executor](#code-executor)         | `code-executor-service` | [tryretool/code-executor-service](https://hub.docker.com/r/tryretool/code-executor-service) | No service type.                                               |

### api

The **api** container manages the core functionality for a Retool deployment instance, such as:

- Frontend interactions (e.g., building apps and workflows).
- Hosting apps and workflows.
- Managing users and permissions.

**api** runs the following services:

import MainBackend from "../_partials/_service-main-backend.mdx";
import DbConnector from "../_partials/_service-db-connector-services.mdx";

#### Network ingress and egress

**api** requires ingress from client interactions, such as loading an app in a browser. These are typically handled by a load balancer (e.g., nginx) which proxies the requests.

The following table displays ingress and egress requirements for **api**.

| Container             |       Network ingress        |       Network egress        |
| :-------------------- | :--------------------------: | :-------------------------: |
| **code-executor**     |  |  |
| **postgres**          |  |  |
| **retooldb-postgres** |  |  |
| **Temporal**          |  |  |
| **Resources**         |  |  |

#### Replicas

You can replicate **api** as you scale to manage higher volumes of workflow traffic.

The following diagram illustrates a typical resource query trace as it processes through a self-hosted deployment.

### jobs-runner

The **jobs-runner** container manages background tasks, runs database migrations for Retool version upgrades, and [Source Control](../../source-control/index.mdx). It runs the following service:

import JobsRunner from "../_partials/_service-jobs-runner.mdx";

#### Network ingress and egress

The following table displays ingress and egress requirements for **jobs-runner**.

| Container    |       Network ingress        |       Network egress        |
| :----------- | :--------------------------: | :-------------------------: |
| **postgres** |  |  |

#### Replicas

You must not replicate **jobs-runner**. It performs tasks and migrations that must be performed as a single container.

### workflows-worker

The **workflows-worker** container continuously polls the Temporal Cluster for tasks required to either start or execute blocks within a workflow. It makes requests to **code-executor** to execute blocks and process results, then reports back to Temporal to continue or complete workflow execution.

**workflows-worker** runs the following service.

import WorkflowTemporalWorker from "../_partials/_service-workflow-temporal-worker.mdx";

#### Network ingress and egress

The following table displays ingress and egress requirements for **workflows-worker**.

| Container             |       Network ingress        |       Network egress        |
| :-------------------- | :--------------------------: | :-------------------------: |
| **code-executor**     |  |  |
| **postgres**          |  |  |
| **retooldb-postgres** |  |  |
| **Temporal**          |  |  |
| **Resources**         |  |  |

#### Replicas

You can replicate **workflows-worker** as you scale to manage higher volumes of workflow traffic.

### workflows-backend

The **workflows-backend** container receives and processes requests from **code-executor** to process workflows. It also handles the upload of workflows logs, block results, and run status updates.

import WorkflowBackend from "../_partials/_service-workflow-backend.mdx";

#### Network ingress and egress

The following table displays ingress and egress requirements for **workflows-backend**.

| Container             |       Network ingress        |       Network egress        |
| :-------------------- | :--------------------------: | :-------------------------: |
| **code-executor**     |   |  |
| **postgres**          |  |  |
| **retooldb-postgres** |  |  |
| **Temporal**          |  |  |
| **Resources**         |  |  |

#### Replicas

You can replicate **workflows-backend** as you scale to manage higher volumes of workflow traffic.

### agent-worker
The **agent-worker** container continuously polls the Temporal cluster for pending agent runs to execute. It makes requests to [code-executor](#code-executor) to execute the agent logic, call tools and process results, and then it reports back to Temporal to continue or complete agent execution.

#### Network ingress and egress

The following table displays ingress and egress requirements for **agent-worker**.

| Container             |       Network ingress        |       Network egress        |
| :-------------------- | :--------------------------: | :-------------------------: |
| **code-executor**     |  |  |
| **postgres**          |  |  |
| **retooldb-postgres** |  |  |
| **Temporal**          |  |  |
| **Resources**         |  |  |

#### Replicas

You can replicate **agent-worker** as you scale to manage higher volumes of workflow traffic.

### agent-eval-worker
The **agent-eval-worker** continuously polls the Temporal Cluster for pending agent eval runs. When an eval is running, the **agent-eval-worker** makes calls from Postgres to the LLM provider configured in the [test case](../../agents/concepts/evals.mdx#datasets-and-test-cases), and compares the output to the expected value.

#### Network ingress and egress

The following table displays ingress and egress requirements for **agent-eval-worker**.

| Container             |       Network ingress        |       Network egress        |
| :-------------------- | :--------------------------: | :-------------------------: |
| **code-executor**     |  |  |
| **postgres**          |  |  |
| **Temporal**          |  |  |
| **Resources**         |  |  |

#### Replicas

You can replicate **agent-eval-worker** as you scale to manage higher volumes of workflow traffic.

### code-executor

The **code-executor** container executes single block runs from the Workflows IDE, multiple blocks as part of a Workflow execution, and any arbitrary user code written in a Workflow. It executes JavaScript and Python code directly, or makes requests to **workflows-backend** to run resource queries against databases, APIs, etc.

By default, the **code-executor** uses [nsjail](https://github.com/google/nsjail) to sandbox code execution. [nsjail](https://github.com/google/nsjail) requires [privileged](https://docs.docker.com/reference/cli/docker/container/run/#privileged) container access. If your deployment does not support privileged access, you can [run the code executor in unprivileged mode](../guides/code-executor-security-privileges.mdx#run-code-executor-in-unprivileged-mode). However, Retool strongly recommends running NsJail with privileged mode, as it provides sandboxing and remains the most secure option.

#### Network ingress and egress

The following table displays ingress and egress requirements for **code-executor**.

| Container             |       Network ingress        |        Network egress        |
| :-------------------- | :--------------------------: | :--------------------------: |
| **api**               |   |  |
| **workflows-backend** |  |   |
| **workflows-worker**  |   |  |

#### Replicas

You can replicate **code-executor** as you scale to manage higher volumes of workflow traffic.

### postgres

**postgres** manages the internal PostgreSQL database in which an instance stores its apps, resources, users, permissions, and internal data. All deployment instances contain a default `postgres` container for testing purposes. You must [externalize this database](../guides/storage-database.mdx) before using the deployment instance in production.

### retooldb-postgres

You can optionally [configure your deployment to use Retool Database](../guides/retool-database.mdx) with a separate PostgreSQL database. You can use `retooldb-postgres` to get started but Retool recommends you migrate to an externally hosted database for use in production.

## Other dependencies

Self-hosted deployment instances have additional dependencies.

### Resources

Each data source you use with Retool is represented by a resource, such as APIs and external databases. Your deployment instance must have access to any of its resources to interact with data.

import Temporal from "../_partials/_temporal-info.mdx";

### Temporal

## Docker images

Retool uses Docker images that configure the containers and their respective services. Each container runs one or more services to distribute functionality. This approach allows for efficient scaling of a Retool deployment instance as needs grow.

Retool maintains two image repositories on [Docker Hub](https://hub.docker.com/u/tryretool):

- The [backend](https://hub.docker.com/r/tryretool/backend) image is used for most containers and services.
- The [code-executor-service](https://hub.docker.com/r/tryretool/code-executor-service) image is used to execute blocks of code in Retool Workflows.

Each Docker Hub repository tag corresponds to a particular version of Retool. You specify the version to use when deploying or upgrading a release. For instance:

- 
    tryretool/backend:
    
  
- 
    tryretool/code-executor-service:
    
  

For information about hardened variants of these images, including `arm64` support and security changes, see [Hardened images (beta)](./hardened-images.mdx).

---

## Container logs

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

You can use Retool's backend container logs to monitor your deployment's errors and performance. To access logs for Retool product events, such as user creation, use the [audit logs](../../org-users/guides/monitoring/audit-logs.mdx) instead.

## Access container logs

Retool sends container logs to standard output by default. The method to view container logs depends on your deployment provider. Refer to your provider's documentation to learn more about available logging options.

For deployments using [Docker Compose](../tutorials.mdx), use `sudo docker compose logs`.

See the [Docker logs documentation](https://docs.docker.com/engine/reference/commandline/compose_logs/) for a complete list of options you can pass to `docker compose logs` or `docker logs`.

For deployments using Kubernetes, use `kubectl logs`:

```
kubectl logs -p  -n 
```

## Use logs to debug

The `api` and `db-connector` containers or pods log events related to queries and requests. For events related to [Source Control](../../source-control/index.mdx), see the `jobs-runner` container or pod logs.

To follow the lifecycle of a request in Retool, look for the `requestId` field. You can then query using the request ID and any associated `QUERY_REQUEST`, `QUERY_COMPLETE`, `RESPONSE_SEND`, and `QUERY_RESULT` events, which contain the query's environment, source, and more details.

Set the `DEBUG` and `LOG_LEVEL` [environment variables](../reference/environment-variables/index.mdx) to enable more verbose logging.

## Send logs to log aggregators

Because logs are piped to standard output by default, you can connect them to log aggregators. For example, send logs to Datadog by [registering an agent](https://docs.datadoghq.com/containers/docker/log/?tab=containerinstallation). See the documentation for your log aggregator for more details.

On Docker-based deployments, you configure logging drivers by creating and configuring a `daemon.json` file, then restarting Docker. See [Docker's documentation](https://docs.docker.com/engine/reference/commandline/dockerd/#daemon-configuration-file) for more details.

---

## Custom code execution security

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

The [code-executor](./architecture.mdx#code-executor) container executes arbitrary user-written code for custom JavaScript or Python libraries in [workflows](../../workflows/index.mdx). Retool recommends running [code-executor](./architecture.mdx#code-executor) with sandboxing enabled, for flow safety and data security reasons. But Retool does support running without sandboxing enabled, as it requires privileged container access, which some organizations do not allow.

[Privileged](https://docs.docker.com/reference/cli/docker/container/run/#privileged) container access is commonly restricted by default and why Retool allows code-executor to run in _unprivileged_ mode. Should this be the case, Retool recommends an exception be made so that Retool can run as a trusted product or framework. This will allow code-executor to safely manage your operational code.

## Security, functionality, and usability

Retool follows three core philosophies when developing the self-hosted architecture: _security_, _functionality_, and _usability_.

| Priority | Description |
| --- | --- |
| Security | Protect systems and data with strong safeguards. |
| Functionality | Build powerful and flexible features. |
| Usability | Provide straightforward and low-friction experiences. |

Too much focus on one priority can be at the detriment of the others. Retool's approach is to always strike the right balance across all three.

Due to the potential impact that executing arbitrary code can have, Retool prioritizes security and functionality above usability. As such, Retool's architecture is built so that this custom code is executed in a secure, sandboxed environment but without arbitrary restrictions. This approach ensures that customers don't have to review and ensure that all custom code written by their users is free from bugs or malicious code.

## Sandboxed environment with NsJail

Retool uses [NsJail](https://github.com/google/nsjail), a process isolation tool for Linux, to securely execute custom code. NsJail leverages Linux kernel features such as namespaces, control groups (cgroups), and [seccomp-bpf](https://en.wikipedia.org/wiki/Seccomp) [syscall](https://man7.org/linux/man-pages/man2/syscall.2.html) filters to isolate custom code from the rest of the system.

By doing so, NsJail is able to isolate the file system, restrict network access, and enforce CPU and memory limits to prevent any single workflow from impacting overall system stability.

## Priviledged and unprivileged modes

Docker containers can be run in either _unprivileged_ or  _privileged_ mode.

| Mode | Description |
| --- | --- |
| Unprivileged | If the container cannot run with `--privileged`, NsJail cannot apply its sandbox. In this case, custom code runs without Retool’s isolation safeguards, and you must fully trust all user-written code. |
| Privileged | In self-hosted Retool, the code-executor container is configured with Docker’s `--privileged` flag. This allows NsJail to configure the kernel features required for sandboxing. NsJail then executes user code inside a jailed process with reduced privileges. |

The code-executor container is configured to run in privileged mode by default. Retool only uses the minimum amount of elevated permissions necessary to implement sandboxing and relinquishes these once no longer required.

:::note
Only the code-executor container is configured to run in privileged mode—all other backend services operate separately without any form of elevated permissions.
:::

Retool strongly recommends that self-hosted organizations always use privileged mode unless there are specific reasons which prevent this.

## Exceptions for using unprivileged mode

In some cases, it may not be possible for a self-hosted deployment instance to run code-executor in priviledged mode. For instance:

- Managed container orchestration environments, such as [GKE Autopilot](https://cloud.google.com/kubernetes-engine/docs/concepts/autopilot-overview) and [AWS Fargate](https://aws.amazon.com/fargate/), do not allow the use of privileged containers.
- Kubernetes clusters with pod security policies can explicitly block privileged containers.

Retool provides customers with the option of [running the container in unprivileged mode](../guides/code-executor-security-privileges.mdx) for these types of situations. When you configure code-exector to use unprivileged mode, however, many of the safeguards that sandboxing provide are no longer in place. This assumes that you fully trust all user-written custom code that is executed.

## Risks of non-sandboxed custom code environment

:::warning
A non-sandboxed environment should only be used if an organization can trust that all users will not write code that is either malicious or critically flawed.
:::

| Risk Category | Examples |
|-------------------|--------------|
| Data risks | Exfiltrating credentials or sensitive data to an external attacker; accessing or manipulating restricted data. |
| Account risks | Compromising or taking control of other user accounts. |
| Execution risks | Interfering with future code execution. Bugs or malicious code can cause unexpected or unstable results. |
| System risks | Damaging the deployment instance by deleting or modifying critical system files. Bugs in user code can also cause unintended damage, such as erroneous deletion of files or unpredictable outcomes. |
| External misuse | Coordinating targeted attacks using the deployment instance. |

---

## Hardened images for self-hosted Retool

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

Self-hosted instances can use _hardened images_, which are available on the [edge release channel](../../releases/index.mdx#edge). These images are designed to improve supply-chain security, reduce the attack surface, and support modern infrastructure while remaining functionally compatible with existing deployments.

Refer to the [changelog entry for hardened images](/changelog/hardened-images) for information about how to transition to using hardened images.

This guide explains what hardened images are, how they differ from classic images, and what to consider before adopting them in your environments.

## Hardened vs. classic images

Hardened images introduce several changes compared to the existing **classic** Docker images:

- **Chainguard-based, Wolfi OS images:** Hardened images use a minimal [Wolfi](https://wolfi.dev/) base and are patched and maintained through Chainguard. System-level packages are curated and updated to reduce known vulnerabilities and tighten supply-chain security.

- **Multi-architecture support:** Hardened images are published as **multi-architecture Open Container Initiative (OCI) images** that support both `amd64` and `arm64`. When you run them on `arm64` nodes, you can often reduce compute requirements and improve performance compared to equivalent `amd64` instances.

- **Bazel-built OCI images instead of Dockerfiles:** Hardened images are built using Bazel, which assembles OCI layers directly instead of using traditional Dockerfiles. This enables finer-grained control over file layout, permissions, and dependencies.

Classic images remain supported, and you can continue using them if hardened images do not fit your requirements. Over time, Retool plans to make hardened images the default for self-hosted deployments.

## Image repositories and release channels

Hardened images use the same repositories as [classic images](./architecture.mdx#containers-and-services): `tryretool/backend` for most backend services such as `api`, `jobs-runner`, and workflow workers.

Refer to the [Self-hosted architecture](./architecture.mdx#docker-images) guide for an overview of how these images map to containers and services.

### Hardened image tags

Hardened images are first released on the [edge channel](../../releases/index.mdx#edge) as beta builds. Tags use the following pattern: `X.YYY.Z-edge-hardened-beta`, where `X.YYY.Z` matches the corresponding edge version for classic images. 

The changes available on the hardened image tag contain the same changes as the classic images. These changes can be found in the [edge release notes](../../releases/edge/index.mdx).

Classic edge tags (for example, `X.YYY.Z-edge`) continue to be published alongside hardened tags, and you can choose which series to deploy.

## Changes that may affect your deployment

Hardened images are intended to be functionally compatible with classic images, but there are some important behavioral and operational differences to be aware of.

### System dependency packages

Hardened images include a smaller, more controlled set of _system dependency packages_ at the OS level. Examples of system dependencies include tools like `curl` or `ssh`, or other utilities installed by the base image.

In classic images, some tools may be present because of the underlying distribution or how the image is built. With hardened images:

- System dependencies are explicitly curated and patched.
- Extra tools that Retool does not depend on are removed where possible.
- Newly added dependencies are evaluated for security and long-term support.

For broader security recommendations, refer to [Security hardening best practices](./security-hardening.mdx).

### Modified or custom Retool images and debugging tools

Some customers extend Retool images to add additional packages or tools, or rely on interactive debugging inside the containers. For example:

- Building a custom Docker image `FROM tryretool/backend` and running `apt-get install ...`.
- Baking extra utilities directly into the containers.
- Opening a shell in a Retool container to run ad-hoc debugging commands.

Hardened images significantly change how these patterns work:

- The underlying OS is no longer Debian-based; common tools like `apt` are not available.
- Image layers are generated by Bazel, and the file layout, users, and permissions may differ from classic images.
- Directly modifying hardened images can bypass many of the security benefits they provide.
- Nonessential system utilities are removed, so you may see fewer CLI tools available and different default users/permissions compared to classic images.

Retool does not recommend, and cannot support, building custom images that modify hardened Retool images in place. If you have additional runtime or debugging needs:

- Prefer running sidecar containers or separate services that you control.
- If you must create a custom assembly that embeds Retool, use a workflow that is compatible with Wolfi/Chainguard images and follows their security guidance.
- Keep custom tooling off the main Retool containers whenever possible.
- Use centralized logging and observability tools, such as those described in [Container logs](./container-logs.mdx) and [Collect self-hosted telemetry data](../guides/telemetry.mdx).
- Run debugging tools from separate admin or bastion hosts rather than from within Retool containers.

If you currently rely on custom Retool images or in-container debugging workflows, plan for additional validation and manual changes when testing hardened images.

### MSSQL with Windows Authentication

The only known product feature not currently supported in hardened images is **Microsoft SQL Server with Windows Authentication**. This configuration is complex to maintain and introduces additional security and build performance considerations.

If you currently use MSSQL with Windows Authentication, you should continue using _classic images_ while Retool determines the long-term support model for Windows Authentication.

### code-executor and other workloads

Many customers also run the separate [code-executor](../guides/code-executor-security-privileges.mdx) service.

While hardened images are in public beta:

- Hardened images are available for core backend services first.
- A hardened `code-executor-service` image is under active development and may not yet be available for your version.
- You may therefore have a _mixed environment_ where core backend services use hardened images, while some auxiliary services (such as `code-executor`) continue to use classic images.

This mixed setup is expected and supported during the public beta. You can gradually migrate each workload to hardened images as they become available, starting with non-production environments.

## Infrastructure and `arm64` considerations

Hardened images are published as multi-architecture images that support both `amd64` and `arm64`. However, full support for an end-to-end `arm64` deployment depends on hardened images being available for all required services, including `code-executor`. 

Retool is actively working to expand `arm64` coverage across all services.

## Frequently asked questions

Refer to the following frequently asked questions.

### Do hardened images change how Retool behaves?

Hardened images are intended to be **functionally equivalent** to classic images for supported features. Most differences are at the OS, packaging, and security hardening layers.

You should not see changes to how apps, workflows, or queries behave, aside from:

- Unsupported configurations such as MSSQL with Windows Authentication.
- Any custom tooling or workflows that rely on incidental OS tools inside the containers.

### Can I switch between classic and hardened images?

Yes. Because hardened images share the same underlying product version as classic images, you can:

- Upgrade from classic to hardened images on a given edge version.
- Roll back to classic images on that same version if necessary.

Always follow the guidance in [Upgrade deployments](./update-deployment.mdx) when changing image tags, and test changes on non-production instances first.

---

## Multi-instance deployment

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

If running one instance of Self-hosted Retool with [multiple environments](../../org-users/guides/configuration/environments.mdx) doesn't meet your requirements, you can run multiple instances of Retool each with its own database. Multiple instances are sometimes used to meet certain security, compliance, and networking requirements.

Each separate instance of Retool can be treated as an “environment” in the typical programming context. Developers typically build in development and staging, and end-users often access apps only in production. It is also possible to spin up more instances to represent Quality Assurance (QA) or User Acceptance Testing (UAT) environments.

There is no limit to the number of separately deployed instances of Retool you can spin up in your organization with a single license key. Users are also de-duplicated in your organization by email address, so additional instances do not affect your user count.

## When to use a single Retool instance

In general, you should only deploy the instances you need. If you’re starting out with a small, defined set of use cases for one team, or if you don’t need to isolate Retool instances by VPC, you can start with a single instance.

Retool allows you to define [multiple environments](../../org-users/guides/configuration/environments.mdx) within a single instance so that you can safely build, run, and test apps for your end-users.

## When to deploy multiple instances

You might have development endpoints where editors can safely build and test apps on non-production data. If so, it's recommended to mirror a typical Software Development Life Cycle (SDLC):

- Deploy a development instance of Retool in the development VPC.
- Deploy a production instance of Retool in the production VPC.

This ensures your development Retool instance can only point to development endpoints and helps organize different user types across your organization.

Our [Source Control](../../source-control/index.mdx) feature lets you safely promote application changes between instances via a Git-based workflow. You can only have one Git repository associated with an instance—all pull requests must go through a single group of reviewers.

### Isolate team development workflows

Retool recommends you split out instances for each team if:

- You operate under a full-stack or [Spotify](https://www.atlassian.com/agile/agile-at-scale/spotify) model and have many autonomous teams building tooling on their own.
- You don't have shared resources or endpoints across teams, but teams want to manage their own development workflows independently.

### Legal and compliance purposes

If you are launching or operate into a market that requires network traffic to be isolated to specific regions, you may need to isolate your Retool instance as well. In other cases, a production instance might need to be locked down to be read-only (i.e., no editors can build apps).

### Testing new features and upgrades

If you want to safely test out a new beta feature or instance-wide setting (e.g., a new SSO configuration), you can spin up a separate Retool instance for this purpose. See [upgrade deployments](update-deployment.mdx) for more information.

---

## Retool AI for self-hosted deployments

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

Self-hosted customers can deploy and embed Retool AI for use with apps and workflows. To learn more about how you can use Retool AI, refer to the [Retool AI actions concept](../../queries/concepts/actions.mdx) documentation, and the [AI actions how-to guides](../../queries/guides/ai/chat.mdx)

## AI platform options

You can configure a direct connection to a [supported AI platform](../../data-sources/concepts/models.mdx) by providing an API key. You also have the option to use a Retool-managed OpenAI connection. For production use cases, Retool recommends you provide an API key and directly connect to an AI platform.

The Retool-managed OpenAI connection is disabled by default. Contact your Retool account representative or our [support team](mailto:support@retool.com) to gain access.

## Retool-managed Vectors

[Retool-managed Vectors for self-hosted deployments](../guides/vectors.mdx) is built on [Retool Database](../guides/retool-database.mdx). To store vector data, you must configure and enable Retool-managed Vectors and Retool Database.

## AI Actions

import Partial from '../../_shared/_retool-managed-self-hosted.mdx';

## Data sharing and retention

When Retool AI is used to build or configure a query, application, or workflow (e.g., prompting [Ask AI](../../queries/concepts/ask.mdx) to help write queries), the input is shared with the applicable third-party LLM provider, listed in our [Subprocessors](https://docs.retool.com/legal/subprocessors) page. Inputs are deleted within 30 days.

Any inputs or outputs that correspond to the categories above may continue to be used to inform product improvements. Retool does not use any inputs submitted to, or outputs generated from, Retool AI that is embedded within a deployed application or workflow (e.g., text stored in [Retool-managed Vectors](../../data-sources/quickstarts/retool-vectors.mdx)). These inputs and outputs are treated as "Customer Data" in accordance with the [Customer Terms of Service](https://docs.retool.com/legal/customer-terms-of-service), [Security Practices](https://docs.retool.com/legal/security), and [Data Processing Addendum](https://docs.retool.com/legal/dpa).

## Additional resources
Refer to the following documentation for additional ways you can use AI within Retool: 
* [Assist](../../apps/guides/assist)
* [Agents](../../agents/index.mdx)
* [AI in app building](../../apps/concepts/ai.mdx)

---

## Security hardening best practices

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

Retool provides different security hardening options that you can customize. Use the following best practices when evaluating and configuring your deployment according to your use case, threat model, and risk assessment.

For an overview of Retool's hardened container images and how they relate to security posture, see [Hardened images (beta)](./hardened-images.mdx).

:::note Kubernetes infrastructure 

If you deploy Retool on Kubernetes, review your hosting provider’s documentation:

- [Amazon EKS Best Practices Guide for Security](https://aws.github.io/aws-eks-best-practices/security/docs/)
- [Security concepts for applications and clusters in Azure Kubernetes Service (AKS)](https://learn.microsoft.com/en-us/azure/aks/concepts-security)
- [GKE - Harden your cluster's security](https://cloud.google.com/kubernetes-engine/docs/how-to/hardening-your-cluster)

:::

## Increase security of your deployment

Retool uses [environment variables](../reference/environment-variables/index.mdx) to control certain functions and characteristics for self-hosted deployments. Use the following environment variable recommendations to improve security hardening.

### Set a strong encryption key

Retool encrypts sensitive values like API credentials in its internal database. Set the `ENCRYPTION_KEY` environment variable to a cryptographically random value. If you have the [OpenSSL CLI installed](https://www.openssl.org/docs/man3.2/man1/openssl.html), generate this value with this command: `openssl rand -base64 32`. 

### Set a strong JWT secret

Retool uses a JSON web token (JWT) to sign requests for authentication with Retool's backend API server. If changed, all active user login sessions are invalidated. Set the `JWT_SECRET` environment variable to a cryptographically random value. If you have the [OpenSSL CLI installed](https://www.openssl.org/docs/man3.2/man1/openssl.html), you can generate this value using this command: `openssl rand -base64 32`.

### Disable public apps

Retool supports sharing [apps publicly](../../apps/guides/app-management/embed-apps#public-apps) using a public link. If you want to prevents apps from being shared publicly, set  `DISABLE_PUBLIC_PAGES` to disable public apps and `DISABLE_IMAGE_PROXY` to disable the proxy used for public apps.

### Disable app editing in production

When using source control to promote apps between development and production instances, set `VERSION_CONTROL_LOCKED` to disable editing for all elements (apps, workflows, resources, etc.) in the production environment. This forces app changes to be made using source control. Admins can still delete unprotected elements.

### Enable short sessions

Retool sessions are valid for one week by default. Set `USE_SHORT_SESSIONS` to restrict session length to 12 hours instead.

## Increase security for your users

Configuring authentication options ensures only your users have access to Retool. In addition to single sign-on (SSO) support, you can require strong passwords and two-factor authentication (2FA).

### Single sign-on

Retool supports SSO to enable users to securely access multiple applications and services using one set of credentials. See the [SSO documentation](../../sso/index.mdx) to set up SSO with your identity provider.

### Disable username and password login

You can [disable Retool's built-in authentication method](../../sso/guides/authentication/enforce-sso.mdx) (email address and password) and require that all users log in using SSO by setting the `DISABLE_USER_PASS_LOGIN` environment variable. Make sure to set this variable if you use SSO exclusively.

### Require strong passwords for login

Navigate to **Settings > Beta** to enable **Require Strong Password for Login**. This requires passwords to have:

- A minimum of 12 characters.
- One uppercase letter.
- One lowercase letter.
- One number.
- One special character.

### Require two-factor authentication

You can require 2FA in Retool by navigating to **Settings > Advanced** and enabling **Require Two Factor Authentication**. Users can set up 2FA using either TOTP or FIDO2 hardware keys. 

### Limit default permissions

Retool’s [permissions system](../../permissions/guides/configure-permission-groups) lets you configure permission controls for apps, resources, and workflows. Retool recommends enforcing least privilege for the **All users** group and selectively adding permission groups as needed for users.

### Respond to compromised accounts

If a user’s account is compromised, consider taking the following steps:

- [Disable the user](../../org-users/tutorials/manage.mdx#disable-users) to mitigate the current risk.
- [Review audit logs](../../org-users/guides/monitoring/audit-logs.mdx) to identify suspicious account behavior such as: 
  - User sign-in events.
  - Query runs.
  - User invites.
  - Password resets.
- Enable two-factor authentication for the user, and consider [requiring 2FA for your organization](../../org-users/guides/user-management/two-factor-authentication.mdx#enable-2fa).

After completing an investigation and mitigating the immediate impact, [enable the user](../../org-users/tutorials/manage.mdx#enable-users).

## Prevent query variable spoofing

[Prevent query variable spoofing](../../queries/quickstart#query-variable-spoofing-prevention) is enabled by default. This prevents users from manipulating network requests and passing in arbitrary values to prepared statements. Confirm this is setting is enabled by navigating to **Settings > Beta**.

## Keep your deployment up to date

You are responsible for updating self-hosted instances and for the security of your underlying hosts. You can subscribe to Retool's [changelog](https://docs.retool.com/releases) using [RSS](https://docs.retool.com/changelog/rss.xml) or [JSON](https://docs.retool.com/changelog/feed.json) to stay up to date with releases. 

## Monitor audit logs

Actions that users take within Retool are stored in [audit logs](../../org-users/guides/monitoring/audit-logs.mdx). You can also write these actions to [container logs](container-logs.mdx) and pipe them into your observability tooling by setting the `LOG_AUDIT_EVENTS` [environment variable](../reference/environment-variables/index.mdx#log_audit_events).

---

## Temporal Clusters for Self-hosted Retool

import TemporalOptions from "../_partials/_temporal-options.mdx"
import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

## Compare options

import TemporalCompare from "../_partials/_temporal-compare.mdx"

## Egress

Whether you use a Retool-managed or self-managed cluster, egress is required for Self-hosted Retool to use Temporal. Both `MAIN_BACKEND` and `WORKFLOW_TEMPORAL_WORKER` containers must be able to connect.

If you're using a Retool-managed cluster or a self-managed cluster on Temporal Cloud, your instance must have egress to the internet (public network) on ports for the following domains:

| Port | Domains |
| --- | --- |
| 443 | `*.retool.com`, `*.tryretool.com` |
| 7233 | Your Temporal namespace endpoint. Retool-managed clusters use the format `org-[sid].atdwz.tmprl.cloud`, where `sid` is your organization's ID (e.g., `org-1234567890.atdwz.tmprl.cloud`.  |

### Lifecycle

When the Temporal Cluster is needed:

1. The `MAIN_BACKEND` service enqueues workflows with Temporal.
2. A `WORKFLOWS_TEMPORAL_WORKER` receives instructions from Temporal for the specific tasks required to run a workflow, leveraging the Self-hosted Retool backend.
3. `DB_CONNECTOR` makes a request to your network-protected resources, or `CODE_EXECUTOR` executes custom Python.

### Data and encryption

:::tip

By default, Retool-managed clusters only store encrypted names and IDs of objects in Temporal Cloud—only minimal customer data is sent to the cluster. [All encrypted data in a Retool-managed cluster is retained for 14 days](https://docs.retool.com/legal/security#storage-of-customer-data). [Please reach out](/support) if you would like to make changes to this retention period.

:::

All data sent between your deployment and Temporal Cloud is encrypted with your `ENCRYPTION_KEY`.

Data processing, including query and code execution, occurs within your VPC by workers that you self-host. All coordination and orchestration happens in an isolated namespace within the Retool-managed cluster. No code results or query data is sent outside of your VPC by default.

The process occurs within your VPC. All coordination and orchestration happens in an isolated namespace within the Retool-managed cluster. No query data is sent from your VPC.

Retool-managed clusters perform automatic [mTLS](https://en.wikipedia.org/wiki/Mutual_authentication#mTLS) rotation. If necessary, you can manually perform this when required.

## Local clusters

:::info Each instance requires its own local cluster

If you deploy multiple instances of Self-hosted Retool with Workflows (e.g., staging and production instances), they must each have their own local cluster namespace.

:::

A _local_ cluster is one that is created when deploying Self-hosted Retool with Workflows. Unlike a self-hosted cluster in your VPC, a local cluster is only used by its Self-hosted Retool instance.

 Local clusters enable you to get up and running quickly without needing to commit to a dedicated Temporal Cluster, but can become complex to scale and manage. A Retool-managed cluster, or a self-managed cluster on Temporal Cloud or in your VPC, centralizes the cluster as each instance can use it via a resource-isolated namespace.

## Large scale deployments

Workflows can be deployed to scale to hundreds of concurrent block runs per second. There are three Temporal options for running more than 10 workflows per second:

- Use a Retool-managed Temporal Cluster.
- Use a self-managed Temporal Cluster.
- Use Cassandra as the Temporal datastore.
If you anticipate running workflows at a higher scale, please [reach out to us](/support) to work through a deployment strategy that is best for your use case.

## Telemetry

Retool uses a [Prometheus Metrics exporter](https://typescript.temporal.io/api/interfaces/worker.PrometheusMetricsExporter) to expose [Temporal Worker metrics](https://docs.temporal.io/references/sdk-metrics). This is specified in the Temporal worker's [runtime options](https://typescript.temporal.io/api/interfaces/worker.RuntimeOptions#telemetryoptions).

```
telemetryOptions: {
  metrics: {
    prometheus: { bindAddress: '0.0.0.0:9090' }
  }
}
```

You can optionally specify the endpoint for an [OpenTelemetry collector](https://opentelemetry.io/docs/collector/) if you prefer to use one with the `WORKFLOW_TEMPORAL_OPENTELEMETRY_COLLECTOR` environment variable.

Metrics are available for scraping using the `/metrics` route.

For more information about telemetry, refer to [Collect self-hosted telemetry data](../guides/telemetry.mdx).

---

## Self-hosted Retool upgrade best practices

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

This guide provides high-level recommendations and best practices for updating Retool for production deployments. For deployment-specific installation instructions, use the deployment guide for your Retool deployment type.

## Prepare for the update

Before you upgrade, choose the version to upgrade to and schedule downtime for the upgrade.

### Choose the version

Check the [Releases](../../releases/index.mdx) page to see each version of Retool and its changelog. Read the changelogs between your current version and the version you're upgrading to. Changelogs also contain instructions for managing deprecated features.

Keep your deployment instance up-to-date with the latest release. If your current version is more than 10 versions behind, Retool recommends you upgrade incrementally (e.g., 3.0, 3.10, 3.20...) and verify each installation.

### Schedule downtime

If you run Retool on a single container or node, there will be downtime during the upgrade. If you run a multi-container deployment, see the [Scale Retool infrastructure](../guides/scale-retool-infra.mdx#zero-downtime-upgrades) guide to learn about zero-downtime upgrades. If you run multiple instances, consider scheduling separate downtimes for each: for example, upgrade dev first, then staging, and then production.

Schedule the downtime in advance, even if it's a short time period or during off-peak hours. Clearly communicate to users that they should not use or edit apps during the upgrade. It may be helpful to make an announcement to users a few days in advance.

The amount of time to schedule varies. For example, point-in-time recovery and taking database snapshots can impact the duration.

## Test apps on a sandbox instance

Testing on a sandbox instance is not required, but it is recommended if:

- You usually run a single instance of Retool.
- Your primary Retool instances are heavily used.
- You have done a moderate to high amount of customization in your Retool apps. For example, using custom CSS, custom components, or Retool app development workflows.
- Your current version of Retool is more than two months behind the most recent version.

Pre-update testing includes spinning up a separate, temporary Retool instance with the new version and testing it with your current apps. This allows you test the upgrade process in a low-risk environment and build confidence for upgrading your production instances.

### 1. Spin up a temporary instance

Follow the instructions in your deployment guide for spinning up a new instance, or use an existing sandbox instance. First, install your current version of Retool.

#### Copy your database

Retool recommends seeding the temporary instance with your current apps by copying your existing instance’s PostgreSQL database to the temporary instance. It’s important to use a copy of your existing database. Don't connect to your existing Retool database: upgrades often include database migrations, which may alter the database.

Refer to your database provider for instructions on backing up your database to a file and restoring the new database with that file.

Before you connect your new instance to the database, ensure that the `ENCRYPTION_KEY` and `USE_GCM_ENCRYPTION` environment variables are set to the same value as in your existing Retool instance.

:::warning

Ensure that the temporary instance is configured with these environment variables to prevent disruptions to Retool Workflows on the existing instance:

- Set `TEMPORAL_TASKQUEUE_WORKFLOW=sandbox-test` on containers where `SERVICE_TYPE` includes `MAIN_BACKEND`, `WORKFLOW_BACKEND`, or `WORKFLOW_TEMPORAL_WORKER`.
- Set `WORKER_TEMPORAL_TASKQUEUE=sandbox-test` on containers where `SERVICE_TYPE` includes `WORKFLOW_TEMPORAL_WORKER`.

**Failure to apply these environment variables may cause both the existing and temporary instances to be unable to run Workflows.**

:::

### 2. Install the new version

Run the upgrade on the temporary Retool instance, following the upgrade steps for your deployment type.

### 3. Test your apps

Test the new instance on your most critical apps, and apps which use a variety of resources and components. You might want to write test cases, a checklist, or an internal upgrade runbook to help standardize this process. This can include specific context and commands and become a resource for your team.

After your tests pass, clean up the new instance and upgrade your production instances.

## Upgrade Retool

During Retool upgrades, remind users about the downtime and completed upgrades, and create backups of your instances. Upgrade instances sequentially, starting with the development instance, followed by staging and production.

### 1. Remind your users about the downtime

As a courtesy, it’s helpful to send an announcement to your users shortly before you begin the downtime. If you use Source Control, you should also avoid merging any pull requests while you perform the upgrade.

### 2. Create a backup

You need to create a backup for each of your Retool instances so that you can restore them if needed. If you're upgrading in batches, create a backup before each batch.

If you're deployed on a cloud platform like AWS, the cloud provider might have a convenient way to back up the state of your running instances. For example, AWS offers a way to [back up](https://docs.aws.amazon.com/prescriptive-guidance/latest/backup-recovery/ec2-backup.html) and [restore EC2 instances](https://docs.aws.amazon.com/prescriptive-guidance/latest/backup-recovery/restore.html) as an AMI.

On all deployments:

- Back up your Retool PostgreSQL database. If your PostgreSQL database is managed outside of Retool and has point-in-time recovery enabled, you already have backups. Otherwise, you can enable point-in-time recovery, or take a snapshot of the database.
- Copy and store your `ENCRYPTION_KEY` environment variable, along with any environment variables specific to your instance, in a safe place.

### 3. Install the new version

Retool releases can be pulled from [Docker Hub](https://hub.docker.com/u/tryretool). See the deployment guide for your instance for detailed instructions.

After installation, Retool recommends manually testing the deployment to verify it was successful. You can use the [release notes](../../releases/index.mdx) to better understand what has changed and what to test. Depending on how you're using Retool, you might want to verify your apps, queries, and workflows work as expected. If you're upgrading in batches, test the deployment after each batch.

### 4. Notify your users after the upgrade is complete

As a courtesy to your users, it’s helpful to send an announcement to them to let them know they can use Retool again.

---

## Self-hosted Retool concept guides

import Concept from '/docs/_partials/_doctypes/_concept.mdx';
import EnterpriseRequirement from "./_partials/_enterprise-plan-requirement.mdx";

:::tip Get started with self-hosted Retool

Follow a [tutorial](./tutorial/) to deploy self-hosted Retool on your infrastructure.

:::

---

## Configure SSL and custom certificates

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

Docker Compose deployments of Self-hosted Retool include [https-portal](https://github.com/SteveLTN/https-portal) to automatically configure HTTPS. You can either provision a certificate with [Let's Encrypt](https://letsencrypt.org/) or manually add your own custom certificate.

The process for Kubernetes and other deployment types is similar to the steps for Docker Compose, but might require different settings. For example, with Kubernetes you can use Kubernetes Secrets, and with Heroku you can extend the Dockerfile to copy the certificate into the container.

:::note

`https-portal` requires that port 80 be available and exposed to the internet, so Retool configures this automatically. If you modify this setting, `https-portal` won't function correctly.

:::

## SSL with Let's Encrypt

Provisioning a certificate with Let's Encrypt is a two-step process:

1. Configure DNS
2. Update Docker configuration file

### Configure DNS

First, set up a DNS to point `retool.yourcompany.com` to the Retool server. Next, open the `docker.env` file and update `DOMAINS` with the new domain.

```text
DOMAINS=retool.yourcompany.com -> http://api:3000
```

### Update Docker Compose

Open the `compose.yaml` file and set `STAGE` to `production`:

```yaml
...
  https-portal:
    ...
    environment:
      STAGE: 'production'
    networks:
      - frontend-network
...
```

## Add custom certificates manually

Let's Encrypt can only provision a certificate if your Retool deployment has full internet access. If you deploy Retool on a VPC without complete access to the internet, you can manually add certificates instead.

:::note

If you are using Retool Mobile or hosting your deployment behind a firewall, concatenate your primary certificate file and intermediate certificate file. This ensures that the server presents the full chain of certificates and the root issuer is validated. 

:::

### Modify HTTPS configuration

Update the `https-portal` service in the `compose.yaml` file to use an [nginx image](https://hub.docker.com/_/nginx/), and create two Docker volumes for your certificates. Rename the `https-portal` service to `nginx`.

```yaml
nginx:
  image: nginx:latest
  ports:
    - "80:80"
    - "443:443"
  command: [nginx-debug, "-g", "daemon off;"] # Improve error logging in the container
  volumes:
    - ./nginx:/etc/nginx/conf.d
    - ./certs:/etc/nginx/certs
  links:
    - api
  depends_on:
    - api
  networks:
    - frontend-network
```

### Mount certificates

To mount your certificates, create the directories these volumes point to.

In the `retool-onpremise` directory:

1. Create a `certs` directory if it doesn't exist.
2. Move your `.crt` and `.key` files into the `certs` directory.

### Set up and configure nginx

To configure [nginx](https://nginx.org):

1. Create an `nginx` directory if it doesn't currently exist.
2. Create a configuration file named `nginx.conf`.
3. Add the following to `nginx.conf` and update it with your own information.

```text
server {
    listen 80;
    server_name retool.yourcompany.dev; # container logs. These logs are helpful if you run into issues and need to troubleshoot.

```text
sudo docker compose exec https-portal bash
cd /var/log/nginx
cat error.log
```

## Connect to APIs using HTTPS

If your API's SSL certificates are signed by an internal CA, your Retool deployment cannot connect over HTTPS until you configure it to trust your CA. You do this by setting `NODE_EXTRA_CA_CERTS` to the absolute path of your certificate files. The files need to include one or more trusted certificates in PEM format.

If you use [Source Control](../../source-control/index.mdx), set the `SSL_CERT_FILE` environment variable to the path of your certificate files on your `api` and `jobs-runner` containers.

### Configure Docker Compose

With Docker Compose deployments, you need to store the certificate as a file on the filesystem, and then mount that file to the `api` container.

1. Create a subdirectory in your `retool-onpremise` repo named `ca`.
2. Save your internal certificate in PEM format to `./ca/cert.pem`.
3. Configure the following `compose.yaml` and `docker.env` files.

```yaml
version: '2'
services:
api:
image: tryretool/backend:latest
env_file: ./docker.env
...
volumes:
  - ./ssh:/retool_backend/autogen_ssh_keys
  - ./ca:/retool_backend/ca

db-connector:
...
volumes:
  - ./ca:/retool_backend/ca
```

```text
NODE_ENV=production
...
NODE_EXTRA_CA_CERTS=/retool_backend/ca/cert.pem
...
```

---

## Configure code-executor security and privileges

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

:::danger

Retool strongly recommends running the `code-executor` container in priviledged modes so that [custom code executes in a sandboxed environment](../concepts/custom-code-execution-security.mdx). You should only use unprivileged mode if it is not possible for your deployment to use privileged mode.

:::

The [code-executor](../quickstart.mdx#containers-and-services) service uses a default security configuration. It also runs in a privileged mode that runs workflow code in a sandbox environment.

## Disable default security configuration

If necessary, you can explicitly disable the default security configuration for a link-local address. This is useful if privileged access (e,g, `NET_ADMIN`) cannot be given to the code-executor service container.

First, run the following startup commands with elevated privileges:

```shell
iptables-legacy -A OUTPUT -d 169.254.0.0/16 -m owner --uid-owner retool_user -j DROP
```

```shell
iptables-legacy -A OUTPUT -d 192.168.0.0/16 -m owner --uid-owner retool_user -j DROP
```

Next, set the `DISABLE_IPTABLES_SECURITY_CONFIGURATION` environment variable to `true`.

```dockerfile
DISABLE_IPTABLES_SECURITY_CONFIGURATION=true
```

## Run code-executor in unprivileged mode

The Code executor service uses [NsJail](https://github.com/google/nsjail) to sandbox code execution. NsJail requires [privileged](https://docs.docker.com/reference/cli/docker/container/run/#privileged) container access. If your deployment framework does not support privileged access, (e.g., [ECS Fargate](https://docs.aws.amazon.com/AmazonECS/latest/bestpracticesguide/fargate-security-considerations.html), you can set the `CONTAINER_UNPRIVILEGED_MODE` environment variable to `true`. While you can run without NsJail, we recommend using privileged mode with NsJail enabled whenever possible, as it provides sandboxing and stronger security.

To run in unprivileged mode:

1. Set the `CONTAINER_UNPRIVILEGED_MODE` environment variable to `true` on your code-executor container.
2. Set the container user to run as `retool_user.` You can do this in Docker via the [USER instruction](https://docs.docker.com/reference/dockerfile/#user), or in Kubernetes via a [security context](https://kubernetes.io/docs/tasks/configure-pod-container/security-context/).
3. Ensure the container or pod does not run with `privileged` capabilities. 

The following example runs code-executor in unprivileged mode using Docker Compose:

```dockerfile
  code-executor:
    user: retool_user
    build:
     ...
    environment:
      - NODE_ENV=production
      - NODE_OPTIONS=--max_old_space_size=1024
      - CONTAINER_UNPRIVILEGED_MODE=true
      ...
    privileged: false
    ...
```

The `CONTAINER_UNPRIVILEGED_MODE` environment variable is also used to disable default configurations for link-local addresses to prevent Amazon EC2 metadata leaks. You must also run the startup commands to [disable the default security configuration](#disable-default-security-configuration).

---

## Self-hosted Retool migration guides

import DocCardList from "@theme/DocCardList";

---

## Migrate from Cloud to Self-hosted

import EnterpriseRequirement from "../../_partials/_enterprise-plan-requirement.mdx";

This guide outlines the steps required to copy your apps and configuration from your Cloud to self-hosted deployment. Before you begin, review the [pricing](https://retool.com/pricing) page to confirm your self-hosted plan supports the features you need. Self-hosted plans have a different set of supported features and may have a [different billing structure](/support/billing-usage) than Cloud plans.

## Requirements

To perform this migration, you need:

- A working Cloud instance of Retool.
- A working Self-hosted instance of Retool. To deploy a self-hosted instance of Retool, obtain a license key from your Retool account manager, or using our [Self-hosted Portal](https://my.retool.com/). Follow the [deployment guides](../../tutorials.mdx) for additional instructions.
- [Admin permissions](../../../permissions/guides/configure-permission-groups#default-permission-groups) on both instances.

:::warning

To use [Retool Workflows](../../../workflows/index.mdx) or [Retool Database](../../../data-sources/quickstarts/retool-database.mdx), ensure that you configure your self-hosted deployment to support [Retool Workflows](../../tutorials.mdx) and/or [Retool Database](../retool-database.mdx).

:::

Before migrating, [upgrade your self-hosted instance](../../concepts/update-deployment.mdx) to the most recent version of Retool. Self-hosted releases are typically two weeks behind Cloud releases, so your self-hosted instance may be missing features already on your Cloud instance. Typically this does not cause any issues, but you may need to make minor modifications to your applications on the self-hosted instance.

If your plan includes Source Control, follow the instructions for migrating [with Source Control](#with-source-control). Otherwise, follow the instructions for migrating [without Source Control](#without-source-control).

## With Source Control

If you use [Source Control in Retool](../../../source-control/index.mdx), use the following steps to synchronize apps, resources, and workflows between your Cloud and self-hosted instances. Source Control is available on the [Enterprise plan](https://retool.com/pricing). Reach out to your Retool account manager for more information.

### 1. Configure Source Control

Ensure you have configured Source Control on your Cloud instance and that you can access its repository. Next, set up [source control](../../../source-control/index.mdx) on your self-hosted instance. Use the same provider and repository as your Cloud instance.

### 2. Migrate resources

If your Cloud instance uses [config vars](../../../org-users/guides/configuration/config-vars.mdx) to set credentials, manually copy these config vars to your self-hosted instance. Next, migrate your resources:

- In your Cloud instance, [protect all resources](../../../source-control/guides/protect/resources.mdx). Make sure you merge the resources into the `main` branch.
- In your self-hosted instance, from the [source control deployment dashboard](../../../source-control/guides/manage-deployment.mdx#view-source-control-deployments), [deploy the latest version](../../../source-control/guides/manage-deployment.mdx#full-and-partial-deployments) of the repository.

Confirm that you can see your resources in the self-hosted instance.

Self-hosted instances can use [environment variables](../secrets/environment-variables.mdx#1-define-the-retool_exposed_db_password-variable) or secrets managers, such as [AWS Secrets Manager](../secrets/aws.mdx) and [HashiCorp Vault](../secrets/hashicorp-vault.mdx), to store sensitive credentials. Consider storing credentials, such as database passwords and API keys, using one of these methods.

:::note

All plans support environment variables. The Enterprise plan supports integrations with secrets managers.

:::

### 3. Retool Database

To migrate Retool Database from your Cloud instance to self-hosted, manually export and import your tables. You can [export a CSV](../../../data-sources/quickstarts/retool-database.mdx#export-data-as-a-csv) for each table on your Cloud instance, and [import each CSV](../../../data-sources/quickstarts/retool-database.mdx#import-csv) into your self-hosted database.

### 4. Copy Query Library queries

Queries in the [Query Library](../../../queries/concepts/query-library.mdx) are not synchronized with Source Control. In your self-hosted instance, manually recreate any shared queries used in your Cloud instance.

### 5. Migrate apps and workflows

To migrate apps and workflows:

- In your Cloud instance, [protect all apps and workflows](../../../source-control/index.mdx). Make sure you merge the apps and workflows into the main branch.
- In your self-hosted instance, from the [source control deployment dashboard](../../../source-control/guides/manage-deployment.mdx#view-source-control-deployments), [deploy the latest version](../../../source-control/guides/manage-deployment.mdx#full-and-partial-deployments) of the repository.

:::warning

Workflow [webhook URLs](../../../workflows/guides/triggers/webhooks.mdx) change during the migration, so make sure to update any webhooks you reference elsewhere in your stack.

:::

### 6. Test apps and workflows

On your self-hosted instance, test the most critical apps and workflows.

## Without Source Control

If you don't use Source Control, use the following steps to copy your apps and workflows to your self-hosted instance.

### 1. Recreate resources

Cloud resources may use [config vars](../../../org-users/guides/configuration/config-vars.mdx) to set credentials, such as passwords or API keys. First, manually migrate config vars to the self-hosted instance.

Next, recreate all resources in your self-hosted instance. Ensure both instances use the same resource names. If your resource names differ, imported apps and workflows will not work properly.

Self-hosted instances can use [environment variables](../secrets/environment-variables.mdx#1-define-the-retool_exposed_db_password-variable), or secrets managers such as [AWS Secrets Manager](../secrets/aws.mdx) and [HashiCorp Vault](../secrets/hashicorp-vault.mdx), to store sensitive credentials. Consider moving credentials such as database passwords, or API keys, to one of these methods.

:::note

All plans support environment variables. The Enterprise plan supports integrations with secrets managers.

:::

### 2. Retool Database

To migrate Retool Database from your Cloud instance to self-hosted, manually export and import your tables. You can [export a CSV](../../../data-sources/quickstarts/retool-database.mdx#export-data-as-a-csv) for each table on your Cloud instance, and [import each CSV](../../../data-sources/quickstarts/retool-database.mdx#import-csv) into your self-hosted database.

### 3. Copy Query Library queries

Queries in the [Query Library](../../../queries/concepts/query-library.mdx) are not synchronized with Source Control. In your self-hosted instance, manually recreate any shared queries used in your Cloud instance.

### 4. Export apps and workflows

:::warning

Workflow [webhook URLs](../../../workflows/guides/triggers/webhooks.mdx) change during the migration, so make sure to update any webhooks you reference elsewhere in your stack.

:::

Next, [export your apps and workflows](../../../apps/guides/app-management/import-export.mdx) as JSON. Exports include any queries and configuration options in use, and you create individual exports for each app and workflow. To export an app or workflow, click the **•••** button to open the contextual menu, then select **Export and download**.

Now, import these into your self-hosted organization. To import an app or workflow, click **Create new** and select **From JSON**.

### 5. Test apps and workflows

On your self-hosted instance, test the most critical apps and workflows.

## Update your organization settings

Update the self-hosted deployment to invite users and copy over settings.

### 1. Invite users

After migrating apps and workflows, add your users to the new instance using [single-sign on](../../../sso/index.mdx) (SSO) or email and password authentication. Retool strongly recommends using SSO to authenticate users. SSO is more secure than email and password authentication, and using an identity provider can simplify the process of managing your users.

:::note

All plans support SSO with Google. The Enterprise plan supports SSO using any SAML or OIDC identity provider.

:::

#### Provision users

If you authenticate using SSO, and your identity provider supports JIT user provisioning, you can automatically migrate users by configuring SSO and JIT provisioning on the new instance. When a user signs in using SSO to the new instance, Retool automatically creates a new user account.

If you use email and password authentication, manually migrate users [by sending email invitations](../../../org-users/tutorials/manage.mdx#invite-users). You can bulk invite users by inserting a comma-separated list of email addresses. When a user signs up using the link in the email invite, Retool creates a new user account.

#### Migrate permission groups

You can migrate permission groups with role mapping or by manually recreating permission groups.

If you use role mapping with an OIDC or SAML-based identity provider, you can set up role mapping on the new instance to migrate groups. After configuring role mapping, your permission groups automatically migrate to the new instance.

If you do not use role mapping, recreate your [permission groups](../../../permissions/guides/configure-permission-groups) in your new instance and add your users to them.

### 2. Copy settings

Copy [app themes](../../../apps/guides/presentation-styling/themes.mdx), [custom branding](../../../org-users/concepts/branding.mdx), and any [org-wide JavaScript](../../../queries/guides/javascript/custom.mdx) from your Cloud instance to your self-hosted instance. You must manually recreate themes in your self-hosted instance. Check the [Settings page](https://login.retool.com/auth/login?source=docs&redirectOnLogin=settings) for any other settings you need to migrate to the new instance, including **Beta** feature flags.

## 3. Usage Analytics

To set up [usage analytics](../../../org-users/guides/monitoring/usage-analytics.mdx) on your self-hosted instance, reach out to your Retool account manager to obtain an access token. After you have a token, [follow the instructions](../../../org-users/guides/monitoring/usage-analytics.mdx#enable-usage-analytics-on-self-hosted-retool) to enable usage analytics.

## Downgrade plan

When you no longer need to use your Cloud organization, downgrade it to the Free plan. Navigate to the **Billing** settings and click **Downgrade**. Ensure you have migrated all your apps, resources, workflows, and database tables before doing this.

---

## Migrate from Self-hosted to Cloud

import EnterpriseRequirement from "../../_partials/_enterprise-plan-requirement.mdx";

This guide outlines the steps required to copy your apps and configuration from your self-hosted deployment to Retool Cloud. Before you begin, review the [pricing](https://retool.com/pricing) page to confirm your Cloud plan supports the features you need. Self-hosted plans have a different set of supported features and may have a [different billing structure](/support/billing-usage) than Cloud plans.

## Requirements

To perform this migration, you need:

- A working Cloud instance of Retool. To create Cloud instance of Retool, sign up for an account on [retool.com](https://retool.com/).
- A working Self-hosted instance of Retool.
- [Admin permissions](../../../permissions/guides/configure-permission-groups#default-permission-groups) on both instances.

Before migrating, [upgrade your self-hosted instance](../../concepts/update-deployment.mdx) to the most recent version of Retool. Self-hosted releases are typically two weeks behind Cloud releases, so your self-hosted instance may be missing features already on your Cloud instance. Typically this does not cause any issues, but you may need to make minor modifications to your applications on the self-hosted instance.

If your plan includes Source Control, follow the instructions for migrating [with Source Control](#with-source-control). Otherwise, follow the instructions for migrating [without Source Control](#without-source-control).

## With Source Control

If you use [Source Control in Retool](../../../source-control/index.mdx), use the following steps to synchronize apps, resources, and workflows between your Cloud and self-hosted instances. Source Control is available on [an Enterprise plan](https://retool.com/pricing). Reach out to your Retool account manager if you are interested in an Enterprise plan.

### 1. Configure Source Control

Ensure you have configured Source Control on your self-hosted instance and that you can access its repository. Next, set up [source control](../../../source-control/index.mdx) on your Cloud instance. Use the same provider and repository as your self-hosted instance. Source Control with GitHub and GitLab are supported on Retool Cloud.

### 2. Migrate resources

If your self-hosted instance uses [config vars](../../../org-users/guides/configuration/config-vars.mdx) to set credentials, manually copy these config vars to your Cloud instance.

:::note

If you use [environment variables](../secrets/environment-variables.mdx), [AWS Secrets Manager](../secrets/aws.mdx), or [HashiCorp Vault](../secrets/hashicorp-vault.mdx) to store credentials, you need to store those credentials as secret [config vars](../../../org-users/guides/configuration/config-vars.mdx), or directly within the resource configuration page.

:::

Next, migrate your resources:

- In your self-hosted instance, [protect all resources](../../../source-control/guides/protect/resources.mdx). Make sure you merge the resources into the main branch.
- In your Cloud instance, from the [source control deployment dashboard](../../../source-control/guides/manage-deployment.mdx#view-source-control-deployments), [deploy the latest version](../../../source-control/guides/manage-deployment.mdx#full-and-partial-deployments) of the repository.

Confirm that you can see your resources in the self-hosted instance.

### 3. Retool Database

To migrate Retool Database from your self-hosted instance to Cloud, manually export and import each table into the Cloud instance. You can [export a CSV](../../../data-sources/quickstarts/retool-database.mdx#export-data-as-a-csv) for each table on your self-hosted instance, and [import each CSV](../../../data-sources/quickstarts/retool-database.mdx#import-csv) into your Cloud database.

### 4. Copy Query Library queries

Queries in the [Query Library](../../../queries/concepts/query-library.mdx) are not synchronized with Source Control. In your Cloud instance, manually recreate any shared queries used in your self-hosted instance.

### 5. Migrate apps and workflows

To migrate apps and workflows:

- In your self-hosted instance, [protect all apps and workflows](../../../source-control/index.mdx). Make sure you merge the apps and workflows into the main branch.
- In your Cloud instance, from the [source control deployment dashboard](../../../source-control/guides/manage-deployment.mdx#view-source-control-deployments), [deploy the latest version](../../../source-control/guides/manage-deployment.mdx#full-and-partial-deployments) of the repository.

:::warning

Workflow [webhook URLs](../../../workflows/guides/triggers/webhooks.mdx) change during the migration, so make sure to update any webhooks which are referenced elsewhere in your stack.

:::

### 7. Test apps and workflows

On your Cloud instance, test the most critical apps and workflows.

## Without Source Control

If you don't use Source Control or your plan does not include Source Control, use the following steps to copy your apps and workflows to your Cloud instance. Business and Team plans do not include Source Control.

### 1. Recreate resources

If your self-hosted instance uses [config vars](../../../org-users/guides/configuration/config-vars.mdx) to set credentials, manually copy these config vars to your Cloud instance.

:::note

If you are using [environment variables](../secrets/environment-variables.mdx), [AWS Secrets Manager](../secrets/aws.mdx), or [HashiCorp Vault](../secrets/hashicorp-vault.mdx) to store credentials, store those credentials as secret [config vars](../../../org-users/guides/configuration/config-vars.mdx) or directly within the resource configuration page.

:::

Next, recreate all resources in your Cloud instance. Ensure that resource names are identical between instances. If your resource names differ, imported apps and workflows will not work properly.

### 2. Retool Database

To migrate Retool Database from your self-hosted instance to Cloud, manually export and import each table into the Cloud instance. You can [export a CSV](../../../data-sources/quickstarts/retool-database.mdx#export-data-as-a-csv) for each table on your self-hosted instance, and [import each CSV](../../../data-sources/quickstarts/retool-database.mdx#import-csv).

### 3. Copy Query Library queries

Queries in the [Query Library](../../../queries/concepts/query-library.mdx) are not synchronized with Source Control. In your Cloud instance, manually recreate any shared queries used in your self-hosted instance.

### 4. Export apps and workflows

:::warning

Workflow [webhook URLs](../../../workflows/guides/triggers/webhooks.mdx) change during the migration, so make sure to update any webhooks you reference elsewhere in your stack.

:::

Next, [export your apps and workflows](../../../apps/guides/app-management/import-export.mdx) as JSON. Exports include any queries and configuration options in use.

Apps and workflows are exported individually. To export an app, click the **•••** button to open the contextual menu, then select **Export and download**.

Now, import these into your Cloud organization. To import an app or workflow, click **Create new** and select **From JSON**.

### 5. Perform application testing

On your Cloud instance, test the most critical apps and workflows.

## Update your organization

Update the Cloud deployment to invite users and copy over settings.

### 1. Invite users

After migrating apps and workflows, add your users to the new instance using [single-sign on](../../../sso/index.mdx) (SSO) or email and password authentication. Retool strongly recommends using SSO to authenticate users. SSO is more secure than email and password authentication, and using an identity provider can simplify the process of managing your users.

:::note

All plans support SSO with Google. The Enterprise plan supports SSO using any SAML or OIDC identity provider.

:::

#### Provision users

If you authenticate using SSO, and your identity provider supports JIT user provisioning, you can automatically migrate users by configuring SSO and JIT provisioning on the new instance. When a user signs in using SSO to the new instance, Retool automatically creates a new user account.

If you use email and password authentication, manually migrate users [by sending email invitations](../../../org-users/tutorials/manage.mdx#invite-users). You can bulk invite users by inserting a comma-separated list of email addresses. When a user signs up using the link in the email invite, Retool creates a new user account.

#### Migrate permission groups

You can migrate permission groups with role mapping or by manually recreating permission groups.

If you use role mapping with an OIDC or SAML-based identity provider, you can set up role mapping on the new instance to migrate groups. After configuring role mapping, your permission groups automatically migrate to the new instance.

If you do not use role mapping, recreate your [permission groups](../../../permissions/guides/configure-permission-groups) in your new instance and add your users to them.

### 2. Copy settings

Copy [app themes](../../../apps/guides/presentation-styling/themes.mdx), [custom branding](../../../org-users/concepts/branding.mdx), and any [org-wide JavaScript](../../../queries/guides/javascript/custom.mdx) from your self-hosted instance to your Cloud instance. You must manually recreate themes in your Cloud instance. Check the [Settings page](https://login.retool.com/auth/login?source=docs&redirectOnLogin=settings) for any other settings you need to migrate to the new instance, including **Beta** feature flags.

## Downgrade plan

When you no longer need to use the self-hosted organization, downgrade it to the Free plan. Navigate to [Retool Self-hosted Portal](https://my.retool.com/) and change your plan to a free plan.

---

## Multiplayer for self-hosted Retool Apps

import EnterpriseRequirement from "../_partials/_enterprise-plan-requirement.mdx";

Multiplayer enables multiple users to make changes to the same app simultaneously. Similar to collaborative editing tools like Google Docs, users can see what others are working on and changes are reflected in real-time. 

[Learn more about multiplayer](../../apps/concepts/multiplayer.mdx).

Use the instructions in this guide to set up multiplayer on a self-hosted deployment, which requires additional configuration.

:::note

Multiplayer is enabled by default on cloud instances and requires no additional configuration.

:::

## Multiplayer infrastructure

Enabling multiplayer for self-hosted Retool requires making several infrastructure changes:

1. Deploy a new _multiplayer service_. This service manages the real-time editing state.
2. Update your frontend proxy to route multiplayer traffic to the new multiplayer service.

The multiplayer service and the Retool editor frontend maintain a WebSocket connection. The multiplayer service also requires the ability to connect to the main Postgres database.

The following sections include more details about making this architecture change using Retool's [official retool-helm chart](../tutorials/kubernetes/helm.mdx) or a non-helm deployment.

## Configuring multiplayer with Kubernetes Helm

import Prereqs from '../_partials/_multiplayer_prereq.mdx';

Using the [official retool-helm chart](../tutorials/kubernetes/helm.mdx) is the easiest way to set up multiplayer.

Enable the service by setting the `multiplayer: enabled` option [in your values file](https://github.com/tryretool/retool-helm/blob/3e1759d575cfb05a18ff39ea12424658b766ddcb/values.yaml#L494).

:::note

You can also opt-in to use Retool's preconfigured [ingress rules](https://github.com/tryretool/retool-helm/blob/69a3d90cb736b1b6450684e0db74dad96d756306/charts/retool/templates/ingress.yaml#L33), or you can [configure them manually](#configure-web-proxy).

:::

## Configuring multiplayer for non-Kubernetes Helm deployments

First, provision resources to run the multiplayer service. Then, complete the steps in the following sections.

### Set environment variables

Set the following environment variables for the new container:

```