Soft deletes in PostgreSQL and MySQL
Soft delete is a technique used in databases to mark records as deleted without physically removing them from the database. Instead of permanently deleting data, a flag or a separate column is used to indicate that a record is "deleted" or no longer active. This approach allows for the possibility of later recovering or restoring the deleted data if needed.
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
By implementing soft delete, you retain the deleted data in the database, allowing for potential future retrieval or analysis. It also maintains data integrity by preserving relationships and references to the deleted records. However, it's important to note that soft delete does consume storage space, so consider periodically purging or archiving the deleted data if it's no longer needed.
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)
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
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"`
}
