Actions6
- Database Actions
Overview
This node provides a way to perform SELECT operations on a database table. It allows users to query data from a specified table with flexible filtering, sorting, and output options. This is useful when you want to retrieve specific rows or subsets of data from a database for further processing in an n8n workflow.
Common scenarios include:
- Fetching records that meet certain conditions (e.g., all users from a specific city).
- Retrieving limited or paginated results.
- Sorting data by one or more columns.
- Selecting distinct rows to avoid duplicates.
- Handling large numeric values safely as text or numbers.
Practical example:
- You have a customer database and want to get all customers who signed up after a certain date, sorted by their last name, returning only the first 100 results.
Properties
| Name | Meaning |
|---|---|
| Table | The database table to select data from. Can be chosen from a list or specified by name. |
| Return All | Whether to return all matching rows or limit the number of results. |
| Limit | Maximum number of rows to return if "Return All" is false. |
| Select Rows (where) | Conditions to filter rows. Multiple conditions can be combined. Each condition includes: Column, Operator (e.g., equal, not equal, LIKE, greater than), and Value. If no conditions are set, all rows are selected. |
| Combine Conditions | How to combine multiple conditions: AND (all must be true) or OR (at least one must be true). |
| Sort | Rules to sort the results by one or more columns, each with direction ASC or DESC. |
| Options | Additional settings including: • Connection Timeout (ms) • Connections Limit • Query Batching mode (single, independently per item, or transaction) • Output Columns (which columns to include) • Large Numbers Output format (numbers or text) • Output Decimals as Numbers (boolean) • Select Distinct (remove duplicate rows) • Detailed Output (show executed query details) • Replace Empty Strings with NULL (boolean) |
Output
The node outputs JSON data containing the selected rows from the database. The structure includes:
- An array of objects where each object represents a row from the database.
- Each row contains key-value pairs corresponding to column names and their values.
- If detailed output is enabled, additional debug information about the executed query is included.
- Large numeric values can be output as strings to preserve precision.
- Decimal types can optionally be output as numbers instead of strings.
No binary data output is indicated for this operation.
Dependencies
- Requires a connection to a MySQL-compatible database.
- Needs appropriate credentials (API key or authentication token) configured in n8n to access the database.
- Uses internal methods to fetch available tables and columns dynamically for property options.
Troubleshooting
- Empty results: Check if the table name is correct and conditions are properly set. If no conditions are set, all rows are returned.
- Timeouts: Adjust the connection timeout option if queries take too long.
- Incorrect data types: Use the "Output Large-Format Numbers As" and "Output Decimals as Numbers" options to handle numeric precision issues.
- Query errors: Enable "Detailed Output" to see the exact SQL query executed, which helps diagnose syntax or logic errors.
- Performance issues: Limit the number of returned rows or use proper indexing on the database side to improve query speed.
- Connection limits: Avoid setting too high a connections limit to prevent database overload.