Documentation

Welcome to Retool! We're a fast way to build custom internal software.

You'll find the 5 minute demo, quickstart guide, and documentation for each of our connectors and components here. If you've got any questions -- chat with us on the bottom right!

Get Started    Guides

Reading from SQL

Write your own SQL statements. Use parameters from anywhere else.

Overview

To read from a database, you write SQL statements. For example, this query pulls in all your users:

select * from users;

Name it users, save it, and you'll be able to access it elsewhere on the page.

Writing a SQL statement, and saving it as `users`.

Writing a SQL statement, and saving it as users.

Once saved, access a query's results via query.data. SQL queries return data in a column-based format:

{
  "column1": [1, 2, 3],
  "column2": [1, 2, 3],
  ...
}

Data from SQL queries are NOT returned as an array of objects!

If you want to use the data as an array of objects, you can use the helper function formatDataAsArray to do so like below.

{{ formatDataAsArray(sqlQuery.data) }}

Examples

Select users that are active or inactive, depending on a checkbox.

select * from users where active = {{checkbox1.value}}

Select users that have a first name that is an exact match with a text input.

"Mary" will match only users with first name "Mary".

select * from users where first_name ILIKE {{textinput1.value}}

Select users that have a first name that is a substring match with a text input.

"Ma" will match "Mary" and "Margaret".

select * from users where first_name ILIKE {{'%' + textinput1.value + '%'}}

Conditionally match a check box if it is is defined. Otherwise show all matches.

select * from users where (
	{{!checkbox1.value}} OR active = {{checkbox1.value}}
);

If checkbox1.value is false, then this evaluates to select * from users where (TRUE OR active = false), which returns all users. If checkbox1.value is true, this evaluates to select * from users where (FALSE OR active = true), which returns all active users.

Filtering users that have a first name match an element in an array

select * from users where first_name = ANY({{ ['George', 'Chris' ] }})

Security

In order to prevent SQL injection, Retool converts SQL queries into prepared statements. We defer to specific database driver for the exact behavior of the query. However, most databases do not support prepared statements that have dynamic column names or dynamic table names, since it's difficult to prevent SQL injection in those cases.

That means writing something like the below won't work, since the table name is dynamic, and Postgres doesn't support dynamic table names in prepared statements.

select * from {{textinput1.value === 'getUsers' ? 'users' : 'payments'}}

Instead, you should write 2 SQL statements: select * from users and select * from payments. Then in your Table component, you can render the correct SQL statement depending on the value of textinput1:

{{ textinput1.value === 'getUsers' ? users.data : payments.data }}

-->