Working with JSON Schema Form

Forms are one of the most common aspects of internal tools — they’re typically how your end-users will create or edit data. In this guide, we’ll cover how to build a simple form, populate it with data, validate the input, and set up a query to send the form data to a database.


JSON Schema Form or Form?

This page covers building a form using the JSON Schema Form component. You can also use the Form component to create a form in Retool. While the functionality of these two components overlaps quite a bit, there are pros and cons to using each. If you’re not sure which form component to use, see Choosing a form component.

For specifics on which fields the JSON Schema Form accepts, see the native documentation here.

1. Creating the form

Let’s start by dragging a JSON Schema Form component from the right-hand side component library onto the canvas:

You’ll notice that the form comes pre-populated with some sample inputs and data. These elements are generated from several schemas: JSON objects that specify the various aspects of the form. You can see the default schemas in the right-hand side inspector when the form component is selected:

Thankfully, there’s no need to always write these schemas by hand — Retool can automatically generate the JSON for us based on the schema of a SQL database. Click on the “Generate schema from database” link in the right-hand side inspector and you’ll see a modal window appear:

Next, we’ll select our SQL database and table from the dropdowns. At the bottom of the window, you’ll see a preview of the schemas to be generated. For now, we don’t need to make any changes, so let’s click on the “use generated schema” button.

Great! Our JSON Schema form component now includes inputs for every column in the database table. It also made some decisions based on the column types — you’ll see that our boolean active column appears as a checkbox.

2. Making changes

The column order doesn’t exactly match how we’d like to present things to the salespeople using this form, so it’d be nice to rearrange those. We also don’t want to show the id column, since we’ll handle auto-incrementing that value.

To accomplish this, we’ll just need to make some edits to the generated JSON Schema and UI Schema fields in the inspector on the right-hand side. We’ll rearrange the order of the columns in the JSON schema from top-to-bottom and delete the id property from both schemas:

You can make further modifications to the schemas to change input types, label names, and so on. Learn more about how to write the JSON schema.

Finally, we’ll need to setup the data model for the form. In the “Default Form Data” field in the inspector, let’s change the values to correspond to our form fields. Since we want the form to be blank for the salespeople to enter data, we’ll use empty strings as values.

3. Inserting a new record into the database

Now that we’ve got our form setup, we’ll need to connect it to a query to write its data to the database. To save some time, we’ve already got a database resource configured with write permissions. (Haven’t done this yet? Take a look at Writing to SQL to learn how.).

With our form component selected, open the “On Form Submit” dropdown in the inspector and select “Create a new query.” This will both create a new blank query and automatically set that query to be triggered when the “Submit” button on the form is pressed.

Inside of the query editor, we'll select our database (that's the resource with write access to our database) and table. Then we’ll change the “Action type” to Insert a Record.

From here, we’ll need to create a Changeset that maps each value in our form to the correct column in the database table. When it’s all filled in, it should look something like this:

Note: You’ll notice that we’re referencing another query, query1, in the id value. That’s a simple SQL query which selects all of the rows in our table. To auto-increment the id when we add a new row, we’re using the value to return all of the ids in the table. We then find the maximum value in that array and add 1 to it.

Now we can save this query and try submitting some data via our form. Success!

4. Clearing form on submit

You may have noticed that while our form submission was successful, the data we entered remains in the form. On a successful submit, we should probably clear the form so the salesperson can enter another customer. To do this, we can simply enable the “Clear after submission” toggle in the JSON Schema Form properties:

5. Pre-populating the form and updating record

We now have a working form, but it’d be nice if the sales team could also see all of the existing customers. It would help them confirm when they’ve successfully added a new customer and provide the ability to make changes to existing customer data.

We can make this happen with some small changes to our existing form. Let’s first drag in a table component and hook it up to our query1, which selects all of the rows in the customer table:

Now we can use the selected row of the table to populate the data for the form. Going back to the “Default Form Data” field in the right-hand side inspector, we can delete everything and simply type in {{}}. That’ll return an object with the column names as keys and the selected row cells as values:

Now the form is automatically populated with the values from the currently selected row in the table. If we submit the form in this state, however, we’ll be inserting a new record, not updating the existing selected row. To fix this, we’ll create a new query of a different action type: Bulk upsert via primary key.

We’ve set id as the primary key column. If the id matches an existing row, the data in that row will be updated. If the id doesn’t match, a new customer record will be inserted.

Instead of manually building out the changeset as we did earlier, with a bulk query we can simply pass in {{ [] }}, which is an array that contains all of the form’s data.

Let’s change our form component to use this new query:

There’s one more thing we’ll need to setup before running this query, however. You’ll remember that we previously created a little hack to auto-increment the id when we submitted the form. Now, we need to either pass the selected table row’s id or auto-increment a new id, depending on whether the salesperson is adding a new customer or modifying an existing one.

Let’s add back in the id to the form schema and set it to be hidden, so it’s not visible to the salespeople using this form. Then we’ll add a little ternary operator to the Default Form Data field to check if there’s a selected row in the table. If so, we’ll use all of that row’s data to populate the form. If not, we’ll only populate the hidden id field with our previous incrementing expression. Here’s what our new schema looks like:

A couple small things before we wrap up. We’ll enable the “Clear selection” toggle on the Table component, so the sales person can deselect a row clear the form when they want to enter a new customer. And let’s configure query1 to run again after our new query finishes, so the table updates with the form submission.

Now, we can try editing an existing customer and inserting a new one. Success!

6. Validation

Last but not least, let’s look at how we can enable some basic validation on our form. All of the fields in this form should be required, so we don’t have incomplete customer records. We’d also like to validate the email address to make sure it’s provided in the correct format. If any of these validation checks fail, the user should be instantly notified.

Thankfully, the react-jsonschema-form has some built-in validation functionality that we can specify by changing the schema. We’ll first list each input in the “required” property of the JSON Schema. For email, we’ll add the "format": email to check against the pre-defined semantic format. Finally, we’ll make sure the “Live validation” toggle is enabled, so that the validation runs on every user change to the form.

That’s it! We now have basic validation working on our form. For more advanced validation options, like cross-field dependencies and custom error messages, be sure to check out the react-jsonschema-form documentation.

Did this page help you?