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.

Maori

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.

Yonatan

This does not make sense to me - why would you need to specify a where clause to update an existing record. The user object should already have the id if it is an existing record and I would hope gorm would be smart enough to know that if the id exists, it should update. I wrote a gorm like library for Java, and it only requires a call to save() regardless of whether the record is new or not - it will insert it if it does not have an id, and update if it does. – Doorknob

The question is “if it exists I want to update some fields”。So you have to check if it exist, and if so, you could update the selected field only. if you use db.save(&user), all fields in user world be written to db. – Yonatan

Gorm now has first class support for upsert gorm.io/docs/create.html#Upsert-On-Conflict – Cloy

According to the docs, you can simplify the clause to clause.OnConflict{UpdateAll: true} if you want to resolve conflicts by simply updating all columns (except PK) – Herbivore

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

Template

Is this safe against race conditions? – Herbivore

Perhaps this should be done with a transaction to protect against data races? – Lyrate

Answer 3

There is a better way to do it:

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

In this example, if a user with email "[email protected]" 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.

Titanite

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.

Persuader

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

Malcommalcontent

The correct link is here: jinzhu.me/gorm/crud.html (could not edit because it's less than 6 characters) – Being

Changed embedded link from curd to crud – Varnish

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"  

Fukien

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).

Preview

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
}

Rosiarosicrucian

One could simply pass zero valued struct as update param to skip updating the field instead of using reflect. Unless of course it is a pointer than the zero value is nil hence setting it to 0 / "" (and other zero values) will update it. – Shantung

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

Denims

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}) 
    } 
...

Bailee

Answer 11

Use Gorm Save.

Save is a combination function. If save value does not contain primary key, it will execute Create, otherwise it will execute Update (with all fields).

db.Save(&user)

// Save update value in database, if the value doesn't have primary key, will insert it
func (db *DB) Save(value interface{}) (tx *DB) {

Overnight