CRUD Operations – Delete

Today, let's continue exploring GORM. In this topic, you'll learn how to delete records in various scenarios: a single record, multiple records at once (batch deletions), and records with associations. You will also discover a unique feature of GORM's approach to deleting records and learn about its possible benefits and drawbacks in detail.

Soft delete

Soft delete is an approach where you don't physically remove records from the table. Instead, each table contains a column usually named deleted_at. When a record is deleted, a timestamp value is assigned to this column. As a result, the system automatically filters out soft-deleted records from query results made with GORM's methods. Despite this, you can still access these records if necessary.

When working with GORM, every model that includes the gorm.Model field automatically has soft delete capabilities via the DeletedAt field. However, if you don't want to include the gorm.Model field in your models, you can enable the soft delete feature by adding a field of the gorm.DeletedAt type to your model, for example:

type User struct {
    ID        uint
    DeletedAt gorm.DeletedAt
    Name      string
}

Deleting a single record

The most basic operation is deleting a single record. It involves two steps: first, you retrieve a record, and then after the record is successfully retrieved, you can use the db.Delete() method to remove the record from the table.

After you've downloaded the example-library project files, you can go ahead and delete a single record from the publishers table:

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

    // Retrieve the `Publisher` record:
    var publisher Publisher
    result := db.Where("Name = ?", "Dutton").First(&publisher)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Publisher: %v\n", result.Error)
    }

    // Delete the `Publisher` record:
    result = db.Delete(&publisher)
    if result.Error != nil {
        log.Fatalf("cannot delete Publisher: %v\n", result.Error)
    }

    fmt.Printf("Rows deleted: %d\n", result.RowsAffected)
    fmt.Printf("Deleted Publisher with ID: %d, Name: %s", publisher.ID, publisher.Name)
}

// Output:
// Rows deleted: 1
// Deleted Publisher with ID: 12, Name: Dutton

The idea of retrieving a record using db.Where() before deletion is a safer approach. It confirms the record's existence in the table and allows for error handling if not found, thereby ensuring that the correct record is deleted.

Now, if you inspect the publishers table and locate the record you just deleted, you'll notice that the deleted_at column has a timestamp value set:

Working with soft-deleted records

While soft-deleted records are omitted from queries made using GORM's methods, you can still retrieve them when necessary. GORM allows you to do it using the db.Unscoped() method:

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

    // Retrieve a soft-deleted `Publisher` record:
    var publisher Publisher
    result := db.Unscoped().Where("Name = ?", "Dutton").First(&publisher)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Publisher: %v\n", result.Error)
    }

    fmt.Printf("Retrieved soft-deleted Publisher with ID: %d, Name: %s,\nDeletedAt: %v\n",
        publisher.ID, publisher.Name, publisher.DeletedAt)
}

// Output:
// Retrieved soft-deleted Publisher with ID: 12, Name: Dutton,
// DeletedAt: {2023-07-21 12:15:11.8217477 -0500 -0500 true}

And if you wanted to permanently delete a soft-deleted record, you would have to chain the Unscoped() and Delete() methods via the following syntax:

// Permanently delete a soft-deleted `Publisher` record:
result := db.Unscoped().Where("name = ?", "Dutton").Delete(&Publisher{})
if result.Error != nil {
    log.Fatalf("cannot permanently delete Publisher: %v\n", result.Error)
}

After executing the above code and inspecting the publishers table, you'll find the record with name "Dutton" has been entirely removed.

Deleting records with associations

In a database, when you deal with many-to-many relationships, you need to delete an associated record from the join table if you delete a record from one of the main tables. This action is necessary to preserve data integrity. It also prevents the creation of orphaned records, which no longer have a legitimate relationship.

Consider the library database, where the authors and books tables have a many-to-many relationship via the author_books join table. If you wanted to delete a record from the books table, you would also need to remove the corresponding record in the author_books table:

package main

