CRUD API: How do you specify which fields to update?

Question

Let's say you have some kind of data structure, which is persisted in some kind of database. For simplicity, let's call this data structure Person. You are now tasked to design a CRUD API, which allows other applications to create, read, update and delete Persons. For simplicity, let's assume that this API is accessed via some kind of web service.

For the C, R and D parts of CRUD, the design is simple. I'll use C#-like functional notation - the implementation could be SOAP, REST/JSON, or something else:

class Person {
    string Name;
    DateTime? DateOfBirth;
    ...
}

Identifier CreatePerson(Person);
Person GetPerson(Identifier);
void DeletePerson(Identifier);

What about update? The natural thing to do would be

void UpdatePerson(Identifier, Person);

but how would you specify which fields of Person to update?

---

Solutions that I could come up with:

• You could always require a complete Person to be passed, i.e. the client would do something like this to update the date of birth:

  p = GetPerson(id);
  p.DateOfBirth = ...;
  UpdatePerson(id, p);
  

  However, that would require some sort of transactional consistency or locking between the Get and the Update; otherwise, you could overwrite some other change done in parallel by some other client. This would make the API much more complicated. In addition, it's error prone, since the following pseudo-code (assuming a client language with JSON support)

  UpdatePerson(id, { "DateOfBirth": "2015-01-01" });
  

  -- which looks correct -- would not only change DateOfBirth but also reset all other fields to null.

• You could ignore all fields that are null. However, how would you then make a difference between not changing DateOfBirth and deliberately changing it to null?

• Change the signature to void UpdatePerson(Identifier, Person, ListOfFieldNamesToUpdate).

• Change the signature to void UpdatePerson(Identifier, ListOfFieldValuePairs).

• Use some feature of the transmission protocol: For example, you could ignore all fields not contained in the JSON representation of the Person. However, that usually requires parsing the JSON yourself and not being able to use the built-in features of your library (e.g. WCF).

None of the solutions seems really elegant to me. Surely, this is a common problem, so what is the best-practice solution used by everybody?

Answer 1

If you don't have tracking of changes as requirement on this object (e.g. "User John changed name and birth date"), simplest would be to override whole object in DB with one you receive from consumer. This approach would involve slightly more data being send through wire, but you are avoiding read before update.

If you have activity tracking requirement. Your world is much more complicated and you will need to design how to store information about CRUD actions and how to intercept them. That is the world you don't want to dive into if you don't have such requirement.

As per overriding values by separate transactions, I would suggest to do research around optimistic and pessimistic locking. They mitigate this common scenario:

1. Object is read by user1
2. Object is read by user2
3. Object written by user1
4. Object written by user2 and overwritten changes by user1

Each user has different transaction, therefore standard SQL with this. Most common is optimistic locking (also mentioned by @SJuan76 in comment about versions). Your version your record in DB and during write you first take a look into DB if versions match. If versions don't match, you know somebody updated the object in the meantime, and you need to response with error message to consumer about this situation. Yes, you have to show this situation to user.

Notice that you need to read actual record from DB before writing it (for optimistic locking version comparison), so implementing delta logic (write only changed values) may not require additional read query before write.

Putting in delta logic highly depends on contract with consumer, but notice that easiest for consumer is to construct full payload instead of delta also.

Answer 2

We have a PHP API at work. For updates if a field isn't sent in the JSON object it gets set to NULL. Then it passes everything to the stored procedure. The stored procedure attempts to update every field with field = IFNULL(input, field). So if just 1 field is in the JSON object only that field is updated. To explicitly empty a set field we have to have field = '', the DB then updates the field with either empty string or the default value of that column.

Answer 3

Specify updated fields list in Query String.

PUT /resource/:id?fields=name,address,dob Body { //resource body }

Implement merge stored data with model from request body:

private ResourceModel MergeResourceModel(ResourceModel original, ResourceModel updated, List<string> fields)
{
    var comparer = new FieldComparer();

    foreach (
            var item in
            typeof (ResourceModel).GetProperties()
                    .Where(p => p.CustomAttributes.All(a => a.AttributeType != typeof (JsonIgnoreAttribute))))
    {
        if (fields.Contains(item.Name, comparer))
        {
            var property = typeof (ResourceModel).GetProperty(item.Name);
            property.SetValue(original, property.GetValue(updated));
        }
    }

    return original;
}