Use Javascript with SQL Queries
Learn how to use Javascript and SQL Queries to manage your data
The simplest way to have access to your MySQL / PostgreSQL / Google Sheets data in UI Bakery is to use 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. This means that you can use a Variables selector to reference the Components properties. Type in a {{ somewhere inside your query and select a variable you'd like to use.
SELECT customers.*, COUNT(orders.orderNumber) as orders_count FROM customers
LEFT JOIN orders on orders.customerNumber = customers.customerNumber
WHERE customers.customerNumber = {{ ui.input.value }}
GROUP BY customers.customerNumber;
If you use an Input value and want to trigger your Action when the Input's value changes, set the Action into the Triggers setting of a Component.
On Enter input trigger
When using a string or a date variable, refer to UI Bakery variable with SQL query quotes:
SELECT customers.*, COUNT(orders.orderNumber) as orders_count FROM customers
LEFT JOIN orders on orders.customerNumber = customers.customerNumber
WHERE customers.name = '{{ ui.input.value }}'
GROUP BY customers.customerNumber;

Sending date in an SQL format

Anytime you use a JavaScript Date object in your SQL queries, UI Bakery will automatically convert it to a SQL friendly date format, so that this query
SELECT * FROM orders
WHERE orders.orderDate >= "{{ui.datepicker.value)}}";
is converted into:
SELECT * FROM orders
WHERE orders.orderDate >= "2022-03-05";
However, if you need custom date or date and time, you'll need to use the following snippet:
{{ moment(ui.datepicker.value).format('YYYY-MM-DD HH:mm:ss') }}

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 items.map(item => {
return `('${item.name}', ${item.age})`;
}).join(',');
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.