Go: Persist Db Structs

So the reason for this example is that I was struggling to write a struct to the database. Specifically it was the r2.Point struct from github.com/golang/geo. It’s a simple 2d coordinate type with float64 X and Y values. That’s it. And I wanted to store it in the database.

The code for these examples can be found here.

To see an example of the error:

go run broken.go

You should see the following result:

2018/01/24 20:55:10 sql: converting argument $2 type: unsupported type r2.Point, a struct
exit status 1

The reason for this is that implementers of DB drivers need to support the following types:

• int64
• float64
• bool
• []byte
• string
• time.Time

Notice that r2.Point isn’t one of these?

So what are we going to do about it? Well the people behind the database/sql package thought about this a little and provided the Valuer interface to help with this conversion.

We need to make sure that we try writing a type that meets this interface so that the driver can do the conversion to a type that it understands.

Take a look in write.go to see how we approach this. If you’re new to Go, notice that we’ve used composition to write a type to add more behaviour to r2.Point. Here’s what was done:

// Location allows us to implement the Value() function for writing r2.Point to the database
type Location struct {
    r2.Point
}

// Value does the conversion to a string
func (p *Location) Value() (driver.Value, error) {
    data := p.String()
    log.Printf("Converted r2.Point to a string: %s", data)
    return data, nil
}

Run the file with:

go run write.go

Now we get this error:

2018/01/24 21:33:12 Converted r2.Point to a string: (1.000000000000, 2.000000000000)
2018/01/24 21:33:12 sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *main.Location
exit status 1

Again, package implementers to the rescue - we can use Scanner. The DB driver will use this interface to get our struct to do the converstion from the type that the DB persisted the data as to the type that we want. Wierdly, as we’re using sqlite3 this turns out to be []uint8. The last file, writeread.go implements the Scan function:

// Scan converts from a string to a Location
func (p *Location) Scan(v interface{}) error {
    var s string
    switch v.(type) {
    case string:
        s = v.(string)
    case []uint8:
        s = string(v.([]uint8))
    default:
        return errors.Errorf("Don't understand type %T", v)
    }

    _, err := fmt.Sscanf(s, "(%f,%f)", &(p.X), &(p.Y))
    if err != nil {
        return err
    }

    return nil
}

Run it with:

go run writeread.go