retool basics - using Retool to read and write MySQL

Introduction

In our daily life, we will encounter many kinds of data, such as name, mobile phone, ID number, home address, etc. These data are very suitable to be stored in database. We can process and analyze these data in the database. MySQL is the most popular database. Proficiency in reading and writing data in MySQL is a necessary skill to make a application

SQL is a language that interacts with MySQL. You can use SQL to add, delete, modify, and query data. Next, I will explain how to read and write MySQL in Retool

Objective

1. Learn to read and write MySQL data in Retool

Prerequisites

1. register a Retool account

2. have a MySQL instance with a public IP address. If not, you can purchase a small MySQL instance from AWS or Azure

SQL Basics

To keep this post short, I'll only cover the commonly used knowledge points here; for a more comprehensive understanding of MySQL, you can read https://www.w3schools.com/MySQL/default.asp

Table Structure

| id | name  | google_account |
|----|-------|----------------|
| 1  | tom   | account1       |
| 2  | bob   | account2       |
| 3  | Emma  | account3       |
| 4  | Mia   | account4       |
| 5  | James | account5       |

Table are similar to Excel, table must be configured (designed with a table structure) before use, such as defining which fields the table has, field types, and whether there are default values.

Table usually have a primary key id that uniquely identifies a row of the data, making it easier for SQL to modify and delete the row

The following explanation in this post will be based on the table_a and table_b as an example

The structure of table_a is as follows

