Quick Start with initially free Low Code Front End Development using Retool against ElephantSQL PostgreSQL

Lucas Jellema

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.image

Go to retool.com. Click on Try for free to create a temporary account. Provide first name, last name, email address and password.

image

Define a URL prefix – this can be anything (that is still available and makes for a valid URL).

In Home, click on tab Resources.

image

Create new resource

image

Click on PostgreSQL

image

Define name and connection string for the PostgreSQL connection

image

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.

image

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.

image
Click on Next.

Provide the name of the new App, for example Atlas.

image

Click Next. The App is created and shown.

image

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.

image

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.

image

Then resize the table and the columns and reposition the search bar on top of the table, as shown in the screenshot:

image

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.

image

Click on the link generate from a resource to have form items generated from a query definition.

image

Select as Resource the resource created for the PostgreSQL database and as Table the table created with the countries records:

image
Set the required property for the form fields – only id, name and iso2 need to be provided.

Click on button Generate Form.

image

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.

image

Next, click on the Form component. Set its initial data property to

{{table.selectedRow.data}}

image

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.

image

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.

image

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.

image

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.

image

Add an event handler to the button, to clear the form when the button is pressed.

image

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.

image

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}}

image

Then also add an event handler to this query, to refresh the countries query under the table when the record is deleted:

image

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.

image

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 Schreierhttps://medium.com/sequoia-capital/retool-building-software-with-software-e2d908a57846

https://retool.com/ – homepage for Retool


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next Post

Pseudo Change Timestamp–Finding recently changed table rows using Flashback Version Query to provide a surrogate last_changed column

My challenge: replicate changes from database A to database B, once per day. The tables involved are pretty big (100M records). I really would not want to process them all into database B. However, the tables do not have a LAST_DATE_MODIFIED column that has a timestamp that is updated with […]
%d bloggers like this: