Skip to main content

Execute Query

Executes a MySQL SELECT query and returns the results as a table.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).
  • Transaction Id - The unique identifier of a transaction (optional). Use when the query is part of a database transaction.
  • SQL Query - The SQL SELECT statement to execute. Supports mustache template variables (e.g., {{userId}}).

Options​

  • Credentials - MySQL database credentials (optional). Use this if you want to execute the query without a Connect node.
  • Parameters - Additional connection string parameters (optional). Only used when credentials are provided.

Output​

  • Result - The query results returned as a table structure with columns and rows.

How It Works​

The Execute Query node executes a SQL SELECT statement and returns the results. When executed, the node:

  1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
  2. Optionally retrieves the transaction if a transaction ID is provided
  3. Processes mustache template variables in the SQL query using the message context
  4. Decodes any HTML entities in the rendered query
  5. Creates a MySqlCommand with the SQL query
  6. Executes the query using ExecuteReader
  7. Loads the results into a DataTable
  8. Converts the DataTable to a table structure with columns and rows
  9. Returns the table structure as output

Requirements​

  • Either: A valid connection ID from Connect node OR valid database credentials
  • Valid SQL SELECT statement
  • If using transactions, a valid transaction ID from Start Transaction node

Error Handling​

The node will return specific errors in the following cases:

  • ErrInvalidArg - Neither connection ID nor credentials provided
  • ErrNotFound - Connection ID not found in connections dictionary
  • ErrRuntime - Query execution failed (syntax error, table doesn't exist, etc.)

Usage Examples​

Basic SELECT Query​

SELECT * FROM users WHERE status = 'active'

Query with Template Variables​

SELECT ID, Name, Email, City
FROM users
WHERE City = '{{city}}' AND Age > {{minAge}}

In your message context:

{
  "city": "New York",
  "minAge": 25
}

JOIN Query​

SELECT
  o.OrderID,
  o.OrderDate,
  c.CustomerName,
  p.ProductName,
  od.Quantity
FROM orders o
INNER JOIN customers c ON o.CustomerID = c.CustomerID
INNER JOIN order_details od ON o.OrderID = od.OrderID
INNER JOIN products p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= '{{startDate}}'

Aggregate Query​

SELECT
  category,
  COUNT(*) as total_products,
  AVG(price) as avg_price,
  SUM(stock) as total_stock
FROM products
GROUP BY category
HAVING total_products > 5
ORDER BY avg_price DESC

Query with Subquery​

SELECT CustomerName, TotalOrders
FROM (
  SELECT
    c.CustomerName,
    COUNT(o.OrderID) as TotalOrders
  FROM customers c
  LEFT JOIN orders o ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName
) AS customer_stats
WHERE TotalOrders > {{minOrders}}

Common Use Cases​

  1. Data Retrieval - Fetch customer information, product details, order history
  2. Reporting - Generate sales reports, inventory summaries, user analytics
  3. Data Validation - Check if records exist, verify data integrity
  4. Dashboard Data - Retrieve metrics and KPIs for dashboards
  5. Export Operations - Extract data for export to Excel, CSV, or other formats
  6. Search Functionality - Implement search features in automation workflows
  7. Conditional Processing - Query data to determine next automation steps

Working with Query Results​

Access Results in JavaScript​

// Query results are in $item.result
const results = $item.result;

// Access table structure
const columns = results.columns;  // Array of column names
const rows = results.rows;        // Array of row objects

// Iterate through rows
rows.forEach(row => {
  console.log(`User: ${row.Name}, Email: ${row.Email}`);
});

// Get first row
const firstRow = rows[0];

// Count rows
const rowCount = rows.length;

// Check if results are empty
if (rows.length === 0) {
  console.log('No results found');
}

Use with Loop Node​

Connect the Query node to a Loop node to process each row:

Query → Loop (over $item.result.rows)
  └─ Process each row

Filter Results​

// Filter results in JavaScript
const activeUsers = $item.result.rows.filter(row => row.Status === 'active');
const highValueOrders = $item.result.rows.filter(row => row.Total > 1000);

Tips for Effective Use​

  • Template Variables - Use mustache syntax {{variable}} to make queries dynamic
  • SQL Editor - The node provides syntax highlighting for SQL statements
  • Query Optimization - Use WHERE clauses to limit results, add indexes to improve performance
  • Column Selection - Select only needed columns instead of using SELECT *
  • Limit Results - Use LIMIT clause for large datasets to prevent memory issues
  • Direct Credentials Mode - Use the optional Credentials field to query without a Connect node (creates temporary connection)
  • Transaction Support - Include queries in transactions for data consistency
  • Result Validation - Always check if results are empty before processing

Performance Best Practices​

Optimize Queries​

-- Good: Specific columns, WHERE clause, LIMIT
SELECT UserID, Name, Email
FROM users
WHERE Status = 'active'
LIMIT 100

-- Avoid: SELECT * without conditions
SELECT * FROM users

Use Indexes​

Ensure your MySQL tables have proper indexes on columns used in WHERE, JOIN, and ORDER BY clauses.

Pagination​

For large datasets, implement pagination:

SELECT * FROM products
LIMIT {{pageSize}} OFFSET {{offset}}
const pageSize = 100;
const page = 1;
const offset = (page - 1) * pageSize;

Connection Reuse​

Connect (once)
  ↓
Query 1
  ↓
Query 2
  ↓
Query 3
  ↓
Disconnect

Better than connecting/disconnecting for each query.

Common Errors and Solutions​

Query Execution Failed​

Error: ErrRuntime: Query execution failed

Common Causes:

  • Syntax error in SQL
  • Table or column doesn't exist
  • Invalid template variable values
  • Permission denied on table

Solutions:

  • Test query in MySQL client first
  • Verify table and column names (case-sensitive on Linux)
  • Check template variables are properly substituted
  • Ensure database user has SELECT permission

Connection Not Found​

Error: ErrNotFound: Connection not found

Solutions:

  • Ensure Connect node runs before Query node
  • Verify connection ID variable is correct
  • Or use the Credentials option to connect directly

Table Doesn't Exist​

Error: Table 'database.tablename' doesn't exist

Solutions:

  • Verify table name spelling and case
  • Check if database is specified in connection
  • Use SHOW TABLES; to list available tables
  • Ensure you're connected to the correct database

Column Not Found​

Error: Unknown column 'columnname' in 'field list'

Solutions:

  • Verify column name spelling and case
  • Use DESCRIBE tablename; to see column names
  • Check for typos in template variables

Advanced Examples​

Dynamic Table Name​

-- Note: Table names cannot be parameterized in prepared statements
-- Build query in JavaScript first:
const tableName = 'users_' + $item.year;
const query = `SELECT * FROM ${tableName} WHERE status = '{{status}}'`;
// Use query in SQL Query field

Multiple Template Variables​

SELECT * FROM products
WHERE category = '{{category}}'
  AND price BETWEEN {{minPrice}} AND {{maxPrice}}
  AND stock > {{minStock}}
  AND created_date >= '{{startDate}}'
ORDER BY {{sortColumn}} {{sortOrder}}
LIMIT {{limit}}

Conditional WHERE Clauses​

Build query dynamically based on conditions:

let whereClauses = [];
if ($item.city) whereClauses.push(`City = '{{city}}'`);
if ($item.minAge) whereClauses.push(`Age >= {{minAge}}`);
if ($item.status) whereClauses.push(`Status = '{{status}}'`);

const whereClause = whereClauses.length > 0
  ? 'WHERE ' + whereClauses.join(' AND ')
  : '';

const query = `SELECT * FROM users ${whereClause}`;

Date Range Queries​

SELECT
  DATE(created_at) as date,
  COUNT(*) as orders,
  SUM(total) as revenue
FROM orders
WHERE created_at BETWEEN '{{startDate}}' AND '{{endDate}}'
GROUP BY DATE(created_at)
ORDER BY date

Transaction Example​

Connect
  ↓
Start Transaction (get trx_id)
  ↓
Query 1 (read data, use trx_id)
  ↓
Query 2 (read related data, use trx_id)
  ↓
Commit (commit trx_id)
  ↓
Disconnect

See Also​