Front end development can be an art. An almost pixel by pixel design, a careful crafting UX or even CX. And there is a time and place and technology set for that. And there is a range of front end applications that may not require or even justify that same diligence. Front ends that need to do a job – such as providing insight in data or allowing straightforward manipulation of data. Front ends that may never see an external user or customer but only a small internal user group. Retool is a low code application stack that focuses on that second category of frontend. It is not particularly crude, but it is CRUD oriented: creating, reading, updating and deleting data. Data that resides in wide variety of sources – databases, cloud data services, GraphQL and REST APIs. In my career,
I have worked with several CRUD style front end development technologies and I even helped create one. Retool is similar to all of them – and also seems to take it to the next level, with a WYSIWYG style UI development, its broad set of integrations and the easiest developer experiences I have seen so far. There is some similarity with Oracle Visual Builder – but that tool does not offer as easy access to this wide array of databases
In this article I will describe the quick development of a table and form UI on top of the countries table that I created in the free ElephantSQL PostgreSQL database instance as described in my earlier article Quick start with free managed PostgreSQL database on ElephantSQL The end result will be a web application in which users can explore the country data – sort, filter- and check details. Records can be updated and deleted and new records can be created. All data manipulations are performed against the underlying database.
Go to retool.com. Click on Try for free to create a temporary account. Provide first name, last name, email address and password.
Define a URL prefix – this can be anything (that is still available and makes for a valid URL).
In Home, click on tab Resources.
Create new resource
Click on PostgreSQL
Define name and connection string for the PostgreSQL connection
Test connection. When successful: proceed! As long as not – try to fix the problems.
In the home page, click on Create New and select From database in the dropdown.
In the popup, select the database (the resource that was just created for the PostgreSQL database connection) and select the table countries. Then set the column name to search by to name.
Click on Next.
Provide the name of the new App, for example Atlas.
Click Next. The App is created and shown.
A table component has been created – bound to the countries table in the PostgreSQL database. The table supports sorting and page-based scrolling. Filters can be added and data can be downloaded. A search field is included and the data is filtered (by name) against the string entered into this field.
Edit App
The App is already quite powerful. And we can do better yet. Quite easily as a matter of fact. Let’s add a few things:
- reduce the number of columns in the table
- add a form to show the details for the selected country record
- allow editing of a country record in the form
- allow creation of a new country record
- allow delete of a country record
Click on the button Edit App. We switch now to the development mode in which we can edit the app. Let’s first inspect what the generate App from Table has produced.
Click on the table component. The inspect panel shows a property palette with details for the table component. These can easily be edited.
Now click on the generated query query1. The SQL statement in this query is displayed. A simple select * from countries – slightly complicated with a where clause that filters in terms of the value of the search bar component.
Hide Columns
Select columns that are not required in the table display (only id, name, iso2 and capital should be displayed). For each such column, set property Show Column? to false.
Then resize the table and the columns and reposition the search bar on top of the table, as shown in the screenshot:
Feel free to remove the two text labels.
Add a form to show the details for the selected country record
Drag the Form component from the palette and drop it next to the table.
Click on the link generate from a resource to have form items generated from a query definition.
Select as Resource the resource created for the PostgreSQL database and as Table the table created with the countries records:
Set the required property for the form fields – only id, name and iso2 need to be provided.
Click on button Generate Form.
When the form generation is complete, click on the formTitle. Set its Value property to:
Details for {{table.selectedRow.data.name}}
The value between double curly brackets is an expression that is dynamically evaluated by Retool. It refers to the table component – which happens to be called table and takes the name property of the data object for the currently selected row. This means that this element will at any time use the name of the currently select country.
Next, click on the Form component. Set its initial data property to
{{table.selectedRow.data}}
Again this is an expression that resolves to the data object corresponding to the currently selected table record. The values shown in the form fields are retrieved from this object.
Time to see the form in action. Click on the button Preview (upper right hand corner). The app is somewhat streamlined. Click on various country records and note how the form is synchronized with the selected country and provides the detail values.
Allow editing of a country record in the form
It would be nice to be able to edit the values in the form fields and then save the changes to be persisted in the database. That can be done.
Click on the query that was created when the form was added. Its name is something like form1SubmitToCountries. Change the action type for this query to Update a record, or create a new record if it doesn’t exist. This query can be executed to insert or update a record based on the contents of the form items.
Set the Filter By condition for the query. The expression to use is: {{form1.data.id}} This ensures that the row to be updated is the one that has the same ID value as the value in the id field.
One more thing to take care of: we want the data in the table to be refreshed when the data in the form has been submitted. We do this by adding an event handler to the submit query.
Select the query used for fetching the data in the table as the one to be executed.
Time to see all this in action. Click on Preview to run the app. Select a country in the table, see the form synchronize. Change the value for the name or capital of the country and click on the big submit button. Find that the data in the table is refreshed as well. (you can also check directly in the table in the database if the data has been changed)
Allow creation of a new country record
With all we have in place, it is quite simple to create new country record. Drag a button from the component palette to the form. Set its text to Create new record.
Add an event handler to the button, to clear the form when the button is pressed.
You can now switch to preview. Click on the new button. The form fields are cleared. You can enter values for the required elements – id, name, iso2 – and click on the submit button. This will result in a new record created in the countries table in the database. The table component in the UI is refreshed – and will show the new record when you search for it.
Allow delete of a country record
We need another query (database interaction) to delete a record. Click on + New in the resources panel. Select Resource Query.
Then edit the resource query:
Select the PostgreSQL database resource, the countries table and the action type Delete a record. Define the filter condition as before, using expression {{form1.data.id}}
Then also add an event handler to this query, to refresh the countries query under the table when the record is deleted:
Add a button to the form. Set its Text property to Delete Country. Add an event handler that executes the resource query that you have just created for deleting the database record.
Switch again to Preview mode. Select a country you do not like – for example the country you have just created – and click on the Delete Country button in the form. The record is deleted and the table is refreshed.
Resources
Optimizing Low Code in Retool to Empower Services Colleagues By Charlotte Evans, Platform Services Degree Specialist – https://link.medium.com/6FH585PdFrb
Retool: Building Software with Software – By: Bryan Schreier – https://medium.com/sequoia-capital/retool-building-software-with-software-e2d908a57846
https://retool.com/ – homepage for Retool
