unsupported Scan, storing driver.Value type []uint8 into type *[]string

Question

I have implemented rest api using golang, gin and gorp

Employee structure:

type Employee struct {
  Id            int64  `db:"id" json:"id"`
  Firstname string `db:"firstname" json:"firstname"`
  Lastname  string `db:"lastname" json:"lastname"`
  Dob           time.Time `db:"dob" json:"dob"`
  Skills        []string `db:skills json:"skills"`
}

In POST sending request as:

func PostEmployee(c *gin.Context) {
  var emp Employee
  c.Bind(&emp)

  skills, _ := json.Marshal(emp.Skills)

  if emp.Firstname != "" && emp.Lastname != "" {

    if insert, _ := dbmap.Exec(`INSERT INTO employee (firstname, lastname, dob, skills) VALUES (?, ?, ?, ?)`, emp.Firstname, emp.Lastname, emp.Dob, skills); insert != nil {
        emp_id, err := insert.LastInsertId()
    .....
    }
  ......
  }

This save data to mysql database, works perfect.

For retrieving data from database implemented GET request

 func GetEmployees(c *gin.Context) {
   var emps []Employee
   _, err := dbmap.Select(&emps, "SELECT * FROM employee")
   log.Println(err)
   if err == nil {
     c.JSON(200, emps)
 } else {
     c.JSON(404, gin.H{"error": "no employee(s) into the table"})
 }

GET query doesn't gives any data from database and log.Println(err) log says:

 Scan error on column index 4: unsupported Scan, storing driver.Value type []uint8 into type *[]string

Any ideas?

Solution

Two Approaches:
1. Implement sql.Scanner and driver.Valuer interfaces for a custom type
Benefits:

• Simple to store and retrieve
• Don't have to query/load from another table

Caveats:

• String Size Is Limited by sql column definition (i.e. in this case its 255). Depending on your database architecture this will either be truncated or will result in an error that needs handling.
• Jump through hoops to delete specific skills for someone/everyone at the database level.
• Searching needs to be done through contains instead of equal operator
• Modifying the skills structure will be difficult in the future.

package tgorm

import (
    "database/sql/driver"
    "encoding/json"
    "errors"
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "github.com/stretchr/testify/assert"
    "strings"
    "testing"
    "time"
)

type Skills []string

func (s Skills) Value() (driver.Value, error) {
    if len(s) == 0 {
        return "[]", nil
    }
    return fmt.Sprintf(`["%s"]`, strings.Join(s, `","`)), nil
}

func (s *Skills) Scan(src interface{}) (err error) {
    var skills []string
    switch src.(type) {
    case string:
        err = json.Unmarshal([]byte(src.(string)), &skills)
    case []byte:
        err = json.Unmarshal(src.([]byte), &skills)
    default:
        return errors.New("Incompatible type for Skills")
    }
    if err != nil {
        return
    }
    *s = skills
    return nil
}

type Employee struct {
    Id        int64     `db:"id" json:"id"`
    Firstname string    `db:"firstname" json:"firstname"`
    Lastname  string    `db:"lastname" json:"lastname"`
    Dob       time.Time `db:"dob" json:"dob"`
    Skills    Skills    `gorm:"type:varchar(255);" db:"skills" json:"skills"`
}

func (e Employee) TableName() string {
    return "employee"
}


func getMemoryDataBase() *gorm.DB {
    db, err := gorm.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    db = db.Debug()
    db.AutoMigrate(Employee{})
    return db
}

func TestSaveEmployee(t *testing.T) {
    db := getMemoryDataBase()
    emp := Employee{
        Id:        1,
        Firstname: "Fake",
        Lastname:  "Emp",
        Dob:       time.Time{},
        Skills:    []string{"C#", "GO", "C++"},
    }
    skills, _ := json.Marshal(emp.Skills)
    err := db.Exec(`INSERT INTO employee (firstname, lastname, dob, skills) VALUES (?, ?, ?, ?)`, emp.Firstname, emp.Lastname, emp.Dob, skills).Error
    assert.Nil(t, err)
    var emps []Employee
    err = db.Raw("SELECT * FROM employee").Scan(&emps).Error
    assert.Nil(t, err)
    assert.Equal(t, []Employee{emp}, emps)
}

2. Move Skills into a separate table, with a reference to the employee.

Benefits:

• Extend Table Definition
• Better searching capabilities
• Easier to drop skills for someone/everyone at the database level

Caveats:

• Needs another query/load for Skills
• Increased Database Schema Definition

package subgrom

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "github.com/stretchr/testify/assert"
    "testing"
    "time"
)

type Skill struct {
    Id          int64  `db:"id" json:"id"`
    Skill       string `db:"skill" json:"skill"`
    EmployeeRef int64
}

type Employee struct {
    Id        int64     `db:"id" json:"id"`
    Firstname string    `db:"firstname" json:"firstname"`
    Lastname  string    `db:"lastname" json:"lastname"`
    Dob       time.Time `db:"dob" json:"dob"`
    Skills    []Skill   `db:"skills" json:"skills" gorm:"foreignkey:EmployeeRef"`
}

func (e Employee) TableName() string {
    return "employee"
}

func getMemoryDataBase() *gorm.DB {
    db, err := gorm.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    db = db.Debug()
    db.AutoMigrate(Employee{}, Skill{})
    return db
}

func TestSaveEmployee(t *testing.T) {
    db := getMemoryDataBase()
    emp := Employee{
        Id:        1,
        Firstname: "Fake",
        Lastname:  "Emp",
        Dob:       time.Time{},
        Skills:    []Skill{{Skill: "C#"}, {Skill: "GO"}, {Skill: "C++"}},
    }
    err := db.Create(&emp).Error
    assert.Nil(t, err)
    var emps []Employee
    err = db.Preload("Skills").Find(&emps).Error
    assert.Nil(t, err)
    assert.Equal(t, []Employee{emp}, emps)
}

Comments

[setiabb]

Thanks for your excellent answer, I tested the first approach with gorp and I found a mistake in your solution, For the value method, the marshaling on an array that has some elements returns an error, the last line of the Value method should be like this: return fmt.Sprintf(`["%s"]`, strings.Join(s, `","`)), nil