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:

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

To select undeleted (live) rows:

1. 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:

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

For such models Bun updates rows instead of deleting them:

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

1. UPDATE users SET deleted_at = current_timestamp WHERE id = 123

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

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

1. SELECT * FROM users WHERE deleted_at IS NULL

To select soft-deleted rows:

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

1. SELECT * FROM users WHERE deleted_at IS NOT NULL

To select all rows including soft-deleted rows:

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

1. SELECT * FROM users

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

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

1. 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:

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

5. created_at timestamptz NOT NULL DEFAULT now(),
6. deleted_at timestamptz
7. );

You can create a view that omits deleted users:

1. CREATE VIEW users AS
2. SELECT * FROM all_users
3. WHERE deleted_at IS NULL

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

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

4. ID   uint64
5. Name string
6. }

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

1. var deletedUsers []User
2. err := db.NewSelect().
3. Model(&deletedUsers).
4. ModelTableExpr("all_users").
5. Where("deleted_at IS NOT NULL").
6. 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:

1. 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:

1. 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:

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