# How to Write SQL Queries in Retool

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

## TL;DR

In Retool, write SQL queries in the Code panel after connecting a database resource. Use {{ componentName.value }} syntax to safely pass component values as parameterized inputs — Retool handles SQL injection prevention automatically. Queries run automatically when inputs change (if set to Automatic trigger) or on demand when triggered by event handlers.

## SQL Queries in Retool: From Basics to Dynamic Filtering

SQL resource queries are the most common query type in Retool. They connect directly to a database resource (PostgreSQL, MySQL, MSSQL, Oracle, Snowflake, etc.) and run SQL that your browser sends to Retool's backend, which executes it against your database and returns the results.

Retool uses parameterized queries automatically when you write {{ componentValue }} in your SQL — the value is passed as a bound parameter, not string-interpolated. This prevents SQL injection and handles type conversion correctly.

This tutorial covers the full lifecycle of SQL queries in Retool: creating and running SELECT queries, adding dynamic filters from form components, writing mutation queries (INSERT/UPDATE/DELETE) with proper parameterization, and using JOINs for relational data.

## Before you start

- A Retool account with at least one database resource configured (Settings → Resources)
- Basic SQL knowledge (SELECT, WHERE, JOIN, INSERT, UPDATE, DELETE)
- A Retool app to write queries in
- Database credentials and access to a test database (not production for learning)

## Step-by-step guide

### 1. Create a new SQL query in the Code panel

In the Retool editor, open the Code panel from the left sidebar. Click + to add a new query. In the Resource dropdown, select your database resource (PostgreSQL, MySQL, etc.). Give the query a descriptive name like getOrders or searchCustomers. The query editor opens with a SQL text area. By default, the trigger is set to Automatic, which runs the query on page load and whenever its {{ }} inputs change.

**Expected result:** A new SQL query is created and connected to your database resource. The editor shows a SQL text area.

### 2. Write a basic SELECT query with {{ }} parameter binding

Write your SQL in the query editor. Use {{ componentName.property }} to reference component values safely. Retool automatically parameterizes these values — they are passed as bound parameters to the database driver, preventing SQL injection. You can reference any component value: textInput1.value, table1.selectedRow.data.id, select1.value, datePicker1.value, state1.value, etc.

```
-- Basic SELECT with a single filter
SELECT id, name, email, status, created_at
FROM customers
WHERE status = {{ statusFilter.value || 'active' }}
ORDER BY name ASC
LIMIT 100;

-- With multiple filters:
SELECT o.id, o.total_amount, o.status, c.name as customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = {{ orderStatusSelect.value }}
AND o.created_at >= {{ startDate.value }}
AND o.created_at <= {{ endDate.value }}
ORDER BY o.created_at DESC;
```

**Expected result:** The query runs and returns data matching the component filter values. Changing the filter component value re-runs the query automatically.

### 3. Build optional dynamic WHERE clauses

A common pattern is optional filters — the query returns all rows when a filter is empty, and filtered rows when a value is selected. Use conditional expressions in the WHERE clause to make filters optional. The pattern 1=1 allows adding AND clauses without special-casing the first condition.

```
-- Optional filters pattern
-- Returns all orders if filters are empty
-- Applies filters only when they have values
SELECT o.id, o.total_amount, o.status, c.name as customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE 1=1
  AND ({{ statusSelect.value === '' }} OR o.status = {{ statusSelect.value }})
  AND ({{ searchInput.value === '' }} OR c.name ILIKE {{ '%' + searchInput.value + '%' }})
  AND ({{ !startDate.value }} OR o.created_at >= {{ startDate.value }})
  AND ({{ !endDate.value }} OR o.created_at <= {{ endDate.value }})
ORDER BY o.created_at DESC
LIMIT {{ table1.pageSize || 50 }}
OFFSET {{ table1.paginationOffset || 0 }};
```

**Expected result:** When filters are empty, all rows return. When filters have values, results are narrowed. All filter combinations work correctly.

### 4. Write INSERT queries for form submissions

