Skip to main content

Connect to CockroachDB

Connect CockroachDB to Retool to query data, manage records, and build apps and automations with SQL.

CockroachDB is a relational database management system. You can create a CockroachDB resource to securely connect to your databases and create apps and automations that use SQL queries to perform create, read, update, and delete operations.

What you can do with CockroachDB in Retool​

  • Query with SQL: Write SQL queries with joins, aggregations, subqueries, common table expressions (CTEs), window functions, and CockroachDB-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 CockroachDB functions and stored procedures with parameters and return values.
  • Work with advanced types: Query and manipulate JSONB columns, arrays, hstore, and other CockroachDB data types.

Before you begin​

To connect CockroachDB to Retool, you need the following:

  • CockroachDB database: An accessible CockroachDB instance.
  • Network access: Your CockroachDB server must accept connections from Retool's IP addresses.
  • Database credentials: Username and password with appropriate permissions.
  • Retool permissions: Ability to create and manage resources in your organization.

Create a CockroachDB resource​

Follow these steps to create a CockroachDB 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 CockroachDB and click the CockroachDB tile to begin configuration.

CockroachDB resource selection.

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 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
User database (readonly)A read-only CockroachDB database that contains information about all registered users.
Orders databaseA read/write CockroachDB database with customer order data.

3. Configure resource credentials​

Configure the connection settings for your CockroachDB resource.

You can autofill the configuration fields using a CockroachDB connection string.

Connection string example
cockroachdb://username:password@host:26257/dbname

CockroachDB connection settings.

Host​

The hostname or IP address of your CockroachDB server.

Examples
mydb.abc123.jxf.gcp-us-central1.cockroachlabs.cloud
cdb-instance.c.my-project.internal
cdb.example.com
192.168.1.100

Port​

The port number for your CockroachDB server. Default is 26257.

Database name​

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

Connection options​

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

Examples
search_path=public,app_schema
application_name=retool
statement_timeout=30000

4. Configure authentication​

CockroachDB supports authentication using Username and password. For sensitive values like passwords and access keys, use configuration variables or rather than hardcoding them.

CockroachDB authentication settings.

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 CockroachDB. This controls which geographic region your requests originate from, which is useful for reducing latency or meeting data residency requirements.

SSL/TLS​

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

FieldDescription
SSL/TLSEnable SSL/TLS encryption for the connection.
SSL hostOverride the hostname used for SSL certificate verification. Useful when connecting through proxies or load balancers.
Reject unauthorizedReject connections if the server's SSL certificate cannot be verified. Enabled by default for security.
CA certificateProvide your certificate authority (CA) certificate for SSL verification.
Client certificate and keyFor mutual TLS authentication, provide client certificate and private key.
GCP self-signed certFor Google Cloud SQL, enable this option to connect with self-signed certificate support.

For cloud databases SSL certificates may be required. Download the appropriate CA certificate from your cloud provider's documentation.

6. Test the connection​

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

  • Network access: Ensure your CockroachDB 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 CockroachDB port (default 26257).
  • Cluster setting: Ensure the CockroachDB cluster settings 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.

7. Save the resource​

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

Query CockroachDB data​

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

Create a query​

You can create a CockroachDB 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 manipulate data from your CockroachDB 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 CockroachDB resource when prompted.
  5. Review the generated query and click Run query to add it to your app.
retrieve 25 rows from users table using @CockroachDB

CockroachDB query with Assist.

Action types​

CockroachDB 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 typeDescriptionUse case
Insert a recordAdd a single new row to a table.Create records from form submissions.
Update an existing recordModify 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 existUpdate an existing record or create a new one if the record doesn't exist.Upsert operations based on filter criteria.
Delete a recordRemove rows based on filter criteria.Delete records with confirmation.
Bulk insert recordsAdd multiple new rows to a table in a single operation.Bulk create records from imported data.
Bulk update via a primary keyUpdate multiple records identified by primary key.Batch update operations.
Bulk upsert via a primary keyInsert 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

Select from the action types listed in the Action types table above.

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.

Example
{{ { status: "active", role: roleSelect.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.

Changeset from table inline editing
{{ table1.changesetObject }}
Changeset from form
{{ form1.data }}

Data types and formatting​

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

Request data formats​

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

Value typeDescriptionExample
StringsUse single quotes in SQL or embedded expressions.'text value' or {{ textInput.value }}
NumbersProvide numeric values directly. No quotes needed.123 or 45.67
BooleansUse TRUE or FALSE in SQL.TRUE or {{ checkbox.value }}
DatesUse ISO 8601 format strings or CockroachDB date functions.'2025-01-21' or NOW()
TimestampsUse ISO 8601 format with timezone or CockroachDB timestamp functions.'2025-01-21T10:30:00Z' or CURRENT_TIMESTAMP
NULL valuesUse NULL keyword in SQL or null in embedded expressions.NULL or {{ textInput.value }}
JSON/JSONBUse JSON strings or JavaScript objects.'{"key": "value"}' or {{ JSON.stringify(object1.data) }}
ArraysUse CockroachDB array syntax or JavaScript arrays.ARRAY[1,2,3] or {{ [1, 2, 3] }}

Response data formats​

CockroachDB queries return data in these formats:

Data typeJavaScript typeNotes
Integer typesnumberReturns JavaScript numbers.
Numeric/DecimalstringReturns as strings to preserve precision. Parse with parseFloat() if needed.
Text/VARCHARstringReturns as JavaScript strings.
BooleanbooleanReturns true or false.
DatestringReturns ISO 8601 date string (e.g., 2025-01-21).
TimestampstringReturns ISO 8601 timestamp string with timezone.
JSON/JSONBobjectAutomatically parsed to JavaScript objects.
ArraysarrayCockroachDB arrays become JavaScript arrays.
NULLnullReturns JavaScript null.

Common use cases​

The following examples demonstrate typical CockroachDB 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

Add a Form component (form1) with input fields for the data you want to collect (name, email, role).

Create a query in GUI mode to insert a new row using the form data:

FieldValue
Action typeInsert a record
Table nameusers
Records{{ { name: form1.data.name, email: form1.data.email, role: form1.data.role, status: 'active' } }}

Add an event handler to the form's Submit event that runs the insert query and displays a success notification.

update data with inline editing

Add a Table component and enable inline editing by setting Enable saving to true.

Create an update query in GUI mode that saves changes made in the table:

FieldValue
Action typeUpdate a record
Table nameusers
Primary key columnid
Changeset{{ table1.changesetObject }}

Add an event handler to the table's Save changes event that runs the update query and refreshes the data query.

delete rows with confirmation

Add a Table component that displays your data and a Button component in an action column for deleting rows.

Create a delete query in GUI mode:

FieldValue
Action typeDelete a record
Table nameusers
Primary key columnid

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:

FieldValue
Action typeBulk upsert by key
Table nameproducts
Primary key columnsku
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 CockroachDB 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 CockroachDB 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 CockroachDB 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 CockroachDB.

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 CockroachDB row-level security policies to restrict data access.
  • Audit database access: Enable CockroachDB 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. CockroachDB 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.