Intro to Database Migrations in Spring Boot 3 with Flyway

hey everyone Wazoo here with another video and in today's episode we're going to be talking about another fun topic database migrations yeah I know it's what you were thinking right I guess before we get started on any code maybe it's worth going through what a database migration is and how it can help our app and why we eventually will need them so imagine that you are maybe for now you're still learning uh web development you're still learning spring Boot and your or any other server language that you're working with and you are doing everything on your own local machine and it's an environment that you can easily blow away and get rid of and junk whenever you need to whenever you want to jump onto something else or if you make a mistake you can easily just get rid of it and start fresh with something new now it gets more interesting when you actually start to release work for others to use so let's say for example that we've got a an application and let's see we'll just call this and we've got one in this Square this is your app that people can use so when you're talking to others or when you're working in more sort of development environments this is what's called production and it sounds scary maybe to some people it sounds you know very serious and it's not it's really just it's a it's a space that people can access your app from wherever they are across the internet which is probably one of the coolest feelings to have when you do any kind of development work it's kind of exciting that just to hear and see that other people are using whatever it is that you wrote and it's and have made available for others so you've got an app that people can use and maybe over here let's just label this as your local machine and we often we as in developers we often just refer to this as development but this can be you know in today's world this can be your own local machine this could be a app container that you're logging into like something on a remote server that you're just treating as a playground for whatever it is that you're building so regardless whatever this is this is your local machine now again like what I was saying in the introduction it's nice and easy to let's use this as our maybe a circle so a circle will be our database so it's nice and easy to blow away your local database whenever you want to you make a mistake you come up with a different table you enter some bad data or you just want to start from scratch because you you've decided to go in a totally different direction then it's totally easy to just blow this one away and recreate it as you need it let's just do this recreate it as you need it and there won't be any side effects so no one else outside of this box is the wiser that that's what you're doing but things change when you have something available for others to use online so if you've got a database here and suddenly people have started populating it with whatever data you're trying to capture in your app let's you know we've we've built on this channel we've built a basic to do app as well as a Blog application using spring boot take those apps as examples where maybe you've you've made that available for other people to use on a Cloud Server and so people have logged in they've started logging their own to-do's or they've started creating their own blog posts so now you've got data that other people are touching actively touching and you you can't necessarily just trash it whenever you want to I mean you could but you really don't want to I mean you kind of want to keep the people happy that are using your stuff generally speaking right so that's when things start to change and the other factors that go into change are for example again in our application you've got a let's say you've got a blog post a blog post table and you're tracking an ID and I think a title and as well as like the body content right and let's say the timestamps so let's say that in on your local development side you've got the same thing set up you've got a post you've got an ID you've got a title and you've got a body and timestamps you've got a matching environment between your local Dev machine and production now what happens on development when you want to add let's say an image so let's say like a a Blog a blog post image in this example or like a thumbnail let's just call this thumbnail instead of image maybe that's a little bit more representative so now you've got a situation where your local database is different from what's already existing in production and this is where the benefits of database migrations come into play so database migrations is kind of a source control like a git for your database so you can capture states of your database you don't capture the data that's in the database you just you just capture the structure the schema that it that your database is composed of so The Columns of your tables any sequences things like that is what you need to control what's important in controlling the actual data is easily it's easy to back up that and restore it when when you need it but in order to do anything with that data itself you need the structure to go along with it the backbone of what composes your actual database so I think this paints a good picture no pun intended as to what goes on with the database and why it's important for a database migrations the other the other picture that can happen is not only can you have a different set of code running in production than you do in local development but where you have several local machines all working on the same code base and each one of them could be working in different on different phases of the application so this is now a situation where you've got one or more other people helping you on your project and so each one of them have to have their own database instances on their own local servers their own local environments that they're working from and so you all might be touching the same table creating elements that interact with that table you have to find a way of making sure that your databases and your schema is in sync that way you can equally share the project between everyone working on the same code base so I think those scenarios illustrate what the importance of using database migrations are okay so here we are back at start.spring.io we want to create a new project so we want to choose the maven project type Java of course Springview version of the latest which right now is 3.0.4 and we want to give this a artifact name of spring boot 3 database migration with Flyway I know it's kind of a long name but it's a little bit more descriptive for our purposes okay and then we want to keep it a jar file Java 17 and so let's go ahead and add some defense dependencies we want to add the dev tools we want to add the H2 we want to add Flyway wouldn't that be handy and we want to add spring web just because I'm always used to adding spring web which gives us the local embedded Tomcat server and let's see we need the validation and the data jpa so we've got all these dependencies linked up to our project let's go ahead and hit the generate button download the zip file extract it to your favorite place in your hard drive and open it within your favorite IDE and I'll see you in there so here we are in IntelliJ I'm using it for this project let's expand the what's was created here and you'll see that we've got a regular spring boot start.io project here what we're going to do is we are going to take a look at the same models that we created in our blog application our spring boot blog application and we're going to attach migrations to them so we're going to go through sort of a sample exercise as to how you would manage and control changes to your objects and your database as you are developing your application so before we go any further let's just Define all of our H2 properties all of our database properties in our application.properties and these are a standard the standard settings that I've been using in just about every project so far application properties set up here for supporting our local H2 file database you've seen this stuff before you've seen that we allow the H2 database console now the other property that we want to show is spring.jpa.show SQL equals true so that way we can verify all of the SQL that we're seeing in our console log output as well as we need to tell hibernate the hibernate layer in the past we've had a create we've been using the create drop flag for this ddl Auto property of hibernate but now this time we want to use something called validate if you highlight over it in IntelliJ at least you'll get some help helper text here saying that this flag will validate the schema and make no changes to the database which is what we want so we want to make sure that during startup Java spring boot will go through the process of having Flyway take a look at our existing database and check for any changes and alert us if anything is different from the last time that it's been used and I'll show you how as we go through this I'll show you how this is done what we want to start with is in our resources folder we need to create two subfolders so we need to create a new folder called DB and then within the DB folder we need to create another new folder called migration and this is a convention used by Flyway this is the default folder tree that Flyway will check for any migrations that you might have for your project so what we want to do is we want to create our first migration let's say we have a in our back in our application let's create a new package called models as we do and let's create a post model so here we are creating a an entity and a data we know from our other project that we've been working from we've got an ID that's a long and we've got a title Okay so we've got our basic post model set up here we've got an ID field which we're using as the primary key using The annotation at ID we've got a title and remember how we had the body text and there was a special definition that we gave it of text because it's basically just a blob of text so we've got these three properties in our post model so far so what we can do in our very first migration is we want to create a new table we want to create a SQL file and then create our table within that SQL file so Flyway uses a convention where we begin any migration file with a capital V and then the version number and then two underscores and then whatever description you want to give that migration so for example in this case we'll be using V1 underscore underscore and then create post table dot SQL so what we want to do is write some basic SQL here so create table if not exists and we're calling it post and then our ID field is a bigint not null whoops we're using the auto increment and it is our primary key then we've also got a title which is a varchar and we'll give it a length of 255. I know we haven't really specified that before but in this case we'll give it a length of 255 it's also not null so every blog post needs a title and then our body so in this case we're just giving it I'm just calling this a varchar of a length 5000. and then we end it with a semicolon and so this is our first migration that we apply with Flyway so let's go ahead and start it up and I will show you what shows up in the log that everything seemed to start up correctly and we will go through the log messages okay so first we've got our database pool starting up and we've got our H2 console available and then we've got the initialization of our Flyway module which is as of this recording it's the Community Edition 9.5.1 and we've got our database set up here and then we've now Flyway has picked up that first migration that we've set up it's created the schema history table and it's used it's run that migration of version one so let's go ahead into the H2 console portal and check out what what is being created by flightway let's go to localhost 8080 slash H2 console and let's blow this up a bit so if we log in okay we now see that we have our post table set up which currently just has the ID title and body and we've now got a new table here called Flyway schema history so if we take a look at that we've now got a we've now got a table that tracks the different migrations in our database so we've got our description which is what we've given what we've given it in the file name which is create the post table we've got a version number of one and we've got the name of the script and then we've got what's called a checksum along with a who installed it and at what time it was installed Etc so the checksum is the important one because what's going to happen now is we are going to stop the server and we're going to make a change in our object model and now what we want to do is we want to go back into our model and we want to add the timestamps to our post entry so we want to do a local date time of created at and a local date time of updated ad and so now if we run it I'll just show you what happens Okay so we've got a halt in the execution and we've got a whole bunch of logging showing up and then we'll have a message here so schema validation we're missing a column created at in table post okay so it tells you what the error is so no problem so what we're going to do is now we're going to add another script another migration script to add those time scan stamp columns to the post table so let's go ahead and let's shrink this down so we've got room here to work so back in our migration folder let's create a new file and this time we'll call it V2 underscore underscore add underscore timestamps to post table SQL okay and so what we're going to do is some basic SQL here we're going to alter table post add created at timestamp and then alter table post add updated at and then we'll we're using the timestamp keyword so that's all we should need so let's go ahead and run this again okay we've started everything up and let's go back into our logs and now we now we see migrating schema public to version two add timestamps to post table so now we've got our timestamp columns showing up in our post table let's go back and look at the H2 console here let's refresh just to reset it let's log in with password and we'll take a look at the post table and look we've now got the created at and updated at they're empty for now but they're given a field value of timestamp a field type of timestamp sorry so one last property that we need to add to post is we want to make a use of a sequence which is how hibernate juggles the generating of our ID values so back in post remember how we have a an annotation here called generated value and strategy equals generation type sequence okay so this is what hibernate uses to track the IDS of newly created post models so let's go ahead and let's stop and start this again just so I can show you the output here so we've now we've added this new field since our last migration and it blows up again and we should see in the error let's scroll all the way to the right so schema validation missing sequence post sequence so let's add that sequence to our post table so again back in our migration folder let's create a new script and we'll call it V3 underscore underscore add sequence to post table dot SQL okay and so it gave us a hint as to what kind of sequence hibernate like the name of the sequence that hibernate was looking for so let's create a sequence post seek so start with one and increment by 50. Min value of 0 and max value and I just gave this one a lot of nines so we've now got our ad sequence SQL defined so let's go ahead and run that and it should start everything up without any issues and again if we look back in our log we've now migrating schema public to version three add sequence to post table so now we've got our sequence set up to manage the auto creation Auto generation of IDs whenever we need to so let's go ahead and let's check the database again it's a lot of refreshing of this database password password and let's look at the sequences and we've got a post sequence right here so perfect everything is going along so far so now we move on to something a little bit more trickier and that's when we have relations between databases so we know how to we've sort of covered how to do many to many one-to-many relationships in our hibernate annotations so now we're sort of more looking at it from a database SQL level so let's go back to our project we'll stop it here and what we want to do is we want to add the our Authority table so in models let's add a new Java class and we'll just call it Authority and it's also a entity and data annotations okay and we only have one property here so this is our IDE and our column is length of 16 just for our example case and string name so that's the name of our actual column and we've named it our ID so that's our basically our primary key okay so if we if we run this as is we're going to get the usual alert that we're not we're missing the authority table so instead of running this and then stopping it and going back and creating the migration I'm just going to go ahead and create the migration right away so again in the migrations folder create a new file we're going to call it V4 underscore underscore and then create Authority table dot SQL and then we're just going to use create table if not exists Authority and then we've got a field our column name of name and length 16 and it is our primary key okay so if we go ahead and run this everything should run hunky dory okay good so okay now what we want to do is we want to create the account table which is just a way of tracking all of our account information so basically our username email password that kind of thing so let's go ahead and create in our models folder let's create a new Java class we'll just call it account and again we'll give it The annotation of data and entity okay and then what we're going to do is we're going to again again use the ID annotation the generated value of strategy equals generation type sequence and then that's going to be a long and then we want to capture the email the password and then we want to capture the first name and last name fields and then we also want the timestamps so local date time created at and local date time updated at okay so similar to our post model so let's go ahead and create a V5 migration so in the migration folder create a new file and we'll call it V5 create account table dot SQL okay so create table if not exists and then account and then we've got a remember our ID is a bigint not null Auto increment primary key then we've got an email which I'm just using a varchar of length 255. not null we always need an email we always need a password so password is firechar 255. not null and then we've got first name firechar 255 and a last name for our char 255 and then are created at timestamp and updated at timestamp okay and then we've also got a sequence for our ID similar to The Post table so let's create a sequence account sec and then we're going to start with one increment by 50 Min value of zero max value the same large number of nines and then cycle Okay so we've got our sequence set up here which matches our ID Auto increment that hibernate will use when working with our account model so let's go ahead and let me just double check our okay so let's go ahead and start it up let's make sure everything starts up okay everything looks all right we didn't get any errors and it should have run the the fifth here we go migrating schema public to version five so now our schema is on the version 5 script of our database okay so now it is time to add the relationship between the post and the account okay so in our account model and hibernate in the hibernate world we have got a one-to-many relationship with posts Okay so we've got we we're we're using a list of posts and we're calling this relationship posts and then back in posts we are creating a many-to-one relationship back to account so let's go to the end here and let's Okay so we've got a join column we've given it the name account ID and we're referencing the ID field of the account account table okay so now if we run this let's just see what error we get so remember that an account can have one-to-many posts underneath that or created by that account okay so let's go to our assist or validate yeah here it is so missing column account ID in table post so that is our hint on the migration script we need to run next so in the my back in the migration folder let's create a new file and we will call it V6 underscore underscore add account ID to post table dot SQL okay so we can alter the table post and we want to add the account ID column as a bigint and we need it so we're just going to call this not null so let's go ahead and start it up and everything's yep everything looks like it started up okay so now what we have we also have a many-to-many relation between account and Authority so if you remember back in our account table our accounts can have multiple authorities so account could be like a roll admin or a role user or sorry and a role user so underneath this post one to many mapping we have a many-to-many mapping so this is where we've got our we're using our many to many hibernate okay we're using our many to many hibernate annotation and we're creating a join table called account Authority using the two columns of our account ID and Authority name to make that join table which we are then linking back to this account model so if we run it it's going to say the the validator is going to say that we we have a missing table account Authority so let's go ahead and create that migration script right now so in the migrations folder let's create a new file again and we will call it vs 7 underscore underscore create account Authority table dot SQL and again we'll use the create table if not exists account underscore Authority and we're we have one column which is called account underscore ID which is a bigint not null and it's our primary key and then we have another column called Authority name which is a varchar 16. okay and that should be all we need to set up that many many so that the many to many relationship is set up correctly so let's go ahead and restart the server and I don't see any errors there are none okay everything looks like it started up okay so let's talk about rolling back so in the process of creating migrations of there's usually a case where you run a migration and then you realize that it was run an error or you want to change something and you want to get rid of what you just ran the migration you just ran and in database migration speak this is called often called a rollback so we want to roll back the recent change that we've put in place on our database so with the Flyway Community Edition the ability to create a undo script which is what basically you're undoing a migration that you've run that's only available on the paid version of the Flyway package so in our in our use case what we're going to have to do is create a new migration script which just basically rolls back what we've what we want to roll back so let's go ahead and let's create something that we want to roll back so in our migration folder let's create a new migration here and we're going to call it V8 underscore underscore and then just Add accounts so we want to add some accounts to our database Okay so we've got some SQL here so we're going to insert into account email password first name last name created at and updated at and then we're going to use values of user1 Foo at food.com password and then user one is the first name foo's last name and then the current timestamp for the timestamp fields and we're going to do the same thing for with user two password user 2 Etc so let's go ahead and run this and everything should Start Up Normally okay it does and then so let's go back in our H2 console and let's take a look so we've got we should have two yep so we've got two users in our account table two accounts in our account table so now we we suddenly decide uh oh we've run the wrong migration script and I don't actually want any users in this table at all so because we're not using the premium version of Flyway then the only way really to fix this is to just add a new migration so to the migrations migration folder let's create a new file and we'll just call it v9 underscore underscore delete accounts dot SQL and then just a simple delete from account so this will delete everything in that account table so with the with the free version of the Flyway package this is about all we can do okay so now when we go to our table let's log in and let's check our account and we've got none here so the net result even though we've added some accounts by mistake is that we were able to roll them back with more SQL again if you had the paid version of Flyway then you can do What's called the rollback and roll back the current migration that you applied without needing to with the proper history being tracked by Flyway itself and that's it for today's episode yeah I hope you found this little project we put together for database migrations useful and it gave you some insight as to how important they can be in any project if you liked what you saw just give this video a like subscribe to the channel for notifications on more projects like this and I'll see you in the next video take care everyone peace [Music] thank you [Music]

Channel: Wazoo Web Bytes

Views: 6,734

Keywords: flyway, database, flyway spring boot, spring boot, flyway tutorial, springboot, spring boot flyway, flyway vs liquibase, how flyway works, flyway database migration, flyway migrations, spring boot flyway example

Id: p1V5GcKUJv0

Length: 34min 51sec (2091 seconds)

Published: Mon Mar 20 2023