How to Create or Update a record with GORM?

Question

Gorm has a FirstOrCreate method and a FirstOrInit but how to check afterwards if the record was actually created? I like to create a record if it does not exists and if it exists I want to update some fields.

Answer 1

update 2020.10.09

Thanks for @vaelin

From 1.20.x on, GORM provides compatible Upsert support for different databases( Upsert-On-Conflict)

// Update columns to new value on `id` conflict
DB.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}}, // key colume
  DoUpdates: clause.AssignmentColumns([]string{"name", "age"}), // column needed to be updated
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET "name"="excluded"."name"; SQL Server
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age"; PostgreSQL
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age=VALUES(age); MySQL

With gorm 1.9.x or below, it's more effecient to update first, then insert when not existed.

// update only set name=nick
if err := db.Model(&newUser).Where("id = ?", 3333).Update("name", "nick").Error; err != nil {
    // always handle error like this, cause errors maybe happened when connection failed or something. 
    // record not found...
    if gorm.IsRecordNotFoundError(err){
        db.Create(&newUser)  // create new record from newUser
    }
}

FirstOrInit and FirstOrCreate are different. If there is no match record in database, FirstOrInit will init struct but not create record, FirstOrCreate will create a record and query that record to struct.

Answer 2

The most upvoted answer did not work for me, but this did:

user := NewUser(email, password)
if db.Model(&user).Where("email = ?", email).Updates(&user).RowsAffected == 0 {
    db.Create(&user)
}

This works for gorm v1.9.15 and go 1.13

Answer 3

There is a better way to do it:

    if err := db.Where(User{Email: "some@email.com"}).
       Assign(User{Email: "some@email.com", Age: 45}).
       FirstOrCreate(&User{}).Error; err != nil {
         c.Next(err)
         return
    }

In this example, if a user with email "some@email.com" is found, then the field "Age" will be updated. On the contrary, if no user if found, then it is created.

Note that I am discarding the created user, but you can keep the reference if you want. Also, for some GORM reasons, it is required to provide at least a filter field in the Assign clause, that's why you see email being populated twice.

Answer 4

FirstOrInit doesn't create a new record. It only finds the first matched record and initialises it with given conditions if unfound.

For both FirstOrCreate and FirstOrInit, you can use RowsAffected. If return value is "1", the record was found in the DB, i.e. it already exists, and thus wasn't created. If return value is "0", it wasn't found.

... if it exists I want to update some fields.

I'm not sure where you want this updation. Locally in your map/struct or in the DB. If local, then I'm confident you can do that now. If in the DB, I would suggest using Attrs or Assign methods.

Answer 5

Here's example from gorm documentation CRUD section

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}

db.NewRecord(user) // => returns `true` as primary key is blank

db.Create(&user)

db.NewRecord(user) // => return `false` after `user` created

Answer 6

gormDB.Where(entity.AggregatedData{Type: v.Type}).Assign(entity.AggregatedData{Type: v.Type, Data: v.Data}).FirstOrCreate(v)


 SELECT * FROM "aggregated_data"  WHERE ("aggregated_data"."type" = '2') ORDER BY "aggregated_data"."id" ASC LIMIT 1 

and if exist then

 UPDATE "aggregated_data" SET "data" = '[{"a":2}]', "type" = '2'  WHERE "aggregated_data"."id" = '2' AND (("aggregated_data"."type" = '2'))  

else

INSERT INTO "aggregated_data" ("data","type") VALUES ('[{"a":2}]','1') RETURNING "aggregated_data"."id"  

Answer 7

See Attrs here. It won't exactly tell you whether the record was actually created, but will let you update some fields only if record was actually created (which seems to be what you want to achieve in the end).

Answer 8

func CreateOrUpdate(db *gorm.DB, model interface{}, where interface{}, update interface{}) (interface{}, error) {
    var result interface{}
    err := db.Model(model).Where(where).First(result).Error
    if err != nil {
        if !errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, err
        } else {
            //insert
            if err = db.Model(model).Create(update).Error; err != nil {
                return nil, err
            }
        }
    }
    //not update some field
    reflect.ValueOf(update).Elem().FieldByName("someField").SetInt(0)
    if err = db.Model(model).Where(where).Updates(update).Error; err != nil {
        return nil, err
    }
    return update, nil
}

Answer 9

Seems like you can just use

db.Save(&MyModel)

After poking through the answers here, I was looking through gorm code and realized the go doc on save states

// Save update value in database, if the value doesn't have primary key, will insert it

Answer 10

Just put it in an array:

db.Save([]User{user}).Error

By reviewing the source code, it sets all updates from a slice, and on a conflict it updates all.

The most voted answer is still the best because it makes everything explicit. However, if you just want it brief and know your models well, putting it in a slice can be a short form of that.

Source code:

switch reflectValue.Kind() {
  case reflect.Slice, reflect.Array: 
    if _, ok := tx.Statement.Clauses["ON CONFLICT"]; !ok {
        tx = tx.Clauses(clause.OnConflict{UpdateAll: true}) 
    } 
...