CODEWITHSUNDEEP
gogo-gorm
hashchen
hashchen

Reputation: 1039

nil does not set database record to NULL

I am using gorm, and I have a nullable column gender which can be set either male or female, but I am not able to set it back to NULL using nil once the column is set with a value of either male or female

The following is my code. To simplify things a bit, I only include the fields related to my question

type User struct {
    gorm.Model

    Username    string       `gorm:"type:varchar(40);unique" json:"username"`
    NickName    string       `gorm:"type:varchar(32)" json:"nickname"`
    Gender      *string      `gorm:"type:enum('male', 'female');default:null" json:"gender"`
}

and then I update my gender to male:

db.Model(&User{}).Where(&User{
    Model: gorm.Model{ID: 1},
}).Update(
    &User{
        Gender:   utils.GetStringPtr("female"),
        NickName: "nick name",
    },
)

and then I tried to update the gender to NULL

db.Model(&User{}).Where(&User{
    Model: gorm.Model{ID: 1},
}).Update(
    &User{
        Gender:   nil,
        NickName: "nick name",
    },
)

However, the gender stays the same female, and it is NOT changed to NULL.

Any ideas how I can fix this?

Upvotes: 5

Views: 14240

Answers (4)

Yiannis
Yiannis

Reputation: 96

This does not work with the Updates command of GORM. If you configure it correctly, in the best case scenario you will have an empty string in the DB.

You need to use the Save method of GORM in order to store NULL in your DB.

If you are working on an API, this is a PUT method not a PATCH.

You also don't need an extra package for the struct. You can use

type User struct {
    gorm.Model

    Username  string         `gorm:"type:varchar(40);unique" json:"username"`
    NickName  string         `gorm:"type:varchar(32)" json:"nickname"`
    Gender    sql.NullString `gorm:"type:enum('male', 'female');default:null" json:"gender"`
}

and have a function to transform the string to sql.NullString

func StringToSQLNullString(s string) sql.NullString {
    if s != "" {
        return sql.NullString{
            String: s,
            Valid:  true,
        }
    }
    return sql.NullString{}
}

In the end, you need to get your User from DB, to keep the CreatedAt property and then save it. Your code will be like this (For null, you don't need the gender)

updatedUser := &User{
  ID: 1,
  NickName: "nick name",
}

existingUser := &User{}
_ = db.First(existingUser, updatedUser.ID).Error

updatedUser.CreatedAt = existingUser.CreatedAt

_ = db.Save(updatedUser).Error

Including the gender, the struct is like this

updatedUser := &User{
  ID: 1,
  NickName: "nick name",
  Gender: StringToSQLNullString("male")
}

EDIT

Else, if you only want to update the gender your own answer is the way

db.Model(&user).Updates(map[string]interface{"gender": nil})

Upvotes: 0

Leroy A.
Leroy A.

Reputation: 9

Use sql.Nullstring

example: Unable to use type string as sql.NullString

Upvotes: 0

ttrasn
ttrasn

Reputation: 4851

You must use null package.

for example :

type User struct {
    gorm.Model

    Username    string       `gorm:"type:varchar(40);unique" json:"username"`
    NickName    string       `gorm:"type:varchar(32)" json:"nickname"`
    Gender      null.String  `gorm:"type:enum('male', 'female');default:null" json:"gender"`
}

then for set element to null you must do this :

gender := null.StringFromPtr(nil)

from your code, you must do this:

db.Model(&User{}).Where(&User{
    Model: gorm.Model{ID: 1},
}).Update(
    &User{
        Gender:   null.StringFromPtr(nil),
        NickName: "nick name",
    },
)

Upvotes: 2

hashchen
hashchen

Reputation: 1039

from the official doc, I found this http://gorm.io/docs/update.html

// Update multiple attributes with `struct`, will only update those changed & non blank fields
db.Model(&user).Updates(User{Name: "hello", Age: 18})
//// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// WARNING when update with struct, GORM will only update those fields that with non blank value
// For below Update, nothing will be updated as "", 0, false are blank values of their types
db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

If I do:

db.Model(&user).Updates(map[string]interface{"gender": nil})

this can successfully updates the model column to NULL.

However, I realized this is kinda bad, because if I set a bool column to True, that means I will never be able to set it back to False using struct update. Is there any way to work around this ? What is the design purpose of this.

Upvotes: 3

Related Questions