Database: Delete

Delete Operations

Deleting data in Supabase uses the .delete() method combined with filters to target the right rows. The most common pattern deletes a single row by its primary key:

const { data, error } = await supabase
  .from('posts')
  .delete()
  .eq('id', 42)
  .select();

The .eq('id', 42) ensures only the row with that specific ID is deleted. Without a filter, .delete() targets every row in the table — a potentially catastrophic operation in production. Always double-check your filters before running a delete.

Adding .select() returns the deleted rows, which is useful for confirmation messages or undoing the deletion on the client side:

const { data, error } = await supabase
  .from('posts')
  .delete()
  .eq('id', 42)
  .select();  // data contains the deleted row

Deleting with Conditions

You can delete multiple rows by using any filter. For example, deleting all drafts older than a year:

const { data, error } = await supabase
  .from('posts')
  .delete()
  .eq('status', 'draft')
  .lt('updated_at', '2025-01-01')
  .select();

To delete a specific set of rows by ID, use .in():

const ids = [1, 2, 3, 4, 5];
const { data, error } = await supabase
  .from('posts')
  .delete()
  .in('id', ids)
  .select();

This is efficient for bulk cleanup operations like "delete selected" in an admin panel.

Cascade Deletes

When you define a foreign key, you can specify what happens to related rows when the parent is deleted. The ON DELETE CASCADE option tells Postgres to automatically delete all child rows. For example, if your comments table references posts:

CREATE TABLE comments (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
  body TEXT NOT NULL
);

Now deleting a post automatically deletes all its comments. Without CASCADE, Postgres raises a foreign key violation error if any comments exist — you would have to delete the comments first, then the post. Cascade is convenient but dangerous: a single delete can wipe out thousands of related rows silently. Use it deliberately and only for relationships where child rows have no meaning without the parent.

Soft Deletes Pattern

Hard deletes remove data permanently. Once committed, the data is gone (outside of backups or point-in-time recovery). Many production applications prefer soft deletes instead: marking a row as deleted without actually removing it. This gives you a safety net and preserves referential integrity.

The typical implementation adds two columns:

ALTER TABLE posts ADD COLUMN is_deleted BOOLEAN DEFAULT false;
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ;

Then "deleting" becomes an update:

const { data, error } = await supabase
  .from('posts')
  .update({ is_deleted: true, deleted_at: new Date().toISOString() })
  .eq('id', 42)
  .select();

To exclude deleted rows from queries, add a filter everywhere you select:

const { data, error } = await supabase
  .from('posts')
  .select('*')
  .eq('is_deleted', false);

Or, for a more robust approach, create a view that automatically filters out soft-deleted rows and query that view instead of the table.

RLS Policies for Delete

Row Level Security applies to delete operations just like any other. A typical policy ensures users can only delete their own rows:

CREATE POLICY "Users can delete their own posts"
ON posts FOR DELETE
TO authenticated
USING (auth.uid() = user_id);

The USING clause determines which rows the delete can target. If a user tries to delete a row where user_id is not theirs, the operation silently affects zero rows — no error, just nothing happens. Always check the data array length after a delete to confirm rows were actually removed.

For admin roles that need to delete any row, create a separate policy:

CREATE POLICY "Admins can delete any post"
ON posts FOR DELETE
TO authenticated
USING (auth.jwt() ->> 'role' = 'admin');

Data Integrity and Foreign Keys

Foreign key constraints protect your data from orphaned records. If your comments table has post_id referencing posts(id), Postgres will not let you delete a post that still has comments — unless you use CASCADE or delete the comments first. This is a feature, not a limitation: it forces you to think about what deletion really means for your data model. Always design your foreign key constraints before writing delete logic, and decide per-relationship whether CASCADE, SET NULL, or RESTRICT is the right behavior.

Common Pitfalls

• Forgetting RLS. Without a FOR DELETE policy, the delete is rejected (if RLS is enabled) or allowed for everyone (if RLS is off). Always test with an authenticated user.
• Accidental bulk delete. Calling .delete() without any filter removes every row. Always chain at least one filter — and consider adding a safety check in your code that warns if no filter is present.
• Cascade side effects. A cascade delete can destroy thousands of rows in related tables. Know your foreign key graph. When in doubt, test with a SELECT first.