Custom type how to set scan nil

[mengdu]

The JSON type column in the table may be null. How can I scan return nil?

Code like:

// table struct
type TableName struct {
	Id        int        `json:"id" db:"id"`
	Ext       *MyExt     `json:"ext" db:"ext"`
	SendAt    *time.Time `json:"sendAt" db:"sendAt"`
	CreatedAt time.Time  `json:"createdAt" db:"createdAt"`
	UpdatedAt time.Time  `json:"updatedAt" db:"updatedAt"`
}

type MyExt struct {
	OperId  int       `json:"operId"`
	Oper    string    `json:"oper"`
	Preview int8      `json:"preview"`
	Remark  string    `json:"remark"`
	At      time.Time `json:"at"`
	isNil   bool      `json:"-"`
}

func (n *MyExt) IsNull() bool {
	return n.isNil
}

// Implementation driver.Valuer
func (n MyExt) Value() (driver.Value, error) {
	return json.Marshal(n)
}

// Implementation sql.Scanner
func (n *MyExt) Scan(i interface{}) error {
	switch v := i.(type) {
	case string:
		if v == "" || v == "null" {
                         // How can I scan return nil?
			n.isNil = true
			return nil
		}
		return json.Unmarshal([]byte(v), n)
	case []byte:
		if bytes.Equal(v, []byte("")) || bytes.Equal(v, []byte("null")) {
			n.isNil = true
			return nil
		}
		return json.Unmarshal(v, n)
	default:
		return errors.New("not supported")
	}
}

I now handle the problem of null by implementing the MarshalJSON interface

func (n *MyExt) MarshalJSON() ([]byte, error) {
	if n.isNil {
		return []byte("null"), nil
	}

	type Temp MyExt
	return json.Marshal(&struct {
		*Temp
	}{Temp: (*Temp)(n)})
}

Custom types cannot be like time.Time to handle nil?

Such as SendAt can be nil when value is null

[jakoblorz]

How is the database designed? Does the column ext have a NOT NULL constraint but you need that, so you intend to write "null" into it? Trying to understand your example here. Because on columns where the value is NULL (which is not the same as a string "null"), the sql.Scanner interface is called with nil as the parameter (from the docs):

Scan assigns a value from a database driver. The src value will be of one of the following types: ... nil - for NULL values

[mengdu]

Yes, the column ext is NOT NULL. When the value is "null" or "", is there any way to scan with it as nil?

[kubaracek]

@mengdu Your have 2 problems first is the Value function and second is that you set the column to not null.

func (n MyExt) Value() (driver.Value, error) {
	return json.Marshal(n)
}

I assume this will store a string "null". What you need to do is to return nil in this function. SQLx then correctly sets the Ext *MyExt as nil when scanning the row. Given the column is set as nullable.

So the Value func should be

func (n *MyExt) Value() (driver.Value, error) {
         if n == nil {
            return nil, nil
         }
	return json.Marshal(n)
}