Write INSERT queries triggered by form submission events. Reference form field component values using {{ }} parameterized bindings. Set the query trigger to Manual (it should only run when the form is submitted, not automatically on page load). Trigger it from the form's Submit button event handler.

```
-- INSERT query: createCustomer
-- Trigger: Manual (run from Submit button event handler)
INSERT INTO customers (name, email, phone, status, created_at)
VALUES (
  {{ nameInput.value }},
  {{ emailInput.value }},
  {{ phoneInput.value || null }},
  {{ statusSelect.value || 'active' }},
  NOW()
)
RETURNING id, name, email, created_at;

-- UPSERT pattern (create or update):
INSERT INTO customers (id, name, email, status, updated_at)
VALUES (
  {{ editingId.value || gen_random_uuid() }},
  {{ nameInput.value }},
  {{ emailInput.value }},
  {{ statusSelect.value }},
  NOW()
)
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name,
      email = EXCLUDED.email,
      status = EXCLUDED.status,
      updated_at = NOW();
```

**Expected result:** Clicking Submit triggers the INSERT query, creates a new record, and returns the new row's data.

### 5. Write UPDATE and DELETE queries with row selection

UPDATE and DELETE queries typically operate on the row selected in a Table component. Reference table1.selectedRow.data.id (or any column) in the WHERE clause. Set these queries to Manual trigger. Always include a specific WHERE clause — a DELETE without WHERE deletes all rows.

```
-- UPDATE query: updateCustomerStatus
-- Trigger: Manual
UPDATE customers
SET 
  status = {{ statusSelect.value }},
  updated_at = NOW()
WHERE id = {{ table1.selectedRow.data.id }}
RETURNING id, name, status, updated_at;

-- DELETE query: deleteCustomer
-- Trigger: Manual
-- CRITICAL: Always include WHERE with a specific ID
DELETE FROM customers
WHERE id = {{ table1.selectedRow.data.id }}
RETURNING id;

-- Soft delete (preferred over hard delete):
UPDATE customers
SET deleted_at = NOW(), status = 'deleted'
WHERE id = {{ table1.selectedRow.data.id }};
```

**Expected result:** The UPDATE or DELETE query modifies only the selected row. After triggering, the table refreshes and reflects the change.

### 6. Use JOINs and aggregates for relational data

Retool SQL queries support the full SQL feature set of your database. Write multi-table JOINs, aggregate functions (COUNT, SUM, AVG), subqueries, CTEs, and window functions. The result set is returned as an array of objects where each key is a column name, available as query1.data.

```
-- Complex JOIN with aggregates for a dashboard KPI
SELECT 
  c.id,
  c.name,
  c.email,
  c.status,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total_amount), 0) AS total_spent,
  MAX(o.created_at) AS last_order_date,
  ROUND(COALESCE(AVG(o.total_amount), 0), 2) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
  AND o.status != 'cancelled'
WHERE c.status = 'active'
GROUP BY c.id, c.name, c.email, c.status
HAVING COUNT(o.id) >= {{ minOrdersInput.value || 0 }}
ORDER BY total_spent DESC
LIMIT 100;
```

**Expected result:** The query returns a combined result with customer data and their order statistics in a single result set.

## Complete code example

File: `SQL Query: fullCRUDQuerySet`

