Modeling Permissions and Roles in Prisma Database
00:00 Let's go over to our schema.prisma file, and we'll come down here and we'll create these models. So model permission, and we'll let Copilot fill in some of this stuff for us. So we're going to have an ID that's like everything else we've done. We'll have an action entity and access.
00:17 So this would be our create, read, update, delete. I'm going to add that as a comment. We can't do enums as part of SQLite. You could do an enum with other databases. I've done that before. In retrospect, I actually didn't really like doing that very much for various reasons,
00:35 but there is a danger here where we are specifying this as a string, and so you could potentially put in an action that doesn't make any sense. And so that's where doing validation with a Zod or something like that can be really helpful throughout the application code.
00:54 And you can have some spots specifically where it's like all the permission stuff goes through these utilities to make sure that you never end up with an action entity or access that isn't valid. So we also have our entity. So I'll add a comment there for the user or note. Those are the only entities
01:12 that we're going to add support for, but you could, of course, add entity support for user image and note image and stuff like that. But yeah, those are what we're going to be thinking about for this first part. And then own or any.
01:29 So this is pretty basic access. Like if I'm the owner, I'm the creator, for example, the owner of the note. So we've got the owner right here. If I am the owner, then I have access, or if I have the any access,
01:47 that means I can access and perform this action on this entity for any model in the database. So that would be for admins, for example. And yeah, then we've got our description. This would be useful in a admin page where you're looking at the roles and you want to configure and create new roles
02:06 and different things like that. And then create a permission that you attach to a role. You can describe what that permission is for. We make that optional because often it's going to be self-explanatory. This is a create user own permission or a update user own, probably be more sensible. Okay, great.
02:27 And then we have our roles and we add a unique constraint on the action entity and access. It wouldn't make sense to have a permission or have multiple permissions that have the same combination of those. And so adding that unique constraint will help with that. Okay, great. So now let's make a role model. Ha, a role model.
02:47 Ha, ha, ha, ha. Okay, so we've got the ID that you would expect, the name. Just a nice way to easily identify the particular role and kind of describe it a little bit. We do also have a description that's optional. And then our updated at, created at, all that stuff. And then our permissions. So a role can have multiple permissions
03:08 and a permission can belong to multiple roles. Finally, a role can belong to multiple users. And so we'll come up to our users model and we'll say roles. And that's an array of roles. Now, you'll notice there's no IDs. There's no like foreign keys connecting these different tables.
03:26 And that's because these are many-to-many relationships. And so you, like a permission belongs to multiple roles. So you couldn't have a role ID. A role belongs, can have multiple permissions. And so you wouldn't have a permission ID either, be many of them. And so let's actually do our migration
03:45 px prisma db migrate. Actually, whoops. It's just a migrate. There we go. And it is migrate dev. There we go. And to, for the name, I think roles is a fine name for this. But let's take a look at the migration that was created for us.
04:06 It's in the Prisma directory under migrations. And we look at roles and take a look at the SQL. So this is kind of interesting. So we've got our permission. None of this should be too much of a surprise, how that works. And we've got a role. But the surprising thing potentially is this permission to role. So this is a table that Prisma creates for us
04:26 that has the foreign keys to associate these two models together. So this is a table responsible for that relationship. This is one of the challenge with, or challenges with SQL and just SQL-based databases is that you have to have these types of tables.
04:45 But the cool thing is that Prisma makes it so you don't have to worry about making those models. Prisma will make them for you. And then we also have indexes. So we have that unique constraint that we added to our permission. That is going to give us an index, which means that when we look up a particular permission,
05:04 that lookup will be pretty quick. This isn't actually all that useful, to be honest, because we're not gonna have that many permissions anyway. And so even if we were doing a table scan, every time we're making a query, there's not much to scan anyway. But it's also not harmful either. So I'm fine just leaving this the way it is.
05:24 Then we have a permission to role because that is unique as well. We're not gonna have two rows in that relationship table that relate to these permission to roles. Like twice, that wouldn't make any sense. So we can make an index for that.
05:42 And then we'll have a permission to role B index. So this would be the other constraints that we've got on these tables. Yeah, and then the vice versa, or the role to user is gonna have indexes as well. But again, the indexes on these
06:01 actually probably not gonna do a whole lot. Maybe the role to user could do a little bit more because we're gonna have more users, but we're not gonna have a ton of permissions, not gonna have a ton of roles. So those indexes, they're not harmful, but they probably won't help a lot. But yeah, most interesting thing here
06:18 is just these tables that Prisma creates for us to manage those relationships. And now users will have roles, and then you can get the permissions from those roles. So we're in a pretty good spot as far as our database is concerned. Good job.