SQL Application

The following SQL App provides a sample that demonstrates the use of SQL queries and how those queries are connected to components and event handlers with Retool.

Steps

The following steps focus on accessing Retool and using Retool Database to provide a base understanding of the Retool Platform.

• The example will populate three tables into the Retool Database using provided CSV files and provide an overview of each table and associated queries that will be demonstrated.
• The fundamentals-sql app will be imported as a Retool App.
• Each section of the app will be closely examined to understand different SQL Queries and how Retool components / event handlers interact.

Create a Retool account and login

Create a Retool account and login to your account as shown below.

Create a table in RetoolDB using CSV files

For this laboratory, there are several CSV files necessary to be able to power the fundamentals-sql app. There are three CSV files that you need to download and then import into Retool Database.

• sample_students.csv
• sample_majors.csv
• sample_departments.csv

Once the files are downloaded, we create tables in the database:

• In the Retool Home Screen, select Database.
• In Database, select Import CSV > Into a new table.
• Select file sample_students.csv.
• A dialog appears displaying the CSV columns, database field and type. Select Create table.
• Repeat the process for the remaining CSV files, sample_majors and sample_departments.

Set Foreign key relationships

When importing a CSV, the foreign key relationships in the sample_majors and sample_students tables are not imported and do not exist. This needs to be set by hand via modification of column settings.

• Select sample_majors table in Retool Database.
• Select the column department > Settings. This will bring up the settings where you can then specify type as Foreign key and then specify the Table as sample_departments table and Field as id (int4).

Foreign key

• Select Save for this change.
• Repeat this process for the sample_students table. Modify the settings for the major column and department columns.
• For the major column specify:
  • Field type = Foreign key
  • Table = sample_majors
  • Type = id (int4)
• For the department column specify:
  • Field type = Foreign key
  • Table = sample_departments
  • Type = id (int4)

Database Tables Overview

Once the import of the CSVs has been completed, let’s quickly understand each table’s purpose and how they are related.

sample_students table

This table stores students that are enrolled in an Engineering or Science department. The following table provides an overview of the fields.

The department and major columns store foreign keys / identifiers to the sample_department and sample_majors tables.

Column name	Description	Type
id	Primary key for entries	Integer
first_name	Student first name	Text
last_name	Student last name	Text
student_id	Student identifier	Text
major	Student major field of study	Foreign key (sample_majors)
department	Department where major is taught	Foreign key (sample_departments)

sample_majors table

This table stores majors within an Engineering or Science department. The following table provides an overview of the fields.

The department column stores foreign key / identifier to the sample_department table.

Column name	Description	Type
id	Primary key for entries	Integer
major	Major field of study	Text
department	Department where major is taught	Foreign key (sample_departments)

sample_departments table

This table stores departments within a university. The following table provides an overview of the fields.

Column name	Description	Type
id	Primary key for entries	Integer
department	Department within the university	Text

Import fundamentals-sql app into Retool

Next we will import the Retool fundamentals-sql app into Retool. This application is meant to provide a working example with components, code and event handlers that demonstrate real-world actions that are commonly used in building Retool Apps.

SQL App

• Click the Fundamentals SQL App link to download the JSON.
• Once the template has downloaded go to Retool Home Screen, select Apps > Create > From JSON/ZIP.
• This will display a dialog to provide the downloaded file, the name of the app and the folder.
• Select Upload a file and select the downloaded file fundamentals-sql.json.
• Specify an App name, fundamentals-sql.
• Leave Add to folder to None(root).

Sometime the application may not display select component values properly (e.g. major). You may need to access the queries listed in the next section and run them to initialize the query and thus the select component values.

Code Overview

The fundamentals-sql app has several SQL Statements that are leveraged in the following sections. To access these queries, go to Code. The following table provides an overview of each query that will be examined in the following sections.

Code / Queries

Query Name	Description
getStudents	This query uses SQL SELECT to query data in a single table, in this example the sample_students table.
getStudentsJoin	This query uses SQL JOIN to query data across the tables sample_students, sample_departments, sample_majors to create a single view of the data.
getMajors	This query uses SQL SELECT to query data in a single table, in this example the sample_majors table.
getDepartments	This query uses SQL SELECT to query data in a single table, in this example the sample_departments table.
createStudent	This query uses SQL INSERT to add a new student to the sample_students table.
deleteStudent	This query uses SQL DELETE to delete a student from the sample_students table.
updateFirstname	This query uses SQL UPDATE to update the first_name column in the sample_students table.
orderStudentsById	This query uses SQL ORDER BY to update the ordering of SQL SELECT using the ID column in the sample_students table.

