Connect to PostgreSQL

Learn how to connect to PostgreSQL and interact with data in Retool.

Create a PostgreSQL resource to connect Retool to PostgreSQL databases. PostgreSQL is an open-source relational database management system known for its reliability, robustness, and extensive feature set. PostgreSQL resources enable you to securely connect to your databases and build apps and automations to query data with SQL, perform create, read, update, and delete operations, execute transactions, call functions and stored procedures, and work with PostgreSQL-specific features like JSONB, arrays, and full-text search.

What you can do with PostgreSQL

• Query with SQL: Write SQL queries with joins, aggregations, subqueries, common table expressions (CTEs), window functions, and PostgreSQL-specific functions using SQL mode for reading data.
• Manage data: Insert, update, and delete records using GUI mode for write operations.
• Execute transactions: Perform multi-statement transactions with automatic rollback on errors.
• Call functions: Execute PostgreSQL functions and stored procedures with parameters and return values.
• Work with advanced types: Query and manipulate JSONB columns, arrays, hstore, and other PostgreSQL data types.

Before you begin

To connect PostgreSQL to Retool, you need the following:

Cloud-hosted organizations

• PostgreSQL database: An accessible PostgreSQL instance (version 9.6 or later recommended).
• Network access: Your PostgreSQL server must accept connections from Retool's IP addresses.
• Database credentials: Username and password with appropriate permissions, or AWS IAM authentication for RDS, or GCP service account for Cloud SQL.
• Retool permissions: Ability to create and manage resources in your organization.

Self-hosted organizations

• PostgreSQL database: An accessible PostgreSQL instance (version 9.6 or later recommended).
• Network access: Your Retool instance must be able to reach your PostgreSQL server.
• Database credentials: Username and password with appropriate permissions, or AWS IAM authentication for RDS, or GCP service account for Cloud SQL.
• SSH tunnel (optional): If your database is not publicly accessible, configure an SSH tunnel.
• Retool permissions: Edit all permissions for resources in your organization.

Create a PostgreSQL resource

Follow these steps to create a PostgreSQL resource in your Retool organization.

1. Create a new resource

In your Retool organization, navigate to Resources in the main navigation and click Create new → Resource. Search for PostgreSQL and click the PostgreSQL tile to begin configuration.

Searching for PostgreSQL in the resource creation page.

Use folders to organize your resources by team, environment, or data source type. This helps keep your resource list manageable as your organization grows.

2. Configure connection settings

Configure the following connection settings for your PostgreSQL resource.

PostgreSQL connection settings form.

Resource name and description

Specify a name for the resource that indicates which database it connects to. Include a description that can provide more context to users and Assist about how to use the resource.

Example Name	Example Description
User database (readonly)	A read-only PostgreSQL database that contains information about all registered users.
Orders database	A read/write PostgreSQL database with customer order data.

Host

The hostname or IP address of your PostgreSQL server.

Examples

mydb.abc123.us-east-1.rds.amazonaws.com
postgres-instance.c.my-project.internal
postgres.example.com
192.168.1.100

Port

The port number for your PostgreSQL server. Default is 5432.

Database name

The name of the database to connect to on the PostgreSQL server. Leave blank to connect to the default database.

Connection options

Additional connection parameters for PostgreSQL. Add custom connection options as query parameters.

Examples

// Set search path
((search_path = public), app_schema);

// Set application name
application_name = retool;

// Connection timeout
connect_timeout = 10;

// Statement timeout
statement_timeout = 30000;

SSH tunnel

If your PostgreSQL server is not publicly accessible, configure an SSH tunnel to connect through a bastion host. Provide SSH host, port, username, and authentication method (password or private key).

Outbound region

Cloud-hosted organizations

Cloud organizations can optionally configure an Outbound region to control which geographic region Retool uses to connect to your database. This is useful for reducing latency or meeting data residency requirements.

Self-hosted organizations

Self-hosted organizations do not have the outbound region field. Connections originate from your Retool instance's network.

3. Configure authentication

PostgreSQL supports multiple authentication methods based on your deployment type and security requirements.

Authentication method	Use cases
Username and password	Standard authentication for most PostgreSQL deployments. Simple to set up and widely supported. Use configuration variables or to store credentials securely.
AWS IAM authentication	Recommended for Amazon RDS or Aurora PostgreSQL instances. Eliminates the need to manage database passwords. Provides fine-grained access control through IAM policies. Automatically rotates credentials.
GCP service account	Recommended for Google Cloud SQL for PostgreSQL. Uses service account JSON key for authentication. Eliminates password management and integrates with IAM permissions.
Connection string	Convenient when you have a pre-formatted connection string with all parameters. Useful for quick testing or when migrating from other tools. Can include authentication and connection settings in a single field.

