Skip to main content

Connect to ClickHouse

Connect ClickHouse to Retool to query analytics data and build apps and automations with SQL.

ClickHouse is an open-source column-oriented database management system designed for real-time analytics. After you create a ClickHouse resource in Retool, you can:

  • Query analytical data using SQL with full support for ClickHouse-specific functions and aggregations.
  • Run queries on billions of rows using ClickHouse's columnar storage and vectorized query execution.
  • Build dashboards that connect to your ClickHouse cluster with live data.
  • Insert records from Retool apps and automations.

Before you begin​

To connect ClickHouse to Retool, you need the following:

  • Database credentials: Username and password with appropriate read and write permissions.
  • Network access: Database must accept connections from Retool's IP addresses. For databases not publicly accessible, configure an SSH tunnel.
  • Retool permissions: Ability to create and manage resources in your organization.

Create a ClickHouse resource​

Follow these steps to create a ClickHouse resource in Retool.

1. Create a new resource​

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

ClickHouse resource selection.

2. Configure general settings​

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

Example nameExample description
Analytics database (readonly)A read-only ClickHouse database with product analytics and event tracking data.
ClickHouse warehousePrimary ClickHouse cluster with user behavior and metrics data.

3. Configure resource credentials​

Configure the connection settings for your ClickHouse resource.

ClickHouse connection settings.

Host​

The hostname or IP address of your ClickHouse server.

Examples
abc123.us-east-1.aws.clickhouse.cloud
clickhouse.example.com
192.168.1.100

Port​

The port number for your ClickHouse server. ClickHouse communicates over HTTP or HTTPS:

  • Default HTTPS port: 8443 (used when SSL is enabled — recommended)
  • Default HTTP port: 8123 (used when SSL is disabled)

Database name​

The name of the database to connect to on the ClickHouse server. Defaults to default if not specified.

SSH tunnel​

If your ClickHouse 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).

4. Configure authentication​

ClickHouse uses username and password authentication.

ClickHouse authentication settings.

Database username​

The ClickHouse username to authenticate with. The default ClickHouse user is default.

Database password​

The password for the specified user. Store this value in configuration variables or rather than hardcoding it.

5. Configure advanced options​

Configure optional settings available under the Advanced options section of the resource configuration form.

Outbound region​

If your organization uses outbound regions, select the region that should be used for requests to ClickHouse. This controls which geographic region your requests originate from, which is useful for reducing latency or meeting data residency requirements.

SSL/TLS​

SSL is enabled by default and strongly recommended for production ClickHouse connections.

FieldDescription
SSL/TLSEnable SSL/TLS encryption for the connection. Enabled by default.
CA certificateProvide your certificate authority (CA) certificate for SSL verification.
Client keyFor mutual TLS authentication, provide the client private key.
Client certificateFor mutual TLS authentication, provide the client certificate.
Verification modeHow the server certificate is verified: Full verification (validates certificate and hostname), Verify CA certificate (validates certificate only), or Skip CA certificate verification (disables verification).

When SSL is enabled, Retool connects on port 8443 by default. When disabled, port 8123 is used.

ClickHouse Cloud requires SSL. Keep SSL enabled and use the default port 8443 when connecting to ClickHouse Cloud instances.

6. Test the connection​

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

  • Network access: Ensure your ClickHouse server accepts connections from Retool's IP addresses or your self-hosted instance.
  • Credentials: Verify username and password are correct.
  • Port: Confirm the port matches your SSL setting (8443 for HTTPS, 8123 for HTTP).
  • SSL configuration: If using SSL, verify certificate 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.

7. Save the resource​

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

Query ClickHouse data​

Once you've created a ClickHouse resource, you can query it in apps, workflows, and agent tools using SQL.

Create a query​

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

Use Assist to generate queries from natural language prompts. Assist can create queries to retrieve, filter, and aggregate data from your ClickHouse 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 ClickHouse resource when prompted.
  5. Review the generated query and click Run query to add it to your app.
get total events by type from the events table using @ClickHouse

Query configuration fields​

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.

simple analytics query

The SQL statement to execute against your ClickHouse database. ClickHouse supports standard SQL along with ClickHouse-specific extensions for analytical workloads.

Example
SELECT
  toDate(event_time) AS date,
  event_type,
  count() AS total,
  uniq(user_id) AS unique_users
FROM events
WHERE event_time >= now() - INTERVAL 30 DAY
GROUP BY date, event_type
ORDER BY date DESC, total DESC
LIMIT 1000
query with embedded expressions

Use embedded expressions to make queries dynamic based on component values.

Example
SELECT
  user_id,
  event_type,
  event_time
FROM events
WHERE event_type = {{ eventTypeSelect.value }}
  AND toDate(event_time) >= {{ dateRange.value.start }}
  AND toDate(event_time) < {{ dateRange.value.end }}
ORDER BY event_time DESC
LIMIT {{ table1.pagination.pageSize }}
OFFSET {{ table1.pagination.offset }}

Data types and formatting​

ClickHouse has its own type system distinct from standard SQL databases. Understanding these types ensures correct data handling in your Retool apps.

Response data formats​

ClickHouse queries return data in these formats:

ClickHouse typeJavaScript typeNotes
Int8/16/32numberSigned integers.
Int64numberSigned 64-bit integer. Values outside ±2^53 may lose precision as a JavaScript number.
UInt8/16/32numberUnsigned integers.
UInt64numberUnsigned 64-bit integer. Values exceeding 2^53 may lose precision as a JavaScript number.
Float32/Float64numberFloating-point numbers.
DecimalstringReturns as strings to preserve precision. Parse with parseFloat() if needed.
String/FixedStringstringReturns as JavaScript strings.
Date/Date32stringReturns ISO 8601 date string (e.g., 2025-01-21).
DateTime/DateTime64stringReturns ISO 8601 datetime string.
Array(T)arrayReturns as JavaScript arrays.
Nullable(T)T | nullReturns null for missing values.

ClickHouse query examples​

The following examples demonstrate typical ClickHouse operations in Retool apps.

display aggregated metrics in a chart

First, create a query named metricsQuery to aggregate event data by time period.

Example query
SELECT
  toStartOfDay(event_time) AS day,
  count() AS events,
  uniq(user_id) AS unique_users
FROM events
WHERE event_time >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day

Next, add a Chart component to the app and bind its data to {{ metricsQuery.data }}. Set the x-axis to day and add series for events and unique_users.

filter analytics data with user input

First, add input components for users to specify filters:

  • A Select component (eventTypeSelect) for filtering by event type.
  • 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
  user_id,
  event_type,
  event_time,
  properties
FROM events
WHERE event_type = {{ eventTypeSelect.value }}
  AND toDate(event_time) >= {{ dateRange.value.start }}
  AND toDate(event_time) < {{ dateRange.value.end }}
ORDER BY event_time DESC
LIMIT 500

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.

run a top-N analysis

Use SQL aggregation to find the most common values in a column.

Example query
SELECT
  event_type,
  count() AS occurrences,
  round(count() * 100.0 / sum(count()) OVER (), 2) AS percentage
FROM events
WHERE toDate(event_time) = today()
GROUP BY event_type
ORDER BY occurrences DESC
LIMIT 10

Display the results in a Table component to show the top event types for the current day.

Best practices​

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

Performance​

Use indexes​

Add indexes on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY statements to speed up queries.

Example
CREATE INDEX idx_order_date ON orders (order_date);
CREATE INDEX idx_user_status ON users (status, created_at);

Limit result sets​

Always use LIMIT to restrict the number of rows returned, especially for large tables displayed in tables or charts.

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

Use prepared statements​

Retool automatically uses prepared statements for SQL database queries, which improves performance and prevents SQL injection. Use embedded expressions instead of string concatenation to take advantage of this.

Example
-- Use embedded expressions (Retool handles parameterization automatically)
SELECT * FROM orders WHERE user_id = {{ currentUser.id }}

-- Avoid string concatenation, which bypasses prepared statement benefits
SELECT * FROM orders WHERE user_id = '{{ currentUser.id }}'

