Create PostgreSQL Tables Using Supabase's Interface

Lecturer: [0:00] Let's begin by creating our first table inside of our Supabase project. I'm going to click create a new table, and then I'm going to give it some configuration details.

[0:09] The first table we're going to create is called user, and it's just going to be a table containing all of the users for our project. I'll just say users for our app is the description, and then the primary key is going to be the actual identifying key for our user.

[0:26] It has to be unique, meaning that every user has one ID, and it only belongs to that user. It's a unique way to identify a specific user.

[0:36] By default, this is an integer. That would be one, two or three, but I tend to like to switch it to a UUID, which is a randomly generated alphanumeric string, and it'll be a little bit more unique. It also means that people won't be able to guess IDs.

[0:53] With an integer, if a user is user 1, the next user is user 2, you can pretty much guarantee that you'll be able to find a user at ID3, and sometimes that's not desired for security reasons. Because of that, let's just set it to UUID, and you'll see what that looks like here in a sec. I'm going to click save, and it's going to create a new table.

[1:15] You can see it has some create new table user, adding primary key of user. Here we go, we've added our first table. Once we begin to have users, they will be shown here. Before we create our first user, let's actually add a new column, which we can do by clicking this plus here.

[1:33] We're going to add another piece of information here which is the username. I'm just going to say it public username in interface, not a great description, but If you've worked with basically any application that has usernames, you know what a username is.

[1:49] The type here we have a ton of different options. I'm just going to give it a value of text, and this is going to be just a text field that is a username. We can specify a default value here, and we can say whether it's allowed to be empty or not.

[2:05] In this case, we'll say, yes, it's allowed to be empty and there is no default value. I'll save that, and it will add that new column to our user table.

[2:16] With that in mind, we can actually create our first user. As I mentioned, these are columns, these are the specific fields inside of this table, and a column of ID, and a column of username, and now our row here is going to be our first actual instance of a user. Let's create one. This needs to be a UUID of some kind. When we use supabases auth, it'll create a UUID for us.

[2:40] For now, I'll just go find a UUID somewhere. You can just do a Google search for UUID generator or anything like that. I'll just paste in a UUID I found here from a UUID generator.

[2:51] Then for username, I'm just going to put my first user. Now I can save that. You can see we have our first instance of a user. Again, this is our first row which has an ID -- this is a UUID -- and then a username of my first user.

[3:06] Now, we'll create our second table. This is going to be a new table called message. This is going to be a list of messages inside of a chat server. I'll just say messages sent from users. In some ways, this is going to be a lot like what we did before. It's going to have an ID. This is going to be an integer in this case because we don't need it to be a UUID.

[3:29] It may be useful in this case to know how many messages have been sent. We can do that by just using integer. Then we can say, for instance, if the idea of a message is 40, we know that there has been 40, or I guess 41 if I would be inclusive. There have been 41 messages sent so far inside of our application.

[3:49] I'm going to add a new table. You can see it creates it. It adds a primary key column ID to message. Now we're going to add a couple more fields here, a new text column that's going to be called content. This is going to be the content of the message. Then we're going to say that it is not allowed to be empty, but there is no default value.

[4:09] This means you will not be able to create a new message without this content being set. Let's save that. We're going to add another field called created app. This is going to be a type of timestamp. You can see that the default value here gets changed to a drop-down, or you can give it a value.

[4:28] In this case, we want to use this function called now, which is provided to us by default. We want to say, "Allow empty is not true." What's going to happen here is it will say, "This new column called created at, when a message is created, defaults to now." That will say, "Hey, this is created now," whenever this thing is inserted into the database.

[4:50] Using this will be able to understand when this message was created. I'll just save that. We'll have another column there. The final column here is going to be user ID. This is going to be the first of what we call a foreign key relation. This is a way of corresponding different tables together.

[5:10] A user ID is going to be of type UUID. We're not going to allow it to be empty. What's going to happen is this needs to be related to a column on another table. The table here will be user and the column will be ID.

[5:28] Anytime that a message is created, the user ID column on that message needs to match the column ID on the table user. If we save that, what we can do is create our first message coming from a user. To do that, I'm just going to come back to user here.

[5:45] I'm going to copy this ID. Then when I create a new message, I'm going to say OK, this ID here. Well, that'll be auto-generated. The content will be hello. Our user ID here is going to be a UUID. Note, this is a foreign key to user ID. I'm going to paste in the UUID here.

[6:03] Then if I click this, you can see that it actually goes and looks up the associated user and says, "OK, this is my first user." This will be a valid foreign key relation to that user.

[6:15] Now if I save that, what happens here is I can save this message, which is hello, and was created at, you can see that gets filled in here as May 12th. This user ID field here relates specifically to this user.

[6:29] I have this relationship set up now between messages so users have messages that they personally send. As you might imagine, we will implement that in the UI to allow users to send messages.

[6:42] Then when someone else reads a message, they can see, "Oh, it's from this specific user," based on this user ID field.