```javascript
-- === Query 1: getCustomers (SELECT with optional filters) ===
SELECT 
  c.id, c.name, c.email, c.status, c.created_at,
  COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE 1=1
  AND ({{ searchInput.value === '' || !searchInput.value }}
    OR c.name ILIKE {{ '%' + searchInput.value + '%' }}
    OR c.email ILIKE {{ '%' + searchInput.value + '%' }})
  AND ({{ statusFilter.value === '' || !statusFilter.value }}
    OR c.status = {{ statusFilter.value }})
GROUP BY c.id
ORDER BY c.created_at DESC
LIMIT {{ table1.pageSize || 50 }}
OFFSET {{ table1.paginationOffset || 0 }};

-- === Query 2: createCustomer (INSERT) ===
INSERT INTO customers (name, email, phone, company, status, created_at)
VALUES (
  {{ nameInput.value }},
  {{ emailInput.value }},
  {{ phoneInput.value || null }},
  {{ companyInput.value || null }},
  'active',
  NOW()
)
RETURNING *;

-- === Query 3: updateCustomer (UPDATE) ===
UPDATE customers
SET
  name = {{ nameInput.value }},
  email = {{ emailInput.value }},
  phone = {{ phoneInput.value || null }},
  company = {{ companyInput.value || null }},
  updated_at = NOW()
WHERE id = {{ table1.selectedRow.data.id }}
RETURNING *;

-- === Query 4: deleteCustomer (soft DELETE) ===
UPDATE customers
SET 
  status = 'deleted',
  deleted_at = NOW()
WHERE id = {{ table1.selectedRow.data.id }}
AND status != 'deleted'  -- prevent double-delete
RETURNING id, name;
```

## Common mistakes

- **Writing {{ 'string' + variable }} string concatenation in SQL instead of parameterized bindings** — undefined Fix: Use {{ '%' + searchInput.value + '%' }} for the ILIKE value — Retool sends this as a parameterized value, not raw string interpolation. This is safe from SQL injection.
- **Setting mutation queries (INSERT/UPDATE/DELETE) to Automatic trigger** — undefined Fix: Change mutation query triggers to Manual. Automatic trigger runs queries whenever inputs change — an INSERT set to Automatic will fire every time a form field changes, creating duplicate records.
- **Writing a DELETE query without a WHERE clause in testing** — undefined Fix: Never run DELETE without WHERE in a production database. Always test with a RETURNING clause to preview what would be deleted before removing the WHERE.
- **Referencing table1.selectedRow.data.id in a query that runs automatically — when no row is selected, the value is undefined** — undefined Fix: Either set the query to Manual trigger, or add a guard: AND ({{ !table1.selectedRow.data }} OR id = {{ table1.selectedRow.data?.id }}) to handle the null case.

## Best practices

- Always use {{ }} parameterized bindings for user-supplied values — never string-concatenate values into SQL to prevent SQL injection.
- Provide default values for optional filter bindings: {{ statusFilter.value || 'active' }} prevents null parameters from breaking queries.
- Set mutation queries (INSERT, UPDATE, DELETE) to Manual trigger — they should only run when explicitly triggered, not automatically on page load.
- Use RETURNING (PostgreSQL) or similar to get back the modified row's data, useful for refreshing UI state after mutations.
- Add a success event handler on mutation queries to automatically refresh the SELECT query that populates the table.
- Use soft deletes (deleted_at timestamp) instead of hard DELETE for any business-critical data to enable recovery.
- Test SQL queries with LIMIT 5 first when working with large tables to verify the query structure before running without a limit.

## Frequently asked questions

### Does Retool automatically prevent SQL injection when using {{ }} bindings?

Yes. When you use {{ componentName.value }} in a Retool SQL query, the value is passed as a parameterized bound variable to the database driver — it is never string-interpolated into the SQL text. This means SQL injection is not possible through {{ }} bindings. Never manually build SQL strings by concatenating user input.

### Can I use stored procedures or functions in Retool SQL queries?

Yes. Retool passes your SQL directly to the database, so any SQL supported by your database works — including CALL storedProcedure({{ param }}) for PostgreSQL/MySQL, EXEC for MSSQL, and any database-specific function calls. Retool does not restrict or modify your SQL syntax.

### Why does my Retool SQL query run automatically when I change a form field, even though I did not click any button?

Queries set to 'Automatic' trigger re-run whenever any of their {{ }} input bindings change. If your INSERT or UPDATE query references a form field and is set to Automatic, it fires on every keystroke. Change the trigger to 'Manual' for mutation queries and run them explicitly from button event handlers.

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-write-sql-queries-in-retool
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-write-sql-queries-in-retool