Use Javascript and 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.
Creating a SQL Table in 25 seconds
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.
1
SELECT customers.*, COUNT(orders.orderNumber) as orders_count FROM customers
2
LEFT JOIN orders on orders.customerNumber = customers.customerNumber
3
WHERE customers.customerNumber = {{ ui.input.value }}
4
GROUP BY customers.customerNumber;
Copied!
Variable reference
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:
1
SELECT customers.*, COUNT(orders.orderNumber) as orders_count FROM customers
2
LEFT JOIN orders on orders.customerNumber = customers.customerNumber
3
WHERE customers.name = '{{ ui.input.value }}'
4
GROUP BY customers.customerNumber;
Copied!

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:
1
const items = [{ name: 'first', age: 30 }, { name: 'second', age: 34 }];
2
return items.map(item => {
3
return `('${item.name}', ${item.age})`;
4
}).join(',');
Copied!
Based on the data, this will result in a similar string:
1
(string): "('first', 30),('second', 34)"
Copied!
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:
1
INSERT INTO customers (name, age) VALUES {{ data }};
Copied!
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.
Last modified 1mo ago