Build SQL update query for HTTP Patch request in Golang

It seems pretty straightforward to implement HTTP Patch request. The handler receives the request, unmarshals the body, validates the payload, sanitizes it and then sends it to persist layer for persisting into the Database. Let’s look into an example.

For example, we are updating a user resource.


type UserResource struct {
    FirstName   string `json:"first_name,omitempty" db:"first_name"`
    LastName    string `json:"last_name,omitempty"  db:"last_name"`
    Age         int    `json:"age,omitempty"        db:"age"`
    Email       string `json:"email,omitempty"      db:"email"`
}

We expect the user to send partial fields to update the resource in an HTTP Patch request. WE use omitempty tag in our UserResource, which means the user resource object might be missing multiple properties; thus, we cannot just prepare a statement with hard-coded fields and placeholders. I will have to build it dynamically, and this is where all the complexity started.

One easy solution is using an ORM. For example, GORM has a function: Updates. It supports updating DB with struct or map[string]interface{}, when updating with struct, it will only update non-zero fields by default and updating with map, you can pass selective fields.

db.Model(&user).Updates(map[string]interface{}{"first_name": "John", "last_name": "Doe", "age": 18})

What if you didn’t use any ORM in your project. Well, the only solution is now to make SQL update queries dynamically. You might use string manipulation or a complex reflect package to build the query. When I was looking for potential solution, I found an excellent open-source library: https://github.com/imdario/mergo

Mergo merged structs and maps in Golang. By using the transformer WithOverride, you can overwrite the values of the destination object.


if err := mergo.Merge(&dst, src, mergo.WithOverride); err != nil {
  // handling error
}

In our case, we can retrieve the user resource from DB, override the user resource with request data and persists the user resource object again.


var userRequest UserResource
json.Unmarshal([]byte(`{"LastName": "Smith"}`), &userRequest)

// Retrieve user resource from DB 
user, err := RetiveUser(userId) 


// Override user with partial userRequest object value
if err := mergo.Merge(&user, userRequest, mergo.WithOverride); err != nil {
	// handle error
}

// Update user in DB again
if err := UpdateUser(user); err != nil {
	// handle error
}

Inside UpdateUser function, you will not have to write SQL queries dynamically. You write a regular update query with all fields. Because by merging the userRequest object with the user object, you have all the fields and values.

 

Eftakhairul Islam

Hi, I'm Eftakhairul Islam, a passionate Software Engineer, Hacker and Open Source Enthusiast. I enjoy writing about technical things, work in a couple of startup as a technical advisor and in my spare time, I contribute a lot of open source projects.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Read previous post:
Upgrade to PHP 7.4 from PHP 7.2

This is the upgrade of my previous post . Previously, I showed how to upgrade to 7.2. Now, it's time...

Close