Retool Database quickstart
Learn how to store and interact with data in Retool Database.
Retool Database is a fast, secure solution for saving and editing data to use in Retool apps. It combines a PostgreSQL database with a spreadsheet-like interface to manage your data. On Retool Cloud, the underlying database is managed by Retool. On Self-hosted deployments, you host your own PostgreSQL database.
If you intend to use Retool Database on Retool Cloud, review the usage and storage details to ensure it meets your requirements.
Features
With Retool Database, you can:
- Store tables of data in a securely hosted PostgreSQL database that's only available as a resource within your Retool organization.
- Visually interact with data using a spreadsheet-like interface to edit, search, filter, and sort records.
- Import or export data in CSV format.
- Build apps and write queries to interact with Retool Database like any other resource.
- Make use of multiple environments, such as staging and production.
- Configure resource permissions.mdx) to control access to Retool Database within your organization.
- Capture form submissions for forms created in Retool.
Get started
On Retool Cloud, Retool Database is available automatically for all users. Log in to Retool and navigate to the Resources tab. If you don't have an account yet, sign up for free.
On Self-hosted Retool, follow the configuration guide to configure Retool Database, backed by your own PostgreSQL database.
The Retool Database resource, available in the Retool Hosted section, represents your Retool-managed PostgreSQL database resource. You can view and manage your database and the data it contains using the Database editor UI, or configure resource settings, such as environments.
Interact with data using the Database editor UI
You can visually interact with your database much like a spreadsheet. To access it, hover the cursor over the resource and click View.
Databases organize data into tables that can have different structures (e.g., separate tables for product and customer data). Each tab along the top of the interface corresponds to a table—click on a tab to view its data.
You can open the Table options menu by clicking the arrow next to the name.
You can quickly navigate between tables using the Quick open menu on the left or pressing CMD/CTRL-K
.
Click on a field's name in the header to open the Field options menu that provides access to various options.
To quickly edit existing table data, double-click on a cell and update its value, then click Save. You can also interact with certain fields using checkboxes or select options.
Create tables
Retool Database prompts you to create a new table automatically if one doesn't already exist. You can create an empty table or import existing data in CSV format.
Import CSV
To import existing CSV-formatted data, drag a CSV file onto the drop zone. Convert your file to a CSV before you upload it, as XLSX files are not supported. Once you upload a file, Retool attempts to identify the type for each field and map it to an appropriate field type to create a table schema.
You have the opportunity to review and make changes to the new table schema in the Schema mapping section before importing data. You can change field names and types, drag fields to change their order, or remove certain fields from being imported by clicking the X button.
You can also configure how imports are processed using the Import settings section.
- Column heading: Use the first record of CSV-formatted data to set field names.
- Create a primary key: Create a primary key field for imported data (recommended).
- Use a CSV column: Select an existing CSV field to use as the primary key. This is useful if you are importing data with an existing primary key (e.g.,
id
). If turned off, Retool creates a new primary key field and sets values.
Set the primary key
A primary key is a field that uniquely identifies each table record. Retool automatically includes an id
field for the primary key when you create a new table and it's the default option during a CSV import.
You can configure a table's primary key at any time. Click the table's tab and select Edit Primary Key, then select a field in the New Primary Key dropdown menu.
If your data doesn't contain a single field that can uniquely identify records, you can use a composite key. When enabled, you can select multiple fields and use their combined values as a primary key.
If necessary, you can delete a table's primary key. Tables without a primary key are read-only and you cannot make changes to data unless there is a primary key set.
Copy table schema
You can create a new, empty table based on an existing table's schema. Open the Table options menu for an existing table and select Copy schema. Retool creates a new table and configures it with the same fields.
Tables created using an existing schema do not have a primary key set and are initially read-only. You must configure a primary key before you can edit table data.
Add or edit records
Click Add record to save new data to the current table. This opens the Add record modal that contains input fields generated from the current table's schema. Each input reflects the field's type, whether the field is nullable, and any default value.
After completing the form, click Create to save the record to the table.
You can also edit existing records using the same modal by clicking the Expand button that appears when hovering the cursor over a record.
To delete records, select them using the checkbox and click the red Delete button at the bottom of the window.
You cannot undo changes or the deletion of records in the Database editor UI. We strongly recommend you export table data as a CSV before making any significant changes.
Manage fields
To add a new field, click the + button to the right of the last field. Specify the name and type, then click Save. You can also add a new field to the left or right of existing fields. Click the field name to open the Field options menu and select either Insert field left or Insert field right.
Set field types
You can set a field's type, which reflects the data it contains. Click the field name to open the Field options menu and select Settings, or configure it when adding a new field. Each type includes additional options for configuration.
Setting the correct field type helps ensure that only valid data is entered. It also configures additional methods of input and determines how best to filter data. Some examples include:
- Date and Date + Time: Input dates using a date picker.
- Boolean: Toggle Boolean values using a checkbox.
- Enum: Configure a list of possible values from which to select.
Configure fields
If you need more control over a field, click Advanced to set advanced options. You can configure the PostgreSQL type, default field value, and whether a field should allow null (nullable) or require unique values. Refer to PostgreSQL's official documentation to learn more about PostgreSQL schemas and data types.
Search records
Use CMD/CTRL-F
to open Quick find.
Click Quick find and enter a search term to search through all records in the current table. Any results are highlighted and you can move between results using the search box.
Filter records
Click Filter to define filter conditions and show only records that match all of them. For example, you can show only records with a quantity
field value less than 9000
and contain the
in the name
field value.
You can add field filters from the Field options menu. Click the field name in the header and select Filter by this field.
Click Apply filter to filter records. The Filter button remains active while filters are applied and displays the number of filter conditions in use. To disable the filter, remove all filters and click Apply filter again.
Sort records
You can sort records by multiple fields in ascending or descending order. Click the Sort button to select fields and specify sort order. You can configure sorting to use multiple fields. In the event that multiple records have the same field values, the next field sorting option is applied.
You can quickly add fields for sorting from the Field options menu. Click the field name in the header and select either Sort ascending or Sort descending.
Click Apply to sort records. The Sort button remains active while sorting is applied and displays the number of sorting options in use. To disable sorting, remove all sorting options and click Apply again.
Customize view options for fields
Click the Fields button in the toolbar to customize how fields appear in the Database editor UI. You can change the order of fields, pin a single field to the left, and toggle visibility.
Pin a field
A pinned field is fixed to the left of the Database editor UI and is always visible when scrolling horizontally. Click and drag a field to the Pinned section to pin it.
You can also pin a field from its Field options menu by clicking Pin this field.
Reorder fields
You can drag-and-drop fields to reorder them in the Database editor UI. Click and drag fields into different positions to change their order.
Hidden fields
If you need to work with large tables with many fields, you can hide fields that you don't need to use. Toggle the switch to the right of a field's name to show or hide fields. When there are hidden fields, the Fields button remains active and displays the number of hidden fields.
You can also hide a field from its Field options menu by clicking Hide this field.
Link records across tables
You can link records between tables in the Database editor UI to see relevant data in one place. For example, you could link a table with order information to another table with user details. The foreign keys field references a field from another table that contains unique values, such as the primary key. Once a value is set, you can view the relevant linked record.
- Click the + button to the right of the last field and select Foreign key as the field type.
- Specify the table and field on which to link. You can only select fields with unique values.
- Set a value in the new field that corresponds to a value of the field you selected.
Once linked, you can click the Link icon to jump to the related record.
The values you specify for a Foreign key field do not need to be unique. You can link to the same unique record on the linked table multiple times (e.g., multiple orders for the same customer).
Use multiple environments
Multiple environments are only available on paid Retool plans.
You can use Retool Database with multiple environments to build and test apps using staging data without impacting critical data.
The Retool Database resource is automatically configured for any additional environments in use by your organization. Each environment contains an isolated set of tables—any changes you make in one environment do not affect others. Use schema migrations to sync changes between environments.
The Database editor UI loads tables for the currently selected environment, which is production by default. Use the Environment menu in the upper-right to select which environment tables to use.
Query your data
There is a limit of 1,000 queries per minute for Retool Database that applies across the whole organization.
You write queries to interact with Retool Database data in the same way as any other SQL database resource connected to Retool.
Select the Retool Database resource in the query editor, then specify SQL mode to write raw SQL queries, or GUI mode to construct queries with an interface. As with other SQL databases, you can explore tables in the database.
You can switch between multiple environments from your apps which automatically refreshes any queries you've written. Provided you use the same table names, your app automatically reloads data from the selected environment.
You can also use and query Retool Database in the Query Library by selecting the retool_db resource.
Access data externally
To access Retool Database data outside of your apps, you can:
- Export a CSV of your data.
- Use Retool Workflows to connect it to other data sources and integrations.
- Use connection strings to access your data in SQL clients or external applications.
Export data as a CSV
You can export a copy of table data at any time in CSV format. Open the Table options menu for an existing table and select Export CSV.
In the Query Library, you can also export CSVs after running SQL commands. Click the Download csv button next to the query results to download a CSV of your data.
Use Retool Workflows with Retool Database
With Retool Workflows, you can schedule ETL tasks to push and pull data between Retool Database and third-party endpoints and integrations. In a Workflows Query block, select retool_db as the resource to access Retool Database data.
Connection strings
Retool is currently rolling out connection strings. Check back soon if they are not yet available to you.
Admins can view connection strings on the Connection tab in the Settings modal, accessed from the dropdown on the Retool Database title. Retool provides connection strings as PSQL commands and PostgreSQL connection URLs. On Retool Cloud, you can reset your database password for the current environment by selecting Reset Password in this tab.
You can copy the PSQL command directly to the command line, or use the PostgreSQL connection URL in external applications. For example, use the connection URL in PostgreSQL clients such as PgAdmin, platforms such as Zapier, or any ORM which connects to PostgreSQL. Some tools may not support connection strings with role names and passwords, so you may need to enter these fields directly.
Tables created using connection strings might not immediately show up in the Retool Database UI. To display a new table, click the menu in the top left corner and select the table.