Setting UUID field to NULL with gorm

Question

I'm having a database table which contains a assigned_id field which has the type uuid.

The database is postgres and my ORM is gorm. I was wondering how its possible to set that assigned_id to NULL?

I've tried setting the field to nil in the model, as well as using gorm.Expr("NULL"), nil or sql.NullString. None of those methods work.

Maybe someone will be able to answer that question for me.

Answer 1

So you can declare a custom type to handle uuid (or any other non-default type). I've provided the implementation and it works for me with examples. NOTE: I've already declared a custom type to handle null string but I've left it out for brevity.

Also I did not do a complete test coverage of this code, so please take it as a starting point.

type NullUUID struct {
    UUID  uuid.UUID
    Valid bool
}

func (nd *NullUUID) Scan(value interface{}) (err error) {
    var s uuid.UUID
    if err := s.Scan(value); err != nil {
        return err
    }

    // if nil then make Valid false
    if reflect.TypeOf(value) == nil {
        *nd = NullUUID{Valid: false}
    } else {
        if err != nil {
            return err
        }
        *nd = NullUUID{s, true}
    }

    return nil
}

func (nd NullUUID) Value() (driver.Value, error) {
    if !nd.Valid {
        return nil, nil
    }

    return nd.Value, nil
}

func (nd NullUUID) MarshalJSON() ([]byte, error) {
    if !nd.Valid {
        return []byte("null"), nil
    }

    return json.Marshal(nd.UUID.String())
}

func (nd *NullUUID) UnmarshalJSON(b []byte) error {
    var str string

    err := json.Unmarshal(b, &str)
    if err != nil {
        nd.Valid = false
        return err
    }

    id, err := uuid.Parse(str)
    if err != nil {
        nd.Valid = false
        return err
    }

    nd.UUID = id
    nd.Valid = true
    return err
}

This also handles marshalling and unmarshalling so it either prints the value or "null", but you can edit this is required.

Here is my tested example:

 id | text 
----+------
    | sda

// Row result:
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, Valid:false}, Text:main.NullString{String:"sda", Valid:true}}

// Row marshalled:
{"id":null,"text":"sda"}

// Row unmarshalled:
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, Valid:false}, Text:main.NullString{String:"", Valid:false}} // seems to be a bug in my null string unmarshalling but I don't think it's relevant here

Just in case, I also tested non null uuid and here is the result:

// Row marshalled
{"id":"889f2163-ee59-4632-b238-3ac9574f111e","text":null}

// Row unmarshalled
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x88, 0x9f, 0x21, 0x63, 0xee, 0x59, 0x46, 0x32, 0xb2, 0x38, 0x3a, 0xc9, 0x57, 0x4f, 0x11, 0x1e}, Valid:true}, Text:main.NullString{String:"", Valid:true}} // again seems to be a bug in my null string unmarshalling but I don't think it's relevant here

// Printed UUID string
889f2163-ee59-4632-b238-3ac9574f111e