Storing a Go Slice in a MySQL Column
I recently had to write code to store a []string
in a MySQL JSON
column. Because the MySQL driver interface speaks bytes, this can be easily done by marshaling the JSON into []byte
before sending it to the databse and unmarshaling the JSON into the []string
after retrieving it from the database. I looked into ways to make this code more convenient, since that's a decent amount of work for each query.
Finally, I cobbled together the right interfaces. Now, I can run:
// insert
stringSlice := []string{"first", "second"}
query := "INSERT INTO table_name (string_slice) VALUES (?))"
param := Array(&stringSlice)
_, err := d.db.Exec(query, param)
// select
query := "SELECT string_slice FROM table_name WHERE table_name_id = 1"
var stringSlice []string
err := d.db.QueryRow(query).Scan(Array(&stringSlice))
This is accomplished in two parts.
First, implement thedriver.Valuer
and sql.Scanner
interfaces with a StringArray
type. This part is informed by StackOverflow.
// StringArray is a custom type that implements the sql.Scanner and driver.Valuer interfaces
type StringArray []string
var _ driver.Valuer = StringArray{}
var _ sql.Scanner = &StringArray{}
// Value implements the driver.Valuer interface
func (a StringArray) Value() (driver.Value, error) {
return json.Marshal(a)
}
// Scan implements the sql.Scanner interface
func (a *StringArray) Scan(value interface{}) error {
if value == nil {
*a = nil
return nil
}
bytes, ok := value.([]byte)
if !ok {
return fmt.Errorf("expected bytes: %v", value)
}
return json.Unmarshal(bytes, a)
}
Second, add a wrapper function (Array
) to avoid runtime errors when calling Scan
. This part is heavily inspired by lib/pq
's Array
function', and to be honest I don't completely understand why it's needed. If you don't do it, the sql
library complains at runtime when you try to SELECT
. I only need to work with slices of strings, but this is easily extended by adding more XxxArray
s .
func Array(a interface{}) interface {
driver.Valuer
sql.Scanner
} {
switch a := a.(type) {
case []string:
return (*StringArray)(&a)
case *[]string:
return (*StringArray)(a)
default:
panic(fmt.Sprintf("unsupported type: %T", a))
}
}