更新有效负载中包含的列

Question

I have a struct mapping to mysql table as below and want to update fields which are sent in a PUT request payload

type Notification struct {
 Id                 int64     `json:"id"`
 Type               NotificationType
 Subject            string    `json:"confidence"`
 Body               string    `json:"body"`
 CreatedDate        time.Time `json:"created_dt"`
 CreatedBy          int64     `json:"created_by"`
 ParentNotification int64     `json:"parent_id"`
 IsExpired          bool      `json:"expired"`
}

Currently I am asking all the fields to be included in payload even though the data is not changed and updating all columns using query with db.Exec(query) statement.

Is there any way where I can ask client to include only fields which are changed in payload and update only those fields?

{
 "body" : "new body"
 "subject" : "new subject"
}

I am working with below packages in db side.

"database/sql"
_ "github.com/go-sql-driver/mysql"

Answer 1

One way to do what you want, that is, have the client send only data that they want to change, is to have one extra "param" type per each "db table" type. So, for example, given your Notification type you would have a NotificationParams type like so:

type NotificationParams struct {
    Id      int64 `json:"id"`
    // use pointers so that a field that is not present in the json
    // will result in a nil pointer value, which tells you that no
    // update is needed for that column, it also allows the client
    // to send an empty string if they want to delete the content of
    // some column, e.g. Body.
    Type    *NotificationType
    Subject *string `json:"confidence"`
    Body    *string `json:"body"`
    // and other fields that you want to allow the client change
}

And then in your handler or whereever you can do something along these lines:

params := &NotificationParams{} // unmarshal from json
notif := &Notification{} // read from db using params.Id

// change only those fields that were provided
if params.Type != nil {
    notif.Type = *params.Type
}
if params.Subject != nil {
    notif.Subject = *params.Subject
}
if params.Body != nil {
    notif.Body = *params.Body
}

// do some validation...

// if ok save notif using mysql UPDATE

---

Update

If you want to avoid having to write a large number of if statements you could write a couple of "setter" or "apply" functions (whichever name you like more) that do that for you, one for each type that you want to support, e.g. one for string, one for time.Time, etc.

Take this function for example:

// The variadic list of functions can be used to transform (think
// sanitization, normalization, etc.) the value before setting
// it to the dst pointer.
func ApplyStr(dst, src *string, fns ...func(string) string) {
    if src != nil {
        s := *src
        for _, fn := range fns {
            s = fn(s)
        }

        *dst = s
    }
}

And then you would use such a function like in this playground example.