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 rows from a database, whether previously soft deleted or not:
db.NewDelete().Model(user).Where("id = ?", 123).ForceDelete().Exec(ctx)
DELETE FROM users WHERE id = 123
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"`
}
Monitoring performance
To monitor Bun performance, you can use OpenTelemetry instrumentation that comes with Bun and Uptrace.
Uptrace is an open source DataDog competitor that supports OpenTelemetry tracing, OpenTelemetry metrics, and logs. You can use it to monitor applications and set up automatic alerts to receive notifications via email, Slack, Telegram, and more.
You can also check the following guides to monitor features specific to your RDBMS:
