MySQL Visual Query Builder¶
Builds and runs MySQL SELECT queries from a visual-style configuration, including multi-table JOINs, filters, grouping, ordering, and pagination. It can return results as plain text with JSON, or export them as HTML, XLSX, and PDF tables.
Usage¶
Use this node when you want to query a MySQL database without writing SQL directly. Provide a credentials URI and configure the main table, selected columns, JOINs, filters, grouping, and sorting. Choose an output format to get human-readable text, HTML for viewing, or XLSX/PDF for sharing. Typically used after establishing access to a MySQL database and before downstream processing or reporting.
Inputs¶
| Field | Required | Type | Description | Example |
|---|---|---|---|---|
| credentials_path | True | STRING | Database connection URI used to authenticate and route the request. | mysql://username: |
| timeout | True | INT | Request timeout in seconds for the query execution. | 60 |
| main_table | True | STRING | Primary table to select from. | users |
| database | True | STRING | Database name containing the main table. | sales_db |
| selected_columns | True | STRING | Comma-separated column list to select, or * for all columns. | users.id, users.name, orders.total |
| join_config | True | STRING | JSON array describing JOINs. Each item requires 'type', 'table', and 'on'. Optional 'database' defaults to 'mysql'. | [{"type": "INNER", "table": "orders", "database": "sales_db", "on": "users.id = orders.user_id"}] |
| where_conditions | False | STRING | SQL WHERE conditions (omit the WHERE keyword). | users.active = 1 AND users.signup_date >= '2024-01-01' |
| group_by_columns | False | STRING | Comma-separated column list for GROUP BY. | users.country |
| having_conditions | False | STRING | SQL HAVING conditions (omit the HAVING keyword). | COUNT(orders.id) > 5 |
| order_by_columns | False | STRING | Comma-separated ORDER BY clauses. Add DESC for descending. | users.signup_date DESC, users.id ASC |
| limit_count | True | INT | Maximum number of rows to return. | 100 |
| offset_count | True | INT | Number of rows to skip (for pagination). | 0 |
| output_format | True | text \| html \| xlsx \| pdf \| all | Choose text (plain text + JSON), html (HTML table), xlsx (Excel as base64), pdf (PDF as base64), or all (returns all available formats). | text |
Outputs¶
| Field | Type | Description | Example |
|---|---|---|---|
| result | STRING | Human-readable summary of the query results (plain text). | Visual Query Results (100 rows): Row 1: id: 1 \| name: Alice \| total: 125.00 ... |
| json_result | STRING | Raw result payload serialized as a JSON string, typically including rows and row count. | {"data": [{"id": 1, "name": "Alice", "total": 125.0}], "row_count": 1} |
| html_table | STRING | HTML table representation of the results when output_format is html or all. Empty for text-only output. | ... |
| xlsx_data | STRING | Base64-encoded XLSX data when output_format is xlsx or all. Empty for other formats. | |
| pdf_data | STRING | Base64-encoded PDF data when output_format is pdf or all. Empty for other formats. |
Important Notes¶
- Credentials URI required: Provide a valid MySQL-style URI in credentials_path (e.g., mysql://username:
@host:port/db). - JOIN config format: join_config must be valid JSON array. Each item must include 'type', 'table', and 'on'. Optional 'database' defaults to 'mysql'.
- Allowed JOIN types: INNER, LEFT, RIGHT, FULL, CROSS. CROSS JOIN ignores 'on'.
- SQL fragments only: where_conditions, having_conditions, order_by_columns should be SQL fragments without leading keywords (WHERE, HAVING, ORDER BY).
- Pagination: limit_count and offset_count translate to LIMIT and OFFSET in the final query.
- Output formats: 'text' returns text and JSON; 'html' returns HTML + JSON; 'xlsx' returns XLSX(base64) + JSON; 'pdf' returns PDF(base64) + JSON; 'all' returns all formats.
Troubleshooting¶
- Invalid credentials URI: Ensure credentials_path starts with mysql:// and includes host, database, and valid placeholders for auth.
- Malformed join_config: If parsing fails, validate that join_config is valid JSON and an array of objects with 'type', 'table', and 'on'.
- Unsupported JOIN type: Use only INNER, LEFT, RIGHT, FULL, or CROSS.
- Empty results: If no rows are returned, check filters, LIMIT/OFFSET, and selected_columns.
- Export fields are empty: Ensure output_format matches the expected export; e.g., choose html for HTML table or xlsx/pdf for file exports.
- SQL errors: Validate column names, table names, and SQL fragments in where_conditions/having_conditions/order_by_columns.