Project Files

• Starter Repo

• Lesson Solution

• Redwood's Documentation for working with the prisma.schema file

• Prisma's Documentation

• Prisma VS Code Extension

• Supabase

Transcript

[00:00:00]

[00:00:04] Welcome to the advent of JavaScript. This is the day one challenge. And in today's challenge, we are going to set up our database for our full stack application. And this will just allow us to help set up the infrastructure for our application so that we can work with real data as we hotwire all the pieces within our application.

[00:00:22] Now, if you're new to the backend, that's totally fine. The awesome part about working within Redwood is a true full stack framework. So it already has an API layer built in for us. And you'll find that if you know JavaScript, you're still writing JavaScript within the backend, which is really nice.

[00:00:38] If you're new to database design, let me walk you through it. So I provided a UML diagram, which is really just a picture of what your database should look like. When you're working with databases, there are two main principles that you need to keep in mind. One is that every table should have an ID. And this will just allow you to easily reference that data anytime you want to be able to grab something out of it.

[00:01:01] You know that every row will have its own unique ID. Now you'll notice there are a couple of, I'm going to say tables, that don't have IDs. And that's because these are actually enums. What that means is, for a role, I want to establish an admin and a user. those are the only two values that can possibly exist for a role.

[00:01:22] So, an enum is unique to Postgres, which is the type of database that we're going to be using maybe you've heard of SQL, SQLite, or MongoDB. Any of those are perfectly great options, but within Postgres you have the ability to use an enum.

[00:01:35] So that, again, that's just saying these are the only values that can possibly exist for our user status. Now the second principle is that you want to reduce duplication within your database. So for example, instead of typing out for this status, invited, declined, or accepted, every time a user is invited, declined, or accepted, we want to store that, in this case as an enum, or as a relationship on another database.

[00:02:02] And the reason being, is that if you type out invited every time, you might misspell it. Or another time you might call it invite, or invites, or invited. And so by abstracting that data, not only are you making it easier to work with, you're also reducing your chances of introducing bugs or errors. So within our application, we're going to have a users table.

[00:02:24] And this is all the information that we need for our users. We need an email, a password, first name, last name, their avatar. And then it's also just a good idea to know when the user was created, when it was last updated, and then I'm going to give our user a role. Every user can also have a status to know whether they have accepted, declined, or have been invited to a particular event.

[00:02:45] And you'll notice these errors, show those relationships between the table. Here where I'm saying event ID, I'm actually referencing the event table to say this is the content that is related to our user status.

[00:02:57] because this is a Secret Santa application, every user will be paired to another user. So our pairings here allow us to establish the event that they've been paired for. The person that they're being paired with and when this particular pairing was created or last updated. Then every user can also have a wish list.

[00:03:16] So this allows them to set up the items that they would like to receive. And so they can include a name, a URL, this user ID says what user set that as their wish list item when it was created and updated. If there's a specific order that we want to have within our listing, then The event that this wish list applies to, so say you have one list for 2023, you have a different list for 2024.

[00:03:41] It also provides a site image, a site title, and a site description for that particular wish list item. So some of these things might make a little bit more sense if you look at it inside of Figma So I'm gonna jump over here and let's take a look at our wish list. So you'll see that we have a site URL a site title a description and an image And if we want to control the order that these are being displayed in.

[00:04:03] So it might make a little bit more sense if you look at this UML diagram in parallel with Figma. Then we also have a thank you table. So this would allow somebody to write a thank you note after the event has passed. And they can send a message to a specific user. So I think some of this stuff will start to make sense as we continue to work within the code.

[00:04:23] But for right now, for today's challenge, you have all of the information that you need within this diagram. So with that said, let's actually jump into the code. Since this is our first challenge, I do want to help you get up and running. So, if we come over to GitHub, this is the starter repo. You can either download the zip, you can copy the URL to git clone it, which is what I'm going to do, or you can open this up directly within Gitpod.

[00:04:48] Okay, like I said, , I'm going to use git, to come over to my terminal. So this is Warp. I'm going to go into my projects directory and I have a redwood folder that I'm going to use and I'm going to say git clone and I'm going to paste in that url. And now I can navigate inside that particular folder. So this is our starter project.

