How to use sqlx.types in modl models for json postgres type?

[rmrio]

I want to use postgresql json type for keep golang maps.

type Card struct {
    Number string
    Name   string
    CVV    int
}

type Person struct {
    Id    int
    Name  string
    Cards map[int]Card    // want to save this filed in postgres json
}

I know that i need to implement some sql.Scanner and driver.Valuer interfaces, but they already implemented for type JsonText in sqlx.types package. The JsonText type looks like what i need.

So, give me a right way please.

[rmrio]

https://github.com/jmoiron/sqlx/pull/117 PR were helpful. Also map[int]Card is not supported by json.Marshal, need to use map[string]Card instead.

The code, may be helpful for someone:

type Card struct {
    Number string
    Name   string
    CVV    int
}

type JsonArray map[string]Card

type Person struct {
    Id    int
    Name  string
    Cards JsonArray
}

func (p JsonArray) Value() (driver.Value, error) {
    if len(p) == 0 {
        return nil, nil
    }
    return json.Marshal(p)
}

func (p JsonArray) Scan(src interface{}) error {
    v := reflect.ValueOf(src)
    if !v.IsValid() || v.IsNil() {
        return nil
    }
    if data, ok := src.([]byte); ok {
        return json.Unmarshal(data, &p)
    }
    return fmt.Errorf("Could not not decode type %T -> %T", src, p)
}

func main() {

    db, err := sql.Open("postgres", "user=modl_user password=qwerty dbname=modl_test sslmode=disable")
    if err != nil {
        fmt.Println(err)
    }

    dbmap := modl.NewDbMap(db, modl.PostgresDialect{})
    dbmap.TraceOn("", log.New(os.Stdout, "modltest: ", log.Lmicroseconds))
    table := dbmap.AddTableWithName(Person{}, "persons").SetKeys(true, "Id")
    column := table.ColMap("cards")
    column.SetSqlType("json")

    err = dbmap.CreateTablesIfNotExists()
    //defer dbmap.DropTables()
    if err != nil {
        fmt.Println(err)
    }
    person := &Person{}
    person.Name = "Alex Smith"
    person.Cards = make(JsonArray)
    visa := Card{"4432-3433-2311-2343", "Alex Smith", 235}
    mc := Card{"5535-5443-2320-0009", "Jina Smith", 431}
    person.Cards["0"] = visa
    person.Cards["1"] = mc
    err = dbmap.Insert(person)
    if err != nil {
        fmt.Println(err)
    }
    fmt.Println("Person id", person.Id)
}

[rmrio]

Ok, saving to db working good, selecting one field working good too. But i get an error when I select all rows from a table. The error is non-struct dest type struct with >1 columns (3) How i can fix that? Should I implement another interface? The full example code is here

type File struct {
    Stat
    Id   int64
    Name string
}

type Stat struct {
    Bytes int64 `json:"b"`
}

func (p Stat) Value() (driver.Value, error) {
    return json.Marshal(p)
}

func (p Stat) Scan(src interface{}) error {
    v := reflect.ValueOf(src)
    if !v.IsValid() || v.IsNil() {
        return nil
    }
    if data, ok := src.([]byte); ok {
        return json.Unmarshal(data, &p)
    }
    return fmt.Errorf("Could not not decode type %T -> %T", src, p)
}

func main() {
    logger := log.New(os.Stdout, "", log.Lshortfile)
    db, err := sql.Open("postgres", "user=modl_user password=qwerty dbname=modl_test sslmode=disable")
    if err != nil {
        logger.Fatalln(err)
    }
    dbmap := modl.NewDbMap(db, modl.PostgresDialect{})
    dbmap.TraceOn("", log.New(os.Stdout, "modltest: ", log.Lmicroseconds))

    dbmap.AddTableWithName(File{}, "files").SetKeys(true, "Id")

    err = dbmap.CreateTablesIfNotExists()
    if err != nil {
        logger.Fatalln(err)
    }
    defer dbmap.DropTables()

    // populate
    for i := 0; i < 10; i++ {
        f := File{}
        f.Name = fmt.Sprintf("abcfile%v", i)
        err = dbmap.Insert(&f)
        if err != nil {
            logger.Println(err)
        }
    }

    results := []File{}
    err = dbmap.Select(&results, "SELECT * FROM files")
    if err != nil {
        // err: non-struct dest type struct with >1 columns (3)
        logger.Fatalln(err)
    }
}

[alexflint]

It may be because your Scan function takes a non-pointer receiver. Your call to Unmarshal then unmarshals into a copy of Stat that is local to the Scan function. Try func (p *Stat) Scan(src interface{}) error { ... }