Writing SQL Queries

Learn how to use Javascript and SQL Queries to manage your data
The simplest way to access your MySQL / PostgreSQL / Google Sheets data in UI Bakery is by using the SQL Query action step.

Firing a SQL query to a database

  1. 1.
    Connect a MySQL, PostgreSQL, or Google Sheets data source.
  2. 2.
    Create an action.
  3. 3.
    Choose a SQL Query action step and select a data source.
  4. 4.
    Add your query, click Run Action and check the Result tab.
  5. 5.
    Add a component. It will be automatically configured to show the data.
Use Cmd + Esc (Mac) or Ctrl + Space (Windows/Linux) to autocomplete tables / columns.

Using UI values

A SQL query in UI Bakery is a simple string, so you can use a variables selector to reference the component's properties and other 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.
For example, here's how you can add '%' to a variable value to make it a wildcard search:
WHERE name LIKE {{ '%' + ui.input.value + '%' }}
You don't need to cover variables with quotes by default, variables will be sent separately, and quotes will be added automatically. 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.
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 tab to see the query and variables that are sent to the database:
query: "SELECT * FROM users WHERE id > ? ",
params: {
0: 1
Multiselect array value can be directly used with no need to convert it to a string:
WHERE id in ({{}})
Once the action is complete, you need to either use an Auto trigger setting, so that the action will be triggered any time the referenced UI component values are changed, or assign the action to a component trigger:
On Enter input trigger

Sending date in an SQL format

Sending dates to the query doesn't require any special formatting:
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:
WHERE DATE(created_at) > {{ ui.datepicker.value }}
Use moment library to additionally convert JS Date to a specific format:
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 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:
{{ ui.toggle.value ? 'AND paid = 1' : '' }}
Now the quotes will NOT be added automatically, so you need to add them manually and make sure the query is valid:
WHERE name LIKE '%{{ ui.input.value }}%'

Executing multiple insert queries

Sometimes you'll need to insert multiple items in a row. You can achieve it with the following small JavaScript snippet.
First, add a Code step that will take your data and generate the values of the INSERT:
const items = [{ name: 'first', age: 30 }, { name: 'second', age: 34 }];
return => {
return `('${}', ${item.age})`;
Based on the data, this will result in a similar string:
(string): "('first', 30),('second', 34)"
Now, add a SQL query as a second step with the rest of the INSERT statement. It'll also reference the {{data}} property (the result of the previous step execution) that has the generated values of the query:
INSERT INTO customers (name, age) VALUES {{ data }};
SQL query with a dynamic values block
It's how you can generate parts of the query dynamically and implement more features in your apps faster.