How to Integrate Retool with PostgreSQL

Navigate to the Resources tab in Retool and click Add Resource. Select PostgreSQL from the database resources section. Give the resource a clear name — PostgreSQL Production or your application name followed by DB (e.g., Acme App DB) works well. In the configuration fields, enter your database host (IP address or hostname), port (5432 by default for PostgreSQL), database name (the specific database within your PostgreSQL server — not the server itself), and the username and password for the database user. For local development databases, the host is typically localhost but this only works for self-hosted Retool instances; for Retool Cloud, you need a publicly accessible host. If your database uses SSL — which is strongly recommended for any internet-accessible database — select the appropriate SSL mode from the dropdown. Retool supports require, verify-ca, and verify-full SSL modes. If you have a custom SSL certificate (for self-signed certs), paste the certificate contents into the SSL CA field. For databases behind a firewall or in a private VPC, Retool supports SSH tunneling: enable the SSH tunnel toggle and enter your bastion host details (hostname, port, username, and private key). Click Save. Retool tests the connection with a simple SELECT 1 query and shows a green Connected status if successful.

Create a new query in the Code panel, select your PostgreSQL resource, and confirm the query mode is set to SQL (not GUI mode — that is for write operations). The query editor is a standard SQL editor with syntax highlighting and autocomplete. Write a basic SELECT query to fetch records from a table. Use Retool's {{ }} syntax to parameterize values that should come from UI components — Retool automatically converts these to prepared statement parameters, preventing SQL injection. For example, to filter by a search input, write WHERE email ILIKE {{ '%' + searchInput.value + '%' }}. The {{ }} expression is evaluated as JavaScript, so string concatenation, conditional expressions, and component references all work. If a component has no value (empty text input), the parameterized query receives an empty string — you may need to conditionally include filter clauses. A common pattern is to use a WHERE clause that matches all records when the input is empty: WHERE ({{ searchInput.value === '' }} OR email ILIKE {{ '%' + searchInput.value + '%' }}). For pagination, reference a Pagination component or Number input: LIMIT {{ pagination.pageSize }} OFFSET {{ (pagination.page - 1) * pagination.pageSize }}. Enable Run query on page load in the query settings so it fetches data automatically.

1-- Example parameterized SELECT with search and pagination
2-- All {{ }} values become prepared statement parameters (SQL injection safe)
3SELECT
4  u.id,
5  u.email,
6  u.full_name,
7  u.plan_tier,
8  u.created_at,
9  u.last_login_at,
10  COUNT(o.id) AS order_count,
11  COALESCE(SUM(o.amount_cents), 0) / 100.0 AS total_spent
12FROM users u
13LEFT JOIN orders o ON u.id = o.user_id
14WHERE
15  u.deleted_at IS NULL
16  AND (
17    {{ searchInput.value === '' }}
18    OR u.email ILIKE {{ '%' + searchInput.value + '%' }}
19    OR u.full_name ILIKE {{ '%' + searchInput.value + '%' }}
20  )
21  AND (
22    {{ planFilter.value === 'all' || !planFilter.value }}
23    OR u.plan_tier = {{ planFilter.value }}
24  )
25GROUP BY u.id, u.email, u.full_name, u.plan_tier, u.created_at, u.last_login_at
26ORDER BY u.created_at DESC
27LIMIT {{ pagination.pageSize || 50 }}
28OFFSET {{ ((pagination.page || 1) - 1) * (pagination.pageSize || 50) }};

Drag a Table component from the Component panel onto the canvas. In the Table's data source field, set it to {{ getUsers.data }} (using the name of your SELECT query). The Table renders with one column per field returned by the query. Configure columns using the Columns section in the Table's right panel: set column labels, enable Editable on fields users should be able to modify (like plan_tier or full_name), set number formatting on monetary columns, and configure date formatting on timestamp columns. The id and created_at columns are typically display-only (not editable). Enable Row selection so users can click a row to see its data in other components. Add a Save Changes button below the table. Create a new query using GUI mode for the update operation: select your PostgreSQL resource, switch to GUI mode (if not already), set Table to users, Action to Update rows, and configure the filter to WHERE id = {{ table1.changesetArray[0].id }}. However, for bulk updates across multiple changed rows, use a JavaScript query that iterates over table1.changesetArray — the array of all rows the user has edited — and triggers the update query for each row. Wire the Save Changes button's onClick event to this JavaScript query. Add an On success event handler to refresh the main getUsers query and display a success notification.

