NodeJs Sequelize Migration with PostgreSQL

hi everyone welcome back today in this video we're going to be discussing about migration using postgre SQL squiz or in nodejs application for those of us who are conversing with building backend applications API end points I'm very sure we've dealt with several kind of databases from relational databases to nosql or non-relational databases and for you to have a very very professional application you need to you know have your database as a single source of truths you've been able to run several migrations to updating datas in your database what exactly is migration or why is migration needed migration is basically you being able to make changes to your database and being able to revert changes to your database so in that sense it keeps a previous state of your database and you're able to track each changes that was made to your database if you really want to build a very professional application an application that will be used by several users thousands of users migration is the best and professional way to handle your database calls and your database requests and in this video I'm going to be walking you through how to run migrations in post postgress SQL databases using squiz or having explained all of that let's dive straight into the Practical approach of the video so if you need the complete code base for this particular tutorial on this particular project you can have it in the GitHub repository I'm going to drop the link in the description below but for those of you who want to follow me through the process while I explain everything one step at a time then you can join me on the journey now for the first part of this particular tutorial what we have to do is just set up a very simple node server not really on the Node side of things but just a very simple project folder for another for our migration in the database now I've created a folder called squiz postra migration node you can have any folder name of your choice all I just want to do now is I'm going to open up this I'm going to create like a new file inside of here which is just going to be index.js and um let me open up this in the terminal so I'm going to open up this in the new terminal and then just to be sure we on the right terminal yeah so the next thing I want to do is um initialize a package.json so that we can be able to install our dependencies and now we have a package of Json initialized and the next thing I would do here is just to create to install the dependencies which will be working with and for our own case like I said we're not dealing with a node server we're not doing all of those things what we are basically doing is just the migration aspect of things so the dependencies that I'm going to be installing is the postgress dependencies the seized dependency Express and the squiz CLI so that we can run some seized command within the project terminal so I'm going to install PG which is basically the project and the postgress dependency that is been used in sqer node and I'm also going to install PGH store which is also used alongside with postgress and I'm also going to add squiz so these three packages are going to be installed and added into our application and once they added the next thing we're going to be installing is um the dev dependency which we'll be working with and in our own case we're going to be working with squiz CLI so right now if you check the dependency you would see we have PG we have PG store and we have SQ now I'm going to also install like I'm going to add the squiz CLI so I'm going to install the squiz CLI now with the help of calized CLI like I said earlier we can be able to run calized commands within our application like the SQ init and all of those kind of you know commands and scripts now having installed that the first thing we'll be doing here is MPX squiz init now this would create a config.js file also creat a folder for our migrations Cedar and models and in our case we're going to be dealing with migrations which is where all our migration script is going to be our models is like a schema for our database and the cedar is when you want to deal with dummy datas like just to see your application for our own case we're not going to be working with CED that so that would be removed we don't need a c as for now so what we need here is the config the migration and the models now the next thing I would want to do here or the next thing we have to do here is we have to go inside of the config Json and you can see that we have some pre updated informations for our databases so when you're on development your database username is root your password is nor the database is this when you're on test you have this when you're on production you also have this but we're going to be making a very slight change from what we have right now to something else but before we bring in that change I'm going to create a new file not inside of the config but outside of the config I'm going to create a new file called um EMV EMV file and the reason why we're going to be creating a EMV file is basically because I want to put in the DB name I also want to put in the DB host I also want to put in the DB password and also the DB user now for this particular informations it might be slight different from mine and yours because of whatever postgress database you have set up you know on your own end but in my own case for my DB name I'm going to be using test DB for my DB host host it's going to be Local Host I'm also going to be using my password and I'm also going to be using my users postgress now this is what I have in my local computer you know this is what I have in my local computer because I'm dealing with my databases on my local computer that's why I have this so now the next thing would be to change this from a Json to a JS file so that we can be able to export it and be able to also use the EMV inside of here like being able to extract these things and and use them inside of here so we have to change this to a JS file and the next step would be to go inside of the models find the index.js file and also change this from Json to JS and when you save this you no longer have the error here but there's something we need to do here we need to this is not how to export information from a JS so we have to switch this but before we can switch this since we're going to be making use of the EMV that's the M package we Al would have to you know add the M package in our application so while that is going on okay that has been added now while we have that added let's just confirm that we have it there and yes can we can see that we have it there so for the config.js we're going to switch all of these things to something else so I'm just going to copy what I have from one of my applications which I prebuilt before now and um just going to copy all of that and paste it right inside of here now what can you see is the difference between what we have now and what we have before you would notice that we are now able to make use of the data or the value coming from the EMV file because this is a JS file and no longer a Json file and aside from that we are also able to require this package which allows us to access ACC values from our EMV from our EMV file which is why we have the DB user the DB password the DB name and the DB host now for the dialect um dialect is basically like what kind of relational database are you dealing with in my own case or our own case it's postgress but if it's my SQL it's going to be my SQL if it's whatever other kind of databases is going to be there for you to have Clarity of this you can go to the documentation that's the Mig the ssiz documentation they have everything there if you go to get started you know if you want to install my SQL if you want to work with myia DB if you want to work with SQ light if you want to work with tdos or Oracle but in our own case we're working with postgress as you can see that was the reason why earlier we had to install PG and also how to install PGH store now that is basically that so we've been able to install that and the next step would be to now create a new migration in our application and let's take for example we have a user table and our user table would have first name last name email address now we want to create that table in our database it makes no sense going into the database you know to start writing queries inside of here trying to create a new table and all of those kind of stuffs because take the use case whereby you have to push this application online to production would you have to go into your production database to start creating tables that's wrong so that is the reason why we have the migrations in place now if you look at the documentation here for you to create a new migration we've been able to do this which initializes the project and the whole folder for us the next thing would be to create the first model and the migration and for you to create a new model which also generates a migration file all you have to do is just take this command and I'm going to copy this Command right now and inside of here I'm going to paste that command and the command is basically npx equalized model generates for generating a user schema and in the schema we want the first name column the last name column and the the email address now when I run this I get a new model file called user and I also get a new migration file which have all of the attributes which is also called columns and each of these columns have their own types which we can also add other properties and other configurations to each column and right now we can be able to you know migrate this column into the database and it automatically gets created and in squiz there is a package for that I'm sorry there's a command for that rather and because we're going to be running that command all the time I wouldn't want to do that from you know the terminal all the time so I'll go into the package.json and create the command which is basically migrate and here I would have MPX equalize CLI DB migrate and the next thing I would also have down which is to to un migrate if there's a word like that and this would basically be npx squiz CLI DB migrate undo so now with this if we go ahead and run this migration but before we run the migration I just want to point out some stuffs to you guys like for example right now you can see we have an ID with a primary key an auto increment which means for every new data you put into the database the ID Auto increases by itself it cannot allow a null value and other kind of configurations for the ID I'm just going to copy this same thing for the first name we don't want the first name to be blank so we want the first name to be there we also want the last name to be there we also want the IM email address to be there now we have this and the next thing we want to do now is just run a migration mpm run migrate now why am I using mpm run migrate normally what I'm supposed to do is write out all of these things you know and put it here but because we're going to be running other migration so I don't want to be typing those things same time again so that's the reason why I had to add it here as a command so when I run mpm run migrate and I click on enter you can see it tells you configuration file whatever whatever it says using environment development and right now it has created a migration for you so which means when you go to the database and check the table now you can see we have a user table and if you open the user table we have all the columns that we needed ID first name last name email created that and updated at as something that comes at default with you know postgress and squiz and you would notice we have another table which we did not create which is basically the squiz meta and what this table does for you is it keeps track of all the migrations that was done in your application so if you open up this you would find out that you get some information here right now you're getting this and this is because this was the first migration that we run and if you check this you would notice that it's exactly the same name with the migration file that was here so we run this migration and that's the reason why we are keeping track of this migration here so the equalized meta table keeps track of all the migrations that are run or that are running in your application and right now we have the user table with all the fields that we created that's basically how migration Works in you know in an application in our own case this is a nodejs application and yeah that's it so moving forward word you know when you're dealing with applications there are cases where you might want to create some stuff you might want to add new columns you might want to do some updates to some columns in your application and all those kind of stuffs that comes up when you're dealing with you know servers and API end points and right now we have um first name last name email now on the cost of developing your application there might be a need for you to create a new column in your database and that column might be called address so take for example we need to add an address field how do you expect to add an address field would you want to go into your database and start creating a new table definitely no you wouldn't want to do that and the best way to do that efficiently without having to affect anything in your application is just to run a new migration and when you run a new migration you updates what you have in your database and for the sake of running a new migration what do you do all you need to do is you can come to the documentation here and when you come to the documentation there is a command to create a new migration file that's somewhere here I think we have it here let me look for that yes this is where we have it this is a script to do that and now when you paste this here this is different from the first one we had because the first one created the model and created the migration file but we're no longer creating a model because we already have the user model the only thing we're doing right now is we're trying to add a new column we're trying to update the user table so I'm going to name this uh I'm going to name it add address field to user table so when I create this this would create a new migration file and this is the migration file and right now you will notice that when we created the first migration we had a lot of stuffs inside of it but this new migration does not have anything because you have to be the one to put in what you want to put in there understand so for our own case what we want to do here is basically have a weight query interface and we want to run the add column and in the add column we want to put users which is you know this as is users which is the name of our table and the new the new col we want to put there is address now we put that now the next thing would be to specify all the configurations for the address the first thing we do is the type has to be squiz do string because it's going to be a string allow no we want to allow no so which means it can be a n value then the default value should be n so which means if you don't provide an address automatically it's going to be n and for the down it basically awaits query interface do remove column and we're removing the address from the user column now what's the difference between down and up because you can notice that here also we also have down and up now when you run a migration whatever you have here is what gets executed when you undo a MIG migration whatever you have here is what gets executed so which means if we run this migration now this is the next thing that would execute and if we undo the last migration this is what we're going to have so if I have this now and I run here I try to run mpm run migrate You' notice that this has been migrated successfully and now if we check our DB refresh our DB you find that we now we now have an address column in our table and that's because we've run a new migration that automatically added an address column to every users in the table and just for us to confirm if we go to The squiz Meta you find that we now have a new history added because we run this new migration here that's what we ran the first one was the create user the second one was the add address F to the user table now we have a history of two migrations that was executed now for for some reason we might decide that okay we want to remove the address field we don't we don't want to have the address field anymore for the users all right we want to do away with that and all you have to do is you don't have to go into your database and start deleting datas and start deleting columns messing everything around all you just have to do is just mpm run down and automatically it reverts the last migration that was done and in our case the last migration that was done was adding an address field to the user table and now if you go straight to the user table and try to refresh this you find out that we no longer have an address field and every other thing we have Remains the Same if you go to the squiz meta and try to check this you find out that we no longer have the address migration because that has been removed from the history of the migration and that's the beauty migration gives to you when you're working with databases you know it lets you update your database in a very smooth process without having to affect anything in your database in production without having to mess around anything with your data it just gives you a very full functionality of you being able to easily add datas update your databases change your columns change your data types all sort of stuffs that you can do with my with database seized or allows you to do that via migration and this is exactly what I just want to show you guys today because this is the beauty of working with squiz orm being able to make changes to your database without you know going into your database messing around with stuffs so this is basically what we'll be working you through today if you want to get more insight of you know how migration Works how sqy works and everything you can go to the s.org it has the document ation for everything that you need to understand but the sake of this video is to show you how to easily update your database add datas to your database add columns to your database and all of those stuff and I hope you enjoyed this video I hope I was able to explain in a very simple term the best way to work with database and yeah like I said earlier on this is not applicable to only postgress you can have this done with any relational database as long as you have seized or working alongside with it and you can find that here these are all the database that are supported we have my SQL my RAR DB SQL and the rest of them so if you want to play around with other kinds of database you can check them out for our own case we had to use postgress but if you want to use my SQL same thing applicable to that I hope I've been able to explain this in details and I hope you enjoy this video and if you do can give us a thumbs up subscribe to the channel because more amazing videos will be released you know on daily basis on weekly basis I'm going to be releasing M videos and if you have any them you want to add to this video the comment section is widely open you can add that if you want if you feel we need to you know do a new video from what we have now concerning other kind of databases you can add that if you experience any error while trying to set this up the same way I did you can also point that out in the comment section and I'll be there to answer all your questions but for now we have to put a pause to the video because we've come to the end of the video thank you so much for watching and I'll see you in the next video

Channel: Amaechi Kingsley

Views: 2,869

Keywords:

Id: x-eOAYpsy7M

Length: 23min 9sec (1389 seconds)

Published: Thu Oct 12 2023