SQL Query

The SQL action allows you to write and run SQL scripts against SQL databases and Google Spreadsheets. UI component values and other variables can be added to the SQL query using the {{ui.input.value}} expression in the query body.

Basic query with variables example:

SELECT * FROM users 
WHERE name LIKE {{ ui.input.value }}

Using variables

Everything that comes in the {{ }} brackets is a JavaScript expression. This means that you can use variables, functions, and operators in the query body.

Adding '%' to a variable value to make it a wildcard search:

SELECT * FROM users 
WHERE name LIKE {{ '%' + ui.input.value + '%' }}

Note: you don't need to cover variables with quotes, variables will be sent separately, and quotes will be added automatically.

Default values can also be set in case a variable is not provided or empty:

SELECT * FROM users 
WHERE id > {{ ui.input.value || 1 }}

Tip: check the Payload section to see the query and variables that are sent to the database:

{
  query: "SELECT * FROM users WHERE id > ? ",
  params: {
    0: 1
  }

Insert query example (note - no quotes around the variables):

INSERT INTO users (name, email) 
values ({{ui.input.value}}, {{ui.emailInput.value}}); 

Insert with default values:

INSERT INTO users (name, email)
values ({{ui.input.value || 'John'}}, {{ui.emailInput.value || ''}});

Multi-select array value can be directly used with no need to convert it to a string:

SELECT * FROM users 
WHERE id in ({{ui.select.value}})

Date&Time variables

Sending dates to the query doesn't require any special formatting:

SELECT * FROM users 
WHERE created_at > {{ ui.datepicker.value }}

JS Date object will be automatically converted to SQL date format which is YYYY-MM-DD.

If your columns store date and time, and you only need to compare dates, wrap the date column with DATE() function:

SELECT * FROM users 
WHERE DATE(created_at) > {{ ui.datepicker.value }}

Use moment library to additionally convert JS Date to a specific format:

SELECT * FROM users 
WHERE created_at > {{ moment(ui.datepicker.value).format('YYYY-MM-DD HH:mm:ss') }}

Use JavaScript to generate queries

By default, all new data sources send the query and variables separately, so you need to additionally configure the data source to send the query as a pure string.

To enable this setting on a Data Source level:

• Go to the Data Sources Edit modal;

• Uncheck the Convert SQL queries to prepared statements checkbox.

After that, the queries will be compiled and sent as a single string, so you can use JavaScript to generate the query:

SELECT * FROM users 
WHERE 1=1
{{ ui.toggle.value ? 'AND paid = 1' : '' }}

Note: now the quotes will NOT be added automatically, so you need to add them manually and make sure the query is valid:

SELECT * FROM users 
WHERE name LIKE '%{{ ui.input.value }}%'

Data transformation

If the database returns its data in a different format than the components expect, enable transform result toggle or add a new Code step to transform the data.

Access an inner array object and map it to a new array:

return {{data}}.map(item => {
  return {
    id: item.id,
    name: item.name.toUpperCase(),
  };
});

Add a new key to the array of objects:

return {{data}}.map(item => {
  return {
    ...item, // put all the keys from the original object
    created_at: new Date(), // add a new property
  };
});

Filter array of objects, short version:

return {{data}}.filter(item => item.id > 10);

Multiline version:

return {{data}}.filter((item) => {
  return item.id > 10;
});

Writing SQL Queries