Sqlx: Support named types

[wyattjoh]

type Role string

// Contains the relationship in-between the business
// and a user
type BusinessRole struct {
    ID         int64 `json:"id" db:"id"`
    BusinessID int64 `json:"business_id" db:"business_id"`
    UserID     int64 `json:"user_id" db:"user_id"`
    Role       Role  `json:"role" db:"role"`
}

db.MustExec(`CREATE TABLE business_roles (
        id                      BIGSERIAL PRIMARY KEY,
        business_id             BIGSERIAL REFERENCES businesses(id),
        user_id                 BIGSERIAL REFERENCES users(id),
        role                             TEXT
);`)

db.NamedQuery(
        `INSERT INTO plans (business_id, user_id, role) VALUES (:business_id, :user_id, :role) RETURNING id`,
        businessRole,
)

I get the error: sql: converting Exec argument #2's type: unsupported type Role, a string. Not sure if I'm using this wrong, or it isn't supported. Suggestions welcome!

[jszwedko]

@wyattjoh That error comes from database/sql -- basically it doesn't know what to do with the Role type as far as marshalling to the database. You can implement driver.Valuer with something like:

func (r Role) Value() (driver.Value, error) {
    return string(r), nil
}

so that it can marshal it to the database (I also recommend implementing sql.Scanner so you can read the values out).

[jszwedko]

Interestingly it does seem like string opaque types may be the only unsupported opaque primitive type https://github.com/golang/go/blob/master/src/database/sql/driver/types.go#L230

[wyattjoh]

Makes sense, it just screams deamon casting everywhere:

// Value implements the driver.Valuer interface
func (r Role) Value() (driver.Value, error) {
    return string(r), nil
}

// Scan implements the sql.Scanner interface
func (r Role) Scan(src interface{}) error {
    r = Role(string(src.([]uint8)))

    return nil
}

[JakeXu]

I think you should write like below, if not the string value will be lost:

// Value implements the driver.Valuer interface
func (r Role) Value() (driver.Value, error) {
    return string(r), nil
}

// Scan implements the sql.Scanner interface
func (r *Role) Scan(src interface{}) error {
    if src == nil {
    *r = ""
    } else {
        *r = Role(string(src.([]uint8)))
    }
    return nil
}