Create tables using the Data Editor

Create and edit database tables using the Retool UI.

Retool apps need data access to be useful and databases are a common resource. After connecting to a database, Retool apps can read and write data but they can't create and edit existing tables. This is what the Data Editor is for. It allows you to create and edit tables in existing databases without leaving Retool. This is particularly useful if you need to create development data or persist Retool app data.

Database support

The Data Editor works with these databases:

📘

All Retool cloud instances come with a managed PostgreSQL instance named managed_db. You can use this database to create new tables for use with your Retool apps.

Permissions

You must be a member of the Admin group to access the Data Editor. Additionally, database resources must be configured with write permissions to be accessible through the Data Editor.

Create a table

Open the Data Editor by clicking your user name in the top right and then select Data editor.

After opening the Data Editor, follow these steps to create a table.

  1. Select the database you want to add a table to.
  2. Click Create Table and enter a table name.
  3. The Data Editor displays a table preconfigured with an id column. Each record you insert into the database is automatically assigned the next available integer, creating a unique record key.
  4. Create your database schema by adding columns to your table.
    1. You can manually add columns by clicking + in the table header and specifying the column name and data type.
    2. Alternatively, you can upload a CSV to automatically enter a database schema and populate the table with records. You can manually adjust inferred data types by clicking on the header row.
  5. When you're done editing the table, you can access the data from your Retool apps using the query builder.

Edit a table

To edit an existing table:

  1. Open the Data Editor and select the database and table.
  2. Add columns individually by clicking + in the table header and specifying the column name and data type. You can also import a CSV file to add records in bulk.
  3. If needed, edit individual records by clicking on the cell.

Troubleshoot CSV uploads

If you have issues uploading CSV files, check the errors and solutions below.

Column "id" specified more than once

When you attempt to infer a schema using a CSV file that contains an id column, the Data Editor raises an error because the CSV column conflicts with the new table’s default id column.

To resolve this issue, refresh the page and confirm that the schema was inferred correctly by inspecting the header row. You can then upload the records from your CSV file using the Upload CSV and detect conflicts button.

Record conflicts

When you upload a CSV file, Retool checks for duplicate records. To resolve conflicts, you can:

  • Accept all updates. This causes conflicting database records to be overwritten by the records in your CSV file.
  • Reject all updates. This prevents conflicting database records from being overwritten, importing only those records with unique primary key values.
  • Accept selected updates. Select individual records from your CSV file to overwrite conflicting database records.

Common use-cases

The Data Editor can be useful in many scenarios but below are some common use-cases.

Generate development data

Getting access to the databases you need to power your production apps can take time. You might also want to build against non-production tables to ensure that you don’t accidentally modify sensitive data.

You can use the Data Editor to generate tables that match your production database. Paired with a fake data generator like mockaroo.com, you can get a database up and running with realistic development data.

Persist Retool app data

Many applications require a persistent datastore to maintain user state and there are a few ways to do this with Retool. You can use temporary state to manage state within a single app session. Or, you can use a browser's localStorage to manage state across apps and sessions. You can also use the Data Editor and database tables to durably store your Retool app state, without interfering with existing production data. This last option provides a more durable, cross-device solution.


Did this page help you?