Do you miss an upsert function in SQL and/or Cosmos DB?

Upsert is a function that does Insert or Update with one call, depending on whether the item exists or not. It has different implementations in many databases. Upsert is very helpful in implementing Web API, as it reduces the calls to the server and the size of the request. In this blog you can find my read of how it should work.

Use cases:
1. If there is no such object in the database it should be created
2. Update only the properties that changed
3. Add item(s) to a collection
4. Update an item in a collection

As I mentioned last time, I believe that the code should be self-explanatory, so I’ll only briefly explain how to use it. You can find the full solution on GitHub: https://github.com/tachev/NoSQL2SQL.git

Take for example this object:

public class Item : IDocument
{
    public string Id { get; set; }
    public string Name { get; set; }
    public List<item> ListOfItems { get; set; }
}

Examples of how to implement the use cases:

1. If there is no such object in the database it should be created

Item item = new Item() { Name = "Item Name" };
var id = await database.UpsertItemAsync(item); 

The id will be generated and it can be used for the following upserts

2. Update only the properties that changed

Item item = new Item() { Id = id, Name = "New Name" };
await database.UpsertItemAsync(item);

3. Add item(s) to a collection

var updateItem = new Item
{
    Id = id,
    ListOfItems = new List<item> {
        new Item {Id = "10", Name = "New subitem", IntField = 10 }
    }
};

await database.UpsertItemAsync(updateItem);

4. Update an item in a collection

var updateItem = new Item
{
    Id = id,
    ListOfItems = new List<item> {
        new Item {Id = "10", Name = "Updated subitem", IntField = 10 }
    }
};

await database.UpsertItemAsync(updateItem);

That’s it. Simple, yet very powerful function.

Let me know if you have any questions or comments on how I can improve the blog and/or the source code.