Retool provides a convenient way to build forms that connect to your database. However, when you add or remove fields from a database table, you often have to manually update your form in the Retool interface to match those changes.
This article demonstrates how to build a JSON Schema form that can automatically adapt to your database structure. This means you won’t need to tweak the UI after you add a field or even a table. The JSON Schema form in the context that we’ll be using here isn’t without its limitations but depending on your use case it can really come in handy sometimes.
I’ll go through the steps one by one and explain the general idea, feel free to modify it as you see fit. I’ll be using PostgreSQL in this example. An overview of the flow is: choosing a table to insert a record into, getting the information about the table from the information schema, generating a JSON schema for the table on the fly and inserting a record. The main point being that you can change the table with a click and a new form will be generated.
1. Get all tables from the schema
Copied!SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
2. Generate a table selection dropdown with Retool dropdown component
Use the query results as a data source for a dropdown component (called tableSelect). This dropdown allows you to select which table to use for generating the JSON Schema form in the next steps.
3. Retrieve table columns and types.
Now, we select the table. We need a way to generate a JSON Schema form for it. First thing we need to do is get all the fields for that table and the types associated with each. We can do this by querying the information schema:
Copied!SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '{{ tableSelect.selectedItem.table_name }}' ORDER BY ordinal_position;
Remember to transform the results with formatDataAsArray(data) in order to use it in a loop in the next steps. The query would look and return something like this in retool:
4. Type conversion
With this we can almost make a JSON Schema Form, there’s just one small issue: the JSON Schema Form and PostgreSQL do not have the same types. We’ll need to convert the data types from PostgreSQL to JSON Schema Form while constructing the form:
Copied!const columnsResponse = getColumnsForTable.data; const typeMap = { 'character varying': 'string', 'text': 'string', 'integer': 'integer', 'bigint': 'integer', 'smallint': 'integer', 'numeric': 'number', 'decimal': 'number', 'real': 'number', 'double precision': 'number', 'boolean': 'string', 'date': 'string', 'timestamp without time zone': 'string', 'timestamp with time zone': 'string' }; const schema = { title: _.startCase(tableSelect.selectedLabel) || "Please select table", type: "object", required: [], properties: Array }; columnsResponse.forEach(col => { const { column_name, data_type } = col; if (column_name === 'id') { // Skip "id" field or handle it differently if needed } else { const columnTitle = _.startCase(column_name); // Default to 'string' if type is not found in the map const jsonType = typeMap[data_type] || 'string'; schema.properties[column_name] = { type: jsonType, title: columnTitle }; } }); return schema;
5. Generate Retool form UI
We also need to generate the UI for the json schema form:
Copied!const columns = getColumnsForTable.data; let uiSchema = Array; columns.forEach(col => { const { column_name, data_type } = col; if ( data_type === 'integer' || data_type === 'bigint' || data_type === 'smallint' || data_type === 'numeric' || data_type === 'decimal' || data_type === 'real' || data_type === 'double precision' ) { uiSchema[column_name] = { 'ui:widget': 'updown' }; } else if ( data_type === 'date' || data_type === 'timestamp without time zone' || data_type === 'timestamp with time zone' ) { uiSchema[column_name] = { 'ui:widget': 'date' }; } else { uiSchema[column_name] = { 'ui:widget': 'text' }; } }); return uiSchema;
Our result looks like this:
Take note that we are calling the buildJsonSchemaScript and the buildUISchemaScript as event handlers from getColumnsForTable. And getColumnsForTable is called automatically when the tableSelect component changes.
So if we select a table now we’ll have the form:
6. Database insert with an SQL Retool query
Now that we can dynamically build and display a JSON Schema form, the last piece is to insert the form data into the database. Since PostgreSQL can interpret JSON using json_populate_record, we can leverage this function for the insertion:
Copied!INSERT INTO "public"."{{ tableSelect.value }}" ({{ getColumnsForTable.data.map(el => el.column_name).toString() }}) SELECT {{ getColumnsForTable.data.map(el => el.column_name).toString() }} FROM json_populate_record( NULL::"public"."{{tableSelect.value}}", '{{ JSON.stringify(jsonSchemaForm1.data)}}'::json );
We specify the columns explicitly to ensure we’re not inserting into id fields or any auto-generated columns (if that’s your table setup). The jsonSchemaForm1.data holds the form data submitted by your dynamically generated JSON Schema form.
Summary
By dynamically constructing both the JSON Schema and UI Schema for your form, you reduce the need to make continuous UI changes whenever your database schema evolves. This can save a lot of time in scenarios where fields or even entire tables change frequently.
The key steps are:
- Retrieving all table names from information_schema.tables.
- Selecting a table from a dropdown.
- Fetching columns (and types) from information_schema.columns.
- Mapping PostgreSQL types to JSON Schema data types.
- Generating the UI Schema to specify which widgets to use.
-
Using json_populate_record to insert the JSON data directly into the chosen table.
This approach provides the flexibility to add columns or whole new tables in your database without ever needing to adjust your Retool UI. Whenever the table schema changes, the form will automatically reflect the new columns and data types, delivering a low-maintenance solution.
Leave a Reply