Implementing Soft Deletes with supabase-js

[kiwicopple]

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 PostgreSQL views and the supabase-js library.

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

---

What Are Soft Deletes?

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.

---

Step 1: Add the deleted_at Column

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

Run this SQL in your Supabase SQL editor:

ALTER TABLE items ADD COLUMN deleted_at timestamptz;

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

---

Step 2: Create a View for Active Records

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

CREATE VIEW active_items AS
SELECT *
FROM items
WHERE deleted_at IS NULL;

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

---

Step 3: Soft Delete a Record

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

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

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

---

Step 4: Query Active Records

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:

const { data, error } = await supabase
  .from('active_items') // Query the view, not the table
  .select('*');

if (error) console.error('Error fetching active items:', error);
else console.log('Active items:', data);

---

Step 5: Restore a Soft-Deleted Record (Optional)

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

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

This effectively un-deletes the record.

---

Benefits of Using Views for Soft Deletes

• Cleaner Queries: No need to add WHERE deleted_at IS NULL to every query. Just query the view (active_items).
• Separation of Concerns: Views abstract the logic of filtering deleted records from your application code.
• Efficiency: PostgreSQL handles the filtering in the view, reducing the complexity in your app.

---

Full Example with supabase-js

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

// 1. Soft delete an item
await supabase
  .from('items')
  .update({ deleted_at: new Date().toISOString() })
  .eq('id', 123);

// 2. Query active (non-deleted) items
const { data, error } = await supabase
  .from('active_items') // Query the view, not the table
  .select('*');

if (error) console.error('Error fetching active items:', error);
else console.log('Active items:', data);

// 3. Restore a soft-deleted item
await supabase
  .from('items')
  .update({ deleted_at: null })
  .eq('id', 123);

---

Conclusion

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

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!

[psteinroe]

Great guide! I you want to take it a step further, I can recommend using a RULE. It "converts" any DELETE into an UPDATE. Makes it worry-free, since clients dont need to know about soft-deletions at all.

CREATE RULE "_soft_deletion" AS ON DELETE TO "items" DO INSTEAD (
  UPDATE items SET deleted_at = now() WHERE id = old.id AND deleted_at is null
);

a great blog post on this topic: https://evilmartians.com/chronicles/soft-deletion-with-postgresql-but-with-logic-on-the-database

[CarelFdeWaal]

Something to consider is the performance impact of soft deletes. Adding soft deletes can impact query performance, as filtering on deleted_at becomes common. To mitigate this, consider adding a composite index on the id and deleted_at columns:

CREATE INDEX idx_id_deleted_at ON items (id, deleted_at);

This is particularly effective for optimizing joins and queries involving both columns.