Select an authentication method from the Authentication dropdown and provide the required credentials. For sensitive values like passwords and access keys, use configuration variables or rather than hardcoding them.

PostgreSQL authentication settings with username and password.

4. Configure SSL/TLS

Configuring SSL/TLS is optional but highly recommended. Enable SSL/TLS for encrypted connections to your PostgreSQL server.

Field	Description
SSL/TLS	Enable SSL/TLS encryption for the connection.
SSL host	Override the hostname used for SSL certificate verification. Useful when connecting through proxies or load balancers.
Reject unauthorized	Reject connections if the server's SSL certificate cannot be verified. Enabled by default for security.
CA certificate	Provide your certificate authority (CA) certificate for SSL verification.
Client certificate and key	For mutual TLS authentication, provide client certificate and private key.
GCP self-signed cert	For Google Cloud SQL, enable this option to connect with self-signed certificate support.

For cloud databases like AWS RDS or GCP Cloud SQL, SSL certificates may be required. Download the appropriate CA certificate from your cloud provider's documentation.

5. Test the connection

Click Test connection to verify Retool can connect to your PostgreSQL database. If the test succeeds, you see a success message. If it fails, check the following:

• Network access: Ensure your PostgreSQL server accepts connections from Retool's IP addresses or your self-hosted instance.
• Credentials: Verify username and password are correct.
• Firewall rules: Check that firewall rules allow traffic on the PostgreSQL port (default 5432).
• pg_hba.conf: Ensure the PostgreSQL host-based authentication file allows connections from Retool's IP addresses.
• SSL configuration: If SSL is required, verify SSL settings match your server's requirements.

After testing the connection, click View in console to open the Debug Tools console. The console displays detailed information about the test, including the test query executed and its response, execution time, and error details if the test fails. This information is helpful for troubleshooting connection issues.

6. Save the resource

Click Create resource to save your PostgreSQL resource. You can now use it in queries across your Retool apps and automations.

Query PostgreSQL data

Once you've created a PostgreSQL resource, you can query PostgreSQL data in your Retool apps and automations.

Create a query

You can create a PostgreSQL query in a Retool app using Assist to generate queries with natural language, or manually using code.

Assist

Use Assist to generate queries from natural language prompts. Assist can create queries to retrieve, filter, and manipulate data from your PostgreSQL resource.

To create a query with Assist:

1. In the Retool app IDE, click the Assist button at the bottom of the left toolbar to open the Assist panel (if not already visible).
2. Write a prompt describing the data you want to retrieve, referencing your resource using @.
3. Press Enter to submit the prompt.
4. Select your PostgreSQL resource when prompted.
5. Review the generated query and click Run query to add it to your app.

Example prompt

retrieve 25 rows from users table using @PostgreSQL

Assist generating a PostgreSQL query from a natural language prompt.

Code

To manually create a PostgreSQL query in a Retool app:

1. In the Retool app IDE, open the Code tab, then click + in the page or global scope.
2. Select Resource query.
3. Choose your PostgreSQL resource.
4. Write your SQL query to read data, or use GUI mode for write operations (insert, update, delete).

Action types

PostgreSQL queries support two modes: SQL mode for reading data with custom SQL queries and GUI mode for write operations. In GUI mode, you can select from multiple action types for different write operations.

Action type	Description	Use case
Insert a record	Add a single new row to a table.	Create records from form submissions.
Update an existing record	Modify an existing row identified by primary key or filter.	Edit individual records with inline editing.
Update a record, or create a new record if it doesn't exist	Update an existing record or create a new one if the record doesn't exist.	Upsert operations based on filter criteria.
Delete a record	Remove rows based on filter criteria.	Delete records with confirmation.
Bulk insert records	Add multiple new rows to a table in a single operation.	Bulk create records from imported data.
Bulk update via a primary key	Update multiple records identified by primary key.	Batch update operations.
Bulk upsert via a primary key	Insert or update multiple rows based on a primary key.	Import data that may contain new or existing records.

Query configuration fields

Configuration fields vary based on the mode and action type you select. Field values can be static (e.g., user_123) or dynamic by using an embedded expression (e.g., {{ userSelect1.value }}).

SQL mode

For reading data with custom SQL queries.

SQL query

The SQL statement to execute.

simple select

Basic SELECT query

SELECT * FROM users
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100

joins and aggregations

JOIN with aggregations

SELECT
  u.id,
  u.name,
  u.email,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC

common table expression (CTE)

CTE for complex queries

WITH recent_orders AS (
  SELECT user_id, SUM(total) as total_spent
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, u.email, ro.total_spent
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
WHERE ro.total_spent > 100

window functions

Ranking and running totals

SELECT
  user_id,
  order_date,
  total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) as order_number,
  SUM(total) OVER (PARTITION BY user_id ORDER BY order_date) as running_total
FROM orders

parameterized query

