Gorm No way to optionally return a single record without logging error

Your Question

How can I find one record that may or may not be present and neither case is an error?

The document you expected this should be explained

https://gorm.io/docs/query.html

Expected answer

Documentation suggests using Limit(1).Find(&foo).

What I would like to do is something like this:

var record *MyStruct
err := db.Where("foo = 1").Limit(1).Find(record).Error // DOESN'T WORK: returns 'invalid value' error
if record == nil {
  doSomething()
} else {
  doSomethingElse()
}

This is clean and very clear. No error is raised if the record is missing and the record is simply nil as you would expect, because it hasn't been found.

Unfortunately it seems this is not possible because if I try this I get invalid value errors.

I have found two other ways to do it, both of which are suboptimal:

Method 1:

record := &MyStruct{}
err := db.Where("foo = 1").First(&record).Error // logs "not found" Error even though nothing is broken if it doesn't exist
if err == gorm.ErrRecordNotFound {
  doSomething()
} else {
  doSomethingElse()
}

This suboptimal because gorm logs a not found message at level Error every time we hit record not found, even though this is completely normal and does not indicate anything is broken. It fills our logs with useless errors.

Method 2

record := &MyStruct{ID: -1}
err := db.Where("foo = 1").Limit(1).Find(&record).Error
if record.ID == -1 { // relies on hacky "magic" flag value
  doSomething()
} else {
  doSomethingElse()
}

This doesn't log useless errors if the record is missing, but is still suboptimal because it relies on "magic" numbers. It's really messy to put the ID as -1 and it isn't even consistent because not all of our database tables have an ID primary key. In some cases there may not be any value at all that is safe to use as a magic flag value.

Perhaps I have missed something. What is the correct way to handle this (quite common) use case?

Comment From: jinzhu

record := &MyStruct{} result := db.Where("foo = 1").Limit(1).Find(&record) result.RowsAffected == 1 result.Error == nil

Comment From: LouisSayers

So basically we're saying do:

func MyStructFind(fooId uint) (*MyStruct, error) {
  var record MyStruct
  result := db.Where("foo = ?", fooId).Limit(1).Find(&record)

  if RowsAffected == 0 { // nil
    return nil, result.Error
  } else {
    return &record, result.Error 
  }
}

I also find this really tedious to do and it seems like it's something that's commonly sought after. It'd be much nicer to be able to do:

func MyStructFind(fooId uint) (*MyStruct, error) {
  var record *MyStruct
  result := db.Where("foo = ?", fooId).Limit(1).FindOrNil(record)

  return record, result.Error 
}

Comment From: NicklasWallgren

FirstOrNil would be really helpful.

Comment From: tpjg

To disable logging for one statement, use a transaction, e.g.

  tx := db.Begin(&sql.TxOptions{ReadOnly: true, Isolation: sql.LevelDefault})
  l := tx.Logger
  tx.Logger = logger.Discard

  record := &MyStruct{}
  err := tx.Where("foo = 1").First(&record).Error 
  tx.Commit()
  tx.Logger = l
  if errors.Is(err, gorm.ErrRecordNotFound) {
    doSomething()
  } else {
    doSomethingElse()
  }

Comment From: Lykr

To be honest, treating nil result as an error is a stupid design.