How to Write SQL Queries in Retool

1-- Basic SELECT with a single filter
2SELECT id, name, email, status, created_at
3FROM customers
4WHERE status = {{ statusFilter.value || 'active' }}
5ORDER BY name ASC
6LIMIT 100;
7
8-- With multiple filters:
9SELECT o.id, o.total_amount, o.status, c.name as customer_name
10FROM orders o
11JOIN customers c ON c.id = o.customer_id
12WHERE o.status = {{ orderStatusSelect.value }}
13AND o.created_at >= {{ startDate.value }}
14AND o.created_at <= {{ endDate.value }}
15ORDER BY o.created_at DESC;

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

1-- INSERT query: createCustomer
2-- Trigger: Manual (run from Submit button event handler)
3INSERT INTO customers (name, email, phone, status, created_at)
4VALUES (
5  {{ nameInput.value }},
6  {{ emailInput.value }},
7  {{ phoneInput.value || null }},
8  {{ statusSelect.value || 'active' }},
9  NOW()
10)
11RETURNING id, name, email, created_at;
12
13-- UPSERT pattern (create or update):
14INSERT INTO customers (id, name, email, status, updated_at)
15VALUES (
16  {{ editingId.value || gen_random_uuid() }},
17  {{ nameInput.value }},
18  {{ emailInput.value }},
19  {{ statusSelect.value }},
20  NOW()
21)
22ON CONFLICT (id) DO UPDATE
23  SET name = EXCLUDED.name,
24      email = EXCLUDED.email,
25      status = EXCLUDED.status,
26      updated_at = NOW();

1-- UPDATE query: updateCustomerStatus
2-- Trigger: Manual
3UPDATE customers
4SET 
5  status = {{ statusSelect.value }},
6  updated_at = NOW()
7WHERE id = {{ table1.selectedRow.data.id }}
8RETURNING id, name, status, updated_at;
9
10-- DELETE query: deleteCustomer
11-- Trigger: Manual
12-- CRITICAL: Always include WHERE with a specific ID
13DELETE FROM customers
14WHERE id = {{ table1.selectedRow.data.id }}
15RETURNING id;
16
17-- Soft delete (preferred over hard delete):
18UPDATE customers
19SET deleted_at = NOW(), status = 'deleted'
20WHERE id = {{ table1.selectedRow.data.id }};

1-- Complex JOIN with aggregates for a dashboard KPI
2SELECT 
3  c.id,
4  c.name,
5  c.email,
6  c.status,
7  COUNT(o.id) AS order_count,
8  COALESCE(SUM(o.total_amount), 0) AS total_spent,
9  MAX(o.created_at) AS last_order_date,
10  ROUND(COALESCE(AVG(o.total_amount), 0), 2) AS avg_order_value
11FROM customers c
12LEFT JOIN orders o ON o.customer_id = c.id
13  AND o.status != 'cancelled'
14WHERE c.status = 'active'
15GROUP BY c.id, c.name, c.email, c.status
16HAVING COUNT(o.id) >= {{ minOrdersInput.value || 0 }}
17ORDER BY total_spent DESC
18LIMIT 100;