Custom properties on a Retool form using a PostgreSQL JSON column

[ilmari]

Hello!

I have a specific requirement I’d like to address some way, but I am not entirely sure how I could achieve the ideal outcome.

I have a Retool app hooked to a postgresql database. There are multiple tables in the DB, and one of the tables there is a bigger table (let’s call it ”products”). I’d like to design the app in a way that a user of the Retool app would be able to define their own ”custom properties” that should be stored in the ”products” table. One way to achieve this on the database level would be to have one extra JSON column in the ”products” table, and in that column we would store something like:

{ ”custom_property_name”: ”Secondary Color”, ”custom_property_value”: ”black” }

Is there any way I could achieve such flexibility on the frontend, namely a Retool form? First, the Retool user would define the name of the field and the value for the field somehow. Second, when they have defined a custom property, the user would see the custom property on the Retool form like any other text input (or similar) component(s).

Has anyone achieved such flexible custom properties like this?

Thank you.

[PatrickMast]

Hello @ilmari,

Maybe you can try using a JSON column in the database.

First, you need to create a JSON Schema for the custom properties. This will define the structure of the data that will be stored in the JSON column. For example, your schema could look like this:

{
  "type": "object",
  "properties": {
    "name": {
      "type": "string"
    },
    "value": {
      "type": "string"
    }
  }
}

Next, you need to create a Retool form to display and edit the custom properties. You can do this by adding a JSON Schema Form component to the canvas. When you configure the component, select the JSON Schema that you created in the previous step.

To allow users to define new custom properties, you can add a button to the form that opens a modal dialog. The modal dialog should contain two fields: one for the name of the property and one for the value. When the user clicks the Save button in the modal dialog, the new property should be added to the JSON Schema and the form should be updated to display the new property.

To save the custom properties to the database, you can use a Retool query. The query should update the JSON column in the products table with the new property data.

Here is an example of a Retool query that you can use to save the custom properties to the database:

UPDATE products
SET custom_properties = jsonb_set(custom_properties, '{{ propertyName }}', '{{ propertyValue }}')
WHERE id = {{ productId }}

This query will update the custom_properties column in the products table with the new property data. The {{ propertyName }} and {{ propertyValue }} placeholders will be replaced with the name and value of the custom property, respectively.

The {{ productId }} placeholder will be replaced with the ID of the product that the user is editing. You can get the value of the product ID by using the {{ productId }} variable in your Retool form.

Once you have created the Retool form and query, you can add them to your Retool app and users will be able to define and edit custom properties for products.

Hope this helps.

Patrick

[ilmari]

Thank you @PatrickMast . Great tutorial around exactly what I asked. I’ll look into your approach.