How to Use Table Transformers in Retool

Click on your query in the bottom query panel to open the query editor. Look for the 'Transform' tab (or 'Transformer' toggle depending on your Retool version) next to the query code. Click it to reveal the transformer editor. You will see a default comment showing the 'data' variable is available. The transformer runs after the query executes and its return value becomes what query1.data exposes to the rest of the app.

Inside a query-attached transformer, the variable 'data' contains the raw response from the query before any transformation. For SQL queries, this is an array of row objects. For REST API queries, it is the parsed JSON response body. After your transformer runs, query1.data contains the transformer's return value. The original untransformed response is available as query1.rawData — useful for debugging if your transformer changes the shape unexpectedly.

1// Inside the query transformer:
2// 'data' = the raw query response (before transformation)
3// After transformation, query1.data = what you return here
4// query1.rawData = always the original untransformed response
5
6// Simple passthrough (no transformation):
7return data;
8
9// Verify what raw data looks like:
10console.log('Raw response:', JSON.stringify(data[0]));
11return data;

SQL queries return an array of row objects. Use array methods to reshape the data. Common operations include filtering rows, renaming fields, adding computed fields, or sorting. The transformer replaces query1.data with whatever you return. Example: rename snake_case fields to camelCase and add a computed display field.

1// SQL returns: [{ user_id, first_name, last_name, created_at, is_active }]
2// After transformer, query1.data will have camelCase + displayName
3
4return data.map(row => ({
5  id: row.user_id,
6  firstName: row.first_name,
7  lastName: row.last_name,
8  displayName: row.first_name + ' ' + row.last_name,
9  createdAt: row.created_at,
10  isActive: Boolean(row.is_active),
11  statusLabel: row.is_active ? 'Active' : 'Inactive'
12}));

REST APIs often return nested structures that need flattening before a table can display them. For example, an API might return { users: [ { id, profile: { name, email }, ... } ] }. The transformer lets you extract and flatten this structure. Use data.users or data.data (common API envelope patterns) to access the actual array.

1// REST API returns: { users: [ { id, profile: { name, email }, role, created_at } ] }
2// Flatten for table display:
3
4const users = data.users || data.data || data;
5
6return users.map(user => ({
7  id: user.id,
8  name: user.profile?.name ?? 'Unknown',
9  email: user.profile?.email ?? '',
10  role: user.role,
11  createdAt: new Date(user.created_at).toLocaleDateString('en-US'),
12  isAdmin: user.role === 'admin'
13}));

You can filter out unwanted rows and sort the result directly in the transformer. This is useful for hiding internal records, sorting by a computed field, or deduplicating results. Note that transformer-based filtering is client-side only — all rows are fetched from the database first, then filtered in JavaScript. For large datasets, prefer SQL WHERE clauses for filtering.

1// Filter inactive users and sort by last name:
2return data
3  .filter(row => row.is_active !== false)
4  .sort((a, b) => (a.last_name ?? '').localeCompare(b.last_name ?? ''))
5  .map(row => ({
6    ...row,
7    displayName: row.first_name + ' ' + row.last_name
8  }));
9
10// Deduplicate by id:
11const seen = new Set();
12return data.filter(row => {
13  if (seen.has(row.id)) return false;
14  seen.add(row.id);
15  return true;
16});

Retool includes Lodash as a built-in library accessible via the _ variable in transformers. Use lodash for grouping, sorting, deduplication, and complex data reshaping. No import statement is needed — _ is globally available.

1// Group users by department using lodash:
2const grouped = _.groupBy(data, 'department');
3
4// Return a flat list with department added as a label row:
5return Object.entries(grouped).flatMap(([dept, rows]) => [
6  { _isDeptHeader: true, department: dept, count: rows.length },
7  ...rows.map(row => ({ ...row, _isDeptHeader: false }))
8]);
9
10// Or simply pick specific fields:
11return data.map(row => _.pick(row, ['id', 'name', 'email', 'status']));

Transformers are read-only — they run synchronously after query execution and can only return a value. They CANNOT: - Call query.trigger() to run another query - Call setState() or variable.setValue() - Make async API calls or use await - Access browser APIs like fetch() or localStorage If you need to trigger a side effect (like running a second query after transformation), use a JS Query instead of a transformer. JS Queries support async/await and can trigger other queries.

1// WRONG — this will silently fail:
2query2.trigger(); // transformers cannot trigger queries
3await myVar.setValue('test'); // transformers cannot set state
4
5// CORRECT — move side effects to a JS Query:
6// In a JS Query:
7const result = await query1.trigger();
8// query1.data is now the transformed result
9await query2.trigger({ additionalScope: { inputData: result.data } });

Once your transformer is set up, the Table component automatically uses query1.data (which now contains the transformed data). Set the Table's Data source in the Inspector to {{ query1.data }}. The table columns are auto-generated from the keys of the first object in the returned array. Transformed field names (like displayName) will appear as column headers.

1// Table Inspector → General → Data source:
2{{ query1.data }}
3
4// The column headers will reflect the keys returned by your transformer,
5// not the original SQL column names.