[00:05:10] Now I'm going to run yarn install and this will download all of the project dependencies that we need. Now this might take a minute the first time you run it. Which is totally fine, you just have to do this the first time. But it's grabbing everything that we need. Awesome. So now I'm going to open this up within VS Code. So the first thing that I want to do is I want to set up our database. So, if I look at my environmental variables, I have an env. defaults file. So I can just duplicate this. And I'm going to call this dot env and I need to give it a database URL to work with.

[00:05:45] Now you could install Postgres on your machine. It's pretty simple to work with. What I think is even easier to use is a back end as a service such as Supabase.

[00:05:55] So I'm going to come over to the browser and I'm going to head over to Supabase and it's free to set up an account. I'm going to create a new project here within my organization and I'm going to call this Secret Santa. I'm going to give it a database password and it will generate a password for us, which is perfect.

[00:06:13] Now I live in Tennessee, so I'm going to use East US, but use whatever region makes sense for you. And I'm going to say create our new project. Now this will take a minute for it to get up and running.

[00:06:25] Now, once everything is up and running, you should see the screen change. Now, I'm going to go into our project settings, click on database, and now there's a section here for connection string. And this URI is the connection string that we want. So I'm going to hit copy and then come back over to VS Code and I'm going to paste this in.

[00:06:45] Now you'll need to replace your password with the password that you set up when you created your database.

[00:06:53] I'm going to hide this because this is a secret. But because this is inside my env file, that's included in my gitignore and will not be committed to the repo.

[00:07:02] Now I'm going to go into my API directory inside my DB folder and inside my prisma. schema file. And here we're saying that the provider is SQLite. That's not true. Supabase is a Postgres database. So I'm going to change the provider there.

[00:07:17] Now if we scroll on down, we have an example model already set up as a user example. So I'm going to delete that to do and I'm going to delete example there. So we have our user model. Now a model is just like a table on a database. So we just want to copy. This list into our schema dot Prisma file as you may have guessed from the name this is using Prisma So we will be working with the Prisma API to set up our database so if you need to look at any documentation for setting up modeling there is some within the Redwood documentation and But there's also some within Prisma's documentation, and I'll include links to both of those within the lesson notes.

[00:07:58] So first if we look at line 14, we're setting up an ID, that's great. We're making it an integer, so it's a number, that's great. And then we're also establishing a couple of properties here. So we're saying that this is the ID field in our table, which is important.

[00:08:13] It needs to know how we're referencing that information. And then we're also setting a default value here so that it will auto increment. That just means that every time a row or a new user is added to our table, we don't have to tell it what the ID is. We don't have to check to see, well, there's five users, so now you're going to be six.

[00:08:30] Now there's six users, you're going to be seven. The database will handle all of that for us. Next up, we want our email address, which we already have, and this is a string. We want it to be unique so that two users cannot be using the same email address. And this is important if we're using email for the user to be able to log in.

[00:08:49] Next up, we have our password. This will get handled with authentication, so we don't need to add that right now. Next up, we have first name. So I'm going to change this name field to be first name, and this is going to be string. Now the question mark at the end means that this is an optional property.

[00:09:05] A user doesn't necessarily have to have a first name. I'm going to hit control shift D to duplicate this line, because we want to give a user a last name. And again, this is going to be optional. Next up, we have an avatar. And this should be optional as well, but that's going to be a string of data.

[00:09:23] And if we come on down, we have created at, and this is going to be a type of date time, and it can default to now, so we don't have to tell it when it was created. The database will take care of that for us. Then we can also use updated at, and fortunately for us, we don't have to manage that either.

[00:09:42] The database can figure out when it was last updated. And then lastly, we have role. So this is going to be an enum. So let's go ahead and set that up.

[00:09:50] So we can say enum, role, and we can pass in the two options that it can have either admin or user. Now if we come back to our role value here, we can say that this is going to be a type of role. So it's referencing our enum. And by default, a user should be a user, not an admin. I'm going to give this a save.

[00:10:12] You can see that there's a little bit of formatting. Prisma has a fantastic VS Code extension that helps do a lot of the heavy lifting for us and has some great IntelliSense. Now let's go ahead and set up our user status table. So here, I'm going to establish a new model. I'm going to call this user status.

[00:10:32] And inside, we want to make sure that we have our ID field. I'm just going to copy this from above and paste that in.

[00:10:39] Here we have the event ID, which we haven't set up our event table yet, so I'll come back to that. Next, we have our user ID, which is going to be an integer. because it's referencing our ID on our users table, which is an integer. But in order to get this relationship to work, what we need to do is we also need to let Prisma know that this is a user, and it's going to be a type of user.

