Four database operations may look similar because each changes data. Insert, update, upsert, and delete, however, carry different business assumptions. Choosing the wrong one can create duplicates, modify the wrong record, or delete a much broader set than intended.
Four operations, four intentions
An insert creates a new record and should fail when the same unique key already exists. An update changes records matching a condition without creating a new one. An upsert updates a record matching a conflict key or creates it when absent. A delete removes matching records and demands the greatest care around its conditions.
| Operation | Purpose | Common risk |
|---|---|---|
| Insert | Record a new event or entity | Duplicates when a request is retried |
| Update | Change a known existing record | Broad condition or missing record |
| Upsert | Synchronize using stable identity | A wrong conflict key merges entities |
| Delete | Remove data under a policy | Bulk deletion or lost audit history |
Start from the business event, not the endpoint name
A new order commonly needs an insert because a duplicate should be visible as a conflict. Product-catalog synchronization may use upsert when the owning system supplies a genuinely stable product code. A customer address change fits an update using customer identity. Data removal should consider soft deletion or an inactive status when history remains important.
Do not select upsert automatically to avoid a duplicate-key error. That error may expose a request sent twice or inconsistent business identity. An upsert can hide the issue by replacing previous data without a deliberate decision.
Conflict keys and WHERE clauses are primary safeguards
Update and delete need specific conditions. NitroNet rejects mutation operations without a WHERE condition so a workflow cannot accidentally alter an entire table. A zero-row result is also routed as not found, allowing the workflow to choose a suitable response or follow-up action.
Upsert needs a conflict key that represents domain identity, such as an external_product_id unique to a source. A technical ID regenerated on every request cannot identify the previous record. Inspect database constraints and agree which system owns identity before selecting the key.
Example: synchronizing customers from a CRM
- Validate the inbound contract.
Ensure external ID, name, and mandatory fields have correct types.
- Normalize values.
Trim whitespace and normalize phone or capitalization without altering identity.
- Select the conflict key.
Use the CRM-owned ID plus tenant identity when data spans organizations.
- Run the upsert.
Map only columns owned by the CRM so local fields are not overwritten.
- Handle outcomes.
Make success, not found for other operations, duplicate, and failure explicit paths.
Invoice recording has a different pattern. An insert protected by an idempotency key or business key is often safer than a blind upsert, because silently changing transaction values can create reconciliation problems.
Guardrails before activation
- Preview source-to-destination mapping, including type compatibility.
- Test existing, absent, duplicate, null, and out-of-constraint records.
- Ensure WHERE conditions or conflict keys come from validated fields.
- Use a single-connection transaction when several writes must succeed together.
- Move through Sandbox before Production and use clearly identifiable test data.
Create a field-ownership matrix when two systems can change the same entity. A CRM may own customer name and email, while an ERP owns credit limit. A CRM upsert should update only its columns. Without this rule, a technically successful synchronization can overwrite another system's decision. Include change source, timestamp, and version when concurrent conflicts are possible.
Delete needs additional checks for foreign keys, audit, retention, and downstream effects. Many domains are safer with an inactive status and a separate authorized purge process. When hard deletion is required, preview criteria and test zero, one, and many matches. Monitor affected rows after deployment; a sudden change may indicate a broken filter or an identity mismatch from the source.
Limitations that should be explicit
External actions are at-least-once. Insert needs an idempotency strategy; upsert needs the right conflict key; delete needs retention and authorization policies.
NitroNet separates Write DB, Update DB, Upsert DB, and Delete DB nodes so intent is visible in the workflow. Guardrails prevent basic mistakes, but data ownership remains a decision for business and database teams. Read about the agent-centric architecture and how NitroNet workflows operate to understand the execution path.