Examine SELECT

In this section we will examine a SQL SELECT statement and how it powers a Retool Table by selecting data from a single table. Select Edit App to be able to see how this application is created.

• Select the table in the Table Using Select section. This will display a tableUsingSelect label.

Table using SELECT

• Look at the Right Panel and inspect the table. For Content > Data source the app uses the getStudents query. It also displays the columns in the table and how Retool will render the data in the table component (e.g. Integer, Text, Tag).

Data source

• The query, getStudents, can be examined by selecting the Code > getStudents query. This will display the SQL SELECT.

getStudents query

• Using the Preview button you can run this query and examine the results below without updating the component.

Preview results

Examine JOIN

In this section we will examine a SQL JOIN statement and how it makes the major and department columns more legible by selecting across multiple tables. The common scenario is that a Retool App will display data that is table friendly from multiple tables. In the first table, tableUsingSelect, the major and departments are numerical values, or more specifically foreign key identifiers.

Table using Single Table

In order to display the major and department, we need to leverage a SQL JOIN.

• Scroll to the next section, Table using JOIN.
• Select the table and the tableUsingJoin label should appear.

Table using Multiple Tables

• Look at the Right Panel and inspect the table. For Content > Data source the app uses the getStudentsJoin query.

Data source

• The query, getStudentsJoin, can be examined by selecting the Code > getStudentsJoin query. This will display the SQL JOIN.

SQL JOIN query

This query starts by identifying what will be displayed on a column by column basis and then identifies after the FROM and JOIN which tables populate those values. The sample_students, sample_majors and sample_department prefix the column names to help establish what table we are accessing the data from and the JOIN ON statement identifies that the majors and departments identifiers will be used to select the correct entries in those tables.

• Using the Preview button you can run this query and examine the results below without updating the component.

Preview results

Examine INSERT

In this section we will examine a SQL INSERT statement and how a Retool Form component can be used to insert new students into the Retool Database / sample_users table.

• Scroll to the next section, Form using INSERT.
• Select the form and the formUserInsert label should appear.

Form using INSERT

• For formUsingInsert, we will use an event handler to take whatever is populated and use it to perform a SQL INSERT when the Submit button is clicked. In the Right panel, select Event handlers and click createStudent.trigger(). This will expose the event handler dialog below.

Form event handler

• In the submit handler shown above, we see the following settings, that defines what happens when the Submit button is selected, in this case run the createStudent query:

  • Event = Submit
  • Action = Control Query
  • Query = createStudent
  • Method = Trigger

• Let’s examine the createStudent query to see how data is extracted from the Form. Select Code > createStudent .

createStudent query

• A SQL INSERT statement is used to put entries into the sample_students table. The values of first_name, last_name, student_id, major, and department are inserted. The values are extracted from the form by using the {{ }} syntax. The values use the form label.data.component name. In this case this is formUsingInsert.data.textInput1 to extract the value for first_name for example as shown below:

textInput1 field

• What if you are unsure that the value is set to? Check out the State tab in the Left panel where you can examine queries, components, and globals. If we use the State tab, we can see that the value of the textInput1 component is, Chris.

State for textInput1

• The remaining fields are extracted in the same way including the values for textInput2, textInput3, select1, and select2.

When completing the form, the Department and Major select components are pre-populated with values. These values come from the department and major tables and require defining queries to extract the current values.

• Select the Department select1 component. This refers to the getDepartments Data source. The Value is set to the {{item.id}} and Label set to {{item.department}}. This will set values to the primary key ids for department (e.g., 1, 2) but display the associated department name (e.g., Engineering, Science) for easier selection.

select1 component

• Select select1 and see the values.

select1 component values

• Select the Major select2 component. This refers to the getMajors Data source. The Value is set to the {{item.id}} and Label set to {{item.major}} . This will set values to the primary key ids for major (e.g., 1, 2, 3 etc.) but display the associated major (e.g., Mechanical, Chemical, Electrical etc.) for easier selection.

select2 component

• Select select2 component and examine the values.

select2 component values

• Let's examine the queries that are powering each of the select components. Go to Code > getDepartments. The following query is displayed:

getDepartments query

• Using the Preview button you can run this query and examine the results below without updating the component.

Preview query

• Let's examine the queries that are powering each of the select components. Go to Code > getMajors. The following query is displayed

getMajors query

• Using the Preview button you can run this query and examine the results below without updating the component.

Preview query

Examine UPDATE

In this section we will examine a SQL UPDATE statement. Using an editable column and event handlers we can make changes in the table or delete an entry from the table.

• Scroll to the next section, Table using UPDATE and DELETE.
• Select the table and the tableUsingUpdateAndDelete label should appear.

Table using update and delete

• Look at the Right Panel and inspect the table. For Content > Data source the app uses the getStudentsJoin query.
• Next examine the First name column. Make editable is enabled on that column allowing a user to update the value of a selected cell and save the result. Next we have a Row action that identifies a Delete Student action that will be covered in the next section. Finally there is an Add-on that has a Save actions, updateFirstName.trigger(), that will trigger when the cell is updated and Save is selected.

Table properties

• Let’s first examine the Save actions.
  • Select it and you can see an Event handler is specified, updateFirstname.trigger().
  • Disable save is set to false.
  • Clear changeset on successful save is enabled.

Save actions

• Selecting the Event handlers, we can edit it and see the query that is identified for updating the student, updateFirstname.

Save event handler

• Let’s examine the updateFirstname query to see how data is extracted from the row in the table. Select Code > updateFirstname. We are using inline JavaScript to pull the value selected in the table via the changesetArray as seen below.

updateFirstname query

• What is a changeset? When a user selects a first name to change, that value and its id is stored in an array. We can extract this by referring to the table, changeset array, and the first_name and id attributes. This then provides a valid SQL UPDATE by changing the first_name column for an entry identified by the primary key, id. To see the changesetArray in action, select a first name in the table as shown below and modify the first name. Do not select Save yet. We want to see how changesets work.

Edit first name

• Now go to the State tab and select tableUsingUpdateAndDelete. Examine the changesetArray and see the first_name is set to the new value, “Tom”. The id specifies which student primary key this relates to, in this case “5”.

Changeset in State tab

• Go back to the table and select Save to modify the entry in the database. When the entry is saved by selecting Save, the changesetArray appears as follows. No entries exist as we enabled Clear changeset on successful save.

Changeset with no entries

Examine DELETE

In this section we will examine a SQL DELETE statement. Using an editable column and event handlers we can make changes in the table or delete an entry from the table.

• Scroll to the next section, Table using UPDATE and DELETE.
• Select the table and the tableUsingUpdateAndDelete label should appear.

Table using update and delete

• Next let’s understand how we can delete a student entry. In the Right panel, examine the Row actions > Delete Student. Select the Delete Student Row action.

Row action

• This will expose the Label, Icon used and Event handlers, deleteStudent.trigger().

Row action properties

• Let’s examine the deleteStudent query to see how the student is deleted from the table. Select Code > deleteStudent.

deleteStudent query

• In order to delete the student, we need to access the student’s id attribute, the primary key. How do we do this? This is done by identifying the selectedRow.id value. When the user selects a row, a trash icon appears on the right.

Delete row

• Selecting the trash icon will trigger the event handler, deleteStudent.trigger(). The query deletes the student using the id = {{ tableUsingUpdateAndDelete.selectedRow.id }}. The selectedRow attribute will examine which row the trash can was selected in and get the student identifier.
• An advanced setting defined in the deleteStudent query, provides a warning dialog as shown below. Selecting OK will execute the query, deleting the student from the sample_students table.

Table properties

Examine ORDER BY

In this section we will examine the use of ORDER BY to filter a column with ascending or descending values.

• Scroll to the next section, Table ordering content based on ID.
• Select the table and the orderById label should appear.

Table ordering based on content ID

• Look at the Right Panel and inspect the table. For Content > Data source the app uses the orderStudentsByID query.

orderStudentsByID data source

• The query, orderStudentsByID, can be examined by selecting the Code > orderStudentsByID query. This will display the SQL JOIN with SQL ORDER BY.

orderStudentsByID query

• In this query notice at the end of the query the use of ORDER BY identifying the column sample_students.id. Ordering will be done by ascending order as defined by ASC. We could have also specified DESC for descending order. Select Preview to see the query execute and return the IDs in ascending order.

Preview query

Summary

Now that you have a base understanding the SQL App, insert some students, update their first names and delete them. Examine the Retool Database to see them get added, updated and deleted.