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!

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.

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!

• Reading with SQL: https://docs.retool.com/docs/sql-queries
• Writing to SQL: https://docs.retool.com/docs/sql-writes
• Retool SQL “quirks”: https://docs.retool.com/docs/sql-query-faq,
• General doc on connecting a database to Retool: https://docs.retool.com/docs/connect-database-resource

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.

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

This will work:

FAQs

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

❓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 in the SQL Cheatsheet.

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

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

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