GoLang, REST, PATCH and building an UPDATE query

since few days I was struggling on how to proceed with PATCH request in Go REST API until I have found an article about using pointers and omitempty tag which I have populated and is working fine. Fine until I have realized I still have to build an UPDATE SQL query.

My struct looks like this:

type Resource struct {
    Name        *string `json:"name,omitempty"        sql:"resource_id"`
    Description *string `json:"description,omitempty" sql:"description"`
}

I am expecting a PATCH /resources/{resource-id} request containing such a request body:

{"description":"Some new description"}

In my handler I will build the Resource object this way (ignoring imports, ignoring error handling):

var resource Resource
resourceID, _ := mux.Vars(r)["resource-id"]

d := json.NewDecoder(r.Body)
d.Decode(&resource)

// at this point our resource object should only contain
// the Description field with the value from JSON in request body

Now, for normal UPDATE (PUT request) I would do this (simplified):

stmt, _ := db.Prepare(`UPDATE resources SET description = ?, name = ? WHERE resource_id = ?`)
res, _ := stmt.Exec(resource.Description, resource.Name, resourceID)

The problem with PATCH and omitempty tag is that the object might be missing multiple properties, thus I cannot just prepare a statement with hardcoded fields and placeholders... I will have to build it dynamically.

And here comes my question: how can I build such UPDATE query dynamically? In the best case I'd need some solution with identifying the set properties, getting their SQL field names (probably from the tags) and then I should be able to build the UPDATE query. I know I can use reflection to get the object properties but have no idea hot to get their sql tag name and of course I'd like to avoid using reflection here if possible... Or I could simply check for each property it is not nil, but in real life the structs are much bigger than provided example here...

Can somebody help me with this one? Did somebody already have to solve the same/similar situation?

SOLUTION:

Based on the answers here I was able to come up with this abstract solution. The SQLPatches method builds the SQLPatch struct from the given struct (so no concrete struct specific):

import (
    "fmt"
    "encoding/json"
    "reflect"
    "strings"
)

const tagname = "sql"

type SQLPatch struct {
    Fields []string
    Args   []interface{}
}

func SQLPatches(resource interface{}) SQLPatch {
    var sqlPatch SQLPatch
    rType := reflect.TypeOf(resource)
    rVal := reflect.ValueOf(resource)
    n := rType.NumField()

    sqlPatch.Fields = make([]string, 0, n)
    sqlPatch.Args = make([]interface{}, 0, n)

    for i := 0; i < n; i++ {
        fType := rType.Field(i)
        fVal := rVal.Field(i)
        tag := fType.Tag.Get(tagname)

        // skip nil properties (not going to be patched), skip unexported fields, skip fields to be skipped for SQL
        if fVal.IsNil() || fType.PkgPath != "" || tag == "-" {
            continue
        }

        // if no tag is set, use the field name
        if tag == "" {
            tag = fType.Name
        }
        // and make the tag lowercase in the end
        tag = strings.ToLower(tag)

        sqlPatch.Fields = append(sqlPatch.Fields, tag+" = ?")

        var val reflect.Value
        if fVal.Kind() == reflect.Ptr {
            val = fVal.Elem()
        } else {
            val = fVal
        }

        switch val.Kind() {
        case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
            sqlPatch.Args = append(sqlPatch.Args, val.Int())
        case reflect.String:
            sqlPatch.Args = append(sqlPatch.Args, val.String())
        case reflect.Bool:
            if val.Bool() {
                sqlPatch.Args = append(sqlPatch.Args, 1)
            } else {
                sqlPatch.Args = append(sqlPatch.Args, 0)
            }
        }
    }

    return sqlPatch
}

Then I can simply call it like this:

type Resource struct {
    Description *string `json:"description,omitempty"`
    Name *string `json:"name,omitempty"`
}

func main() {
    var r Resource

    json.Unmarshal([]byte(`{"description": "new description"}`), &r)
    sqlPatch := SQLPatches(r)

    data, _ := json.Marshal(sqlPatch)
    fmt.Printf("%s\n", data)
}

You can check it at Go Playground. The only problem here I see is that I allocate both the slices with the amount of fields in the passed struct, which may be 10, even though I might only want to patch one property in the end resulting in allocating more memory than needed... Any idea how to avoid this?

Solution

Alright, I think the solution I used back in 2016 was quite over-engineered for even more over-engineered problem and was completely unnecessary. The question asked here was very generalized, however we were building a solution that was able to build its SQL query on its own and based on the JSON object or query parameters and/or Headers sent in the request. And that to be as generic as possible.

Nowadays I think the best solution is to avoid PATCH unless truly necessary. And even then you still can use PUT and replace the whole resource with patched property/ies coming already from the client - i.e. not giving the client the option/possibility to send any PATCH request to your server and to deal with partial updates on their own.

However this is not always recommended, especially in cases of bigger objects to save some C0² by reducing the amount of redundant transmitted data. Whenever today if I need to enable a PATCH for the client I simply define what can be patched - this gives me clarity and the final struct.

Note that I am using a IETF documented JSON Merge Patch implementation. I consider that of JSON Patch (also documented by IETF) redundant as hypothetically we could replace the whole REST API by having one single JSON Patch endpoint and let clients control the resources via allowed operations. I also think the implementation of such JSON Patch on the server side is way more complicated. The only use-case I could think of using such implementation is if I was implementing a REST API over a file system...

So the struct may be defined as in my OP:

    type ResourcePatch struct {
        ResourceID  some.UUID `json:"resource_id"`
        Description *string `json:"description,omitempty"`
        Name        *string `json:"name,omitempty"`
    }

In the handler func I'd decode the ID from the path into the ResourcePatch instance and unmarshall JSON from the request body into it, too.

Sending only this

{"description":"Some new description"}

to PATCH /resources/<UUID>

I should end up with with this object:

ResourcePatch
    * ResourceID {"UUID"}
    * Description {"Some new description"}

And now the magic: use a simple logic to build the query and exec parameters. For some it may seem tedious or repetitive or unclean for bigger PATCH objects, but my reply to this would be: if your PATCH object consists of more than 50% of the original resource' properties (or simply too many for your liking) use PUT and expect the clients to send (and replace) the whole resource instead.

It could look like this:

    func (s Store) patchMyResource(r models.ResourcePatch) error {
        q := `UPDATE resources SET `
        qParts := make([]string, 0, 2)
        args := make([]interface{}, 0, 2)

        if r.Description != nil {
            qParts = append(qParts, `description = ?`)
            args = append(args, r.Description)
        }

        if r.Name != nil {
            qParts = append(qParts, `name = ?`)
            args = append(args, r.Name)
        }

        q += strings.Join(qParts, ',') + ` WHERE resource_id = ?`
        args = append(args, r.ResourceID)

        _, err := s.db.Exec(q, args...)

        return err
    }

I think there's nothing simpler and more effective. No reflection, no over-kills, reads quite good.