Read data from SQL databases

Learn how to write SQL queries in Retool to read data.

To read from a database, you can write SQL statements. For example, this query selects all active or inactive users, depending on whether a checkbox is checked.

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

Name the query getUsers, save it, and access its results elsewhere as getUsers.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. To access the data as an array of objects, use the formatDataAsArray method.

Dynamic table names

To prevent SQL injection, Retool converts SQL queries into prepared statements. The exact behavior of the query depends on your specific database driver. However, most databases do not support prepared statements that have dynamic column names or dynamic table names, because it's difficult to prevent SQL injection in those cases.

That means the following query does not work in PostgreSQL, because the table name is dynamic and PostgreSQL doesn't support dynamic table names in prepared statements.

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

Instead, you should write two queries:

select * from users

select * from payments

Then, you can reference the correct SQL statement depending on the value of textinput1:

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

Disable prepared statements

Admins can disable prepared statements on a resource by checking the Disable converting queries to prepared statements checkbox on the resource detail page. This turns off SQL injection protection and exposes the resource to SQL injection attacks, so it should be used with caution.

Prepared statement placeholder syntax

If you disable prepared statements, you may need to update queries which use JavaScript within {{ }}, because some databases expect unquoted prepared statement placeholders or perform type conversions within prepared statements.

For example, when prepared statements are enabled, the following PostgreSQL query does not need to quote the prepared statement placeholder.

select id, first_name, last_name
from users u
where u.id = {{ numberInput1.value }};

When prepared statements are disabled, the placeholder needs quotes.

select id, first_name, last_name
from users u
where u.id = '{{ numberInput1.value }}'

Consult your database documentation on prepared statements to confirm expected usage.