Query with embedded expressions

SELECT * FROM products
WHERE category = {{ dropdown1.value }}
  AND price BETWEEN {{ minPrice.value }} AND {{ maxPrice.value }}
  AND name ILIKE {{ '%' + searchInput.value + '%' }}
ORDER BY price ASC

GUI mode

For write operations using the graphical interface.

Action type

The GUI mode operation to perform.

Select from action types listed in the Action types table above. Each action type displays relevant configuration fields for that operation.

Table name (all action types)

The database table to query.

Examples

// Static table name
users;

// Dynamic table name
{{dropdown1.value}}

Records (Insert multiple records, Bulk upsert by key)

The data to insert or upsert.

Insert from array

{{table1.data}}

Transform file upload

{{fileInput1.parsedValue.map((row) => ({
      name: row.Name,
      email: row.Email,
      status: "active",
    }))}}

Filter by (Update an existing record, Update/create if not exists, Delete a record)

The WHERE clause conditions for filtering rows.

Simple filter

{{ { status: 'active', role: 'admin' } }}

Dynamic filter

{{{status: select1.value,
    created_at: { $gte: datePicker1.value }
  }
}}

Primary key column (Update a record, Delete a record, Bulk upsert by key)

The column used as the primary key for identifying records.

Example

// Primary key column name
id

// Composite key (for bulk upsert)
["user_id", "product_id"]

Changeset (Update a record, Update multiple records)

The columns to update with new values.

Static changeset

{{ { status: 'inactive', updated_at: new Date() } }}

Changeset from form

{{form1.data}}

Partial update

{
  {
    {
      email: textInput1.value,
    phone: textInput2.value,
    updated_at: new Date()
    }
  }
}

Data types and formatting

PostgreSQL queries return and accept specific data types. Understanding these formats helps you work with PostgreSQL data effectively in Retool.

Request data formats

When sending data to PostgreSQL, use these formats for different value types:

Value type	Description	Example
Strings	Use single quotes in SQL or embedded expressions.	'text value' or {{ textInput.value }}
Numbers	Provide numeric values directly. No quotes needed.	123 or 45.67
Booleans	Use TRUE or FALSE in SQL.	TRUE or {{ checkbox.value }}
Dates	Use ISO 8601 format strings or PostgreSQL date functions.	'2025-01-21' or NOW()
Timestamps	Use ISO 8601 format with timezone or PostgreSQL timestamp functions.	'2025-01-21T10:30:00Z' or CURRENT_TIMESTAMP
NULL values	Use NULL keyword in SQL or null in embedded expressions.	NULL or {{ textInput.value }}
JSON/JSONB	Use JSON strings or JavaScript objects.	'{"key": "value"}' or {{ JSON.stringify(object1.data) }}
Arrays	Use PostgreSQL array syntax or JavaScript arrays.	ARRAY[1,2,3] or {{ [1, 2, 3] }}

Response data formats

PostgreSQL queries return data in these formats:

Data type	JavaScript type	Notes
Integer types	number	Returns JavaScript numbers.
Numeric/Decimal	string	Returns as strings to preserve precision. Parse with parseFloat() if needed.
Text/VARCHAR	string	Returns as JavaScript strings.
Boolean	boolean	Returns true or false.
Date	string	Returns ISO 8601 date string (e.g., 2025-01-21).
Timestamp	string	Returns ISO 8601 timestamp string with timezone.
JSON/JSONB	object	Automatically parsed to JavaScript objects.
Arrays	array	PostgreSQL arrays become JavaScript arrays.
NULL	null	Returns JavaScript null.

Common use cases

The following examples demonstrate typical PostgreSQL operations in Retool apps.

read and display data in a table

First, create a query named tableQuery and write an SQL statement to retrieve some data.

Example query

SELECT id, name, email, status, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100

Next, add a Table component to the app and set its Data property to {{ tableQuery.data }}. The table dynamically updates to reflect the data structure provided by the query.

filter data with user input

First, add input components for users to specify search filters.

• A Text Input component (searchInput) for search terms.
• A Select component (statusSelect) for status filtering.
• A Date Range Picker component (dateRange) for date filtering.

Next, write a query that references the input component values to filter data.

Example query

SELECT id, name, email, status, created_at
FROM users
WHERE
  name ILIKE {{ '%' + searchInput.value + '%' }}
  AND status = {{ statusSelect.value }}
  AND created_at BETWEEN {{ dateRange.value.start }} AND {{ dateRange.value.end }}
ORDER BY created_at DESC

Queries that read data automatically run whenever any referenced input values change by default. As you update the input fields, the query retrieves new data based on the new values.

insert new rows from a form

Insert records from a Form component.

1. Create a form with fields:

• nameInput (Text Input)
• emailInput (Text Input)
• roleSelect (Select)

