Implementing soft deletes with supabase-js

在构建现代应用时，软删除是一个常见的功能，它让你可以“删除”数据，同时保留数据以便将来可能恢复或进行历史跟踪。这在审计追踪或需要撤销意外删除时尤其有用。Supabase 借助 Postgres 视图和 supabase-js 库，让这个过程变得非常简单。

🌐 When building modern applications, soft deletes are a common feature that lets you "delete" data while retaining it for potential recovery or historical tracking. This is especially useful in audit trails or when accidental deletions need undoing. Supabase makes this process seamless with Postgres views and the supabase-js library.

在这篇文章中，我们将展示如何使用 Supabase 实现软删除，以及如何使用 Postgres 视图来高效管理你的数据。

🌐 In this post, we’ll show how to implement soft deletes using Supabase and how to use Postgres views to manage your data efficiently.

什么是软删除？ #

🌐 What are soft deletes?

软删除并不会从数据库中移除记录。相反，它们通过更新一个特定的列（通常叫做 deleted_at）为时间戳来标记为“已删除”。这样数据仍然保留在数据库中，但在大多数查询中会被排除，除非明确需要。

🌐 Soft deletes don’t remove a record from the database. Instead, they mark it as "deleted" by updating a specific column, often named deleted_at, with a timestamp. This keeps the data in the database but excludes it from most queries unless explicitly required.

步骤1：添加 `deleted_at`#

🌐 Step 1: Add the deleted_at column

要实现软删除，先在你的表里加一列 deleted_at。

🌐 To implement soft deletes, start by adding a deleted_at column to your table.

在你的 Supabase SQL 编辑器里运行这个 SQL：

🌐 Run this SQL in your Supabase SQL editor:

1
alter table items
2
add column deleted_at timestamptz;

这个列会存储记录被“删除”的时间戳。

🌐 This column will store the timestamp when a record is "deleted."

步骤2：为活动记录创建视图 #

🌐 Step 2: Create a view for active records

为了默认只获取未删除的记录，可以创建一个 Postgres 视图，过滤掉 deleted_at 不为空的行。

🌐 To ensure you only fetch non-deleted records by default, create a Postgres view that filters out rows where deleted_at is not null.

1
create view active_items as
2
  select *
3
  from items
4
  where deleted_at is null;

有了这个视图，你现在可以查询 active_items 而不是 items，只获取活动的（未删除的）行。

🌐 With this view, you can now query active_items instead of items to get only active (non-deleted) rows.

第3步：软删除一条记录 #

🌐 Step 3: Soft delete a record

不要删除记录，而是用当前时间戳更新它的 deleted_at 列。用 supabase-js 看起来像这样：

🌐 Instead of deleting a record, update its deleted_at column with the current timestamp. Using supabase-js, it looks like this:

1
await supabase.from('items').update({ deleted_at: new Date().toISOString() }).eq('id', 123)

这会为 id 为 123 的项目设置 deleted_at 列。

🌐 This sets the deleted_at column for the item with id 123.

步骤4：查询活动记录 #

🌐 Step 4: Query active records

要只获取未删除的行，请查询 active_items 视图而不是 items 表。用 supabase-js 可以这样做：

🌐 To fetch only non-deleted rows, query the active_items view instead of the items table. Here's how you do it with supabase-js:

1
const { data, error } = await supabase
2
  .from('active_items') // Query the view, not the table
3
  .select('*')
4
5
if (error) console.error('Error fetching active items:', error)
6
else console.log('Active items:', data)

步骤 5：恢复一个软删除的记录（可选） #

🌐 Step 5: Restore a soft-deleted record (optional)

要“恢复”一个软删除的记录，把 deleted_at 列改回 null 就行了：

🌐 To "restore" a soft-deleted record, set the deleted_at column back to null:

1
await supabase.from('items').update({ deleted_at: null }).eq('id', 123)

这实际上就是把记录恢复了。

🌐 This effectively un-deletes the record.

使用视图进行软删除的好处 #

🌐 Benefits of using views for soft deletes

更简洁的查询： 不需要在每个查询中都添加 WHERE deleted_at IS NULL。直接查询视图 (active_items) 就行。
关注点分离: 视图将过滤已删除记录的逻辑从你的应用代码中抽象出来。
效率: Postgres 会在视图中处理过滤，从而减少你应用里的复杂性。

使用 supabase-js 的完整示例 #

🌐 Full example with supabase-js

这里有一个使用 supabase-js 实现软删除的完整示例：

🌐 Here’s a complete example of implementing soft deletes with supabase-js:

1
// 1. Soft delete an item
2
await supabase.from('items').update({ deleted_at: new Date().toISOString() }).eq('id', 123)
3
4
// 2. Query active (non-deleted) items
5
const { data, error } = await supabase
6
  .from('active_items') // Query the view, not the table
7
  .select('*')
8
9
if (error) console.error('Error fetching active items:', error)
10
else console.log('Active items:', data)
11
12
// 3. Restore a soft-deleted item
13
await supabase.from('items').update({ deleted_at: null }).eq('id', 123)

结论 #

🌐 Conclusion

在 Supabase 和 Postgres 中实现软删除很容易。通过将 deleted_at 列与视图结合使用，你可以清晰地区分活动记录和已删除记录，让你的应用逻辑保持干净且易于维护。

🌐 Soft deletes are easy to implement with Supabase and Postgres. By combining a deleted_at column with views, you can cleanly separate active and deleted records, keeping your application logic clean and maintainable.

这种方法既能保留数据以便审计或恢复，又能保持你的应用界面干净高效。今天就开始在你的 Supabase 项目中使用软删除吧！

🌐 This approach provides the flexibility of retaining data for audits or recovery while keeping your app's interface clean and efficient. Start using soft deletes in your Supabase projects today!