GoLang, REST, PATCH and building an UPDATE query

Question

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?

Answer 1

I recently had same problem. about PATCH and looking around found this article. It also makes references to the RFC 5789 where it says:

The difference between the PUT and PATCH requests is reflected in the way the server processes the enclosed entity to modify the resource identified by the Request-URI. In a PUT request, the enclosed entity is considered to be a modified version of the resource stored on the origin server, and the client is requesting that the stored version be replaced. With PATCH, however, the enclosed entity contains a set of instructions describing how a resource currently residing on the origin server should be modified to produce a new version. The PATCH method affects the resource identified by the Request-URI, and it also MAY have side effects on other resources; i.e., new resources may be created, or existing ones modified, by the application of a PATCH.

e.g:

[
    { "op": "test", "path": "/a/b/c", "value": "foo" },
    { "op": "remove", "path": "/a/b/c" },
    { "op": "add", "path": "/a/b/c", "value": [ "foo", "bar" ] },
    { "op": "replace", "path": "/a/b/c", "value": 42 },
    { "op": "move", "from": "/a/b/c", "path": "/a/b/d" },
    { "op": "copy", "from": "/a/b/d", "path": "/a/b/e" }
]

This set of instructions should make it easier to build the update query.

EDIT

This is how you would obtain sql tags but you will have to use reflection:

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

sp := "sort of string"
r := Resource{Description: &sp}
rt := reflect.TypeOf(r) // reflect.Type
rv := reflect.ValueOf(r) // reflect.Value

for i := 0; i < rv.NumField(); i++ { // Iterate over all the fields
    if !rv.Field(i).IsNil() { // Check it is not nil

        // Here you would do what you want to having the sql tag.
        // Creating the query would be easy, however
        // not sure you would execute the statement

        fmt.Println(rt.Field(i).Tag.Get("sql")) // Output: description
    }
}   

I understand you don't want to use reflection, but still this may be a better answer than the previous one as you comment state.

EDIT 2:

About the allocation - read this guide lines of Effective Go about Data structures and Allocation:

// Here you are allocating an slice of 0 length with a capacity of n
sqlPatch.Fields = make([]string, 0, n)
sqlPatch.Args = make([]interface{}, 0, n)

With make(Type, Length, Capacity (optional))

Consider the following example:

// newly allocated zeroed value with Composite Literal 
// length: 0
// capacity: 0
testSlice := []int{}
fmt.Println(len(testSlice), cap(testSlice)) // 0 0
fmt.Println(testSlice) // []

// newly allocated non zeroed value with make   
// length: 0
// capacity: 10
testSlice = make([]int, 0, 10)
fmt.Println(len(testSlice), cap(testSlice)) // 0 10
fmt.Println(testSlice) // []

// newly allocated non zeroed value with make   
// length: 2
// capacity: 4
testSlice = make([]int, 2, 4)
fmt.Println(len(testSlice), cap(testSlice)) // 2 4
fmt.Println(testSlice) // [0 0]

In your case, may want to the following:

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

// With this or simple omit the capacity in make above
sqlPatch.Fields = []string{}
sqlPatch.Args = []interface{}{}

// The allocation will go as follow: length - capacity
testSlice := []int{} // 0 - 0
testSlice = append(testSlice, 1) // 1 - 2
testSlice = append(testSlice, 1) // 2 - 2   
testSlice = append(testSlice, 1) // 3 - 4   
testSlice = append(testSlice, 1) // 4 - 4   
testSlice = append(testSlice, 1) // 5 - 8