Skip to main content

MySQL

Add a query​

You can add a query step by adding a workflow from the page list.
Select the MySQL data source from the workflow step sidebar, and the query input window will appear.
For more information on adding a data source, please refer to the MySQL data source guide.

mysql

Basic query​

Hops supports all common SQL syntax. You can use all SQL commands to query or modify data.

-- Basic SELECT query
SELECT * FROM customer;

-- Filtering with WHERE clause
SELECT id, name FROM customer WHERE advertisement_agreed;

-- Joining tables with JOIN
SELECT payment.id, payment.created_at, customer.name
FROM payment
JOIN payment_method ON payment.payment_method_id = payment_method.id
JOIN customer ON payment_method.customer_id = customer.id;

Using template syntax​

There are two ways to use templates:

  1. {{}}: You can use JavaScript expressions. You can use the state and variables inside Hops.
  2. {{$ }}: It is automatically SQL escape processed according to the type.
    For example, the code {{$ 'abc' }} is interpreted as SELECT 'abc' instead of SELECT abc.
-- Basic usage
SELECT {{ 'name' }} FROM customer; -- Same as SELECT name FROM customer. It queries the name column of the customer table.
SELECT {{$ 'name' }} FROM customer; -- Same as SELECT 'name' FROM customer. The result is a string type value of `'name'`.

-- Conditional WHERE clause
SELECT * FROM customer
WHERE
  ({{$ searchInput.value }} = '' OR name LIKE '%{{ searchInput.value }}%')
  AND advertisement_agreed = {{$ advertisementAgreedCheckbox.checked }}

-- Using array
SELECT * FROM payment
WHERE status IN (
  {{ statusMultiSelect.values.length == 0 ? 'NULL' : statusMultiSelect.values.map(v => '${v}').join(',') }}
)

SQL Escape​

To prevent SQL injection attacks and ensure data type safety, appropriate escape processing is required. Hops supports automatic SQL escape processing through the {{$ }} syntax.

-- Basic string escape
SELECT * FROM customer WHERE name = {{$ textField1.value }}
-- -> SELECT * FROM customer WHERE name = 'textField1.value'

-- Number type escape
SELECT * FROM payment WHERE points_rate > {{$ numberField1.value }}
-- -> SELECT * FROM payment WHERE points_rate > 100

-- Date type escape
SELECT * FROM payment WHERE created_at = {{$ dateField1.value }}
-- -> SELECT * FROM payment WHERE created_at = 'dateField1.value'

Using variables​

You can use variables in the following ways:

1. Using page variables​

Page variables are used in the page.variableName format. For more details, please refer to the Page Variables Guide.

SELECT * FROM customer WHERE id = {{page.userId}};

2. Using workflow inputs​

Workflow inputs are used in the inputs.variableName format. For more details, please refer to the Workflow Variables Guide.

SELECT * FROM payment
WHERE created_at BETWEEN CAST({{$ inputs.startDate }} AS DATE) AND CAST({{$ inputs.endDate }} AS DATE);

3. Using the result of the previous step​

The result of the previous step is used in the outputs.stepName format. For more details, please refer to the Workflow Step Results Guide.

SELECT * FROM payment
WHERE payment_method_id = {{outputs.step1.data[0].payment_method_id}};

4. Using the state value of a component​

The state value of a component in the page is used in the componentName.statusName format. For more information, please refer to the Components document.

SELECT * FROM customer
WHERE name LIKE '%{{searchInput.value}}%';

Query result​

When you run an SQL query, the result is returned in the data: Record<string, unknown>[] structure.

  • data: The top-level object containing the query results
  • Record<string, unknown>[]: An array of records where the column names are keys and the corresponding values are values

Using the result value​

-- First SQL step query
SELECT id, name FROM customer;

-- Result structure
{
  data: [
    { id: 1, name: "John Doe" },
    { id: 2, name: "Jane Doe" }
  ]
}

-- Second JavaScript step query
return outputs.step1.data.map(item => item.name);

Attention​

  1. To prevent SQL injection attacks, appropriate escape processing is required in the template.
  2. If you need complex conditional statements or data processing, it is recommended to run the JavaScript step first and then use the result in the SQL query.
  3. When dealing with large amounts of data, it is recommended to use appropriate LIMIT and pagination.

The following is an example of an SQL query for searching products with a search term and category filter:

SELECT payment.id, payment.created_at, customer.name
FROM payment
JOIN payment_method ON payment.payment_method_id = payment_method.id
JOIN customer ON payment_method.customer_id = customer.id
WHERE
  (
    customer.name LIKE '%{{searchInput.value}}%' OR
    payment.id LIKE '%{{searchInput.value}}%'
  )
  AND payment.created_at BETWEEN CAST({{$ inputs.startDate }} AS DATE) AND CAST({{$ inputs.endDate }} AS DATE)
ORDER BY payment.created_at DESC
LIMIT {{$ paymentTable1.page.limit }}
OFFSET {{$ paymentTable1.page.offset }}

This example includes a search and pagination.