[00:11:05] We also want to establish the relationship here. So we're going to reference the user ID that we set on the table and it's going to reference the ID on our user table. So let me walk through that one more time because this can be a little confusing if the syntax is new to you. But the user ID here allows us to reference the user on our table.

[00:11:28] But we also need to set up that relationship. So we're saying that we have a user that is a type of user. So we're referencing our user model. And then we're going to set up our relationship. So we're going to reference our user ID field here on our user status model.

[00:11:44] And it's going to reference the ID on our user model. And we also have to set it up going the opposite direction. So here on our user model, We need to let it know that we're going to have a status that is going to be a type of user status.

[00:12:01] Now you'll notice here that I still have an error. So what it's saying is that I have a one to one relationship that must use unique fields on the defining side. So it's a little confusing. But within databases there are several different types of relationships that you can have.

[00:12:15] A one to one means that there's only one user and one status, which is not true. We could have multiple user statuses if there's multiple events. So in order to make this a one to many, so that there's one user with potentially many statuses for these different events.

[00:12:32] We're going to use square brackets here so that we're using an array and it knows that there could be many user statuses. Okay, let's come back to our diagram here. And let's go ahead and set up what the actual status could be. So here, I'm going to say status, and this is another enum that we're going to set up.

[00:12:54] So I'm calling this user status and the possible values here are invited, declined, or accepted. So now for our status, this could be a type of user status.

[00:13:07] Now it's saying that I have an error here because user status already exists as a model. Let's actually change this to be status. And I will replace it here and on our table as well. Perfect. So now all of our linting looks good. So I'm going to scroll on down.

[00:13:26] Now let's set up another model. This time for our event. Now for this particular id, we're using a string so we can reference a string here.

[00:13:37] We do want it to be an ID column, but for the default value, we're gonna have it generate a random UU id, which will be a random string of characters. Now we wanna give it a name, which will be a string, and we want it to have a date, which has a type of date time. Then we also want to have a created at, which will be our date time, default to now, updated at, which will be a date time, defaulted to updated at, which again the database will take care of all that stuff for us.

[00:14:10] And then let's set up a send reminder option. And this will be a boolean, so a true or false value, and we can default that to false.

[00:14:19] So now that we have set up our event table, let's go back to our user status table and set up this relationship. So, I'm going to scroll up just a little bit, and here we want to set up our event ID, and this is going to reference a string, because our ID on our event model is a string. Then I'm going to set up our event. So this is going to reference a type of event, because here we have a model. We're going to set up our relationship, so it's going to reference the event ID field here for our user status. And we're going to reference our ID field on our event model. Or we're getting a linting error because we need to establish the relationship going in the other direction.

[00:15:00] So down here on our event model, let's add a user status. We want to set that up as a user status. Perfect. So I'm going to give that a save. And let's come back. We have just three more tables that we need to work through. So next up, we have a model for pairings. And this will allow us to set up the relationships for our secret Santa. So the first thing here is we have an ID field.

[00:15:24] And this is going to be an integer. And it's going to be a type of ID. And by default, we want it to auto increment. Then we have an event ID, which is a type of string. So again, it's referencing our ID here. Let's go ahead and set up that relationship that is an event where we're going to reference our event model and set up our relationship so that it's using our event ID here and it's referencing the ID on our event model.

[00:15:52] Then we need to get the relationship set up going in the opposite direction. So our pairings is going to reference our pairings model. We can have multiple pairings. So now let's set up our Santa ID, which is going to be an integer, and then we want to set up that relationship so that's pointing to our users table. So I'm going to say Santa. It's going to reference our user model, we want to set up that relationship so that we're referencing our Santa ID and it's referencing the ID on our user model.

[00:16:23] Now let's set it up going in the opposite direction. So if we come back up to our user model, I'm going to set up Santa, And so this is going to reference pairings. The user might have multiple pairings if it's over multiple years. So now let's come back down here to our pairings model and let's add another field for our person ID. So this is going to be an integer. I'm actually just going to duplicate this line here, except we want to change Santa for person.

[00:16:53] Then we need to come back up to our user model and set that up going the opposite direction. So instead of saying Santa, we're saying person.

[00:17:01] So I am getting an error here because it's saying that I have an ambiguous relationship. Basically, both Santa and person are referencing pairings and it's not exactly sure what to do with that. So the way to resolve this is we need to add an at relation. We need to give this a unique name so that it knows what we're working with.

