Database Migrations for Beginners | Flyway Tutorial

[Music] hey everyone hope you're doing well so today i'm going to be having a look at database migrations we're going to look at how to version control your database and we're going to focus on basically you know what database migrations are how they work and we're going to demo using java and flyway but the great thing about you know what we're going to go over today is that the concept of database migrations is the exact same across any language any tool the implementation details will differ slightly but we're not going to focus on those anyways we're going to focus on the high level concept so if you enjoy this kind of content please consider subscribing and let's get right into it so first things first let's try to understand what the problem is here what problem we're trying to solve and then we'll go into the the tools are solving the problem so when you're building an application of course over time your application changes in terms of the shape of the application the models the functions everything changes and usually you keep this in source control so you might use git and you know you might push your changes up to github and basically what that does is it gives you you know a few really big benefits um first of all it keeps your you know changes so you don't lose them locally of course um but more importantly it allows you to share your code with other developers that come on and they can you know take the latest version of the application they can also go back and debug you know if certain commits are pointing to certain environments they can go back and debug that and of course for things like continuous deployment um you need a way to to have different versions of your code so that way when you've got a new version you can just upload it and your environments can just take the new version they know what the version they were on before they know the new version they can take that and deploy the code so if we just had an application with no you know list we had no external dependencies that's super simple now as soon as you start doing something a bit more complicated let's say in this in this scenario i'm building an application with a list of users you know maybe these are um subscribers to you know to a an email list and i want to send them emails well now i need to start storing those that data in the database now as soon as we add this dependency we have this kind of external but explicit dependency that we need to track as well which is basically the schema of the database right so this version of the application you can see here i have a an insert into the user i'm inserting the email this is only going to work if my database has a certain schema which is this here a user might have an id might have an email if it doesn't have that it's not going to work the application is going to fail so these these two are basically tightly coupled so the question then becomes how do we make sure that the state of the database or the schema database matches the required state of the application and this is basically the the issue that database migrations solve so you could try to do this manually where you know i build the application i i manually insert some sql and i create this user and then you know the developer comes and joins the team and i send them a sql script or a bunch of sql scripts i export that um but then of course if i'm working on different versions and i've added fields that change things locally then you know i need to somehow reset it and then of course continuous deployment doesn't work at all because you know how are we meant to tell the environments that the change of the database do we you know log on to the databases themselves and update the scripts manually well if you make a mistake there's a whole kind of range of issues that can kind of stand for trying to do this manually so the the solution for this here is basically database migrations um and we'll basically see that as we go below another issues of course as this uh this data evolves so i have email now i change the application now i'm gonna add a name here um again how do you share those changes how do you make sure that everyone has the updated version um yeah all of these things this is what the the issue solves so if we go down to what exactly a migration is what migration tools do and how they solve this issue well a migration all is when somebody says migration it's just a script that's it's just a sql script or it could be a bash script or a javascript or python script it could be any sort of script but its sole purpose is to take the database from one state to another to migrate the schema the state of one database from uh state a to state b right so in this case here we are we've got this create user script and we can see this version zero so this is the first ever migration that we have in the uh the database and all we're doing is we're creating a user table we're adding an id we're adding an email that's in migration right and then later on we might add a name so we're altering the table uh we're adding the name so these are very similar to like commits in a github repo right this is every action that was taken in order to get the the state of the database from zero to where we need to now and of course um in addition to this is one other step that's needed which is basically capturing the the state um i.e where we are in the kind of in the migration life cycle so if we imagine if we just move this aside here and let's say we move this one aside here so we're just focusing on a brand new database if i run the migration here it runs the first one so now we have let's just go up and take this this user table so now we have a user table in this database so great now the next time we add a new script we basically need a way of knowing you know which scripts have we run already because we don't want to re-run the initial script because then we need to start to complicate things with sql checking if things exist so we always need to know where we are in the in the in the migration history basically and this is typically why most migration tools will also maintain within usually the same database uh some sort of version table and this is basically the state this is where we are so when we're on the first one we can say hey we're on version zero so we've run the first script and then when the second script comes along when we we say okay now we can migrate you know it can check the database and it can say well we've already done version zero so we're ready on version zero we don't need to rerun this one but we can see a new version one so we're gonna apply the full name so then it might you know take all these you know apply the full name and update this to one and so on so forth so conceptually this is all we need to know this is database migrations in a nutshell so what we're going to do now is we're going to go into an example like i said we're going to be using um we're going to be using flyway and java but the concepts here are going to be the same for any language so if you're working with java and flyweight this throat is going to be good enough for you to to get started with it but if you are used to javascript or python or whatever it is just don't focus too much on the implementation details uh but the concepts will be the same anywhere you go so yeah let's get right into it so i've got my very simple application running here and you can see very simple java application i'm using gradle as the build tool and i've just got one class which is this app class so i'm just going to close this for a moment all this is doing is i've got a main method here it's getting a connection to the database which you might do in many different ways i'm using something called juke to to build my sql queries so that's what this uh it's initializing basically the the uh the juke context and then i'm just saying hey insert into the user table this email read at example.com execute that and then i'm just getting the results select star or select asterisks from user fetch and then i'm just printing out the results so that's that's all i'm doing now on the right hand side here we can see basically my my postgres database and as you can see in my postgres database um we don't have any database so if i go down to my database connection string here we're seeing we have uh low closed uh postgres in the schema here because i'm using postgres we have a schema but we're going to be on we're going to be using the migrations tutorial schema so we don't have that schema there so i could go you know right click here or you know write some sql and create this schema manually but we don't want to do that we want to do this all through some sort of database migration tool right of course if i try to run this application now because there's no schema because there's nothing there it's just gonna fail it's gonna say hey you know the table user doesn't exist in fact your schema doesn't exist at all so let's bring in flyway uh in this case and let's start to to yeah to see how the migrations work so because i'm using gradle what i'm gonna do is i'm gonna jump over to the build gradle file and all i need to do here is i'm gonna add a plugin so with flyway you can run this in many different ways so you can run it as a command line application um you can run it as a you know directly encoded with java you can use maven or griddle because i've got gradle here i'm just going to use the gradle plugin and i just like the plugin it just means i can keep it all inside my application you don't need to install any external dependencies but again really doesn't matter so i'm just going to add the um the gradle plugin here and the one other thing it requires is it just needs some details about the connection so basically needs you to configure gradle so this is what you would otherwise maybe pass in to the command line arguments you need these details somewhere so that it knows how to connect to your database so for flyway i'm just telling it i'm just using you know postgres driver i'm saying here's the url to the database here's the user here's the password and here's the schema and the default schema one specific thing i think this is flyway specific is that flyway will automatically create the default schema for you i've played around with some other migration tool that don't do that by default they expect you to handle that you know as part of the uh you know as part of the um infrastructure pipeline in this case this is going to create the the this is going to create the schema for us so that's perfect so once we have flyaway or whatever tool it is you have installed i'm going to close that all you need to do is run your migration script so i've not actually got any migrations yet but in this case because i'm using flywheel i just want to show you how it kind of starts to track things if i run flyway migrate here and i've got logging set up you can see here uh it started to log you know there's a bit of a mess but there's a few things to to to focus on which is hey um migration through it's up to date there's no there's no migrations and you can see here skipping file system that's because flyway as well as all migration tool will expect you to have a certain directory in this case db slash migrations with all your migration scripts and the migration skills will be in a certain format but it's not found now we haven't added that anywhere this is a brand new application we don't have that so it's just basically said hey we don't have a migration to run but i've done what i can and in this case what flyaway can is basically it's created the um the schema so i can see now i've got five migrations tutorial that's it's added that and if i go into migration story on the tables we can see there's this flyway schema history table we didn't create that and that's because flyweight created it for us similarly again any tool that you have will create its own version of this and you can see here that the version here is null so we've not actually run any migrations we're on we're on null version we haven't done anything but all we have done is we've run the flywheel internally has run this migrations script um to create the uh the schema which is the migrations tutorial schema so this is basically the starting point for flyway your other languages might be slightly different might be the exact same but conceptually all we're doing here is initializing and initializing the migration tool cool so let's go add some migration so uh i've got migration here um on the right hand side which i'll we'll shall basically copy in fly away looks for a specific directory so db migration so we'll add that in and we're just going to copy this file here so this file note it's v0 that's exactly what we started on underscore underscore and then there's a little description create user table dot sql so this is very specific to flyway any tool that you're working with might be slightly different but ultimately there will be some sort of naming convention whether it's in the file um the file name or it's whether it's within the file i think you know i think i played on with python's alembic and they have the the version inside the file this is the way that fireway does it and the good thing about this one is because you have the basically the version and then the number and it's an incremental number you get to see all the different changes in basically in chronological order in your file system so i like that about flyway but you know whatever tool you have will be similar so all we're doing here is we're creating the user and i'm going to go into gradle i'm going to run flyway migrate and you can see here again in the logs it's um it's validated two migrations because we have the schema migration and we of course have this this new migration um yeah and that's basically it's finished applying this one migration so if we go to our database and i refresh this guy we can see hey we have the user table it's got the id and the email so if i now run my application and all it's doing again is inserting this uh this email oops let's get pilot trying to be smart if i run this guy it's going to be happy it's going to succeed it's going to say hey everything's worked and if it's trying to log at the bottom it's saying hey we've got a new we've got a user read example.com and here's the id of one cool so we've got our basic setup now let's just look at one other scenario which is you know what happens if you need to change the table we need to alter it and one common thing you face is what happens if i need to add a non-nullable field right this is quite a common thing there's a lot of kind of you know different strategies etc that you might face and you'll you'll learn those over times but i think this is possibly one of the most one of the most common ones so what we're going to do um is inside the code if i of course just add all right well now i want to add a name so let's say i want to add um blue at example.com and i'm going to add blue here so that's the name there and of course now that shouldn't work right we don't have a name it doesn't exist uh we need to migrate so that's gonna fail we don't have the name that's fine let's add our migration so i'm gonna copy over again another migration into this now it's gonna be v1 we we're up one version and this is just gonna be called we're just adding adding a name and the most uh kind of thing that i'll stand out here let me just fix up this session so that all uh is green instead of instead of red so we're altering the table user and we're adding the column name and if i just comment this out and if i take this out actually intellij it gives gives us a bit of a a clue so we're adding name text it's not null at this point so it's not going to cause an issue because we already have data in our database but it can be null as soon as i add not null um intellij here is actually going to complain it's going to say actually you know we can't do that um because you've already got data on your database so it can't it can't be null would you want to do so this is a point where we need to figure out different strategies right so you may you know if you're more familiar more advanced with with sql you might start to write you know triggers or functions that go and you know maybe use default the email column into the name i'm just going to keep something very simple here which i had previously and i'm just going to say actually if i don't know what the the name is i think i had unknown but i'm just going to pop in front right so we'll just default everything to friend we'll sort out how how they enter the name later on maybe the application that's a different concern but the most important thing here is we don't want to to be defaulted after this change so if i if i keep this default here and i don't add this in then we can it's always going to default to friend what we want to do is we want to force the application to always add a name now going forward so you know it's quite a it's quite a significant change and this is why we have this second part here which is alter the table uh and actually now remove remove the default so everything before now gets friend everything in the future you need to explicitly pass in the name so if i go to my app here again final one obviously got blue example blue i run this and what i should basically see oops of course i forgot to run the migration so if i just run the migration here that's migrated the database and now i can run the application uh let me just run up here there we go and now if we look at the output we can see that the red example has been defaulted to friend and blue at example.com has been added blue and of course now going forward with app we can't not add the name so it's kind of explicitly having adding a kind of a hard and fast rule there and i mean in terms of kind of database migrations that's the absolute basic that's going to be enough to get you very very far um there's a few other things that's probably worth mentioning with database migrations you'll see lots of tools that may have other commands such as undoing undoing you know all these repair validate i've never had to touch any of these so typically when you want to undo this is similar to undoing a commit you're going to fail forward that's kind of the general advice um if you if you've made a mistake you need to drive fix it by failing forward just add another migration and essentially revert it and the only other thing that's worth knowing is the the baseline which is i'm not sure the terminology is common but it's the same concept which is if you already have a database um if you already have a database and you want to add migrations you already have a bunch of tables of course you need to basically add a baseline you need to say well i'm not starting from scratch but i'm starting from a certain point and conceptually the way you would do that imagine we had no migrations here but we had you know this this user table what you do is you basically need to extract uh basically the sql that would create your entire database so uh here in my case i can just go to sql scripts and i can copy the the dll to the clipboard so if i look at what that looks like um if i just minimize all these if i copy everything it basically takes the shape of the entire schema or every table in the schema and it posts it into one big sql file i know that flyway creates this one automatically for example so i can remove that but this will leave everything else so you can see that just got the user table with the email on the name and then what you would do is you just add that to a file and then you basically perform like a a baseline command which basically updates manually updates this uh this table which if i refresh now you can see the state you basically manually update this table to say actually i've added all these uh sql scripts but you know you can skip them so update the the stay here we can skip them because we've already run them um and then going forward you need to follow the standard state pattern here but i think that's everything i wanted to cover in this video so yeah i'll wrap it up there thank you very much for watching have a good day and i shall see you in the next one [Music] you

Channel: Redhwan Nacef

Views: 46,684

Keywords: Software engineer, software developer, how to, how to code, coding, programming, testing, java, javascript, learn to code, learn coding, learn programming, software, technology, computer science, YouTube, Redhwan Nacef, database migrations, flyway

Id: dJDBP7pPA-o

Length: 17min 33sec (1053 seconds)

Published: Fri Feb 11 2022