Read data from SQL databases

Learn how to construct queries to retrieve data from SQL databases and similar data stores.

You can create queries that use SQL statements to retrieve (read) data from connected database resources, such as a PostgreSQL or a MySQL database.

Web or mobile app

Workflow

1. Add a query

Web or mobile app

1. Navigate to the Code tab in the IDE.
2. Click + to add a new query.
3. Select the resource.

Add a query

Workflow

1. Either:
   1. Right-click on the canvas and select the Resource query block.
   2. Click ⊕ in the left toolbar to open the Blocks tab, then select Resource query block.
   3. Click-and-drag from an existing block to create a new, connected block.
2. Select the resource.

Add a query

There are two modes from which to select when writing an SQL query. The mode you choose depends on whether you need to read or write data:

• SQL: Read data using an SQL statement.
• GUI: Write data using a graphical query editor.

Select SQL mode.

2. Write an SQL statement to retrieve data

You can also use the Ask AI option to generate SQL queries.

Web or mobile app

Write an SQL statement to retrieve data from the database. The following example retrieves the id, name, subscription, and expiration values for all records in the sample_users table of a database.

getUsers

SELECT 
  id, 
  name, 
  subscription, 
  expiration
FROM
  sample_users

SQL statement

You use {{ }} embedded expressions to reference other values within the SQL statement, such as components and queries.

The following example adds a WHERE clause to retrieve only records with an expiration date prior to a Date Input component value.

getUsers

SELECT 
  id,
  name, 
  subscription, 
  expiration
FROM
  sample_users
WHERE 
  expiration < {{ date1.value }}

Demo

Workflow

Write an SQL statement to retrieve data from the database. The following example retrieves the id, name, subscription, and expiration values for all records in the sample_users table of a database.

getUsers

SELECT 
  id, 
  name, 
  subscription, 
  expiration
FROM
  sample_users

SQL statement

You use {{ }} embedded expressions to reference other values, such as blocks or webhook payloads.

The following example adds a WHERE clause to retrieve only records with an expiration date before the expiredBefore value provided by the Start block.

getUsers

SELECT 
  id,
  name, 
  subscription, 
  expiration
FROM
  sample_users
WHERE 
  expiration >= {{ moment() }}
AND expiration <= { { moment().add(1, 'days') } }

Reference values in a query

To prevent SQL injection, Retool converts SQL queries into prepared statements that separate the query from values. This prevents you from writing queries that dynamically reference column or table names.

3. Run the query

Web or mobile app

Click Save & Run to save the query and then execute it. The query results then appear in the Output tab.

You can reference the query output elsewhere in the app using the query's data property. This contains an object with key names that correspond to the column names. Each key contains an array of values.

Workflow

Click ▶︎ to run the block. The query results then appear in the Data and JSON tabs.

You can reference the query output further down the control flow using the query's data property. This contains an object with key names that correspond to the column names. Each key contains an array of values.