How to implement a PATCH with `database/sql`?

Let’s say you have a basic API (GET/POST/PATCH/DELETE) backed by an SQL database.

The PATCH call should only update the fields in the JSON payload that the user sends, without touching any of the other fields.

Imagine the table (let's call it sample) has id, string_a and string_b columns, and the struct which corresponds to it looks like:

type Sample struct {
  ID      int    `json:"id"`
  StringA string `json:"stringA"`
  StringB string `json:"stringB"`
}

Let's say the user passes in { "stringA": "patched value" } as payload. The json will be unmarshalled to something that looks like:

&Sample{
 ID: 0,
 StringA: "patched value",
 StringB: "",
}

For a project using database/sql, you’d write the query to patch the row something like:

// `id` is from the URL params
query := `UPDATE sample SET string_a=$1, string_b=$2 WHERE id=$3`
row := db.QueryRow(query, sample.StringA, sample.StringB, id)
...

That query would update the string_a column as expected, but it’d also update the string_b column to "", which is undesired behavior in this case. In essence, I’ve just created a PUT instead of a PATCH.

My immediate thought was - OK, that’s fine, let’s use strings.Builder to build out the query and only add a SET statement for those that have a non-nil/empty value.

However, in that case, if a user wanted to make string_a empty, how would they accomplish that?

Eg. the user makes a PATCH call with { "stringA": "" } as payload. That would get unmarshalled to something like:

&Sample{
  ID: 0,
  StringA: "",
  StringB: "",
}

The “query builder” I was theorizing about would look at that and say “ok, those are all nil/empty values, don’t add them to the query” and no columns would be updated, which again, is undesired behavior.

I’m not sure how to write my API and the SQL queries it runs in a way that satisfies both cases. Any thoughts?

I think reasonable solution for smaller queries is to build UPDATE query and list of bound parameters dynamically while processing payload with logic that recognizes what was updated and what was left empty.

From my own experience this is clear and readable (if repetitive you can always iterate over struct members that share same logic or employ reflection and look at struct tags hints, etc.). Every (my) attempt to write universal solution for this ended up as very convoluted overkill supporting all sorts of corner-cases and behavioral differences between endpoints.

func patchSample(s Sample) {
	var query strings.Builder
	params := make([]interface{}, 0, 2)

    // TODO Check if patch makes sense (e.g. id is non-zero, at least one patched value provided, etc.

	query.WriteString("UPDATE sample SET")

	if s.StringA != "" {
		query.WriteString(" stringA = ?")
		params = append(params, s.StringA)
	}

	if s.StringB != "" {
		query.WriteString(" stringB = ?")
		params = append(params, s.StringB)
	}

	query.WriteString(" WHERE id = ?")
	params = append(params, s.ID)

	fmt.Println(query.String(), params)
	//_, err := db.Exec(query.String(), params...)
}

func main() {
	patchSample(Sample{1, "Foo", ""})
	patchSample(Sample{2, "", "Bar"})
	patchSample(Sample{3, "Foo", "Bar"})
}

EDIT: In case "" is valid value for patching then it needs to be distinguishable from the default empty value. One way how to solve that for string is to use pointer which will default to nil if value is not present in payload:

type Sample struct {
	ID      int     `json:"id"`
	StringA *string `json:"stringA"`
	StringB *string `json:"stringB"`
}

and then modify condition(s) to check if field was sent like this:

if s.StringA != nil {
	query.WriteString(" stringA = ?")
	params = append(params, *s.StringA)
}

See full example in playground: https://go.dev/play/p/RI7OsNEYrk6

For what it's worth, I solved the issue by:

1. Converting the request payload to a generic map[string]interface{}.
2. Implementing a query builder that loops through the map's keys to create a query.

Part of the reason I went this route is it fit all my requirements, and I didn't particularly like having *strings or *ints laying around.

Here is what the query builder looks like:

func patchQueryBuilder(id string, patch map[string]interface{}) (string, []interface{}, error) {
	var query strings.Builder
	params := make([]interface{}, 0)

	query.WriteString("UPDATE some_table SET")
	for k, v := range patch {
		switch k {
		case "someString":
			if someString, ok := v.(string); ok {
				query.WriteString(fmt.Sprintf(" some_string=$%d,", len(params)+1))
				params = append(params, someString)
			} else {
				return "", []interface{}{}, fmt.Errorf("could not process some_string")
			}
		case "someBool":
			if someBool, ok := v.(bool); ok {
				query.WriteString(fmt.Sprintf(" some_bool=$%d,", len(params)+1))
				params = append(params, someBool)
			} else {
				return "", []interface{}{}, fmt.Errorf("could not process some_bool")
			}
		}
	}

	if len(params) > 0 {
		// Remove trailing comma to avoid syntax errors
		queryString := fmt.Sprintf("%s WHERE id=$%d RETURNING *", strings.TrimSuffix(query.String(), ","), len(params)+1)
		params = append(params, id)
		return queryString, params, nil
	} else {
		return "", []interface{}{}, nil
	}
}

Note that I'm using PostgreSQL, so I needed to provide numbered parameters to the query, eg $1, which is what params is used for. It's also returned from the function so that it can be used as follows:

// Build the patch query based on the payload
query, params, err := patchQueryBuilder(id, patch)
if err != nil {
	return nil, err
}

// Use the query/params and get output
row := tx.QueryRowContext(ctx, query, params...)