SQL in Retool

A quick intro to SQL in Retool

Introduction

SQL, or Structured Query Language, is a language to interact with databases. You can use it to create, read, update and delete data. There are many different versions of the SQL language that vary based on database (i.e. MySQL vs PostgreSQL).

In Retool, you’ll use SQL to write queries that exchange data between Retool and your database. We’re going to go over some basic SQL (feel free to skip if you’re already familiar!) and a few things that are unique to SQL in Retool.

Basic SQL

Feel free to follow this basic SQL tutorial here (external link to W3 schools) if you’d like to really dig in! It’s a fantastic resource that covers all the different SQL operators.

In the context of Retool, I recommend reading the following (short) sections:

Home through Order By (7 sections), then Min and Max through Joins (8 sections).

Joins start to get a little more complicated, but are useful if you’re using two tables in a single query (for example, matching the user’s email from the users table to all the orders they’ve placed in the orders table), so learn about joins at your own risk.

Table structure

Relational databases organize data in tables with rows and columns.

Each row will have a primary key, which is a column that serves as a unique identifier for the row. There can be multiple “Bob”s, so we can differentiate them by their ids, which is the most commonly used primary key. Again, the primary key must always be unique.

Database columns must have a specified type. Here are a few common column data types:

BOOLEAN (true, false)
INT (integer)
FLOAT (floating point numbers)
VARCHAR (Variable Character Field, string of letters or numbers < 255 chars)
TEXT (string with no limit)
DATE (date, ie YYYY-MM-DD, but depending on your database, the format can vary)
DATETIME (date and time, ie YYYY-MM-DD hh:mm:ss)
TIME (time, ie hh:mm:ss)
BLOB (non-text, binary data, like an image)

For example, in the table above, the id is of type INT and name and email are columns of type VARCHAR. Any data inserted into the table must match the declared column type, or else your database will reject the query.

More types listed here.

🔥 Hot tip! Use the built-in schema browser to easily view your column types.

Read vs Write

SQL consists of a few keywords, or operations, that allow you to access the data in your database. There are 4 main data manipulation operations that SQL provides:

SELECT: gets data from a table (read)
INSERT: inserts data into a table (write)
UPDATE: updates data into a table (write) 
DELETE: deletes rows from a table (write)

As you may have noticed, operations can be split into two categories: read and write. In Retool, there are 2 query UIs, one for each of the categories. Generally, read operations should be written in the SQL mode and write operations should be written in the GUI mode.

See screenshots below for a quick preview of each of the modes!

GUI mode is available only for resources with both read and write permissionsGUI mode is available only for resources with both read and write permissions

GUI mode is available only for resources with both read and write permissions

SQL mode, typically for reads, has a blank field to enter SQL statementsSQL mode, typically for reads, has a blank field to enter SQL statements

SQL mode, typically for reads, has a blank field to enter SQL statements

SELECT, FROM, WHERE

These three keywords are the most important (in my very humble opinion) keywords you’ll need. Anytime you want to get columns from a table based on a certain condition, you’ll need SELECT, FROM and WHERE.

SELECT allows you to specify the columns you want to grab
FROM the table in your database
WHERE a certain condition is met.

This query will grab all columns (as denoted by the *) from the user  table where the id is 9. Note, since id is a primary key here, meaning there’s only one user with the id of 9, we’ll only get one row of data returnedThis query will grab all columns (as denoted by the *) from the user  table where the id is 9. Note, since id is a primary key here, meaning there’s only one user with the id of 9, we’ll only get one row of data returned

This query will grab all columns (as denoted by the *) from the user table where the id is 9. Note, since id is a primary key here, meaning there’s only one user with the id of 9, we’ll only get one row of data returned

Retool SQL

Available Docs

We have the following very important docs that we’ll be referencing a few times in the upcoming sections. They cover what you need to know to get started with SQL in Retool!

Dynamic Values

You can use values from Retool components inside your SQL queries with {{ }}.

Let’s take a look at some examples.

If you want to select a status from a dropdown and show all the projects with that status, you can use the dropdown’s value (which can be accessed with select3.value) directly in the WHERE condition.

Prepared Statements

All of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically.

Admins can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

A screenshot of the disable prepared statements checkbox in a resource setup pageA screenshot of the disable prepared statements checkbox in a resource setup page

A screenshot of the disable prepared statements checkbox in a resource setup page

This won’t work (and is an example of SQL injection):

This query is attempting to use a dynamic value as a table name, which counts as SQL injectionThis query is attempting to use a dynamic value as a table name, which counts as SQL injection

This query is attempting to use a dynamic value as a table name, which counts as SQL injection

This will work:

This query is using a dynamic value as a column value, which is not SQL injectionThis query is using a dynamic value as a column value, which is not SQL injection

This query is using a dynamic value as a column value, which is not SQL injection

FAQs

❓How can I use an array in my query?
✅ Depending on your database type, you’ll need to choose the correct syntax listed here.

❓How can I show all data if no search condition is specified?
✅ Depending on your database type, you’ll need to choose the correct syntax listed here.

❓Can I join my Postgres data with my Google Sheets data with SQL?
✅ It's possible to join data from two different databases in a Query JSON with SQL query! Read more about this special query type here. A quick note—Postgres returns data as an object of arrays, which is different than the array of objects returned from Google Sheets. In the Query JSON with SQL query, refer to the Postgres data as
{{ formatDataAsArray(postgresQuery.data) }} to get it in the right structure.

❓Why is GUI mode disabled?
✅ If GUI mode is disabled in the selector, it's probably because your database user does not have write permissions. Try returning to your resource settings and inputting a database user that has both read and write permissions.

Common Error Messages

❓relation “tablename” does not exist
✅ Make sure your table exists, and is spelled and capitalized correctly.

The table’s name in my database is “users” not “Users” and double quotes are case-sensitiveThe table’s name in my database is “users” not “Users” and double quotes are case-sensitive

The table’s name in my database is “users” not “Users” and double quotes are case-sensitive

❓This SQL is incompatible with prepared statements
✅ Table/database names and SQL functions aren't able to be defined using a string created dynamically. Docs with examples here.

select * from "users" works, but select * from {{ "users" }} does notselect * from "users" works, but select * from {{ "users" }} does not

select from "users" works, but select from {{ "users" }} does not

❓ invalid input syntax for type date/string/integer/bool/etc.
✅ Verify the column-to-be-updated’s type and make sure it matches the type you’re attempting to pass in.

Updated about a month ago


What's Next

Head over to the next section where we’ll learn all about Javascript in Retool.

Javascript in Retool

SQL in Retool


A quick intro to SQL in Retool

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.