How to set nullable/optional property of database object to NULL in backend?

Question

We are building API which mainly passes database objects back and forth between user and database, so the main flow of information is quite basic:

Table (view) <-- ORM --> C#/Java/etc. Objects <-- JSON serializer --> JSON objects <-- HTTP --> User

We are using ASP.NET Core with Entity Framework Core if it matters, but I believe this is at least somewhat technology agnostic question.

Most of our backend objects mapped from DB do contain lots of optional properties, meaning that they are nullable in the database. Of course, this would imply that those properties should also be Nullable/Optional/WhateverInYourLanguage in the object-oriented world.

Now the problem arises when the user would want to set one existing property of some object to null. We could then receive maybe following kind of update JSON from the user (lots of details omitted and the structure is stupid, don't mind about that):

{
    "TargetType": "Foo",
    "Id": 65,
    "NullableProperty": null
}

So the user would want to set the value of property NullableProperty to null for an object with the type of Foo and with Id of 65. All well and good so far.

First, we were thinking of generating an object of type Foo from received JSON and updating existing database object with that, but this would lead to a situation where all of the created object's properties would be null, so how would we know what properties to update?

Other solution could maybe be to have update function which would have all optional property parameters set to null, but this would lead to the same problem, would null mean that value for that property was not provided or user-provided null value?

Then we thought that user would have to send whole data object through json, which would contain all the needed values and they would be just copied to the database object. This won't do for multiple reasons (many times users are provided just parts of an object, object sizes may be quite large, there is a risk of losing data, etc.)

One other way which we are not too eager to implement is to reserve some special value to mean null for each data type. For example date 9999-99-99 could mean null for dates, number MinInt would mean null for integers and "" would be null for strings, etc. This could work maybe at least for some extent, but even now it makes me cringe to think about this.

Is there some good design pattern for these kinds of situations? I have given this now some thought, but I cannot seem to reach better conclusions than listed above.

Answer 1

If your JSON API allows partial objects to be sent for updates (as in, a request to update object 65 of type Foo contains only a subset of the properties of type Foo), then the most sensible approach is that the properties that are not mentioned in the JSON description will not be modified.

This means that if you get an update request with the JSON document

{
    "TargetType": "Foo",
    "Id": 65,
    "NullableProperty": null
}

then you process it like this:

1. Retrieve the object of type Foo with Id 65 from the database
2. Modify NullableProperty to set it to NULL, leaving all other properties unchanged
3. Update the database with the modified object

This means that if a property should be set to NULL, then it must be explicitly mentioned on the JSON interface.