TIL: Beware of Nil UUID when updating database with gorm!

 · 
 ·  subscribe to my posts

WHERE conditions required

At work, we are using gorm for dealing with database. It’s a popular SQL manipulation library in Go.

But recently, we got a scary error:

can not update row: id=00000000-0000-0000-0000-000000000000
WHERE conditions required

Which happened from this code snippet:

db, _ := gorm.Open(...)

paper := &Paper{
	ID:   updateID,
	Name: "new name",
}
db.Update(paper)

The above code will generate the following SQL statement:

UPDATE "paper" SET "name" = $1, "updated_at" = $2
WHERE "paper"."deleted_at" IS NULL AND "id" = $3

Everything looks fine, right? The correct SQL statement is generated. The paper with given id is updated. Unit test passes. The code was deployed to production. Customer is happy. And we can sleep well.

Think again! One day, we got the above error!

What actually happens is that there is a record with id = 00000000-0000-0000-0000-000000000000 in the database. The updateID is taken from that record, which in turn is empty. And since gorm happily ignores empty fields when updating, it’ll give us this SQL statement instead:

UPDATE "paper" SET "name" = $1, "updated_at" = $2
WHERE "paper"."deleted_at" IS NULL

Luckily for us, there is no other where conditions, so that error was returned: WHERE conditions required.

But if the code were written slightly different

paper := &Paper{
	ID:   updateID,
	Name: "new name",
}
db.Where("account_id = ?").Update(paper)

In this case, the generated SQL statement will be:

UPDATE "paper" SET "name" = $1, "updated_at" = $2
WHERE "account_id" = $3 AND "paper"."deleted_at" IS NULL

How scary it is! All records for that account will be updated with the new name! And gorm won’t save us this time, since there is a .Where() condition already!

Lesson learned

Let’s always require where id = ?

db.Where("id = ? AND account_id = ?").Update(paper)

Or validate that the UUID can not be nil

By checking for nil value when implementing the Valuer interface:

func (id UUID) Value() (driver.Value, error) {
    if id == uuid.Nil {
	    return nil, errors.New("nil uuid")	
    }
    return marshal(id)
}

Thanks for reading. And we can sleep well now. Hopefully so! 🥺

Let's stay connected!

If you like the post, subscribe to my newsletter to get latest updates:

Author

I'm Oliver Nguyen. A software maker working mostly in Go and JavaScript. I enjoy learning and seeing a better version of myself each day. Occasionally spin off new open source projects. Share knowledge and thoughts during my journey. Connect with me on , , , , or subscribe to my posts.

Back