import (
    "fmt"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
    "log"
    "time"
)

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

    // Retrieve a `Book` by its `title`:
    var book Book
    result := db.Where("title = ?", "The Hobbit").First(&book)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Book: %v\n", result.Error)
    }
    
    // Delete all records in all join tables associated with the `Book` 
    // And then delete the `Book` record:
    result = db.Select(clause.Associations).Delete(&book)
    if result.Error != nil {
        log.Fatalf("cannot delete Book: %v\n", result.Error)
    }
    fmt.Printf("Deleted all author_books record associated with Book ID: %d, Title: %s\n",
        book.ID, book.Title)
    fmt.Printf("Deleted Book with ID: %d, Title: %s\n", book.ID, book.Title)
}

// Output:
// Deleted all author_books record associated with Book ID: 1, Title: The Hobbit
// Deleted Book with ID: 1, Title: The Hobbit

In the above example, you first retrieve a Book from the database. You then use the db.Select() method with clause.Associations as an argument; this special constant from the gorm.io/gorm/clause package allows you to target ALL the associated records in ALL join tables linked to the Book.

Finally, you chain Delete(&book), which directs GORM to first delete all records in all join tables associated with the Book and finally delete the Book record itself. This approach ensures that no orphaned records remain in any join tables when a record in a main table is deleted, efficiently maintaining data integrity in a many-to-many relationship scenario.

Batch deletions

Batch deletions offer an efficient way to delete multiple records at once. To carry out a batch deletion, you should first retrieve all the records that match a specific condition and save them into a slice of a model struct. After retrieving the records, you pass a pointer to this slice to the db.Delete() method.

For example, if you wanted to delete all Book records with titles containing "Harry Potter", you could proceed as follows:

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

    // Retrieve all `Book` records with titles containing "Harry Potter":
    var books []Book
    db.Where("title LIKE ?", "%Harry Potter%").Find(&books)

    // Delete all `Book` records with titles containing "Harry Potter":
    result := db.Delete(&books)
    if result.Error != nil {
        log.Fatalf("cannot delete Books: %v\n", result.Error)
    }

    fmt.Printf("Deleted %d Book records\n", result.RowsAffected)
}

// Output:
// Deleted 7 Book records

It's important to note that when using db.Delete(), you should either provide a condition in a preceding Where() clause or pass in record(s) with primary key(s). If you don't provide either, GORM will treat the operation as a batch delete without conditions, attempting to delete all records in the table.

For example, if you executed the code below, GORM would attempt to delete all Book records:

db.Delete(&Book{}) // DELETE FROM books;

However, as a safety feature, GORM will NOT execute such a batch delete without conditions and will return an ErrMissingWhereClause error instead. To avoid unwanted batch deletions, always ensure that you are providing a condition or record(s) with a primary key(s) when using db.Delete().

Why does GORM use soft delete?

As previously mentioned, soft delete is not a standard convention in all ORMs. Let's examine some advantages of employing the soft delete approach:

• Data recovery: If you accidentally delete a record, you can recover it since it's not physically removed from the table.
• Historical data: Soft deletes allow for keeping historical data, which can be helpful for analytics and reporting.
• Regulatory compliance: Some regulations require companies to retain deleted records for a certain period. For example, financial institutions must often keep transaction records for several years.

However, soft delete is not a "one-size-fits-all" solution; it also has some disadvantages:

• Slow DB performance: Excessive soft-deleted records can lead to slower query performance and increased storage space usage.
• Data privacy: Different countries have data retention laws that may conflict with indefinite data retention. For instance, the European Union's General Data Protection Regulation (GDPR) includes "the right to be forgotten", allowing people to request to delete their data.
• Explicit handling with raw SQL: When performing raw SQL queries on a table with soft-deleted records, you need to filter records explicitly by the deleted_at column and maintain the state of many-to-many relationships and other associations. Constraints like ON DELETE CASCADE will NOT work with soft deleted records; this can lead to unexpected situations, such as deleted records appearing in query results if not properly filtered.

Developers should choose whether to employ soft delete or not based on their specific project requirements. A common practice is to retain soft-deleted records for a particular period (compliant with local regulations), then permanently delete them after this time has passed.

Conclusion

GORM provides different methods for handling record deletions in a database. Understanding these features will equip you with the knowledge to manage data effectively in your Go applications using GORM.

Always remember that the specific requirements of your application should guide your choice of whether to use features like soft delete, batch deletions, and managing deletions with associations.

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