-- SQL for creating table_a
CREATE TABLE `table_a`
(
    `id`       int NOT NULL AUTO_INCREMENT COMMENT 'id',
    `name`     varchar(100) DEFAULT NULL COMMENT 'name',
    `gender`   varchar(10)  DEFAULT NULL COMMENT 'gender',
    `location` varchar(50)  DEFAULT NULL COMMENT 'city',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;


-- SQL for inserting data into table_a
INSERT INTO table_a (id, name, gender, location) VALUES (1, 'gang', 'male', 'Birmingham');
INSERT INTO table_a (id, name, gender, location) VALUES (2, 'liang', 'male', 'Anchorage');
INSERT INTO table_a (id, name, gender, location) VALUES (3, 'hong', 'female', 'Denver');
INSERT INTO table_a (id, name, gender, location) VALUES (4, 'mei', 'female', 'Boston');
INSERT INTO table_a (id, name, gender, location) VALUES (5, 'ming', 'male', 'New York');

the above sql will create a table with four fields, and insert five records

-- Syntax
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

you can use datagrid or DBeaver for table design

Data as follows

The structure of table_b is as follows

-- SQL for creating table_b
CREATE TABLE `table_b`
(
    `id`    int NOT NULL AUTO_INCREMENT COMMENT 'id',
    `year`  int DEFAULT NULL COMMENT 'year',
    `score` int DEFAULT NULL COMMENT 'exam score',
    `a_id`  int DEFAULT NULL COMMENT 'primary key of table_a',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

-- SQL for inserting data into table_b
INSERT INTO table_b (id, year, score, a_id) VALUES (1, 2022, 98, 1);
INSERT INTO table_b (id, year, score, a_id) VALUES (2, 2021, 78, 2);
INSERT INTO table_b (id, year, score, a_id) VALUES (3, 2022, 82, 3);
INSERT INTO table_b (id, year, score, a_id) VALUES (4, 2022, 33, 4);
INSERT INTO table_b (id, year, score, a_id) VALUES (5, 2020, 50, 5);

Data as follows

data reading

In Retool, SQL can be directly used for data reading, and commonly used SQL includes

-- read table_a all data
select * from table_a;

1. select: which fields to read

2. *: represents all fields in the table

3. from: Which table to read data from

4. --: SQL comments

The above SQL query results are as follows

select id, name from table_a;

The above SQL represents reading the id and name fields of table_a and the query result is as follows

select * from table_a where name = "hong" and gender = "female"

The above SQL represents the query of table_a for the data whose name equals to hong and gender equals to female, and the query result is as follows

select a.*, b.* from table_a a left join table_b b on b.a_id = a.id where a.name = "hong"

Since hong's score information is stored in table_b, table_b only has a_id field that can be associated with table_a's id field, so we need to use a join table query syntax. Since the table name is too long, which makes it inconvenient to write, we can add an alias to the table to refer to the table through the alias. table_a a stands for adding table alias a to the table_a, which is a common join table query pattern. table_a a left join table_b b on b.a_id = a.id is a join table query pattern, the above SQL represents the query for the score information of the name of hong, the query results are as follows

table name can be aliased, and similarly, fields can be aliased

select a.id as table_a_id, a.name as name, b.* from table_a a left join table_b b on b.a_id = a.id where a.name = "hong"

the query results are as follows

Data writing

data writing consists of adding, deleting, and modifying operations.

INSERT INTO table_a (name, gender, location) VALUES ('Mia', 'female', 'Los Angeles');

the above sql represents the insertion of a record with the name Mia into table_a. the name and value of the field in the sql should correspond to each other

the execute result are as follows

Suppose Mia used to live in Los Angeles, but now she has moved to Chicago

update table_a set location = "Chicago" where name = "Mia";

the execute result are as follows

Now let’s delete the Mia

delete from table_a where name = "Mia"

the execute result are as follows

Retool SQL GUI mode

In addition to writing SQL directly to write data, Retool also provides GUI mode to facilitate writing data, which reduces the threshold of learning in a visual way.

• A. choose which table to write to

• B. Action type，Retool currently provides several types of operations, such as insert a record, updating a record, or creating a record if it doesn’t exist

• C. Equivalent to the where SQL, you can set the query condition here

• D. set which data to insert or which to update

• E. run the sql and preview the results

• F. you can view the current table structure, which makes it easy for us to understand the field info.

Let’s get real

practice

Create database

If you have already purchased mysql from aws or azure. you need to make mysql accessible over the internet.

for security reasons, you must use security groups inbound rules to restrict ip so that no one else can access your mysql.

you need to give Retool Cloud access to your mysql. refer to https://docs.retool.com/data-sources/concepts/ip-allowlist-cloud-orgs

we create dev mysql user, use this user later to read and write mysql data. the relevant SQL is as follows, for both mysql version 5.7 and 8.0+.

-- mysql 5.7
create user 'dev'@'%' identified by 'root';
grant all privileges on *.* to 'dev'@'%' with grant option;
flush privileges;

-- mysql 8.0
create user 'dev'@'%' identified with mysql_native_password by 'root';
grant all privileges on *.* to 'dev'@'%' with grant option;
flush privileges;

the above SQL is used to create a dev user with unrestricted ip address and a root password, for security reasons, please change the password to a complex one.

create database, name is retool_tutorial

CREATE DATABASE `retool_tutorial` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

create table_a and table_b using the SQL mentioned above.

Create Retool Resource

click on the button in the red box to create a resource

select mysql resource type

configure MySQL

A. give the resource a name. fill in retool_tutorial here

B. the folder where resource is placed. Retool’s resources can be categoried by folder

C. the mysql address

D. MySQL port

E. Database name, retool_tutorial

F. fill in dev here

G. fill in root here, please use a more complex password to avoid malicious access

H. test the connection to check if you can access the database successfully

I. if you can access database successfully, you can save the resource

Read and write MySQL

create a app

A. click on the Create new button

B. select App from the drop down menu

A. click Code button

B. click Resource query type

typing tutorial

select resource retool-tutorial

A. which resource to access

B. select SQL mode

C. enter SQL here

D. the button can preview the SQL execute result

try executing the following sql statements one by one to see the result

-- query all data
select * from table_a;

-- left join query
select a.*, b.* from table_a a left join table_b b on b.a_id = a.id where a.name = "mei"

-- insert record
INSERT INTO table_a (name, gender, location) VALUES ('Sara', 'male', 'Fargo');

Let’s try GUI mode

A. GUI mode

B. choose which table to write

C. insert a record or updating an existing record, etc

example1. insert a record

example2. updating an existing record