Create forms using the JSON Schema Form component

The JSON Schema Form component is based on the react-jsonschema-form library. You create a form by defining the inputs and behavior using JSON or generating them automatically using the schema of a connected PostgreSQL, MySQL, or MS SQL database.

This guide walks you through building an app and form using JSON Schema Form. You can fill in the form to create a new record or update existing data by selecting a record from a table and populating the form. The form also increments IDs automatically.

📘

If you're not sure whether to use Form or JSON Schema Form, learn more about building forms on Retool and compare their features.

Demo

Try out the app to see the form in action (submissions are disabled).

Generate the inputs

First, create a new app. Retool automatically adds a table (table1) and query (query1) to your app that displays records from the products table. Drag a JSON Schema Form component onto the canvas and place it next to the table.

To generate a new form:

1. Click generate schema from a database in the Basic section of the Inspector.
2. Select the onboarding_db database and products table to generate JSON and UI schemas.
3. Click Use generated schema to add the schema to the form.

The structure of the form is defined by the JSON schema and any changes are immediately reflected. You can modify the JSON to remove individual inputs if you want to exclude them from the form. This is useful if you don't need users to provide certain information, such as IDs or timestamps.

📘

JSON Schema documentation

Refer to the react-jsonschema-form documentation to learn more about writing JSON and UI schema for JSON Schema Form.

Validate submissions

You define validation rules in the JSON Schema. Update the JSON's required properties to require the following fields:

"required":[
   "id",
   "name",
   "created_at",
   "updated_at"
],

Each type of input supports a number of validation options depending on their purpose. For instance, URL inputs can return a validation error if an invalid URL is entered. Edit the JSON schema and update the image_url property to expect URL formatting:

"image_url":{
   "type":"string",
   "title":"image_url",
   "format":"url"
},

JSON Schema Form can check the validation status of inputs on every change by enabling Live validation in the Advanced section of the Inspector. Validation occurs as you fill in the inputs and errors are reported within the form.

Populate the form to update existing records

You can also configure this form to make changes to an existing record. A common approach for this is to populate a form with data from a selected table row and then submit changes to update the record.

Selected table row values are available using table1.selectedRow.data. You can set the form's default form data property which automatically populates the inputs. JSON Schema Form matches the name of each input to a corresponding column name from the table to determine where values are placed.

For example, this ternary operator passes the selected row from the table to the form, but only if a table row is selected:

{
  {
    table1.selectedRow.data ? table1.selectedRow.data : null;
  }
}

JSON Schema Form matches input field and column names from the table to determine where values are placed.

Selecting a record in the table loads the values into the form.

Modify inputs

The products table contains a part_number column but records don't have a value for this. As a result, the form displays a validation error because the part_number input expects a string.

The name of each generated input corresponds to the column name from the table. You can modify the input's properties by modifying the appropriate section of JSON. Modify the JSON schema and update its type to accept support nullable values:

"part_number":{
   "type":[
      "string",
      "null"
   ],
   "title":"part_number"
},

Write a query to create and update records

🚧

Remember to save changes to your queries by clicking Save.

The form now has the necessary inputs and can display existing data. Write a query that creates or updates a record when the form is submitted.

Add a new event handler

Add a new event handler to the form using the Interaction section of the Inspector using the following options:

• Event: Submit
• Action: Trigger query
• Query: Create a new query

This creates a new query, jsonSchemaForm1SubmitHandler.

Write the query

Select the new query and make the following changes:

• Resource: onboarding_db
• GUI mode
• Run query only when manually triggered
• Table: products

This updates the query to use the correct database and table, run when manually triggered, and the kind of action it takes.

Set the action type

Select bulk upsert via primary key for the query's action type. This action requires a primary key column so the query knows which record to update. It should be a unique identifier to avoid incorrect changes. In this case, set the value to id.

When the form is submitted, the query looks for an existing record using the provided ID. If there's a match, that record is updated. If not, the query creates a new record instead.

Include the form data

Set the array of records to update to {{ [jsonSchemaForm1.data] }}. Note that the form data is wrapped in brackets to provide it as an Object.

Refresh the table after submission

You can create an event handler to refresh the table to show the changes automatically:

• Event: Success
• Action: Trigger query
• Query: query1

Click Save to save the changes to your query.

Clear the form

There are two options for clearing values from the form:

• Add a button to the table that clears the selected row: Enable Show a button to clear selection in the Selected Rows section of the Inspector.
• Clear the form on submission: Enable Reset after submit in the Basic section of the Inspector.

Increment IDs automatically

The form currently allows users to specify an ID. Since this form will create or update records, a user could accidentally overwrite an existing record if they provided an existing ID. Instead, you should automatically increment the ID and prevent users from providing one in the form.

🚧

The following guidance is provided for demonstration purposes. We strongly recommend using your database's auto-increment feature to create unique identifiers automatically.

For this guide, you can use Math to find the highest ID number in the products table and use it to increment to the following number to use automatically:

Math.max(...query1.data.id) + 1;

First, update the UI schema to disable the id input:

"id":{
   "ui:disabled":"true"
}

Next, update the form's Default Form Data property to provide a new ID if a row isn't selected:

{
  {
    table1.selectedRow.data
      ? table1.selectedRow.data
      : { id: (Math.max(...query1.data.id) + 1).toString() };
  }
}

Since the input for ID expects a string, the number is converted using .toString().

Wrap up

Your form is now ready to use. Try it out and create a new record, or select an existing record and update it.