2. Create insert query in GUI mode:

Field	Value
Action type	Insert a record
Table name	users
Records	{{ { name: form1.data.name, email: form1.data.email, role: form1.data.role, status: 'active' } }}

3. Add form submit handler:

Add an event handler to the form's Submit event that triggers the insert query, then refreshes the data query.

update rows with inline editing

Update records using the Table component's inline editing feature.

1. Enable inline editing:

In the Table component settings, enable Editable for columns you want users to edit.

2. Create update query in GUI mode:

Field	Value
Action type	Update a record
Table name	users
Primary key column	id
Changeset	{{ table1.changesetObject }}

3. Add save changes handler:

Add an event handler to the Table's Save changes event that triggers the update query, then refreshes the data.

delete rows with confirmation

Delete records with user confirmation to prevent accidental deletions.

1. Add delete button:

Add a Button component with Delete label and Danger style.

2. Create delete query in GUI mode:

Field	Value
Action type	Delete a record
Table name	users
Primary key column	id

3. Add click handler with confirmation:

Add an event handler to the button's Click event:

1. Action: Show confirmation modal
2. If confirmed, trigger the delete query
3. Then refresh the data query

bulk import with upsert

Import data from a file and insert or update records based on a unique key.

1. Add File Input component:

Add a File Input component (fileInput1) that accepts CSV or Excel files with Parse contents enabled.

2. Create bulk upsert query:

Field	Value
Action type	Bulk upsert by key
Table name	products
Primary key column	sku
Records	{{ fileInput1.parsedValue.map(row => ({ sku: row.SKU, name: row.Name, price: parseFloat(row.Price) })) }}

3. Add import button:

Add a Button component that triggers the bulk upsert query when clicked, then displays a success notification.

Tips:

• Validate file format before importing.
• Show preview of data before confirming import.
• Display import results (rows inserted vs updated).

work with JSONB columns

Query JSON data stored in JSONB columns.

Query JSONB data

-- Extract JSONB field
SELECT
  id,
  name,
  metadata->>'category' as category,
  metadata->>'tags' as tags
FROM products
WHERE metadata @> '{"featured": true}'

-- Filter by JSONB field values
SELECT
  id,
  name,
  preferences->'theme' as theme
FROM users
WHERE preferences->>'notifications' = 'enabled'

-- Query nested JSONB data
SELECT
  id,
  name,
  settings->'account'->>'timezone' as timezone,
  settings->'preferences'->>'language' as language
FROM users
WHERE settings @> '{"account": {"verified": true}}'

Best practices

Follow these best practices to optimize performance, maintain data integrity, and secure your PostgreSQL queries.

Performance

• Use indexes: Add indexes on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY statements to speed up queries.
• Limit result sets: Always use LIMIT to restrict the number of rows returned, especially for large tables displayed in tables or charts.
• Use prepared statements: Retool automatically uses prepared statements for PostgreSQL queries, which improves performance and prevents SQL injection.
• Avoid SELECT *: Select only the columns you need rather than using SELECT * to reduce data transfer and improve performance.
• Use connection pooling: Retool automatically manages connection pooling for PostgreSQL resources. For high-traffic apps, consider increasing pool size in advanced settings.
• Materialize complex queries: For expensive aggregations or joins that don't change frequently, consider using materialized views in PostgreSQL.

Security

• Use configuration variables: Store database credentials in configuration variables or rather than hardcoding them in resources or queries.
• Use resource environments: Organizations on an Enterprise plan can configure multiple resource environments to maintain separate database configurations for production, staging, and development.
• Apply least privilege: Grant database users only the minimum permissions needed for their operations. Use read-only users for queries that only need to read data.
• Validate user input: Always validate and sanitize user input before using it in queries, even when using embedded expressions or prepared statements.
• Enable SSL/TLS: Use SSL/TLS encryption for connections to protect data in transit, especially when connecting to databases over the internet.
• Use row-level security: For multi-tenant applications, consider using PostgreSQL row-level security policies to restrict data access.
• Audit database access: Enable PostgreSQL audit logging to track database access and changes for compliance and security monitoring.

Data integrity

• Use transactions: Wrap related operations in transactions to ensure atomicity. PostgreSQL automatically rolls back the entire transaction if any statement fails.
• Validate before delete: Always show confirmation dialogs before executing DELETE operations to prevent accidental data loss.
• Handle errors gracefully: Configure error notifications and fallback behavior for failed queries to improve user experience.
• Use RETURNING clause: Include RETURNING in INSERT, UPDATE, and DELETE statements to get affected rows and verify operations succeeded.
• Implement soft deletes: For sensitive data, consider marking records as deleted rather than physically removing them to preserve audit trails.
• Test with environments: Use separate development and staging databases to test queries before deploying to production. Configure different credentials for each environment using Retool environments.