SQL Query

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

Action configuration

To run an SQL Query action, you will first need to select your SQL or Google Sheets data source or connect one, if you haven't done it yet. Then, you'll simply have to select the SQL Query step and specify your query.

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.

An example of a basic query with variables may be the following:

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

Using variables in a query

Here are a few other examples of using variables in an SQL Query👇

• If you're using dollar quoting in SQL queries, remember that SQL skips replacements inside such sections to avoid SQL injection risks.

SELECT $$ 1 $$

Thus, we recommend avoiding this syntax for security reasons.

• You can add % to a variable value to make a wildcard search:

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

You don't need to put variables in quotes - they will be sent separately and quotes will be added automatically.

• You can set default values if a variable is empty or not provided:

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

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

Check the action's Payload tab to see the query and variables sent to the database.

• You can insert a query example:

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

Note that there're no quotes around variables.

• You can insert a query with default values:

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

• A multi-select array value can be used directly without the 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 - you can send them just like this:

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

The JavaScript Date object will be automatically converted to the SQL date format - YYYY-MM-DD.

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

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

You can also use the moment.js library to additionally convert the JavaScript Date to a specific format:

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

More info about using moment.js in UI Bakery you can find here👇

Using JS libraries

Using JavaScript to generate queries

By default, all new data sources send the query and variables separately, so you will have to configure your data source to send the query as a single string.

This setting can be enabled on the Data source level. You need to go to the Data sources page and click the pencil icon next to the data source you want to edit. There, deselect the Convert SQL queries to prepared statements checkbox and update your data source.

Be aware that disabling this setting can cause SQL injections - use it with caution.

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

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

❗Now the quotes will NOT be added automatically - you will have to add them manually and make sure the query is valid:

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

Executing multiple insert queries

With the setting above disabled, you can execute multiple insert queries. This is how you can do that👇

1. First, create a JavaScript Code action that will take your data and generate the values of INSERT:

const items = [{ name: 'first', age: 30 }, { name: 'second', age: 34 }];
return items.map(item => {
  return `('${item.name}', ${item.age})`;
}).join(',');

Depending on your data, this may result in a similar string:

(string): "('first', 30),('second', 34)"

1. Now, add an SQL Query action as the second step with the rest of the INSERT statement.

INSERT INTO users (name, age) VALUES {{ data }};

It'll reference the {{data}} property (the result of the previous step execution) that has the generated values of the query.

Though this method is still possible, we would recommend using the Bulk Create Rows action step instead to avoid any possible issues when the Convert SQL queries to prepared statements setting is disabled.

Data transformation

If the database returns its data in a different format than expected for the components, you can modify it. For example, you can turn on the Transform result toggle in the action's settings or add a new JavaScript Code step to transform the data.

Here are some examples of possible data transformations:

• 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 an array of objects (short version):

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

Multiline version:

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