Golang lib/pq postgresql create a dynamic update statement

Question

I get a JSON from a client on the successful submit of user details.

Some element in the JSON can be skipped since they were not updated.

On the Golang server side, I have an equivalent struct defined.

The server successfully marshals the JSON bytes into the struct.

type user struct {
    Id       *int64  `json:",omitempty"`
    Name     *string `json:",omitempty"`
    Age      *int64  `json:",omitempty"`
}

How do I create a dynamic update statement depending on the available JSON elements?

For example, I might get the Id and Age alone. How can I create a update statement dynamically, like update user set age = $1 where id = $2 Another time it might be Id and Name.

So id can be empty? Seems strange to me; how would you know what to update. — RickyA

Luis Belloch Luis Belloch · Accepted Answer · 2014-09-14T22:26:36

In case you don't want to use an ORM, try a SQL generator like Squirrel, and check every argument before modifying the sql statement:

import (
    sq "github.com/lann/squirrel"
    "fmt"
)

statement := sq.Update("user").Where(sq.Eq{"Id": &u.Id})
if &u.Name != nil {
    statement = statement.Set("Name", "Jack")
}
sql, args, err := statement.ToSql()
fmt.Println(sql)

In some scenarios you may need to query the DB first to make sure the input data is not trying to explicitly set column ´Name´ to null (like update user set Name = null ...).

A different approach will be to use an 'ORM-ish' package like gorp or gorm, both should handle sql updates properly.

Golang lib/pq postgresql create a dynamic update statement

1 Answers