Transformers

Manipulate data with JavaScript.

Retool supports using longer snippets of JavaScript via transformers. If you're looking to write a lot of JS, you shouldn't inline it between {{ }}. Transformers let you write larger, reusable, blocks of code. Transformers, once created, are scoped to the page. You get access to moment, _, etc., so you can write some pretty complicated code.

If you find yourself using a string of JavaScript multiple times throughout your app, it might be worth pulling that out into a transformer.

Create a JavaScript transformer

Click on the Transformers tab in the Query Editor, and click on + New 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 a string value from a table column.

📘

Note

As usual, you can use the {{ }} notation to reference variables in other parts of your app.

You can use the preview button to see the results of a transformer. Note that you have to save the transformer for the rest of the app to get the new value.

You can use {{ yourTransformerName.value }} to reference the transformer in the rest of the app.

Delete a JS transformer

Click on the transformer you want to delete from in the list of transformers. Then, click on the ... next to the Save/Saved button and click on delete.

Query transformers

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

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

After writing this query transformer, instead of having to write query1.data.allFilms.films everywhere, we can just write 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 (basically)

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 via a return statement. This means that if you're writing JS in a Transformer, you cannot:

  • Set the value of temporary state
  • Set the value of a component
  • Trigger a query

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

Example use cases

Create a comment

Use // to create comments.

// Type your comment here
return 5;

Return data

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

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.

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

Using query data

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

// 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 the formatDataAsArray() syntax.

{
  {
    formatDataAsArray(sqlQuery.data);
  }
}

Sort an API query

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

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.

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.

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.

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

_.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 your query editor, in the “Response” tab, add rows to the “Failure conditions” table to use this feature.

The keys in this table are the conditions. If any of these conditions results in a truthy value, Retool will mark the query as failing, and display the error message specified in the value.

Let’s walk through a concrete use case. Let’s say you want to throw an error if your API returns fewer results than you've configured in a text input. You can set a failure condition specifying {{ data.results.length < textinput1.value }}.

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

Note that inside the failure conditions inputs, you have access to three properties .data, .error and .metadata. You can use .data to reference the the data returned by your query (rows in a database, the response of an API, etc.). The .error property is a best effort attempt at extracting an error out of the query's response. The .metadata property contains metadata about your query's response, like 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 would show you the evaluated value based on the .error field from the last query result.

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

Error Transformers

🚧

Deprecated

Error transformers have been deprecated in favor of Failure conditions. Your existing error transformers will continue to work, but new queries will not be able to see them.

Retool lets you create custom error transformers to handle how you want your queries to throw errors. In your query editor next to the "error transformer" label, click "enable" to get started.

Error transformers work as functions with three guiding principles:

  1. If the function returns undefined, 0, null, or false, Retool interprets it as successful and does not throw an error
  2. If the function returns a string, Retool throws an error on the query and returns the string as the error message
  3. If the function returns anything other than (1) or (2), Retool throws an "Unknown error" error

To make this a bit more concrete, let's imagine we're querying a GraphQL endpoint that returns a list of continents. We want our query to throw an "Error: result set too large" error if the returned list is longer than 5 items, and succeed otherwise. Here's what we'd write in our error transformer:

return data.countries.length > 5 ? "Error: result set too large" : null;

If our result set has more than 5 items, the ternary operator returns a string – which Retool automatically interprets as an error, and passes along to the client.

If the result set has fewer than 5 items, the function returns null, which Retool interprets as a success, so no error is thrown.

Error transformers have access to two query properties: .data and .metadata. You can use .data to reference the the data returned by your query (rows in a database, the response of an API, etc.). The .metadata property contains metadata about your query's response, like response headers for a REST API. In our above GraphQL example, the .metadata property contains our response headers, status, request metadata, and a couple of other useful items.

Error transformers are particularly useful for REST APIs or GraphQL endpoints that return 200 codes even when your request failed. You can parse the response, set up custom logic for what actually constitutes an error for the client, and display the appropriate message quickly.