1-- GUI mode generates this SQL (shown for reference) — use GUI mode UI, not raw SQL for updates
2-- For raw SQL update with the selected row:
3UPDATE users
4SET
5  full_name = {{ table1.selectedRow.full_name }},
6  plan_tier = {{ table1.selectedRow.plan_tier }}
7WHERE id = {{ table1.selectedRow.id }};

For inserting new records, drag a Form component onto the canvas or create a Modal that contains input components. Add TextInput components for each field (email, full_name, plan_tier). Create a new query using GUI mode: select your PostgreSQL resource, switch to GUI mode, set Table to users, Action to Insert row, and map each column to the corresponding input component value: email → {{ emailInput.value }}, full_name → {{ nameInput.value }}, plan_tier → {{ planSelect.value }}. Wire this query to a Save button inside the Form. On success, trigger the main getUsers query to refresh the list and close the modal. For DELETE operations, create a query using GUI mode with Action set to Delete rows and configure the filter as WHERE id = {{ table1.selectedRow.id }}. Add a Confirm action to the Delete button's event handler — configure a confirmation message like Delete {{ table1.selectedRow.email }}? This action cannot be undone before the delete query runs. Never write a DELETE without a WHERE clause in Retool; use Retool's admin settings to restrict destructive SQL if needed. Enable Row selection required in the Delete button's disabled property by checking {{ !table1.selectedRow.id }} — this greys out the button when no row is selected.

1-- INSERT example with form input references
2INSERT INTO users (email, full_name, plan_tier, created_at)
3VALUES (
4  {{ emailInput.value }},
5  {{ nameInput.value }},
6  {{ planSelect.value || 'free' }},
7  NOW()
8)
9RETURNING id, email, full_name, plan_tier, created_at;

Retool's query editor supports the full PostgreSQL SQL dialect, including advanced features that are unavailable in MySQL or simpler databases. Common Table Expressions (CTEs) let you break complex analytical queries into readable named steps. Write a WITH clause before the main SELECT to define intermediate result sets. Window functions (ROW_NUMBER(), RANK(), LAG(), LEAD(), running totals) are written directly in the SELECT list and work without any special Retool configuration. JSONB columns are queried using PostgreSQL's JSONB operators: ->> for text extraction, -> for JSON extraction, @> for containment queries, and jsonb_build_object() for constructing JSON. For full-text search, use to_tsvector() and to_tsquery() or the pg_trgm extension with ILIKE for similarity matching. These features are particularly powerful when building analytics dashboards, since Retool can pass complex SQL results directly to Chart components without any client-side transformation. The Chart component reads column names from the query result automatically — name your SELECT aliases clearly (monthly_revenue, user_count, cohort_month) so Chart's axis configuration is intuitive.

1-- CTE with window function example: monthly revenue with running total
2WITH monthly_revenue AS (
3  SELECT
4    DATE_TRUNC('month', created_at) AS month,
5    COUNT(*) AS new_orders,
6    SUM(amount_cents) / 100.0 AS revenue
7  FROM orders
8  WHERE
9    created_at >= {{ dateRangePicker.value[0] }}
10    AND created_at < {{ dateRangePicker.value[1] }}
11    AND status = 'completed'
12  GROUP BY DATE_TRUNC('month', created_at)
13)
14SELECT
15  month,
16  new_orders,
17  revenue,
18  SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total,
19  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
20  ROUND(
21    (revenue - LAG(revenue) OVER (ORDER BY month)) /
22    NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
23    1
24  ) AS month_over_month_pct
25FROM monthly_revenue
26ORDER BY month;
27
28-- JSONB query example: filter users by metadata field
29SELECT id, email, metadata->>'company' AS company
30FROM users
31WHERE metadata @> {{ JSON.stringify({plan: planFilter.value}) }}::jsonb
32  AND metadata->>'country' = {{ countryFilter.value }};