Create a PostgreSQL Join Table in Supabase Studio

We create a PostgreSQL join table for likes in the Supabase dashboard. This has a many-to-many relationship between the profiles and tweets table, allowing us to store each instance of a like between a user and a tweet.

Additionally, we create Row Level Security (RLS) polices to enable select, insert and delete.

Code Snippets

Create likes table

create table public.likes (
  id uuid default gen_random_uuid() primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  user_id uuid references public.profiles on delete cascade not null,
  tweet_id uuid references public.tweets on delete cascade not null
);

Enable Row Level Security

alter table public.likes enable row level security;

Enable insert action with RLS policy

create policy "authenticated users can insert their own likes" ON "public"."likes"
as permissive for select
to authenticated
using (user_id = auth.uid());

Enable delete action with RLS policy

create policy "authenticated users can delete their own likes" ON "public"."likes"
as permissive for delete
to authenticated
using (user_id = auth.uid());

Enable select action with RLS policy

create policy "authenticated users can select likes" ON "public"."likes"
as permissive for select
to authenticated
using (true);

Resources

• Managing user data docs
• Row Level Security docs