Add Relationships Between Tables in Supabase Using Foreign Keys
Supabase is a collection of tools around a PostgreSQL database. Postgres is a relational database, which means we can create tables that are related to other tables - by referencing columns with a foreign key relationship.
In this video, we query our auth.users table to inspect the data that Supabase stores about our user. This is a special table that is managed by Supabase, and not something we should modify ourselves.
In order to store additional data about our user, we create a profile table. This will contain our user's stripe customer ID and whether or not they are currently subscribed. A profile should only ever exist for a specific user, therefore, we can create a relationship between these two tables.
Additionally, this will enforce constraints to stop our data becoming corrupted. It will not be possible to create a profile for a user that does not exist, and we will not be able to delete a user that has a profile.
Instructor: [0:00] Every time someone signs into our application, a new user is created in Supabase. We can view this under Authentication>Users. If we want to see the additional fields stored for our user, we can come over to the SQL view, create a new query, and say SELECT * FROM auth.users. We can see all of the data that Supabase is using to authenticate our user.
[0:22] This is a special table that's maintained by Supabase. If we want to store additional information, such as whether the user has subscribed or not, we need to create our own table. Let's call this one Profile. Profile is going to have an IsSubscribed column that's going to be of type Boolean, and the default value is going to be False.
[0:41] We're also going to store the interval of the user's subscription, so whether they're paying monthly or yearly, which is going to be a text field. Since we want each of the rows in our Profile table to map to a row in our auth.users table, we can set up a relationship between the two. We would like to reference auth.users, and we would like to reference the ID field.
[1:03] We can click Save to set up that foreign key relationship, and then we can save our table. Now, we have our own Profile table to store additional bits of data about our user.
Watch out - make sure row level security is disabled in this example's profile table. Otherwise updates won't work.