GORM Soft Deletes and How They Surprise Your Queries

Soft delete is the feature people turn on in an afternoon and spend a year working around. The idea is sound: instead of removing a row, mark it deleted, keep the data for undo and audit, and hide it from normal reads. GORM makes it one field. The surprises are all in the places where a marker column meets something that does not know about the marker: a unique index, a join, a foreign key, a raw query. This article runs each of those against PostgreSQL 18.6 and shows what happens.

It builds on models, where gorm.Model and DeletedAt first appear, and scopes, since soft delete is implemented as one. The audit side of keeping deleted data is in audit logs in Go.

How it works

Add a gorm.DeletedAt field to a model, or embed gorm.Model. From then on:

• Delete becomes an UPDATE that sets the field to the current time.
• Every query on the model adds AND deleted_at IS NULL.
• Unscoped() removes that condition, and Unscoped().Delete(...) really deletes.

The lab prints the delete statement through a dry run, so you can see what is sent: UPDATE "users" SET "deleted_at"=$1 WHERE "users"."id" = $2 AND "users"."deleted_at" IS NULL. There is no DELETE anywhere. The extra IS NULL in the WHERE makes a second delete of the same user a no-op that affects zero rows.

package main

import (
	"errors"
	"fmt"
	"log"
	"strings"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type User struct {
	ID        uint
	Email     string         `gorm:"uniqueIndex"`
	DeletedAt gorm.DeletedAt `gorm:"index"`
}

type Comment struct {
	ID     uint
	UserID uint
	Body   string
	User   User
}

type Account struct {
	ID        uint
	Email     string `gorm:"index:idx_acct_email,unique,where:deleted_at is null"`
	DeletedAt gorm.DeletedAt
}

func main() {
	db := connect("l0716", &gorm.Config{Logger: logger.Discard})
	must(db.AutoMigrate(&User{}, &Comment{}, &Account{}))

	u := User{Email: "[email protected]"}
	db.Create(&u)
	db.Create(&Comment{UserID: u.ID, Body: "hello"})

	// Delete becomes an UPDATE.
	stmt := db.Session(&gorm.Session{DryRun: true}).Delete(&User{}, u.ID).Statement
	fmt.Println("Delete runs:", strings.Join(strings.Fields(stmt.SQL.String()), " "))
	db.Delete(&u)

	var n int64
	db.Model(&User{}).Count(&n)
	var all int64
	db.Unscoped().Model(&User{}).Count(&all)
	fmt.Println("visible:", n, "including deleted:", all)

	err := db.First(&User{}, u.ID).Error
	fmt.Println("First after delete:", errors.Is(err, gorm.ErrRecordNotFound))

	// The scope is on the model you query, not on the ones you join.
	var cs []Comment
	db.Joins("User").Find(&cs)
	fmt.Println("comment's user after soft delete via Joins:", cs[0].User.Email == "")
	var raw []Comment
	db.Preload("User").Find(&raw)
	fmt.Println("comment's user after soft delete via Preload:", raw[0].User.ID)

	// The unique index still counts the deleted row.
	err = db.Create(&User{Email: "[email protected]"}).Error
	fmt.Println("re-create same email:", strings.Contains(err.Error(), "SQLSTATE 23505"))

	// A partial unique index fixes it.
	db.Create(&Account{Email: "[email protected]"})
	var acct Account
	db.First(&acct)
	db.Delete(&acct)
	err = db.Create(&Account{Email: "[email protected]"}).Error
	fmt.Println("re-create with partial index:", err)
	err = db.Create(&Account{Email: "[email protected]"}).Error
	fmt.Println("third live duplicate still rejected:", strings.Contains(err.Error(), "SQLSTATE 23505"))

	// Restore and hard delete.
	db.Unscoped().Model(&User{}).Where("id = ?", u.ID).Update("deleted_at", nil)
	db.Model(&User{}).Count(&n)
	fmt.Println("visible after restore:", n)
	err = db.Unscoped().Delete(&User{}, u.ID).Error
	fmt.Println("hard delete with a comment pointing at the user:", strings.Contains(err.Error(), "SQLSTATE 23503"))
	db.Unscoped().Where("user_id = ?", u.ID).Delete(&Comment{})
	db.Unscoped().Delete(&User{}, u.ID)
	db.Unscoped().Model(&User{}).Count(&all)
	fmt.Println("rows after Unscoped().Delete:", all)

	// Update on a soft deleted row affects nothing.
	db.Delete(&acct)
	res := db.Model(&Account{}).Where("id = ?", acct.ID).Update("email", "[email protected]")
	fmt.Println("update of a deleted row, rows affected:", res.RowsAffected)
}

Delete runs: UPDATE "users" SET "deleted_at"=$1 WHERE "users"."id" = $2 AND "users"."deleted_at" IS NULL
visible: 0 including deleted: 1
First after delete: true
comment's user after soft delete via Joins: true
comment's user after soft delete via Preload: 0
re-create same email: true
re-create with partial index: <nil>
third live duplicate still rejected: true
visible after restore: 1
hard delete with a comment pointing at the user: true
rows after Unscoped().Delete: 0
update of a deleted row, rows affected: 0

Visibility

After the delete, visible: 0 including deleted: 1. A normal Count skips deleted rows. Unscoped().Count sees them. First(&User{}, id) returned ErrRecordNotFound as if the user never existed. All of that is what you asked for, and it is why soft delete is attractive: no query in the codebase has to remember a filter.

The cost is that the filter is invisible. A new developer who looks at a table in psql sees rows the application never returns. A report written in raw SQL, which GORM does not rewrite, includes deleted rows unless it adds the condition by hand. Anything that reads the table without GORM (an analytics job, a SQL view, a different service) has to know about deleted_at, and the safest way to give that knowledge to them is a view: create view active_users as select * from users where deleted_at is null.

Related rows disappear

The lab put a comment on the user, soft deleted the user, then loaded the comment with its user two ways. With Joins("User") the user's fields were empty (the lab printed true for an empty email), and with Preload("User") the user's ID was 0. Both queries added the soft delete condition to the user table, so the comment is shown as if it had no author.

That may be right (a deleted user is gone) or wrong (a comment history should keep its author's name). It depends on the product, and it needs a decision, not a default. If the author must survive for display, either do not soft delete users and anonymise them instead, or load the user with Unscoped() in that query: Preload("User", func(tx *gorm.DB) *gorm.DB { return tx.Unscoped() }). Test whichever form you choose against your GORM version, because the scoping rules for joined tables are easy to get subtly wrong.

The unique index trap

This is the most expensive surprise. The User table has a unique index on email. After the soft delete, the lab created a user with the same email and got SQLSTATE 23505, a unique violation. The deleted row is still in the table, so as far as the index is concerned the email is taken.

For a real product this means a person who deletes their account cannot sign up again with the same address, and the error message tells them the address is in use. Three fixes exist:

1. A partial unique index. create unique index on accounts (email) where deleted_at is null covers only live rows. GORM can declare it with the index tag option where: gorm:"index:idx_acct_email,unique,where:deleted_at is null". The lab's Account model does that. After deleting the account, creating another with the same email returned <nil>, and a second live duplicate was still rejected with 23505. This is the right answer on PostgreSQL, which supports partial indexes. MySQL does not, so there the usual workaround is a composite unique index over (email, deleted_at) with a non null sentinel, or a generated column.
2. Mangle the value on delete. Rewrite the email to deleted+<id>+<email> in the same update. It works anywhere and it is a hack that corrupts the data you meant to keep.
3. Do not soft delete this table. Move deleted accounts to an archive table in the same transaction. Uniqueness stays simple and the live table stays small.

Pick one when you add the field, because changing the index later means a migration on a live table.

Restore and hard delete

Restoring is an update that clears the field: Unscoped().Model(&User{}).Where("id = ?", id).Update("deleted_at", nil). The lab did that and the visible count returned to 1. You need Unscoped() for the WHERE, because without it the deleted row is not matched.

A hard delete with Unscoped().Delete hit a different wall: the comment still referenced the user, and PostgreSQL refused with SQLSTATE 23503. The soft deleted row is part of the data model, and other tables reference it. This is a purge job's problem, and a purge needs to delete in dependency order, or the foreign keys need an ON DELETE CASCADE (a risky default, see associations). After the lab removed the comment first, the hard delete worked and the table was empty.

Updating a soft deleted row through the normal scope affects nothing. The final line of the lab shows rows affected: 0, and no error. A handler that updates a deleted record gets a successful call that did nothing, so check RowsAffected where it matters.

Counts, caches and search indexes

Other systems that hold copies need to hear about deletes. A cache entry, a search index document or a materialised view built from the table keep the deleted row until something removes it. With a soft delete the database never emits a delete, so a trigger or a CDC pipeline watching for DELETE sees nothing. If those exist, publish a "deleted" event from the code that does the soft delete (inside the transaction, via an outbox, not from a hook; see hooks).

Table size is the other cost. Deleted rows stay in the table and in its indexes until a purge removes them, so queries scan past them and the table grows without bound. A scheduled job that hard deletes rows older than the retention period keeps that in check.

When to use it

• Undo is a product feature (trash, restore within 30 days).
• Audit requires keeping the record and an archive table would complicate reads.
• Foreign keys make hard deletes impractical and data must stay referenceable.

When not to

• When the real need is history. An append only audit table records who changed what and when, without bending every query.
• When the table has unique business keys and your database lacks partial indexes.
• When a legal requirement says deleted means erased. A soft delete is not an erasure, and a retention job must remove the data.

Checklist

• Declare a partial unique index for any unique column on a soft deleted table.
• Decide how related reads treat a deleted parent, and use Unscoped in the preload where history must show.
• Write raw SQL and views with deleted_at is null in mind.
• Plan a purge job that deletes in foreign key order.
• Check RowsAffected after updating by id.

The behaviour is documented in the GORM delete guide, and partial indexes in the PostgreSQL partial index documentation.