Executing Raw SQL using Supabase's Interface

Share this video with your friends

Send Tweet
Published 3 months ago
Updated 2 weeks ago

In this lesson, we will run raw SQL in supabase over the user table to understand the relationship between users and other information captures from the authorization in our user's table.

Kristian Freeman: [0:00] Now, to show you how this user corresponds to the auth users table, let's run just a little bit of actual raw SQL, and let's understand how these things fit together.

[0:09] Let's go to the SQL here, the SQL Tab, and then click the New Query button. I'm just going to give this a query name by renaming it. It's a little easier to see called something like, find public user from auth table. This is just a playground for running SQL statements.

[0:29] What I want to do here is I want to say SELECT* from auth.users. I just want to see everything that's in the auth users table. I'll run that, and you can see I have this single row here with an instance ID, ID role, AUD email.

[0:47] I want to look at specifically the ID here. If I run SELECT ID from auth.users and you can see, this is a UUID with fc8b0e5a, etc. This ID, although we could do some more SQL magic here to pull this out, such as the variable and then use it to look it up.

[1:06] Instead, the easier way to make sure this is what we'd expect is, let's just remember, fc8b0. This is an ID coming from our auth.users table. If we come back to our public table here for user, there is an ID here of sc8b0, etc. Write the rest of the UUID.

[1:26] We know is that when this new user was created using our GitHub OAuth login workflow, another user corresponding to that was created, or I should say, a public user, not another user. In fact, the same user was replicated on to this public user table.

[1:45] We can use this to refer to things like username later on in our application. We will go and fill out all of that information. It's worth noting that this is the conclusion of this new user trigger that we wrote earlier. Though, it took a long time for it to pay off.

[2:01] You can see that it's now working as expected.