Soft deletes in PostgreSQL and MySQL

Introduction

Soft deletes allow marking rows as deleted without actually deleting them from a database. You can achieve that by using an auxiliary 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,nullzero"`
}

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 include deleted_at column to indexed columns using coalesce function 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 indexed columns, you can configure Bun to append zero time as 1970-01-01 00:00:00+00:00 by removing nullzero option:

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