Database Migration with Sequelize in Node Js Express application

so far to speed up the development process we used our database in memory mode so every time we run the application or the tests we have clean database which is forced to be up to date with our latest model definitions but in production we cannot go with that our database must be stored and kept as it is we must not be losing data just because we restarted the application to rewind why did we choose to go with this way until now let's practice a failing condition here in index.js we are calling this sqlize sync function with this options force true and this is wiping out all the data and creating tables from scratch now let's remove this part we don't want this force true in our options and let's also remove this database sqlite and let's run our application [Music] so we are running in development mode and the application is creating the database if there is no database and here we have the database sqlite now let's open this database this time i'm going to use the db browser for sqlite the extension in vs code is not robust and you may be seeing failures while you are trying to browse the tables so i'm going to use this one so opening the database it's here the database sqlite and let's check the users table here we have these 25 entries in this table and we have these columns the inactive activation token created and the the others and these users are being created here in this add users function after the database is initialized we are adding these users and we are setting the values for each of the fields now let's stop the application [Music] and let's update our user model let's add a dummy field for this model let's say it is dummy and the type is sqlize string this is just for the demonstration of how the force true is changing the behavior of the database creation so saving this one and running the application once again so application is running now let's go to our db browser for sqlite again let's close the database first and open it once again to make sure we are loading the data from scratch and when we look at the users table in the columns we don't see the dummy so it is not added to our database and let's go back to our index.js and let's add this force through and saving this one our application is restarting and let's go to the db browser let's close this one again and open the file once again and let's browse the users table and this time here we have the dummy column added to users table so to reflect the latest model changes to database we used this approach but this is not suitable way for the production now let's remove this dummy field so in production we are not going to use this force true but we need to keep our database schema to be up to date with our latest changes now stopping this application and closing this window for now saving the changes now let's think about the evolution of our this user model to better visualize it i'm going to use an extension in vs code it is this git file history you can just search it and install it if you like so in this user object at first we had only these fields username email password then we added user activation related fields then we added token to our database and had relationship with the user and our user model continued to change because we were forcing database sync we didn't had problems while doing these changes so the question is for stored database in production how can we handle these changes properly so let's remove this database once again we are going to use sqlize database migration functionality sqlize has sqlize cli library so let's install this one and see how we are going to use this library so in console let's run npm install sqlize cli now we will use this package in command line so we will use npx to execute this sqlize cli and our first parameter for this function is in it so running sequel cli init so this is creating a couple of folders and a config json under this config directory so now we have this config json we have these migrations models and seeders folders these are created by this sqlize cli init now let's open this config.json we have this keys development test production these are the environment definition and it is matching to ours if we would have the environment as let's say dev then we would be adding dev to here and each of these environment has their own database connection so we are going to use this one for development so removing the others and let's update this development object to have our development database connection for that we can use this development js and let's copy this part the database username password dialect and storage and pasting here so we have this dialect to be sqlite and we have this database to be hoxify removing this one this one and we will have the username this is the username and the password is db p4s and finally we will have the storage which is this one and there is also a host and for that we can just go with this 127 or localhost now let's see the capabilities of this migration tool let's create a model to demonstrate the changes in time let's go step by step at first in user we had the fields email username and password so let's start with that so we run npx sqlize dash cli and our parameter is model colon generate and we will give a name which is user and this user has attributes of we will add the attributes by comma separated values and each attribute has type in it so first attribute is username and we add the type with column string comma we have the email type string and we have password type string so hitting enter so this created two files let's check them one of them is here under migrations this one as you can see this is a timestamp prefixed create user script and the other one is here the user js so let's first check the user.js and here let's format this one and here we see we are having a class extending model and it is initiated with this username email password so it is almost identical with our user model and let's check the other file which is this one this migrations file and in this one we have this up and down functions and this means when we run the migration the up script will be run and in the script we are creating a table of users and it has this fields id username email password and the down function is when we undo that migration so when we undo the migration it's going to drop this user table we don't have the database here so let's create it via this sqli cli migration functionality we have this migration script here this one and we expect this to be creating the database for us so we will run npx sqlize cli and our parameter will be db column migrate [Music] so the script is running in an environment and by default if an environment is not set it is assuming it is in development therefore it is finding the corresponding database information from this development part of this config.json and then connects to this database and runs the migration scripts and in this migration script the first one is creating user so it's going to add the users table and here we have this database sqlite let's check this database with the db browser for sqlite so opening the database and here we have users table and let's browse it and here we have only these fields id username email password so that's how we do the migration let's run this migrate script once again again we are running sql icli db migrate [Music] and in this one we see no migrations were executed database schema was already up to date and how this tool is detecting this schema is up to date it is doing it by checking this table c class meta so let's browse this one and here we have this file name in this table so it is checking this table and finds the last item inserted to this one and then decides if any new migration is required or not now let's run in px sql icli db migrate and let's add one more parameter pando so when we run this it is running the down function so we expect this user table to be gone so opening the database once again let's refresh this one so here the the meta table is updated the latest migration file is removed from here and here we don't see the user table we see the user table is gone now let's enhance our user table for that we are going to create a new migration file so we will run npx sqlize cli and this time the parameter is migration column generate and let's give the name user inactive column [Music] so this is creating a migration file under this migrations folder and as you can see it has a new timestamp for this one and the name is what we have given it so in this one now we can even implement the functions here the up and downs and let's go with the inactive column so let's add we will run query interface and this has functions like add column by the way this is going to be async so we will await here and we also see an example here we will run query interface at column to to our users table and the the column name will be inactive and when we look at our user model our actual user model here we have this inactive and we have this default value so let's go to our new migration file so we will set default value for it so we will add options object in this one we define the type as this is going to be a sqlize boolean boolean and it is going to have a default value which is true and let's implement the down function and in this one we will await query interface remove column from this users table we will remove in active so saving this one now remember in the database we don't have the user table but we have two two migration files one of them is discrete user the other one is updating this user table with adding a new column to it so let's run migration so we will call npmc collide cli db migrate so here we see first this create user is executed then this user inactive column the net the next one is executed and when we look at to our database let's refresh this one like this going to browse data part and refreshing it and here we see this database table says this users and in this sqlize meta we have two entries one of them is the create user and the other one is this user inactive column and when we look at the users table here we have this inactive column added to this table so that is how the the migration is working at this point when we run the migrate undo it is going to be undoing only the last migration which is this user inactive column so when we check the table once again let's refresh this and here we see the inactive column is gone and in this sqlize meta we only have this create user and if you would want to revert all changes on doing all migrations we would be calling undo all but let's first migrate once again see what's going to be migrated and here we see only this user inactive column is migrated because in database equalize meta let's refresh this one we already have this create user therefore this tool is detecting that the latest schema is based on this create users script therefore it runs only the next ones coming after this create user which is just this user inactive column so when we run the db migrate on the all and here we see first the user inactive column is executed the down function inside this one is executed which means the column is removed then this create user down function is executed so when we check the database once again here we see this equalized meta table is empty now and we don't see the users table anymore so that is how the migration to next versions or the previous versions are handled we can also do multiple changes in a one migration script we don't have to add individual migrations each time so like in this one we only added the inactive but when we were introducing this inactivity for user we added both inactive and activation to console let's also add the activation token to this migration for that we need to run both of these changes in the same transaction so we will have transaction coming from query interface [Music] sqlize transaction and we will run these queries in try catch block [Music] let's move this one inside try the first one is we are running the in adding this inactive column to users but we would also need to pass the transaction with this function transaction and we will add the other field copying this one and this is going to be activation activation token and this one will be string and we don't have default value for it again we pass the transaction and if both are success then we will call transaction commit otherwise we will call transaction rollback [Music] and similarly we will update this down function again we will get the transaction and we will run this remove column user inactive and we will pass the options object having the transaction in it and we will do the same thing for the activation token column and then we will await for transaction commit and if there are any errors we will catch it here and we will await transaction rollback [Music] so with this way we are doing multiple changes in our table so saving this one so let's run the migrate once again we undo all the things so our table is our user table is not there so running the my grade so this is adding the user table then running this user in active column let's check the database refreshing this one and opening users and here we have the inactive and the activation token columns so we have our table with the latest changes now we have seen what migrations are and what models are the migration is the most important thing for us we will not be working with models we already have them in our project and it is not required to have the models for running these migrations so removing this models folder and the last one we have this seeders and in this folder we will add the files just like the migration scripts and in them we can do the bulk insertion so let's create a seeder file for that we will run npx sqlize cli seed column generate and let's set the name as add users so it created a file here and we were creating users in this index.js here we were looping and adding the users now we can move this functionality to this seeder file so opening this seeder let's close the others and let's open the index.js now we can just copy this part we don't need token source but let's copy it anyway and pasting here we are going to use bcrypt and now in this query interface we have this example of insertion bulk insert to this table and there is array and the object so we will do same thing let's create 25 users first let's have this hash and we will add users so let's say we will have users array and in this loop we will let's say loop 2 25 and we are not going to use user create this time but we will just push the this object to user array so we will call users push this object and this is user name and email and for inactive let's say it is false all users will be active and we set the hash and since we have the fields of created ad let's check it from the database created at and updated at we have to set values for them because let's check this create user and here we have this created add and update it at and they are not allowed to be null so that's why we have to provide value for them so we will set created as new date and also we will have updated it [Music] with this new date and then we will await query interface bulk insert to users table this user's array and it is also looking for third parameter which is an empty object so we have this up now let's remove this one and let's also add the down version in this one we will just await query interface bulk delete users with null and empty object so saving this one and let's run this we will run npx sqlize cli and we will pass the parameter db column seed column all so it is running the scripts and when we check the database here let's refresh this one and here we have the entries for the user table now we can remove this add user functionality from this index.js we no longer need that and let's remove this part also and removing the user and decrypt dependencies so that's how the sqli cli is working and we will introduce it to our npm scripts to automate whole process we are not going to run the scripts manually like we have seen here consider this one as just a practice to see the capabilities of this library finally let's organize these sqlize scripts with a better folder structure currently these database related files are under root directory and it is mixed with other folders let's extract this into its own folder for that we will create a new file dot sqlize rc and in this file we need pad from this node library and we will export an object and in this object for the config json we will get the path of [Music] we will have a database folder and our config file will be under that folder and the file name will be config json and we will have models path and this one also be similar and it's under database models and we will have cedars pad seeders and we will have migrations path so let's create a folder in our root directory call it database this is the folder name we used in our sqlize rc and let's move this migrations seeders and this config json to this database directory let's see if it's working properly let's remove this database sqlite and let's run npx equalize cli db migrate and here we see the the configuration stored from this database config json and also it's loading the migrations file

Channel: Programming with Basar

Views: 22,693

Keywords:

Id: FfKSb63pppA

Length: 30min 40sec (1840 seconds)

Published: Sun Jul 25 2021