Actions6
- Database Actions
Overview
This node executes SQL queries on a specified database table. It is designed to run custom SQL commands, allowing users to perform complex data retrieval or manipulation operations directly within their workflows. Common use cases include fetching filtered data sets, updating records based on dynamic conditions, or running transactional queries that require atomicity.
For example, you might use this node to:
- Retrieve all products with an ID less than 40 using a SELECT query.
- Execute multiple insert or update statements in a single transaction to ensure data consistency.
- Run parameterized queries to safely inject user input and prevent SQL injection attacks.
Properties
| Name | Meaning |
|---|---|
| Table | The target database table to work on. Can be selected from a list of available tables or specified by name. |
| Query | The SQL query string to execute. Supports n8n expressions and positional parameters ($1, $2, etc.) for safe parameter substitution. |
| Options | A collection of optional settings: |
| - Connection Timeout | Time in milliseconds allowed for establishing the database connection. |
| - Connections Limit | Maximum number of simultaneous connections to the database. Setting too high may cause performance issues or crashes. |
| - Query Batching | How queries are sent: as a single batch, independently per item, or inside a transaction (rollback on failure). |
| - Query Parameters | Comma-separated values used as parameters in the query, referenced as $1, $2, etc., to prevent SQL injection. |
| - Output Columns | For select operations, specify which columns to output, either from a list or via expression. |
| - Large Numbers Output | How to output large numeric types (NUMERIC, BIGINT): as numbers or text (recommended for very long numbers to avoid precision loss). |
| - Output Decimals as Numbers | Whether to output DECIMAL types as numbers instead of strings. |
| - Replace Empty Strings with NULL | Whether to convert empty string inputs to NULL, useful when importing data from spreadsheets. |
| - Output Query Execution Details | Whether to include detailed information about the executed query in the output, such as query text, type, and row counts. |
Output
The node outputs JSON data structured as follows:
- If the query returns results (e.g., SELECT), the output contains an array of rows under
result.resultsalong with metadata likenumberOfRows. - For no-result queries (e.g., UPDATE without returning data), the output confirms success without result rows.
- When detailed output is enabled, additional debug information about the executed query is included under a
debugfield. - The output merges the original input item’s JSON with the query results or status.
No binary data output is produced by this node.
Dependencies
- Requires a configured database connection with appropriate credentials and access rights.
- Supports MySQL dialect for SQL queries.
- No external API dependencies beyond the database itself.
- Proper configuration of connection limits and timeouts is recommended to avoid resource exhaustion.
Troubleshooting
- Connection Issues: Errors connecting to the database may occur if credentials are incorrect, the database is unreachable, or connection limits are exceeded. Verify credentials and network access.
- Query Syntax Errors: Malformed SQL will cause execution failures. Use the SQL editor hint and test queries separately to ensure correctness.
- Parameter Mismatch: Using query parameters ($1, $2, ...) without providing matching values in "Query Parameters" will cause errors. Ensure parameters count matches placeholders.
- Transaction Failures: When using transaction batching, any failing query rolls back all changes. Check individual queries for errors.
- Large Number Precision: Numeric columns with very large values may lose precision if output as numbers; switch to text output for these cases.
- Empty String Handling: If empty strings should be treated as NULL, enable the corresponding option to avoid unexpected data issues.