delete associated records in Supabase

Question

When using Supabase, is there a clean / simple way in which I can delete all associated records together with an item? Say I have Posts and a Post can have many Comments. When I delete the Post I want to delete the Comments as well.

Something like dependend: :destroy in Rails, basically.

const { data, error } = await supabase
  .from('posts')
  .delete()
  .match({ id: 123 })
  .options({ destroyDependent: true }) // set some option that tells Supabase to delete all associated records as well.

dshukertjr · Accepted Answer · 2023-01-01T11:53:46.147

Yes, there is!

The magic happens not when you delete the data, but when you first create your comments table. It's also not a Supabase feature, but rather a postgres feature.

When you create a foreign key constraint, you can set a delete cascade option to tell the table to delete any related data.

For example, you can create a comments table like this:

create table if not exists public.comments (
    id uuid not null primary key DEFAULT uuid_generate_v4 (),
    post_id uuid references public.posts on delete cascade not null,
    user_id uuid references public.users on delete cascade not null,
    created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
    text varchar(320) not null
);

Notice the delete cascade keyword on post_id and user_id definition. Adding these will delete the comment entry if the related post or user is deleted.

Currently, there is no way of creating a column with delete cascade option in Supabase UI, so you would have to create such table using SQL.

Also, if you already have a table and would like to add this delete cascade option, you would have to remove the foreign key constraints and re-add it with delete cascade option. You can find out more about how to add delete cascade to an existing table here, but if your app is not in production, it might be easier to just delete the table and re-create it from scratch!

Edited

If you scroll a bit more to see the answer by @Justus Blümer, you can see how you can alter an existing table to add delete cascade!

score 10 · Answer 2 · answered Sep 12 '22 at 13:29

Based on Tyler's tip here's how to change an existing key constraint to allow for a cascading deletion.

List all your foreign key constraints by executing this in the SQL editor in Supabase. From there, identify the key that you need to change. Insert the name of the table with the items that you'd like to delete based on the deletion of a parent item in another table:

SELECT con.*
    FROM pg_catalog.pg_constraint con
        INNER JOIN pg_catalog.pg_class rel
                      ON rel.oid = con.conrelid
        INNER JOIN pg_catalog.pg_namespace nsp
                      ON nsp.oid = connamespace
    WHERE 1=1
         AND rel.relname = 'YOUR_CHILD_TABLE';

Change the appropriate foreign key constraint (here: child_parent_id_fkey to support cascading deletes:

ALTER TABLE public.YOUR_CHILD_TABLE
DROP CONSTRAINT child_parent_id_fkey,
ADD CONSTRAINT child_parent_id_fkey
    FOREIGN KEY (parent_id)
    REFERENCES YOUR_PARENT_TABLE(id)
    ON DELETE CASCADE;

Very helpful! Worked like a charm to retrofit existing tables. — BTCETHswap, Nov 30 '22 at 06:22
Wonderful solution, have created an [interactive code generator](https://index.garden/a/supabase-cascade) out of this for quick use. — David Wolf, Dec 06 '22 at 13:35

score 0 · Answer 3 · answered Sep 19 '22 at 14:41

This is what worked for me if you already have a table and need to alter it. in REFERENCES you need to mention 'public' before table. See example to understand

    alter table
      public.child drop constraint child_parent_id_fkey,
    add
      constraint child_parent_id_fkey 
    foreign key (parent_id) 
    references public.parent(id) on delete cascade;

delete associated records in Supabase

3 Answers3

Edited