Actions6
- Database Actions
Overview
This node provides an "Insert or Update" (upsert) operation for a database table. It allows users to insert new rows or update existing rows in a specified table based on matching column values. This is useful when you want to ensure data consistency by either adding new records or updating existing ones without creating duplicates.
Common scenarios include:
- Synchronizing data from external sources where some records may already exist.
- Maintaining up-to-date records in a database without manually checking for existence.
- Automating data ingestion pipelines that require conditional inserts or updates.
For example, if you have a customer database and receive daily updates with new and existing customers, this node can automatically insert new customers and update details of existing ones based on a unique identifier like email or customer ID.
Properties
| Name | Meaning |
|---|---|
| Table | The target database table to perform the upsert operation on. You can select from a list of tables or specify the table name directly. |
| Data Mode | How to map input data to table columns: - Auto-Map Input Data to Columns: Automatically maps input property names to table columns (input fields must match column names). - Map Each Column Below: Manually specify each column and its value. |
| Column to Match On | The unique column used to find existing rows to update. Rows with matching values in this column will be updated; others will be inserted as new rows. |
| Value of Column to Match On | When using manual mapping mode, specifies the value to match on the chosen column to identify rows to update. |
| Values to Send | In manual mapping mode, defines the columns and their corresponding values to insert or update. |
| Options | Additional settings including: - Connection Timeout (ms) - Connections Limit - Query Batching mode (single query, independent queries per item, or transaction) - Replace Empty Strings with NULL - Other advanced options |
Output
The node outputs JSON data representing the result of the upsert operation:
- For each input item, it returns confirmation of success or detailed query execution information if enabled.
- The output includes the number of affected rows and optionally the executed query details.
- If no rows are affected, the output indicates no result.
- Binary data output is not applicable 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.
- The node depends on internal methods for loading table and column metadata dynamically.
Troubleshooting
Common Issues:
- Mismatched input field names when using auto-mapping mode can cause failures or incorrect data updates.
- Specifying a non-unique or incorrect "Column to Match On" can lead to unexpected updates or multiple rows being affected.
- Database connection timeouts or limits exceeded due to high concurrency or misconfiguration.
- Empty strings not replaced with NULL might cause constraint violations depending on schema.
Error Messages:
- Errors related to unique constraint violations can occur if "skip on conflict" is not enabled and duplicate keys are inserted.
- Connection errors indicate issues with database credentials or network connectivity.
- Invalid column or table names will cause query failures; verify names via the UI or expressions.
Resolutions:
- Use the "Edit Fields" node before this node to rename input properties to match table columns exactly when using auto-map.
- Ensure the "Column to Match On" is unique and indexed in the database.
- Adjust connection timeout and limit options according to your environment.
- Enable "Replace Empty Strings with NULL" if working with spreadsheet data containing empty cells.
Links and References
- n8n Expressions Documentation
- MySQL INSERT ... ON DUPLICATE KEY UPDATE Syntax
- n8n MySQL Node Documentation (for general usage patterns and query parameterization)