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
Instructor: Let's add a like button for each of our tweets. Rather than just pretty-printing out our array, let's take our array of tweets and map over them. For each tweet, we want to render out a div with its key set to our tweet's ID.
Inside here, let's have a paragraph with our tweet.profiles.name and our tweet.profiles.username, and then another paragraph with our tweet.title. We can see some red squigglies here because TypeScript is not happy. Our tweets could be null. We can use optional chaining here to say, only map over it if it's an array.
Tweet.profiles is also possibly null. Our good friend, optional chaining, can help us out again, and for this name, for this one and for this one. Now, if we go back to the browser and refresh, we'll see each tweet with its author's name, their username and the contents of the tweet. Let's add a like button. For this, we're going to create a new table for likes.
From the Supabase dashboard, go to the Table Editor and then New table. The name of this one is going to be likes. We're going to leave row level security enabled, and then we're going to make the created_at column not nullable and add a new column for user_id, which we're going to set up with a foreign key relationship to the profiles table pointing to the id column.
When that profile is removed, we want to cascade those deletes through to this likes table. Let's click Save to set up that relationship. We're going to click this cog and untick Is Nullable as it doesn't make sense for there to be a like without a user. Let's add another column for the tweet that this user is liking.
Again, we're going to set up a foreign key relationship to the public schema and the tweets table, referencing the id column, and set up cascade deletes. Let's click Save on that one, make this one not nullable and click Save to create our likes table.
Now, we want to be able to insert new rows into this table to represent a user liking a particular tweet and delete rows from this table to represent a user unliking a tweet. Let's enable those two actions with RLS policies. Under Authentication and then Policies, we want to create a new policy on the likes table. We'll create this one from scratch.
Our policy name is going to be, authenticated user can like tweet. It's going to enable the INSERT action. We're going to target the authenticated role, and again, we want to make sure that the user_id column for this like matches the user that's trying to perform this action. They're not trying to like a tweet as another user.
Now we can click Review and then Save policy. Then create another policy from scratch. This one's going to be, authenticated users can delete their likes. We're going to enable the DELETE action targeting the authenticated role and checking that the user_id column matches this user's ID. Let's review and save this policy.
So that our application can display those likes, we're going to need a select policy. We're going to create this one from scratch as well. The policy name is going to be, anyone can select likes. It's going to enable the SELECT action. We want this to apply to all roles, and we want our condition to be true. Let's click Review and then Save policy.
Now we have a likes table which we can insert, delete and select values from.