Actions6
- Database Actions
Overview
This node provides an interface to insert data into a database table. It is designed to facilitate adding new rows to a specified table within a database, supporting both automatic and manual mapping of input data to table columns. This node is beneficial in scenarios where you need to programmatically add records to a database as part of an automation workflow, such as logging events, storing form submissions, or syncing data from other sources.
Practical examples:
- Automatically inserting user registration details into a "users" table.
- Adding transaction records into a financial database.
- Logging sensor data into a time-series database table.
Properties
| Name | Meaning |
|---|---|
| Table | The target database table where the data will be inserted. You can select from a list of available tables or specify the table name directly. |
| Data Mode | Defines how input data maps to table columns: - Auto-Map Input Data to Columns: Automatically matches input property names to column names. - Map Each Column Manually: Specify each column and its value manually. |
| Notice (auto-map) | Informational message shown when using auto-mapping mode, reminding that input field names must exactly match the table column names. |
| Values to Send | When using manual mapping mode, this collection lets you specify pairs of columns and their corresponding values to insert. |
| Options | A collection of additional settings: - Connection Timeout: Time in milliseconds to wait for database connection. - Connections Limit: Max number of simultaneous connections. - Query Batching: How queries are sent (single, independently per item, or in a transaction). - Priority: Insert priority (low or high). - Replace Empty Strings with NULL: Converts empty strings to NULL. - Skip on Conflict: Skips rows causing unique constraint conflicts instead of throwing errors. - Other options related to query execution and output formatting (some shown only for other operations). |
Output
The node outputs JSON data representing the result of the insert operation. Depending on the configuration:
- If detailed output is enabled, the output includes information about the executed query, query type, number of rows affected, and raw results.
- For each input item, the output JSON merges the original input data with any result metadata.
- The output is structured as an array of items, each containing a
jsonobject with the insertion result or confirmation. - No binary data output is indicated by the source code or properties.
Dependencies
- Requires a connection to a MySQL-compatible database.
- Needs appropriate credentials (API key or authentication token) configured in n8n to access the database.
- Relies on internal methods for listing tables and columns to populate dropdowns dynamically.
- No external services beyond the database itself are required.
Troubleshooting
Common Issues:
- Mismatched column names when using auto-mapping mode can cause insert failures or incorrect data insertion. Use an "Edit Fields" node before this node to rename fields if necessary.
- Connection timeouts or limits may cause failures; adjust the connection timeout and limit options accordingly.
- Unique constraint violations can cause errors unless "Skip on Conflict" is enabled.
- Large numbers might be truncated or misrepresented if not handled properly; consider output options for large-format numbers if relevant.
Error Messages:
- Errors related to missing or invalid table names: Ensure the table name is correctly specified or selected.
- Constraint violation errors: Enable "Skip on Conflict" to avoid failures on duplicate keys.
- Connection errors: Check database credentials and network connectivity.
- Query syntax errors: Verify that manual column-value mappings are correct and valid for the target database schema.