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.
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!
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!