# How to Implement Search Functionality in Retool

- Tool: Retool
- Difficulty: Beginner
- Time required: 15-20 min
- Compatibility: Retool Cloud and Self-hosted
- Last updated: March 2026

## TL;DR

Implement search in Retool by adding a Text Input named searchInput and using {{ searchInput.value }} in a SQL ILIKE query. Add a Change event handler with 300ms debounce to trigger the query. For instant client-side filtering, use a transformer with Array.filter(). The Table component also has a built-in search bar you can enable with one click in the Inspector.

## Three Patterns for App-Wide Search in Retool

Search in Retool can be implemented at three levels: built-in table search (zero-code, client-side), client-side transformer filtering (fast, no extra queries), and server-side ILIKE queries (scalable, searches the full dataset). Choosing the right level depends on data volume and search scope.

Built-in Table search filters the rows already loaded in the table component. It requires no configuration and works instantly — but only searches what is currently in the component, not the full database. Client-side transformer filtering is similar but gives you more control over the filter logic and can work across multiple tables. Server-side ILIKE queries run a new database query for each search, which enables searching millions of rows but requires debouncing.

This tutorial builds all three patterns and adds multi-field search across name, email, and company columns as a practical example. It also shows combining search with status filter dropdowns using AND clauses.

## Before you start

- A Retool app with a Table component displaying data from a query
- A PostgreSQL or MySQL database resource configured
- Basic familiarity with SQL WHERE clauses and Retool event handlers

## Step-by-step guide

### 1. Enable the Table component's built-in search bar

The simplest search implementation: select the Table component, go to Inspector → General → Search, and enable the 'Show search bar' toggle. A search input appears above the table header. Users type in it and the table filters rows client-side in real time. This searches across all visible string columns automatically. No queries, no event handlers, no configuration. Limitation: only searches currently loaded rows, not the full database.

**Expected result:** A search bar appears above the table. Typing in it immediately filters the displayed rows without re-querying the database.

### 2. Add a Text Input search bar with a debounced query trigger

For server-side search across the full dataset, add a Text Input named searchInput above the table. Set Placeholder to 'Search by name, email, or company…'. In Inspector → Interaction → Event Handlers, add a Change event that triggers your main data query (e.g., getCustomers). Enable Debounce in the event handler's Advanced Options and set 300ms. This waits for the user to pause typing before sending the query, reducing database load.

**Expected result:** Typing in searchInput triggers the database query 300ms after the user pauses. The table updates with matching results.

### 3. Write a multi-field ILIKE query for server-side search

Create (or modify) the data-fetching SQL query to filter based on {{ searchInput.value }}. Use ILIKE for case-insensitive matching and the % wildcard for substring search. Use OR clauses to search across multiple fields simultaneously. Handle the empty-search case (show all records when the search box is empty) with a conditional expression.

```
-- SQL query: getCustomers
-- Run on page load: ON
-- Trigger: searchInput onChange (debounced 300ms)

SELECT
  c.id,
  c.full_name,
  c.email,
  c.company_name,
  c.status,
  c.created_at
FROM customers c
WHERE
  -- When search is empty, return all records; when not empty, filter:
  ({{ searchInput.value === '' || searchInput.value === null }}
   OR c.full_name ILIKE {{ '%' + searchInput.value + '%' }}
   OR c.email ILIKE {{ '%' + searchInput.value + '%' }}
   OR c.company_name ILIKE {{ '%' + searchInput.value + '%' }})
  -- Additional status filter from a Select component:
  AND ({{ statusFilter.value === '' || statusFilter.value === null }}
       OR c.status = {{ statusFilter.value }})
ORDER BY c.full_name ASC
LIMIT 100;
```

**Expected result:** Searching 'Acme' returns all customers with 'Acme' in their name, email, or company. Empty search returns all records.

### 4. Build a client-side filter using a transformer

For datasets under ~500 rows that are already loaded, use a transformer to filter without hitting the database. Create a standalone transformer named filteredCustomers. It reads from getCustomers.data and applies filter logic using JavaScript. The Table's data source is bound to {{ filteredCustomers.value }} instead of {{ getCustomers.data }}. This is instant — no query latency — but only searches already-loaded data.

```
// Transformer: filteredCustomers
// Reads from getCustomers.data and searchInput.value
// Important: transformers are READ-ONLY — cannot trigger queries or setValue()

const query = searchInput.value?.toLowerCase().trim() || '';
const data = getCustomers.data;

if (!query) {
  return data; // Return all records when search is empty
}

return data.filter(row => {
  const name = (row.full_name || '').toLowerCase();
  const email = (row.email || '').toLowerCase();
  const company = (row.company_name || '').toLowerCase();
  return name.includes(query) || email.includes(query) || company.includes(query);
});

// Table component data source:
// Data: {{ filteredCustomers.value }}
```

**Expected result:** The table filters instantly as the user types, without any database queries. {{ filteredCustomers.value.length }} shows the match count.

### 5. Add a status filter dropdown alongside the search input

Combine text search with a Select dropdown for category or status filtering. Add a Select named statusFilter with options from a query or static values. The SQL query already includes the status filter clause (see Step 3). Add a Change event handler on statusFilter that also triggers getCustomers. Now users can type a search term AND filter by status simultaneously — the AND clause in the SQL handles both conditions.

```
// statusFilter Select component settings:
// Options (static):
//   Label: All Statuses,  Value: ''
//   Label: Active,        Value: 'active'
//   Label: Inactive,      Value: 'inactive'
//   Label: Pending,       Value: 'pending'
// Default Value: '' (All Statuses pre-selected)

// Event handler on statusFilter:
// Event: Change → Trigger query → getCustomers

// Combined result count Text component:
{{ getCustomers.data.length }} results
{{ statusFilter.value ? ` (${statusFilter.value})` : '' }}
{{ searchInput.value ? ` matching "${searchInput.value}"` : '' }}
```

