Soft deletes

Introduction

Soft deletes allow marking rows as deleted without actually deleting them from a database. You achieve that by using a flag column and modifying queries to check the flag value.

For example, to soft delete a row using deleted_at timestamptz column as a flag:

UPDATE users SET deleted_at = now() WHERE id = 1

To select undeleted (live) rows:

SELECT * FROM users WHERE deleted_at IS NULL

Using Bun models

Bun supports soft deletes using time.Time column as a flag that reports whether the row is deleted or not. Bun automatically adjust queries to check the flag.

To enable soft deletes on a model, add DeletedAt field with soft_delete tag:

type User struct {
	ID int64
	CreatedAt time.Time `bun:",nullzero,notnull,default:current_timestamp"`
	DeletedAt time.Time `bun:",soft_delete"`
}

For such models Bun updates rows instead of deleting them:

_, err := db.NewDelete().Model(user).Where("id = ?", 123).Exec(ctx)

UPDATE users SET deleted_at = current_timestamp WHERE id = 123

Bun also automatically excludes soft-deleted rows from SELECT queries results:

err := db.NewSelect().Model(&users).Scan(ctx)

SELECT * FROM users WHERE deleted_at IS NULL

To select soft-deleted rows:

err := db.NewSelect().Model(&users).WhereDeleted().Scan(ctx)

SELECT * FROM users WHERE deleted_at IS NOT NULL

To select all rows including soft-deleted rows:

err := db.NewSelect().Model(&users).WhereAllWithDeleted().Scan(ctx)

SELECT * FROM users

Finally, to actually delete soft-deleted rows from a database:

db.NewDelete().Model(user).Where("id = ?", 123).ForceDelete().Exec(ctx)

DELETE FROM users WHERE id = 123 AND deleted_at IS NOT NULL

Using table views

You can also implement soft deletes using table views. Given the following table schema:

CREATE TABLE all_users (
  id int8 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name varchar(500),

  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

You can create a view that omits deleted users:

CREATE VIEW users AS
SELECT * FROM all_users
WHERE deleted_at IS NULL

PostgreSQL views support inserts and deletes without any gotchas so you can use them in models:

type User struct {
	bun.BaseModel `bun:"users"`

	ID   uint64
	Name string
}

To query deleted rows, use ModelTableExpr to change the table:

var deletedUsers []User
err := db.NewSelect().
	Model(&deletedUsers).
	ModelTableExpr("all_users").
	Where("deleted_at IS NOT NULL").
	Scan(ctx)

Unique indexes

Using soft deletes with unique indexes can cause conflicts on insert queries, because soft-deleted rows are included in unique indexes just like normal rows.

With some DBMS, you can exclude soft-deleted rows from an index:

CREATE UNIQUE INDEX index_name ON table (column1) WHERE deleted_at IS NULL;

Alternatively, you can add deleted_at column to the index using coalesce to convert NULL time, because NULL is not equal to any other value including itself:

CREATE UNIQUE INDEX index_name ON table (column1, coalesce(deleted_at, '1970-01-01 00:00:00'))

If your DBMS does not allow to use expressions in index elements, you can configure Bun to not append zero time as NULL using allowzero option:

type User struct {
	ID int64
	CreatedAt time.Time `bun:",nullzero,notnull,default:current_timestamp"`
-	 DeletedAt time.Time `bun:",soft_delete"`
+	 DeletedAt time.Time `bun:",soft_delete,allowzero"`
}