What Are Database Migrations? | Database Migrations in Node

in this week's video we're going to be taking a look at database migrations so we're going to cover what database migrations are how they're useful and then finally we're going to be taking a look at a library called connex to see just how we can achieve database migrations in our node projects so we've got quite a lot to cover let's get into it so say you're working on some kind of project that's using a sql database now you might already be used to having to actually go through a very manual process of actually setting up your database schema with using some kind of graphical interface like a sql workbench or sql pro but the point is it's a very manual process because you have to actually go into their form use some drop downs use some input boxes and then based on that you can go ahead and actually set up the schema of the tables and then how one table might relate to another table but with database migrations what you can actually do is you can go ahead and set up that very same schema your sort of names of the tables the fields that they're going to have the data types that they're going to be have one table might relate to another table and you can do all of that in code so now this is very beneficial for two reasons number one let's imagine that you're working on a project with you and then let's say one other person on your team right well if you're gonna actually go ahead and set up your database schemas manually using some kind of database manager well now you both have to make sure that when you're setting up your database schemas on your own individual machines that they match and you don't really have some kind of automatic way to make sure that the schemas are actually set up correctly and if you make a mistake and the database games are not entirely the same from your machine to the other machine well then what's going to end up happening is your application might have some serious bugs furthermore the other reason why this can be so beneficial is let's imagine that you are in fact the only person working on your team but now you're already done developing you had your own database and your machine done developing and now it's time to go ahead and deploy the project and make it available to the world well now if you've actually set up your database manually using some kind of database manager well now you're gonna have to go and do that exact same process again on the production server sure you can maybe create like a sql dom file or like a backpack file the point is once again you don't really have a great way to go ahead and transfer that database schema from one machine to the other machine you don't have it from when you're working with your coworkers and you certainly don't have it from when you're going from like a local machine to a production machine but now with database migration since your actual setup since your schema actually lives in code well now just like all of your code gets shared from one person to the other person and also just like with your code now when you're trying to go from development to production all you have to do is kind of you know go into your production machine pull your code down from github and all your changes you had locally are just now magically there on your production machine now since your actual database scheme your sql schema is also going to be living in code it's now going to become very easy for you to transfer that schema from one person to the other person or from your developer machine to the production machine and that's kind of the basic idea of what database migrations are and why they're so useful now let's actually take a look at this library called connect to see just how we can get this to work in a node project okay so here i am now in a folder where i have an empty node project the first thing that i'm going to do is actually go ahead and install this connex library so let me do that now so here now in my terminal you can see that i'm adding connects and i'm also adding sql lite now i should probably mention that the reason why i'm also adding sql lite is because connects is actually database agnostic what that means is you can use it with just about any sql database whether it's mysql postgres sql server and of course sql lite so whenever you want to use kinect you have to a install connects of course but then you also have to install the database driver of the one that you want connects to be able to use for you so in this case because i want to kind of keep things simple i'm going to be using sql lite so here i am now installing connects as well as sqlite okay so now that both connects and sqlite are done installing let's start working on these database migrations and so as it turns out connects actually offers us some commands that we can run straight from within our terminal at the command line the first command that we're going to be taking a look at that kinect offers us is going to be the init command so here you can see now my terminal i'm saying npx i'm calling upon the npx tool that is given to us by node which will then call upon the connect tool that we just installed and then finally we're gonna go ahead and call its init command so now as you can see after that command is executed it tells us in the output that it created a file for us and the name of that file is going to be connectfile.js so let's go take a look at what this file contains in our editors so here now in this connex file we can now see that we're basically exporting an object and this object contains three keys one is called development one is called staging and then one is called production so essentially so far we're basically seeing two things from within this file number one as i've mentioned this is going to be that connexus database agnostic which means here in the development key we can see that decline to sql light and then for both staging and production we can see that they're kind of using postgres sql as an example and then the other thing that's important to notice from this file is the fact that we do in fact have these three keys one for development one for staging and one for production which means that we can actually use different database drivers for sort of different environments so for example if you want to kind of keep things simple during the development phase we can very easily just use something like sequel light but then when it comes time to deploy we can then decide to use postgres and our code won't have to change because connects will of course handle the differences between one database and another database but of course in this example we're not really going to be dealing with any kind of deployments whatsoever so we're going to be getting rid of the staging and production keys and we're just going to go ahead and focus on the sql light to development key and again we're going to be using sqlite just to kind of keep things simple for the sake of this tutorial so now what's going to end up happening is when we actually start running our migrations basically when we start creating tables using this tool in our database essentially this tool connects is going to have to be able to execute sql within our database which means that we have to kind of tell it hey which database we're using and b how to go ahead and connect to that database and so here basically we're saying we're going to be using sqlite3 and because sqlite3 is a very simple database where the entire connection string is basically just going to be the name of the file here inside of this connection object we're just going to go ahead and give it the file name key and then we're going to be telling it the name of the file and where that file is located of course it'd be using a more complicated database like a postgres or mysql then inside this connection object you'd of course include the actual credentials like the name of the database the username the password the port and so on and so forth so since we're telling connection we're going to be using a database that's going to be the name of our database let's go ahead and create that file now okay so now that our dev.sqlite3 file is created as you can see right over here that means that our setup is basically done and we're finally ready to start working on some actual migrations and building up some tables within our database so now we need to actually go ahead and use another command that connects actually offers us so here now in my terminal you can see that i'm calling upon the npx tool once again then calling upon the connect tool and now i'm using its migrate colon make command and then following that is the name of the file so essentially what we're doing now is we're going to be creating what's known as a migration file it's going to be in this exact file where we're going to be writing our javascript code which would then get translated into sql and then that's going to go ahead and actually build up our table for us in our database so here i'm basically creating a file that's going to be called car's underscore table and it's going to be in this table that i'm going to be writing my javascript code to go ahead and build up a cars table which is going to be including an id make model and year so now once that command has been executed we can come on back into our editor and now you can see that we have this new folder that's been created called migrations and within this migrations folder we can see that we have this new file called underscore cars underscore table but in front of this name underscore cards underscore table that's what we kind of named it in the terminal we can also see that we kind of have this long number that's been sort of prepended to the name that we gave the file now this long number really is a timestamp and this is the timestamp that connects itself has actually given to this file now the reason why connex actually adds a timestamp in front of your file name is because connects wants to be able to make sure that it can ensure the order of execution of these migration files for example let's imagine that we have like a cars table and then we also have a people table so let's imagine that now we have a relationship where one person might have many cars which means that the cars table is going to have a foreign key back to the people table so naturally that means that now the people table must exist before we go and we create the cars table so the way that connects can ensure that we actually have the people table getting created before the cars table is by looking at the time stamps in other words if the people table was created first in other words the actual migration file is created first and then we go and create the cards table now even if we actually run the command to go ahead and set both those tables up in the database at the same time and in just a second we're going to see what that command actually is but the point is now connects can actually go and make sure that the people's table gets created before the cards table gets created ensuring that now the foreign key constraint is of course going to work and make sense okay so now we're actually inside of this cards underscore table file and as you can see we're going to be exporting two things the exports up function as well as the exports down function in the up function this we're going to be writing the code that's going to actually create the table for us in the database and then in the down function we're going to go ahead and write the code that's going to kind of delete the table so here you can see in the up function we're going to be using the create table method passing in the name of the table that we want to call the table in this case we want to call the table cars here we're going to be saying that we're going to have a key called id which is going to be its primary key and it's going to be auto incrementing everything we're going to have a key called make which is going to be a string and here we're saying we're going to have a key called model which will be a string as well and then finally we're saying that we're gonna have a year which is going to be an integer so moving on down to the down function here you can see that this is where we write the code to actually delete the table and all we're doing is we're basically calling upon the drop table method passing in the name of the table that we want to drop which of course in this file that would be the cars table and that's all the code necessary to be able to delete the table if we ever decide that we want to do that so now that we actually have all the code necessary to build up our cars table let's head on back to the terminal and let's actually use the command that connects gives us to run this migration and actually add the cars table into our database okay so now moving on back into the terminal you can see that i'm calling upon npx once again calling upon connects and this time i'm using its migrate code on latest command and this is going to be the actual command that we use to go ahead and take the cars table and add it into our actual database okay so it seems like the command has executed without any kind of errors so now let's actually open up some kind of a database tool some kind of database manager and see whether or not the cause table was in fact created exactly as we specified in our code so here i am now in db browser for sql lite this is the gui that i like to use to manage my sql lite databases and as you can see i do in fact have my cars table and then when i expand on it you can see that i do in fact have the id to make the model and the year exactly as i've specified in my migration file so we've already seen how we can run the migrate latest command which will essentially run the up function and actually take our table and add it to the database now let's see how we can actually go and execute the down function and tear the table down and pull it out of our database so here i am now back in my terminal and the command as you can see to kind of start deleting tables is going to be the migrate colon rollback now i want to just point out one very important thing about the migrate rollback command while it does in fact delete tables there's a very specific order in which it actually goes about deleting your tables so in this case because we actually only have the cars table of course it's only going to be the card table that's going to go ahead and get deleted but otherwise the way that this actually works is let's imagine that we did in fact add the cars table and then down the line we actually also added the people table now if i actually go ahead and run the rollback command at first what's going to happen is only the people table will get deleted and then we have to run the rollback command again to then go ahead and be able to delete the card table essentially the way that the rollback command actually works is it's basically going to look at the most recent migration that was run and then delete that first and then start rolling back more and more until you start kind of going back in order it's kind of like reversing time so again it's going to always delete the most recent migration first and then work its way back in time to kind of come back up to the most oldest migrations and that's kind of the order in which it runs so now if we're back in our terminal we're gonna go ahead and execute this command again in this in this case we only have the cars table so the expected outcome is to see that only the cars table was deleted okay so the command seems to have executed without any kind of errors let's head on back into our database manager and now we should see that the cars table is gone and so now as we can see the cars tables of course gone and again the way that we did that was by running the actual rollback command which is given to us by connects well anyways that does it for this video hope you enjoyed it i hope you found it useful if you did please drop a like subscribe and i'll be back next week with another video you

Channel: Coding With Chaim

Views: 24,529

Keywords: what are database migrations, database migrations explained, what are database migrations good for, what is the point of database migrations, database migrations in nodejs, database migrations in node, database migrations in node with knex, knex database migrations, coding with chaim, codingwithchaim, database migrations, database migration, node.js database migration, knex migrations

Id: YEh7yPr8oGE

Length: 10min 33sec (633 seconds)

Published: Wed Jan 06 2021