golang decode JSON request in nested struct and insert in DB as blob

Question

I have a nested struct which I am using to decode JSON request.

type Service struct {
    ID       string `json:"id,omitempty" db:"id"`
    Name     string `json:"name" db:"name"`
    Contract struct {
        ServiceTime int    `json:"service_time"`
        Region      string `json:"region"`
    } `json:"contract" db:"contract"`
}

I am using blob type to store Contract in MySQL. To make it work , I would be needing to create a different struct with Contract as string to insert in DB. Can this be done in better way by using single struct only or is there any other elegant way ?

Are you using some orm or just database/sql, and what driver are you using? — mkopriva
– mkopriva, Commented Nov 16, 2017 at 18:11

mkopriva · Accepted Answer · 2017-11-17 09:10:27Z

11

This depends on what you use to talk to the database, but if you're using database/sql and a driver that provides support for this you can have your Contract type implement the Valuer interface.

type Service struct {
    ID       string    `json:"id,omitempty" db:"id"`
    Name     string    `json:"name" db:"name"`
    Contract Contract `json:"contract" db:"contract"`
}

type Contract struct {
    ServiceTime int    `json:"service_time"`
    Region      string `json:"region"`
}

func (c *Contract) Value() (driver.Value, error) {
    if c != nil {
        b, err := json.Marshal(c)
        if err != nil {
            return nil, err
        }
        return string(b), nil
    }
    return nil, nil
}

And then when you're executing the query just pass in the Service.Contract field and the driver should call the Value method.

sql := // INSERT INTO ...
svc := // &Service{ ...
db.Exec(sql, svc.ID, svc.Name, svc.Contract)

And to be able to retrieve the blob back from db and unmarshal it back into Contract you can have it implement the Scanner interface, again, if a type implements this the driver is expected to call it.

func (c *Contract) Scan(src interface{}) error {
    var data []byte
    if b, ok := src.([]byte); ok {
        data = b
    } else if s, ok := src.(string); ok {
        data = []byte(s)
    }
    return json.Unmarshal(data, c)
}

// ...

sql := // SELECT * FROM ...
svc := // &Service{ ...
db.QueryRow(sql, 123).Scan(&svc.ID, &svc.Name, &svc.Contract)

edited Nov 17, 2017 at 9:10

answered Nov 16, 2017 at 18:25

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

nebi Over a year ago

can u convert a struct type to string ? How ? Why in Service strict Contract has to be Contract pointer type ?

mkopriva Over a year ago

The Contract field on Service doesn't have to be a pointer. Depending on what format you want the blob to be (json, xml, gob, etc) you turn Contract into a string by, for example, using one of the encoding packages.

mkopriva Over a year ago

@nebi I've updated the code examples to show how you would turn your Contract instance into a string and back using json. Note that I do not know what Go type is expected by the db driver you're using for blob columns, maybe string maybe []byte... so you'll have to experiment a little.

nebi Over a year ago

I have implemented like this play.golang.org/p/Fb-VsWRAFa . But if I change *Contract to Contract, sql driver is throwing exception :sql: converting Exec argument $3 type: unsupported type main.Contract, a struct goroutine 22 [running]: runtime/debug.Stack(0xc42004f720, 0x7e7620, 0xc420077620) /usr/local/go/src/runtime/debug/stack.go:24 +0x79 github.com/ant0ine/go-json-rest/rest.(*RecoverMiddleware).MiddlewareFunc.func1.1(0xbf0d50, 0xc02380, 0xc4201021e0) /home/vikash/go_packages/src/github.com/ant0ine/go-json-rest/rest/recover.go:41 +0x6e panic(0x7e7620, 0xc420077620)

mkopriva Over a year ago

ok one sec. By the way, your scan implementation doesn't do anything because you are unmarshaling into res and not assigning that to c.

|

Collectives™ on Stack Overflow

golang decode JSON request in nested struct and insert in DB as blob

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related