Avoid SELECT *​

Select only the columns you need rather than using SELECT * to reduce data transfer and improve performance.

Example
-- Specify only the columns your app needs
SELECT id, customer_name, amount, order_date
FROM orders
WHERE status = 'pending'

Use connection pooling​

Retool automatically manages connection pooling for database resources. For high-traffic apps, consider increasing the pool size in the resource's advanced settings.

Security​

Use configuration variables​

Store values referenced in queries — such as schema names or environment-specific identifiers — in configuration variables or secrets rather than hardcoding them.

Example
SELECT * FROM {{ retoolContext.configVars.SCHEMA_NAME }}.orders
WHERE region = {{ retoolContext.configVars.DEFAULT_REGION }}

Apply least privilege​

Grant database users only the minimum permissions needed. Use read-only credentials for resources used only for reading data.

Example
-- Create a read-only user for reporting queries
GRANT SELECT ON analytics.* TO retool_readonly;

-- Create a separate user with write access for specific tables only
GRANT SELECT, INSERT, UPDATE ON orders TO retool_writer;

Validate user input​

Validate and sanitize user input before using it in queries, even when using embedded expressions or prepared statements.

Example
-- Validate that a numeric input is within an expected range before using it
SELECT * FROM orders
WHERE user_id = {{ numericInput1.value }}
LIMIT {{ Math.min(rowLimit.value, 1000) }}

Enable SSL/TLS​

Use SSL/TLS encryption for connections to protect data in transit, especially when connecting over the internet. Enable this in the resource's advanced settings.

Use resource environments​

Configure multiple resource environments to maintain separate database configurations for production, staging, and development.

Example

Use production, staging, and development environments with separate credentials for each. Apps automatically use the resource environment that matches the current deployment environment.

Data integrity​

Use transactions​

Wrap related operations in transactions to ensure atomicity. Rollback behavior depends on the database and statement type, so verify your resource's transaction semantics before relying on automatic rollback.

Example
BEGIN;
UPDATE accounts SET balance = balance - {{ amount.value }} WHERE id = {{ fromAccount.value }};
UPDATE accounts SET balance = balance + {{ amount.value }} WHERE id = {{ toAccount.value }};
COMMIT;

Validate before delete​

Always show a confirmation dialog before executing DELETE operations to prevent accidental data loss.

Example

Use a Modal component with a confirmation message and wire the DELETE query to run only when the user confirms. Set the query to run manually so it does not execute on page load.

Handle errors gracefully​

Configure error notifications and fallback behavior for failed queries to improve user experience.

Example

Use the query's onFailure event handler to show a Notification with a user-friendly error message. Reference {{ queryName.error }} to display the specific error details when needed.

Implement soft deletes​

For sensitive data, consider marking records as deleted rather than physically removing them to preserve audit trails.

Example
-- Mark a record as deleted instead of removing it
UPDATE users
SET deleted_at = NOW(), deleted_by = {{ currentUser.email }}
WHERE id = {{ usersTable.selectedRow.data.id }}

Test with resource environments​

Use separate development and staging databases to test queries before deploying to production. Configure different credentials per resource environment.

ClickHouse optimizations​

Use time-based filters​

Filter on timestamp columns whenever possible. ClickHouse's MergeTree engine sorts and partitions data by time, making time-based filters highly efficient even on large datasets.

example
-- Efficient: uses partition pruning on event_time
SELECT count() FROM events
WHERE event_time >= toStartOfMonth(now())

-- Inefficient: prevents partition pruning
SELECT count() FROM events
WHERE toMonth(event_time) = toMonth(now())

Use approximate functions​

For large datasets where exact precision is not required, use approximate aggregate functions to reduce computation time.

example
SELECT
  toDate(event_time) AS date,
  uniqCombined(user_id) AS approx_unique_users,
  quantileTDigest(0.95)(response_time_ms) AS p95_response_time
FROM events
GROUP BY date
ORDER BY date DESC

Prefer count() over count(*)​

In ClickHouse, count() without arguments is optimized and does not require reading column data, making it slightly more efficient than count(*).