CRUD Operations — Update

Today, let's continue learning about GORM's CRUD interface. In this topic, you'll take one step further in mastering this ORM library and learn how to perform update operations.

Updating values for a single record

The most basic update operation is modifying values for a single record. GORM's db.Save() method is designed for this purpose; it allows you to update values in all columns of a previously retrieved record and ensures that the database record matches the current state of your model struct.

author := Author{Name: "R.L. Stine"}
db.Debug().Save(&author)

// Debug() Output:
// INSERT INTO `authors` (`created_at`,`updated_at`,`deleted_at`,`name`) 
// VALUES ("2023-08-13 08:59:45.264","2023-08-13 08:59:45.264",NULL,"R.L. Stine") RETURNING `id`

Now that you know the intricacies of the db.Save() method, let's learn how to properly update an Author record using it:

func main() {
    ... // Connect to the `library` database using gorm.Open()

    // Retrieve the `Author` by their `name`:
    var author Author
    result := db.Where("name = ?", "Stephen King").First(&author)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Author: %v\n", result.Error)
    }

    // Update the `Name` in the `author` struct:
    author.Name = "Stephen Edwin King"

    // Update the entire record in the `authors` table:
    result = db.Debug().Save(&author)
    if result.Error != nil {
        log.Fatalf("cannot update Author: %v\n", result.Error)
    }
}

// Debug() Output:
// UPDATE `authors` 
// SET `created_at`="2022-11-14 12:05:08.252",
// `updated_at`="2023-08-13 09:00:51.315",
// `deleted_at`=NULL,
// `name`="Stephen Edwin King" 
// WHERE `authors`.`deleted_at` IS NULL AND `id` = 3

In the above example, only the Name field of the author struct was explicitly updated. However, the db.Save() method will perform an UPDATE query using ALL the fields of the author struct, as you can see from the Debug() method output.

For the other fields CreatedAt and DeletedAt in the author struct that weren't explicitly updated, db.Save() retains their initial values from the retrieved record. Moreover, db.Save() automatically sets a new timestamp value for the UpdatedAt field and uses these values in the UPDATE query; this behavior ensures that the updated database record reflects the current state of the model struct:

Updating multiple records in GORM

When you need to update more than one record with GORM, two primary methods stand out:

Updating a single column for a subset of records: This method is versatile as it works for one or multiple records. With GORM's Update() method, you can target specific records based on a condition and update a single column across those records.

Updating various columns for a subset of records: Sometimes, you might need to update various columns across multiple records. GORM's db.Exec() method allows you to update values in one or more using raw SQL statements.

Updating a single column for a subset of records

In the context of the library database, suppose that the Publisher named "George Allen & Unwin", has been acquired by another named "HarperCollins". As part of this acquisition, all Book records under banner of "George Allen & Unwin" need to be updated to show "HarperCollins" as the new credited Publisher.

The Update() method allows you to modify a value in a single column, publisher_id, for all the matched Book records at once:

func main() {
    // ... Connect to the `library` database using gorm.Open()

    // Retrieve the `oldPublisher` by their `name`:
    var oldPublisher Publisher
    result := db.Where("name = ?", "George Allen & Unwin").First(&oldPublisher)
    if result.Error != nil {
        log.Fatalf("cannot retrieve old Publisher: %v\n", result.Error)
    }

    // Retrieve the `newPublisher` by their `name`:
    var newPublisher Publisher
    result = db.Where("name = ?", "HarperCollins").First(&newPublisher)
    if result.Error != nil {
        log.Fatalf("cannot retrieve new Publisher: %v\n", result.Error)
    }

    // Update the `publisher_id` column value for all `Book` records 
    // originally published under "George Allen & Unwin":
    result = db.Model(&Book{}).Where("publisher_id = ?", oldPublisher.ID).
        Update("publisher_id", newPublisher.ID)
    if result.Error != nil {
        log.Fatalf("cannot update Books to new Publisher: %v\n", result.Error)
    }
}

In the above example, the first step is to fetch the oldPublisher and newPublisher records. Once retrieved, you pass an empty Book{} struct to db.Model() and use the oldPublisher.ID in the Where() method to target the books initially associated with "George Allen & Unwin". Then you chain the Update() method specifying the single column "publisher_id" and its new value newPublisher.ID, effectively transitioning the books to "HarperCollins".

Finally, if the model struct you pass to db.Model() has the gorm.Model field, the Update() method will automatically set a new timestamp value in the UpdatedAt field to reflect the last time the record was updated:

Updating various columns for a subset of records

Having learned how to update a single column, there are scenarios where you might need to update various columns for multiple records. For such cases, you can use raw SQL statements with db.Exec().

Imagine that due to a playful rebranding, all Book records whose title begins with "Harry Potter" must be renamed to "Harry Plopper" and their date_published advanced by one year. To achieve this, you would create a raw SQL statement leveraging the LIKE operator for title matching, combined with the replace function and datetime function and pass it to db.Exec():

func main() {
    // ... Connect to the `library` database using gorm.Open()

    // Update all `Book` records whose `title` begins with "Harry Potter":
    sqlUpdate := `
        UPDATE books
        SET title = replace(title, 'Harry Potter', 'Harry Plopper'),
            date_published = datetime(date_published, '+1 year'),
            updated_at = datetime() -- ⚠️ Manually set the 'updated_at' column value ⚠️ 
        WHERE title LIKE 'Harry Potter%'
    `
    result := db.Exec(sqlUpdate)
    if result.Error != nil {
        log.Fatalf("cannot update Books: %v\n", result.Error)
    }
}

After running the above code, inspect the books table. You'll notice that the title, date_published and updated_at columns of the affected records have been updated:

Now you might wonder why opt for raw SQL statements over ORM-based methods for updating multiple columns? One reason is that raw SQL queries are often more explicit, especially if you're familiar with SQL; apart from that, raw SQL is the only way to go if you need to use SQL functions with GORM.

Finally, if you're interested in the ORM-based methods to update multiple columns, GORM provides the Updates() function using map and struct conditions; you can read more about it in GORM's official documentation.

Intricacies of update operations in GORM

After delving into the mechanics of update operations using GORM, let's recap their intricate behaviors and standard practices:

Start small, then expand: If you're new to GORM, consider practicing by retrieving and then updating a single record at once using db.Save(), as mistakes would affect only one record instead of potentially corrupting multiple records in the table. You can start working with Update() or db.Exec() as you get more practice and understand that you should use conditions that only target the specific subset of records you intend to update.

Importance of primary keys: When you pass a single record retrieved with First() or a slice of records retrieved with Find() to db.Model(), it uses the primary key of those records as the condition for Update() and targets only those records. On the other hand, if you pass an empty model struct, e.g., Book{} to db.Model(), the Update() method would target ALL Book records that match the conditions in the Where() method.

Raw SQL requires extra caution: Remember that raw SQL queries bypass some of GORM's protections. When working with db.Exec() and raw SQL queries, it's crucial to use the ? character to represent variables in your SQL statements; this method ensures that data gets properly escaped, minimizing the risk of SQL injection vulnerabilities.

No explicit warning on mass updates: GORM will NOT stop or alert you when using Update() or db.Exec() statements that could potentially update multiple records; this behavior emphasizes the importance of always double-checking the conditions in your update operations.

Conclusion

So far, so good! You have learned the fundamentals of using GORM's CRUD interface to perform update operations using the db.Save(), Update() and db.Exec() methods.

You also learned about the intricacies of update operations in GORM and the standard practices you should follow when performing update operations.

Now it's time to test your knowledge about update operations using GORM with a few theory and coding tasks; let's go!