In database nomenclature, an upsert (update-or-insert) is an operation that results in the specified record being updated if it exists; and, if not, a new record being inserted.
Microsoft Dataverse’s Web API PATCH operation preforms upserts by default. It’s important to be aware of what this means and, when desired, how to change it, so you’re not inadvertently upset by an unexpected upsert.
What does the below request do?
PATCH {{webApiUrl}}contacts(00000000-0000-0000-0000-000000000001)
(include the "always include" headers)
{
"firstname": "Bob",
"lastname": "Brown"
}
If you said that it updates the contact with the primary key of 00000000-0000-0000-0000-000000000001, setting its first and last names, you are correct if a contact with that ID exists. However, if it doesn’t—thanks to Dataverse’s identity insert behavior—the request will result in a new record being created with the given primary key, first and last name values. What you expected to be an update turned into an insert.
Is upserting a good behavior? It depends.
If you’re modify a record that should already exist, then you want an update or an error, not an upsert. Say a user just deleted the record you’re trying to update. You’d like your update attempt to fail, because you want it to respect the deletion. You don’t want your operation to recreate the record. Upsert is not desired here.
On the other hand, say an external system is leveraging Dataverse’s identity insert feature to push data into Dataverse. If a Dataverse record in exists with the matching primary key, it should be updated; if it doesn’t, it should be created. In a case like this, upsert behavior can be perfect!
Preventing Upserts
Update Only
To keep a PATCH that you intend to be an update from turning into an insert, set header If-Match to “*” on the request.
The wildcard value in this header, in essence, makes the request conditioned on the record identified by the URL having any ETag value. In order for this condition to be satisfied, a record must exist. If it does not, the condition is not met, so the request will fail with a 404 Not Found error. Adding this header forces the operation to update an existing record or return an error, not be an update or insert.
PATCH {{webApiUrl}}contacts(00000000-0000-0000-0000-000000000001)
If-Match: *
(include the other "always include" headers, too)
{
"firstname": "Bob",
"lastname": "Brown"
}
Insert Only
Keeping an insert from turning into an update is easier: Don’t use PATCH; instead POST the new record’s data (in Dataverse, a POST to a table only inserts, never updates).
Alternately, you can use PATCH but force it to only insert by setting an If-None-Match: * header. This condition will only be satisfied if a record identified by the key in the URL does not exist. If one does, the condition is not met, failing the operation with a 412 Precondition Failed error.
PATCH {{webApiUrl}}contacts(00000000-0000-0000-0000-000000000001)
If-None-Match: *
(include the "always include" headers, too)
{
"firstname": "Bob",
"lastname": "Brown"
}
Conclusion
The moral of the lesson: When PATCHing, upsert is the default behavior. When this isn’t desired, always include either header If-Match: * (only perform an update) or If-None-Match: * (only perform an insert).
Note
Don’t forget to include the “always include” headers with every request sent to the Web API.