使用sqlx进行结构嵌入不会从数据库返回值

Question

I have a struct with a time field that may be nil:

type Order struct {
    ...
    PickupTime  *time.Time    `json:"-"`
}

I want to save this to DB with sqlx so I figure I need to use pq.NullTime as suggested here.

Instead of updating the Order object (I don't want to leak DB code into the model layer), I figured I could embedd Order in a PgOrder and alter the PickupTime type:

type PgOrder struct {
    Order
    PickupTime pq.NullTime
}

The problem is that when I simply update an Order in DB, then turn around and fetch that order, the returned PickupTime is empty.

// update
func (pg Postgres) UpdateOrderPickupTime(order *Order, pickupTime time.Time) error {
    _, err := pg.Exec(`UPDATE orders SET pickup_time = $1 WHERE id = $2`, pickupTime, order.ID)
    return err
}

// retrieve
func (pg Postgres) GetOrder(orderID DatabaseID) (*Order, error) {
    pgOrder := PgOrder{}
    err := pg.Get(&pgOrder, `SELECT * FROM orders WHERE id = $1`, orderID)
    if err == sql.ErrNoRows {
        return nil, nil
    }
    ... // at this point pgOrder.PickupTime is 0001-01-01 00:00:00 +0000 UTC

}

If I put a breakpoint betwen updating and retrieving, I can inspect the DB and see that a value is being saved as 2017-04-20 12:05:37-04. So the problem must be in the retrieve portion. If I understand right from the docs, sqlx should be able to handle embedded structs.

Answer 1

If your field is a pointer to something, e.g. *time.Time, or *string you shouldn't need to use the NullXxx types. Those types are to be used when you have a non-nil field, e.g. time.Time, or string while it's corresponding column can be NULL.

If you want to ebmed your type anyway, to avoid potential shadowing already mentioned by @Dmitri Goldring, you can to tell sqlx to skip the field you don't want it to scan the column into. So just as you did with the json tag, you can do so with the db tag:

type Order struct {
    ...
    PickupTime *time.Time `json:"-" db:"-"`
}

type PgOrder struct {
    Order
    PickupTime pq.NullTime
}