Overview
This node provides CRUD (Create, Read, Update, Delete) operations on MSSQL databases with additional support for relational and dependency data fetching. It supports two primary database connection types and can also query a PostgreSQL database to resolve dependencies.
Typical use cases include:
- Retrieving records from an MSSQL table, optionally joining related tables.
- Creating new records with optional GUID generation.
- Updating or deleting records by ID.
- Fetching related data from other tables or resolving dependencies via PostgreSQL queries.
For example, you might use this node to get a user record from an MSSQL database along with related address information from another table, or to create a new order record with a generated unique identifier.
Properties
| Name | Meaning |
|---|---|
| Primary | Selects the primary database connection type: Click or PayamGostar. |
| Table | The name of the table in the MSSQL database to operate on. |
| ID | The unique identifier of the record to get, update, or delete. Required for these operations. |
| Have Relation? | Boolean flag indicating if related tables should be joined when performing a "Get" operation. |
| Relations | Collection of relation definitions used when Have Relation? is true. Each relation specifies: - Field: The local field to join on. - Table: Related table name. - Rename Table: Optional alias. - Table Query Column: Column in related table to join. - Table Select Column: Columns to select from related table, comma-separated. |
| Have Dependencies? | Boolean flag indicating if dependencies should be resolved via PostgreSQL queries after a "Get" operation. |
| Dependencies | Collection of dependency definitions used when Have Dependencies? is true. Each dependency includes:- Table: Source table name. - Field: Source field name. - Force: Whether to retry if not found. - Need other fields in destination?: Boolean flag. - Destination Table: Destination table name (shown if previous is true). - Fields you want from destination: Comma-separated list of fields to fetch from destination. |
| Merge? | Boolean flag indicating whether to merge input properties with output data. Applies to all operations. |
Output
The node outputs JSON arrays representing the result of the database operation:
- For Get operations, it returns an array of objects representing rows fetched from the specified table, optionally including joined relations and resolved dependencies.
- For Create operations, it returns the inserted record's ID or GUID.
- For Update and Delete operations, it returns the affected rows or confirmation of deletion.
If Merge? is enabled, the output objects will include the original input properties merged with the database response under a props key.
No binary data output is produced by this node.
Dependencies
- Requires valid credentials for two MSSQL database connections (referred to as "Click" and "PayamGostar").
- Requires credentials for a PostgreSQL database to resolve dependencies.
- Uses helper functions for executing MSSQL and PostgreSQL queries.
- Environment must allow SQL query execution against these databases.
Troubleshooting
- Empty results on Get: Ensure the provided ID exists in the specified table. If using relations, verify join conditions and related table names are correct.
- Dependency resolution failures: If dependencies cannot be found, check PostgreSQL connection and that source/destination tables and fields are correctly configured. The node retries if
Forceis true but may timeout. - Insert errors: When creating records, ensure required fields are provided and data types match the database schema. If using GUID generation, confirm the database supports
NEWID(). - Update/Delete failures: Confirm the ID exists and the user has permissions to modify the table.
- Credential issues: Verify that all required credentials are set up properly in n8n and have access to the target databases.