00:00 You might think that the first place you'd go is the seed script and we can just create all of our stuff in here and live our lives happily. But we need all of the same permissions and roles data in our production database as well. This isn't created by a user. We don't really have an admin interface for this yet, maybe in the future we will,

00:19 but we'd probably require permissions to access the admin interface. In any case, if we want to provision a new database or something like get a new developer set up on our environment, it'd be nice to be able to just have a really easy way to get that new environment up like

00:38 going and not have to worry about running some script or something. What we can do, one of the really, really cool things about Prisma is that it generates these migration.sql files that are just all of the SQL that we need to run to migrate our database.

00:56 Here's where we're creating the permission role and all of that stuff. What we need to do is we need to add a bunch of insert statements to here and then we can just have this all be part of our regular migration, which is pretty sweet. The challenge is I don't personally really

01:13 savor the experience of writing raw SQL. What we're going to do is write a little script that will help us out. I'm going to create a new script file called tempignored.js right here. I'm going to copy and paste a script. I'll just walk you through this.

01:31 It's not super important for your learning outcomes here. All that we're doing here is we're saying a data source URL. This is going to be different from our regular database. You could stick it in the regular database, but I just want to put it separate because it will make some of this easier.

01:49 We're going to make a temp.ignored.db. The .ignored is just ignored in our .gitignore, so this database doesn't end up in source control. That's why I add the .ignored in different file names. We have our data source URL. We're going to create a new database that has all of our migrations applied.

02:08 Then we're going to make a new Prisma client that is pointing to that. Then we'll create our permissions and our roles. We're going to take the user and note, the create, read, update, and delete on own and any, and create a permission for each one of those permutations. We end up with, I think, 16 permissions.

02:27 Then we create roles based on those permissions as well. For the admin role, they have access of any, so we're going to take all the permissions that have any access, and that will be our admin role. For our user role, this is going to have access to your own stuff.

02:45 All of the things that you create, you will have access to as part of the user role. Then we'll say we're all done. With that then, I can run node temp ignored. This is going to apply all the migrations we have,

03:02 and then run our script here to create those values. We can take a look at the temp ignored DB, and we'll see that we've got no users in there, no notes, none of these things, but we should have permissions, there they are, and roles.

03:19 Now what we need to do is somehow export this data so that we can get the insert statements that would be required to create this data in our migration script. We want to stick some inserts right here. What we're going to do to make that work is run

03:36 a special script from the SQL Lite 3 CLI. It's called dot dump. This is a special command that you can execute within SQL Lite 3, that will basically take a given database, so that's our temp ignored,

03:54 and we'll dump things out into another file here. Our database is under Prisma and temp ignored, so let's update this. It'll be dot slash Prisma slash temp ignored. Now we've got our temp ignored SQL. There we go.

04:12 Now this is all of the SQL that would be required to create that SQL Lite database. Now there's a bunch of stuff that we don't really need in here for what we're trying to do, so we're creating tables and stuff. Our migration script already does all that stuff. The Prisma migrations, we don't need to worry about that either.

04:31 We're just going to come down to the part, let's get rid of all this. There we go, to the part where we're inserting permissions, and then it also is creating our roles. There's some insertions in our roles, and then our relationships between the permission and role, we need to keep those as well. Then our relationships between

04:50 the role to user this table already is created as well, and all these index are created as well. All we need are the insert statements into our permissions, the roles, and then the permission to role table as well. Now we copy all this stuff and stick it at the bottom,

05:07 and you can add manual migration if you want to, and we'll stick those there, and we're all set. So now this will be part of our regular deployment when we're deploying our database and everything. So what we can do now is npx prisma migrate deploy.

05:28 Oh, actually, no, it'll be reset, there we go. Reset the local database to apply, reapply all of these migrations like our roles. And so now if we take a look at our data.db, we should have roles and permissions, which we do, solid.

05:46 So we don't have something else though, something else that's important, and that is we say that this is the admin user, Cody. Well, we didn't add admin. So let's take care of our applying roles to the users we're creating. So we're going to connect a roles, here we go.

06:05 And say connect, or the name is user, there we go. And that takes care of all of our generated users. And then down here, we're going to need the name is user. And then right here, we're going to say connect, but we want to connect multiple. So we're going to provide an array here,

06:26 and we'll say name admin as well. So now if Copilot can get out of my way, we have Cody created with the user and admin role. And then while we're at it, let's go to the auth server. And where we're creating our user right here,

06:44 let's connect this user to, let's see, roles, connect, name, user. You got to spell name correctly though. TypeScript won't like that very much. Okay, great. So now we're handling all of our seed data, connecting to the proper role. Creating new users,

07:05 connecting those users to the proper role. So the last thing, just to make sure this all worked, is let's run the reset again. And that will run all of our migrations, including the roles, which has our manually written SQL. Well, manually written SQL. And so if we take a look at our data DB,

07:25 then we should see our permissions and roles are in there. Our permission to role and role to user now has a relationship for every one of our users in the database, which is pretty awesome. So great job on this one. There's a fair bit of stuff we had to do, kind of funny things to make this work.

07:44 But the point is that when you have a situation where you've got a table that needs to have values in it as part of like when the database is initially created, then what you need to do is manually update the migration script so that it will include all of that data that you need to have

08:03 as part of that assumed to be created data. So it can't be in the seed script because that's just for development. This is the sort of thing that needs to be in production or like created as part of the production migration or seeding. And so we just stick that in the manual migration. However you get those insert statements to work,

08:23 you could write them manually if you want to, but I don't want to. So we used SQLite 3 to dump a temporary database that has all those values we wanted. And then we ran the migrations and we made sure to update all the places that relied on this data. So when we create new users, as well as when we seed users into the database

08:43 and we are all set. Awesome work.