Master Prisma Migrations Basics

hello guys today we're going to learn about Prisma migrate by the end of this video you will be able to create customize and run your migrations using Prisma migrate I'm in remix project with Prisma installed if you would like to know how to install Prisma in your remix or nextjs project please watch the video at Prisma to nextjs app router project Prisma uses model entity first migration pattern meaning that it creates migrations from its schema so let's first go ahead and Define the schema for our project we're going to open Prisma folder and schema. Prisma we will start with the obligatory users and posts let's go ahead and create model user and we're going to put the field ID it will be integer and we'll put at ID and at default Auto increment at ID means that it's a primary key and default value will be Auto increment the next uh field will be email it will be string and it will be unique then we have name which will be string as well posts and then we also add created ad which will be date time and will have default now value and then updated at date time as well and we're going to use Prisma at updated at so Prisma will uh insert the updated at date when it updates the record right here I'm going to put at at map users with a lower case that's how I would like to name my table because otherwise my table will be named uh users with a capital u but I like to name my tables or lowercase with underscores so the next model will be post and again ID integer it will be primary key so we put at ID and default will be Auto increment user ID field is will be integer and then we're going to have a title which is string and then content which will be also string and then a published will be Boolean field and then we're going to Define relationship to a user and user ID will be our foreign key and it will reference ID on user table so the next again we're going to put created at and updated at and then we're going to name our table as post with a lowercase the models user and post are ready so let's go ahead and create those tables in a database let me show you my EnV file I'm connected to my database it's called remore Prisma and let's go ahead and and open the terminal in order to run migration we're going to run npx Prisma migrate Dev now it asks us what would be the name for the migration we can call it create user create users and posts table and as you can see migration got created and applied so let's go ahead and take a look at the prismo folder again and now we have a migrations folder in this folder we have another folder called uh create users and post table right here with a Tim stamp and then we also have migrations log that Tel file uh which you don't want to mess around with so it's going to keep track of the migrations now let's go and jump into the create users and posts table and we have a migration. SQL file and it's just simple SQL file so you have create table users here SQL and create table posts let's go ahead and switch to table plus to see if those tables are got created and indeed we have uh three tables one of them is users and then posts and we also have a Prisma migrations table the same kind of in SQ eyes there is a table where Prisma keeps track of the migrations so let's go back to vs code and let's say we need to add a new column right we want to add it after content and we're going to call it uh summary right it also going to be type of string and want to have it as a small text right as you may have noticed title content now summary they're all string but in terms of my SQL there are different the string types right variable characters text tiny text so in Prisma you can have at DB where you can actually specify um particular for my SQL which kind of uh type of string would you like right so in our case I would like to have my summary as a tiny text let's go ahead and save the changes and you can see my file got nicely formatted this is because I have Prisma extension installed in my vs code so go ahead and install Prisma extension if you're working in Prisma in the vs code so your schema. Prisma file looks nice and it gets formatted now let's go switch to the terminal and and run again npx Prisma migrate Dev and again it's going to ask us for the migration name we can call it add summary column and again our migration ran successfully if we look at the migrations folder again we got another folder called add summary column and it also has a kind of time stamp right here and if we go in into migration SQL we're going to see that we have an alter table statement and it adds a column summary which is Tiny text and it's not null this is how you can create your migrations in development however if you're in production you want to run all those migrations right and you're going to be running it with a command npx Prisma migrate deploy now we're going to hit enter obviously all our migrations are in so it's going to say that um two migrations found no pending migrations to apply and this is how you can use Prisma migrate in your project unfortunately this flow has drawbacks because we are unable to check and review our migrations and because it just creates and run it for us right and we also may have unexpected results let's go switch to table plus we can go to the posts table right and let's go refresh it and as we can see the summary got added actually after updated at however we put it in our model after content another unexpected result may happen when you're renaming column right Prisma will actually drop the existing column and create a new one so if you have data in your table that data is going to be lost so basically we need an ability to review and customize migrations before we run it let's see if we can improve on this flow to do exactly that let's go Ahad ahead and reset unlike SQL I Prisma my grade doesn't have an easy way to roll back so we will have to delete all our tables right here in the database now let's go back to vs code we're going to delete this summary column and we also going to delete migrations folder let's go to package.json file and we're going to add couple of scripts here first we are going to add migration create script we're going to be running pre migrate Dev with a flag create only this will only create the migration for us and not run it the next script will be Mig grade and now we're going to run Prisma Mig grade Dev and then we're going to have Mig grade column prod this will use Prisma migr deploy this we can run in production and we also add a seed script and we're going to run Prisma sljs file that we're going to be using to seed data to our tables let's go go ahead and quickly look at this file so this file just basically inserts two users Alice and Bob and then those users have posts Bob has two posts and Alice has one post so let's go ahead and open the terminal and we can run npm run migration create it's going to ask the name for our migration and we're going to call it again create users and posts tables it just created migration for us so we have migrations folder in our Prisma folder again and we have folder create users and posts table so we can open it again it's just the same as we had it before we reviewed it and everything looks good and we can run npm run my grade and as you can see migration got applied so before we make any changes to our schema let's go ahead and Seed the database run the script npm R seed and this script seeded the database let's switch to table plus and check it and in table plus if we refresh we're going to see users post and again Prisma migration tables and if you click on the users we can see two users here and the posts we have three posts let's switch back to vs code and make changes to our schema or like they say in fancy terms let's evolve our database let's go ahead and add summary column it will be again string and in Prisma if you want to have your column nullable allow NS you can just put a question mark right here and again we'll put add DB uh tiny text let's go ahead Save open the terminal we get a run npm run migration create and it will ask us for the new migration name we're going to call it add summary column and now our migration got created so let's go to migrations folder add summary column and we can see that we have right here alter table so it says posts add column summary tiny text null this however will add column summary at the end of our columns but we want to have it after content Prisma M grade create only gives you an advantage not only review your database migration but also customize it so let's go ahead and do just that we're going to put after and we're going to put the column name we wanted Conta all right let's go ahead and save it now we're going to go ahead run npm run my grade and summary column got added to post table let's switch to table plus we're going to refresh and we're going to see that the summary got added right after content the way we wanted let's go back to vs code and as you can see if we look at a summary column right we made a typo here we put 1 M instead of two the typos can happen you know sometimes you put notable field which you need to have anable so you need to be able to fix it and unfortunately there's no easy to kind of roll back your migration in prismo like you can do in squize I could have just simply rolled back the migration fixed um summary and migrated it again here in Prisma we will have to create a new migration to fix it let's go ahead and add a double m in here so we're going to save it open the terminal again we're going to run npm run migration create and it's going to ask us for migration name we're going to call it updated summary column name let's created yet another migration so let's go ahead and look at the updated summary column folder and we have a migration however as you can see it's dropping column summary and adding column summary with a double M this may not be what you want especially if you have data in your database even if in development losing like a few uh Records you know you'll have to create it again or receip it again so you really don't want to do that besides the new column summary will not be added after content so let's go and fix it because uh my SQL has a rename Command right right that's uh what we want actually so let's go ahead and put the following I'm going to delete this instead of add column we're going to put two and instead of drop we're going to put rename column summary right let's also go ahead and delete that comma and put everything on one line let's go ahead and save and now we open the terminal and run npm around Mig grade and the migration ran successfully let's switch to table plus we're going to check and we can see that the name got renamed to summary with a double M let's go back to vs code and make couple more updates to our schema so you can have a feel how it works right so as I mentioned before the string of uh Prisma will just create you a variable character column which uh may not be long enough so let's go ahead and update content to uh text type so we're going to put put add DB and we're going to use text right here also we can add Index right so the way we can add index is to do add add index and then it provides us with an array so we want to for example search post by title so we may want to add index to a title column so let's do exactly that let's save the changes and let's go ahead and run npm run migration create and it's going to ask us for the name again we just simply call it updated posts table we hit enter again we're going to go ahead and open the folder we can look at the migration right here seems like um you know it's just altering post and content going to be text and it's just creating index so no expected results here so let's go ahead and run this migration then npm run my grade let's go back to table Plus on the bottom we can select structure right in the structure we can see now that the title is still variable character but content now is text also we can see the indexes on post table we have a primary key index also my SQL creates index on a Forin key so you're going to have post user ID for in key and this is the index that we added post title index now in table plus let's go ahead and actually delete all the tables right here we're going to switch to vs code and now we're going to imagine that we are in production right we developed and created all these migrations in local Dev right now we're pushing to staging or production and now we have a command right that we put in a package.json right here npm run my great prod let's go ahead and run it and now this command will just check uh migrations folder against uh the migrations table in the database and check which ones are run or which ones are not R and it's just going to run it so now if we switch to the table plus again we click on refresh and again we got users and posts um tables created and with all our changes and updates right we have summary column with a double m and then we have our indexes on the posts T posts table and our content is a text this is how you can use Prisma migrations in your development FL if you would like to learn how uh Prisma migrations compared to squiz migrations please check out this video about squiz migrations thank you for watching and I will see you next time

Channel: Alex Rusin

Views: 1,955

Keywords: Prisma, Database Migrations, Backend Development, Database Evolution, Data Consistency, Development Environments, Backend Tutorial, Web Dev Guide, Database Management, Schema Management, Prisma Basics, Data Migration, Database Schema, Prisma ORM, Web Dev Skills, Learn Prisma, Migration Basics, Backend Skills, Alex Rusin

Id: _-YCDwm9M7M

Length: 17min 10sec (1030 seconds)

Published: Wed Mar 06 2024