Build a Fullstack App with Next.js, v2 Setup Database Schema with Prisma

[00:00:00]
>> Now let's write a higher schema. So I'm not gonna pretend that I'm some database admin expert that knows everything about optimizing indexes and things like that because I'm not, that's just not what I do. I'm definitely more of a product engineer and I kind of just work with whatever tools I need to build something that people are addicted to or at least I like to think that.

[00:00:23] So this is just how I think about modeling data from my perspective. What I like to do is think about all the information that's on the screen and kind of walk backwards from there. So if I have a design from a designer, I can look at that design and look at all the information that's in that design and that can help me derive the models that I need to create.

[00:00:46] And then there's auxiliary models and connections and relationships that exist from there. And then there's things that aren't on the screen but still need to be maintained in the database and that's other things. But for the most part, when it comes to the product, it's what's on the screen and how do you walk backwards from there?

[00:01:02] And then from there, it's, well, what do the queries look like? Because that's big too. Depending on the queries you write, that's gonna determine what indexes you create, because that's why you have indexes in the first place is to optimise queries. And that's pretty much where I tap out, anything beyond that I let the database experts handle that.

[00:01:17] But we won't even get there, so we're good. Just wanted to give you my thought process on how I model data because you might go through this and be, [SOUND] I would have done that differently. But now you know how I think about it. That's why I did it the way that I did it.

[00:01:31] So anyway, let's do it. If we just take a quick look at what we have here, It's not a complicated schema. I mean, it's gonna be a pretty simple project task management app, where we have a user and the user has projects and those projects have tasks. And that's mostly it.

[00:01:50] There's a lot of relationships between the three, but that's mostly it. So what we can do now is we can go ahead and create this user. So the first thing I'm gonna do is make an ID and go through and kind of get all the other stuff in there.

[00:02:05] So you can say Model User, like I said, this is an ID it's a type of string. You can also do integer if you prefer and have your user IDs be numbers, I don't like that, so I'm gonna say String. And them gonna say this is an id, like that.

[00:02:26] And then I can say default. And I'm gonna use uuid. So this is just basically making the user IDs strings of type uuid. A uuid is a type of universally unique identifier. It's a formatted string that looks a certain way, that's pretty much guaranteed to be unique, that's what UID is.

[00:02:49] So I'm making my user IDs UIDs. Say created at, of course, this is gonna be a date time, and it's gonna default to now. Then I can say updated at. That's also gonna be a date time, and then there's a special updated at. So we don't have to touch it, it just updates that field for us automatically whenever we touch this model.

[00:03:20] Okay, then the users are gonna have an email, which is gonna be a type string. First name, which is also a string, a last name. We have a password. Password,.that's a string. What else do we have on here? Projects and tasks, we'll, get to that in a minute when we do relations.

[00:03:45] So for user, for the most part, that's pretty good. So then we'll keep it moving and we'll say, model project, and we can just copy these first three things here, put them here. Everything's gonna have an ID, a create app, an updated app, so I'm just gonna put that there.

[00:04:07] And a project has a name, that's a string. What else would a project have? It might have a status. No, we have the owner stuff. Yeah, so a project will also have who owns the project. Who does it belong to? If we don't have this, when we do a query, we won't know what project belongs to the user that signed in.

[00:04:30] We'll just be getting random people's projects. So we can say owner ID like this and that's gonna be a string. And then we can say owner. Is going to be a user like that. And then we can say it's gonna have a relation with these fields of ownerId, that maps to the reference on the user's id like that.

[00:04:58] And it's still gonna throw me this little error here. Basically this is saying is, I wanna have an owner field whose type is user and it's related to A user ID and it maps to the owner ID over here. So this owner ID is gonna map to the ID on the user.

[00:05:16] And it's freaking out right now it's, hey you gotta go to the other side this relationship add something there and we could do that but I'm really lazy. So I'm gonna do it the lazy way, you can just run MPX Prisma format and it'll do it for you.

[00:05:33] So I did that and you can see right here added project project, but I'm gonna lowercase that and call it projects plural because it's an array of projects. It's a one to many relationship, so I'm just gonna change that to projects. And keep it the type project array.

[00:05:54] All right, keep it moving with the project. What else do we have on here for the project? We have a description, a due. We got some tasks. And then we got whether it's deleted or not, so let's do that. So for description, We can say String. Whether it's due or not, that's gonna be a date time.

[00:06:17] But maybe we don't want every single one of these to have a description. Maybe that's optional, so you can put a question mark after the type and now it's not required. By default every field is required unless you put a question mark after it. So I'm saying the description is optional, the due date is optional, not all projects have a description, not all projects have a due date, maybe you have an open project that never ends, so you do that.

