Adding a new record to your database or API

There are two ways to add new data to your systems in Retool: through a table component or through a form. The logistics of adding rows depend on which data source you're using, so we'll start with PostgreSQL and tackle other sources later on.

Adding a row via a table

We'll start by assuming you've read your data into a table. Here's the setup we'll be working with: we've got a getUsers query that pulls users from our users table, and we've pulled that data into a table component via {{ }}.

Adding a new row through a table in Retool requires you to connect two queries: an "on row add" query and a "bulk update" query. We'll start with "on row add."

Creating an on row add query

To get started, scroll down in the component inspector (the right sidebar) and locate the "Event handlers" section. Create a new event handler and click on the Query dropdown. This setting lets us select (or create) the query that our table will use to add a row to the underlying database. We don't have a query configured for this yet, so we'll choose the "create a new query" option.

A new query should appear in your query editor (bottom panel) called table1RowSelectHandler.

We'll also rename the query to something simpler – addRow. Once you've renamed your query, make sure to update the "on row add" dropdown in your table settings to choose the new query name.

Now, in the Inspector panel again, toggle on the "Show add row button".

You'll notice a little + icon appeared at the bottom right of our table component; that's how we'll actually add a new row once we configure the addRow query.

If you click it before completing the next (and final) step, you'll notice a grayed out popup appears!

The last thing we need to do before being able to write this query is to make table columns editable. Click on your table, head over to the component inspector on the right, and toggle the "make editable" setting on for each column.

Once that's done, try clicking on the + icon at the bottom of your table: this is the UI you'll use when you want to add a new row.

Writing new data in and saving your changes won't work yet, though: we need to configure our addRow query.


SQL write GUI vs. other resources

For SQL databases, Retool provides a GUI for writes, and that's what we'll be using here. If you're using another resource (like MongoDB or a REST API), you'll need to configure this query differently (e.g. updateOne in Mongo or PUT / POST for a REST API).

We'll start by choosing the database table name (users) and the operation we want to do ("Insert a record"). The UI should update and show you a form field set called the "changeset" - this is where we'll put our new data that we want to insert.

Note: if you don't see the changeset fields, make sure you've selected GUI Mode and not SQL mode in the dropdown next to the resource.

When you add a row through a table in Retool, you reference the new data through {{ table1.newRow.* }}. So for example if you want to reference the new value for the email column that's entered in the new row UI, you'd use {{ }}. Let's populate our changeset with all of those values. This table has a bunch of columns so it takes some manual work :smile:

We've also renamed the table to usersTable to make things a bit more transparent.

Remember that you can change column types by clicking on a column in the component inspector (text, dropdown, modal, etc.). Those changes will propagate through to the new row UI, so if you want to use a boolean for the is_active column, you'll get a checkbox in the UI.


Default values

In this example we directly input all of the values from the new row into the table, but you don't have to do that: anything in {{ }} gets evaluated as Javascript. So if you wanted the create_date value to get set to the current time, instead of pulling in {{ usersTable.newRow.create_date }} you could use {{ moment() }} to get the current timestamp.

Creating a bulk update query

The last thing we need to do to make this work is to configure a bulk update query. This lets you update cell values directly, and Retool requires both this and our above query to add a new row through a table.

Just like before, click on your table and head over to the component inspector. On top of the "on row add" field you'll see a "bulk update action" dropdown: choose "create a new query."

Like before, let's update the query name (bulkUpdate), choose the new query name from the "bulk update action" dropdown, and choose our table (users). This time we'll select "bulk update via primary key" from the "Action type" dropdown instead of "insert a new record."

This query is a bit less tedious to configure than our addRow query: we just need to select the primary key for our table, and add {{ usersTable.recordUpdates }} into the "Array of records to update" input. The .recordUpdates property evaluates to an array of the new values that you've updated in your table. Once you've set a column to be editable (like we did above), you can click on any cell in that column to update its value. Clicking "save changes" will run your bulk update query and write new values to your database.

Now that we've configured a bulk update query and an on row add query, you can edit and add a new row to your database all via the table component.

Adding a row via a form

If you're not using a table or you have more complex needs, you can add a row (or any data) through a form. Retool currently supports two types of form components:

  1. The Form Component: a container with a submit button where you add the inputs, dropdowns, etc.
  2. The JSON Schema Form Component: a full form edited via JSON that can autogenerate based on your schema

The JSON Schema Form component is a better fit for standard forms, while the Form component gives you more flexibility with layout and input types. No matter which you choose, the process for adding data to your database or API is the same. We'll use the Form component here for simplicity's sake.


Video walkthrough

We made a short video on how to add a row via a JSON Schema Form here!

Let's start by dragging a Form component onto the canvas, and naming it addRowForm. I've also added a Text Input component in the form called emailInput, and updated the label to "email."

You can add whatever inputs you want to the form (text inputs, dropdowns, multiselects, etc.). I've added in a few more that we'll need to add a row to our table.

Keep in mind that you can specify input types for Text Input components. For a few of these (like credits used), I've specified a number type.

Now that our form is all set up, it's time to hook it up to a query that will grab the form data and insert it into our users table. We'll connect it to the "submit" button in the form component.

Start by creating a new query called "addRow" (if you've been following along from the section above, you can name it something else like "addRowFromForm"). Choose your desired table from the dropdown, and then the "insert a record" action.

Let's populate the changeset with references to all of the fields in our form. We reference the value of an input with the .value property in curly braces. When you're working with this many inputs, it's important to name them well for easy referencing later on :smile:

Once your changeset contains values for each row in the database, save your query. The last step is to connect this query to our form: click on the Form component and select the addRow query from the "On Form Submit" dropdown setting.

You can configure the Form component to clear inputs on submit, show a loading indicator, enable scrolling, and a lot of other handy stuff too.

If you've configured your query properly, you should be good to go! If you're in editor mode, double click the "Submit" button to check. You'll might run into a data type error or two that you'll need to iron out.

Adding data with other data sources

If you're using something other than PostgreSQL, the specific queries in this tutorial will need to change. For example: if you're using MongoDB, your "on row add" query will use the updateOne operation, and if you're adding a user to Firebase, you'll use the "create a user" action. As always, if you run into any issue, just post in the community (we're active!) or reach out to support through the Intercom widget on the bottom right.

Did this page help you?