**Expected result:** Users can search by text and filter by status simultaneously. Changing either control re-runs getCustomers with both filters applied.

### 6. Add a clear search button and result count display

Add a Button component with an X icon to the right of the search input to clear the search. Create a JS Query named clearSearch that calls searchInput.setValue('') and statusFilter.setValue('') to reset both filters, then triggers getCustomers to reload all records. Display a result count Text component showing how many records match: {{ getCustomers.data.length }} results.

```
// JS Query: clearSearch
// Trigger: Clear button click

await searchInput.setValue('');
await statusFilter.setValue('');
await getCustomers.trigger();

// Result count Text component:
{{ getCustomers.isFetching
  ? 'Searching...'
  : `${getCustomers.data.length} result${getCustomers.data.length !== 1 ? 's' : ''}` }}

// Show clear button only when search is active:
// Clear button Hidden: {{ !searchInput.value && !statusFilter.value }}
```

**Expected result:** Clicking the X button clears both filters and reloads all records. Result count updates dynamically.

## Complete code example

File: `SQL Query: getCustomers (full search + filter)`

```sql
-- getCustomers — full-text search with status filter
-- Run on page load: ON
-- Triggers: searchInput onChange (300ms debounce), statusFilter onChange

SELECT
  c.id,
  c.full_name,
  c.email,
  c.company_name,
  c.phone,
  c.status,
  c.created_at,
  COUNT(*) OVER() AS total_count
FROM customers c
WHERE
  -- Text search across multiple fields (empty = show all)
  (
    {{ searchInput.value === '' || searchInput.value === null || searchInput.value === undefined }}
    OR LOWER(c.full_name) LIKE LOWER({{ '%' + (searchInput.value || '') + '%' }})
    OR LOWER(c.email) LIKE LOWER({{ '%' + (searchInput.value || '') + '%' }})
    OR LOWER(c.company_name) LIKE LOWER({{ '%' + (searchInput.value || '') + '%' }})
    OR CAST(c.id AS TEXT) = {{ searchInput.value || '' }}
  )
  -- Status filter (empty = show all)
  AND (
    {{ statusFilter.value === '' || statusFilter.value === null }}
    OR c.status = {{ statusFilter.value }}
  )
ORDER BY
  CASE WHEN LOWER(c.full_name) LIKE LOWER({{ (searchInput.value || '') + '%' }}) THEN 0 ELSE 1 END,
  c.full_name ASC
LIMIT 200;
```

## Common mistakes

- **Not handling the empty search case in the SQL query — when searchInput.value is empty, the ILIKE clause matches nothing and returns zero results** — undefined Fix: Add a conditional: ({{ searchInput.value === '' }} OR column ILIKE {{ '%' + searchInput.value + '%' }}). This returns all records when the search box is empty.
- **Trying to trigger a query from inside a transformer — transformers are read-only and cannot trigger queries** — undefined Fix: Move the filter trigger logic to an event handler on the searchInput Change event. The transformer only computes the filtered view from already-loaded data.
- **Not debouncing the search input — triggers a database query on every character, causing excessive load and a flickering UI** — undefined Fix: In the Change event handler, enable Debounce with 300ms in the Advanced Options. This batches rapid keypresses into a single query trigger.
- **Building the ILIKE pattern in the SQL query by concatenating in JavaScript (e.g., '%' + searchInput.value + '%') without sanitizing — not a SQL injection risk in Retool's parameterized queries but causes issues if searchInput contains SQL wildcard characters** — undefined Fix: Retool parameterizes query values, so SQL injection is prevented. However, if users search for literal '%' or '_' characters and you want them treated as literals, escape them: searchInput.value.replace(/%/g, '\\%').replace(/_/g, '\\_').

## Best practices

- Always debounce Change event handlers on search inputs — 300ms prevents a database query on every single keypress
- Use the built-in Table search bar for quick prototypes and simple use cases; switch to server-side ILIKE for production apps with large datasets
- Show a result count ('42 results') so users know the search worked and can gauge how specific to make their query
- Add a Clear button to reset all filters at once — users should never have to manually clear multiple filter fields
- Use ILIKE (PostgreSQL) or LOWER() + LIKE (MySQL) for case-insensitive search — never do case-sensitive substring matching for user-facing search
- Sort results so exact matches or prefix matches appear before substring matches using a CASE expression in ORDER BY
- For very large datasets (millions of rows), add a full-text search index (PostgreSQL tsvector) rather than relying on ILIKE — ILIKE performs a full table scan

## Frequently asked questions

### Can I search across data from multiple tables or queries at once?

Yes, with a SQL JOIN query that combines the tables and applies ILIKE across all relevant columns. Alternatively, run parallel filtered queries and display results in separate tables with their own result counts. For a unified 'global search' across unrelated entities, use a SQL UNION to combine results from multiple tables into one query.

### How do I implement fuzzy search (matching despite typos) in Retool?

PostgreSQL has the pg_trgm extension for trigram similarity matching, which handles typos. Enable it with CREATE EXTENSION pg_trgm and use similarity() or % operator in your WHERE clause. For lighter fuzzy matching without extensions, use client-side JavaScript in a transformer with a library like Fuse.js loaded via Retool's Preloaded JS setting.

### How do I make search results highlight the matching text?

This requires custom HTML rendering. In the Table component's column settings, set the column type to 'Custom' and write a custom component that wraps matches in a highlight span. Alternatively, use a List component instead of a Table and use a Text component with inline HTML or a custom component to render highlighted results.

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-implement-search-functionality-in-retool
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-implement-search-functionality-in-retool