[00:06:44] And then we'll get back to the tasks when we do the relationships and now we could just do delete it. So whether this was deleted or not, this is something that a lot of people do for soft deletes. You ever heard of a soft delete? Basically means we're never actually gonna remove it from the database.

[00:06:59] We're just gonna flag it, so we can filter it out on our queries. So that way if you ever restore your account or if you wanna restore this, it's always there, right? That's how they get away with that. So, that's why your data is never deleted, it's just flagged.

[00:07:13] All right, so deleted. We can say that's a boolean and it defaults to false And then we'll keep going. We'll do another one for task. And like I said, we can copy these three. Actually, we can probably copy these four right here, put that in there. And then tasks have a status, a task can be started, completed, whatever other status those are gonna be called enums.

[00:07:48] They're basically a set of different values that never change and we wanna be able to use one of those. So we can have an enum type here and we can call this task status. Like that and then we can just add those statuses. So I have them here, not started, started completed like that.

[00:08:09] Yes
>> If a person said they'd rather soft delete just because you can implement undo right away if the user deletes.
>> Yeah, 100%, so, yeah, that's why they have the soft delete so you can get those people to come back and take their money. So, that's why it's there All right, and then for status we'll say, let me actually uppercase this.

[00:08:41] There we go for status is gonna be type task status. And we can say it's gonna default to just one of these which will probably just be not started, there we go. A task belongs to a project, so we can say projectId, we'll just type String. We can say projects, we'll just type projects that has a relation to the field called project ID and mapping that to the field of ID on a project.

[00:09:16] And because I'm lazy, I'm just gonna go to my terminal, hit up and then enter, format that. And it's gonna add the task to our project but it's plural I'm gonna lowercase it call it tasks Okay, and what else do we have on a task? I know I'm missing something, so yeah, I think yeah, tasks also have owners.

[00:09:41] I'm just gonna put that on there and then description, due date and also delete. So let's do that. So we'll put the owner on here. I think there's a query where there's a view where you wanna get all the tasks that a user created no matter what project they're on.

[00:09:57] So you would need an owner field here because if you didn't have this field, the only query you could make to get all the tasks regardless of the project. So to first get the all the tasks for every project, and then add them together. Just adding this one field saves you that, so that's why we have that here.

[00:10:14] So this is why I think it's important to look at the data that's on the design so you can kind of understand what fields you have to add and relationships and whatnot. So we got that. We also need to do whether this is due or not. Date time.

[00:10:31] This also has a status. Does it have a status? Yeah, no, we already did status. It has it deleted And this is Boolean and it's gonna default to false. Gonna run this again a format that, gonna go back up to our owner you can see it says task lowercase that pluralize it, now we have tasks.

[00:11:04] I think for the most part I got everything in there. The only other stuff I have in here are these unique things, these index things. So I'll put these in here and I'll talk about them. Basically what I'm doing here, for instance on the project. This is telling Prisma that we wanna create a unique constraint on this complex field.

[00:11:27] So we're taking the combination of an owner ID and a name and saying that has to be unique. This is saying a project owned by one owner can't have the same name, so you can't have two projects with the same name. That's what this is saying. And then this is an index.

[00:11:44] It's not it's not a unique constraint, but I am telling it to set aside an index. Because I'm probably gonna write a query for this. I'm probably gonna say I need to find a project that belongs to this owner but has this ID. So I wanna write an index for that.

[00:11:57] Otherwise, it'll scan the entire projects table looking for a project. So it's based off the information that you're looking for, so that's why that index is there. But sometimes you don't really know what these are ahead of time. Indexing strategy happens in production, after production, as you build out features.

[00:12:18] So sometimes you just don't know until you understand what the queries are after you write them so you don't get it right all the time. I have to go back and write these after I wrote the queries, cause I didn't know if I needed them or not. And I probably have some others for, yeah, for the task.

[00:12:37] So, this one is just saying, I wanna index the owner ID of a task because I'm probably gonna write a query that says, give me all the tasks that belongs to this owner. So I need an index there, otherwise, it'll be, it'll still work without the index it'll just be bad.

[00:12:54] Some databases will allow you to turn on the ability to have an error thrown if you make a query that's not indexed. I know you can do that in Mongo, you can say throw me an error if you detect me making a query that isn't taking advantage of an index.

[00:13:09] And then that forces you to go write the index and that's how I learned how to do indexing was just let Mongo scream at me until I go do it.