[00:17:20] I'm going to call this pairings Santa, and I'm going to call this relation name equals pairings person. So now we want to take this and go back down to the bottom, and we need to give this a name to say that here we are working with pairings Santa. And here, this is using our name, pairings, person.

[00:17:45] So we've explicitly tied those pieces of data together. Now let's go back to the browser. So we have our person ID. Let's add two more fields. We need our created at, which we've set these up before. So this should be pretty straightforward. So both of those are using date time.

[00:18:01] And then we want to set up another model for our wish list. So we will call this model wishlist. This is going to use an ID with a type of integer. We also want to set up a name with a string. I'm going to set up a URL with a string. We want to set up a user ID, so this will be an integer. We also need to set up that relationship.

[00:18:27] And then we need to set up the inverse of that. So if we scroll up to the top where we have our user model, I'm going to set up my wish list. And this will reference our wish list model. I'm going to scroll back down. Now we want to set up our created at and updated at. So I'm just going to copy this from above.

[00:18:47] We want to set up an order. And that can be an integer. I'm going to make that optional. We want to give it an event ID. So again, we're setting up another relationship. So in this case, our event ID is a string. But then we need to set up that relationship. So we're going to reference the event ID property here on the event ID field.

[00:19:09] And we need to set up the opposite relationship here. So we'll say this is a wishlist and let's scroll back down.

[00:19:17] Now let's add our site image, site title, and site description. So site. image, which is a type of string that can be optional. Our site title, which is a type of string, and our site description, which is also a type of string. Give that a save. Now last but not least, we need to set up our thank you model.

[00:19:38] So I'm going to create a new model here called our model, and this will have an ID with a type of integer. Then I'm going to have an event ID, which will be a type of string. I'm going to reference our event model. We need to set up the inverse here. So this will be thank you.

[00:19:58] Scroll back down. Then I want to grab our user ID, which will be a type of integer. We want to set up that relationship. So now we need to set up the inverse. So I'm going to scroll up here to the top. We have our user model and set up our thank you. Let's scroll back down to the bottom. And now we have a two user ID. This is the user that wrote the thank you note, and they're sending it to this particular user.

[00:20:25] So I'm going to add this to user ID, which is an integer. And this will be a type of user. Set up that relationship. And while we're here, let's go ahead and set up the name. So that's going to be thank you to user. We can copy this and paste this here. So this will just be user.

[00:20:48] And we'll also want to copy this value and come up to our user table and provide that named relationship. Let me fix the value there. Perfect. This might get a little confusing. So let's actually make this the thank you author. That might be a little bit more clear and they're sending it to this particular user. So we can say thank you to user. And now we have this relationship.

[00:21:17] So user and to user. Perfect. Next up, we have our message, which will be a type of string. And then we also have our created at, which is a date time.

[00:21:31] We can default that to now. Now we've set up all of our data models.

[00:21:35] Now we need to actually apply our models and our structure to our SupaBase database. So in order to get that to work, I'm going to go over to the terminal I'm gonna run yarn Redwood Prisma Migrate Dev. And this should take a minute, but it should run without a hitch.

[00:21:53] So now it's asking me to name my migration. So I'm gonna call this initial setup. I'm gonna hit enter. And so you can see now that it created a file inside of my migrations folder. So let's take a look at this. Now, inside my db, I have a folder called Migrations.

[00:22:09] This has the timestamp and then the name that I just gave this migration and if we take a look It generated all of the SQL code for us that we don't have to write, which is awesome.

[00:22:20] I don't know about you, but I've written SQL code before, and I would prefer not to write it more than I have to. So the benefit to this is if I make changes to the database on my end, and you're working with the same database on your machine, Your database is not going to know about the changes that I made.

[00:22:37] So what it does is it sets up a file to say, Hey, these are all the changes that were made. So now when you pull this code onto your computer, you'll run a migration and it will make those same changes to your machine. So it keeps everything in sync so that you don't have to.

[00:22:51] So now if I go back over to the browser, I'm going to pull up Supabase and let's actually look at our table editor. Hey, check that out. Here are all the tables, all the models that we just created and we have all of the fields set up. We don't have to do any clicking around in Supabase.

[00:23:08] Our migration and Redwood and Prisma handled all of that for us. So congratulations on completing the day one challenge. Look forward to catching you tomorrow as we continue to build out our full stack application.