Database migration using Flyway + Spring boot + Hibernate + SQL

maybe when he was printed GPA you always delegate the database handling creating and updating the tables to the properties that we know as the spring GPA ddl Auto to create weather update or delete and to create a drop and so on so forth but when it comes to production do we think is this always the best solution or the good way or the best way to do this or to leave this or to give a hibernate the full control of our table the question now is what what happens if someone accidentally pushes different properties for example create drop or create as a deal auto value for the for that property what will happen when the application goes through the pipeline we will lose all the data because for example this create property of this detailed Auto property with a credit value will destroy the schema and create it from scratch this is really really important issue this is a huge issue that we need to face or that we need to find a solution for this is is what our new video about so as a reminder if you are new to my channel just go ahead and subscribe also enable the notifications to be notified every time I release a new video right now we are building a great developers community so go ahead and join our servers also join our Facebook group we will have everything in the description below so you are more than welcome to our community thanks a lot guys and see you in the video wait before I see you in the video just go ahead and take one second and smash the like button and give me a thumbs up so I can continue on producing and providing you this kind of content thanks a lot and let's go ahead foreign [Applause] [Music] before we start coding let me first explain or give you some examples on how or why we need to use database migration the first use case is when when you work or when you are in production never never never delegate database management to your application why because mistakes are human or mistakes they come from human beings and if for example one developer by mistake or the administrator by mistake he just changes the ddl auto property to create or to create drop or to drop or whatever in this case you risk losing all your data all your customer and application data and this is really really dangerous so this is the first case the second one second one imagine you want to insert some data or you want to update some data and let's imagine or but let's put the situation where you update everything using Java code inside the application so when when the application starts up so you have some scripts and you have some code that allow you to enter data to update to delete and to manage the database generally speaking so if you want for example if you have a referential or if you have some static data like let's say categories or something like that and you want to update or to insert new data or to update some texts and inside this table and so on and so forth so what you do in the classic approach you need to redeploy the application you need to do all the updates and redeploy the application and then you have also to manage if this is the release date or not if this is environment Dev environment Broad and so on and so forth so what is the solution now for this of for of all these cases like the solution is using a database migration tool so as a first step let's first start with cleaning up our project so this is the project we used for the previous videos so if if you didn't follow that one just go ahead and check the first and the second video to see how we created this small database and in this video we will see how we can also use database migration and use Flyway to generate or to create automatically our database and insert some some data okay so first of all I'm gonna go ahead and drop all these tables so we start from a clean state so the tables are removed and then we also we will go ahead and remove this hibernate sequence the next step is adding the dependency so for this one we will add a new dependency which which is our flyweight dependency so to do this we go to our palm.xml at this dependency uh tag and here let's search for Flyway and here we need the Flyway core and it's from the package Flyway database now I need to update my project or reload my Maven project and then we will see how or what flyby requires to be working fine now let's set up Flyway so we have the dependency and now let me first explain what Flyway needs or what he's gonna look for and also what we need to change within our application so first of all Flyway will look for a folder called DB and inside that folder he's going to look for a file called not a file sorry but it's going to look also for a folder called migration so he's gonna look for something like that I'm gonna go ahead and create a directory and then I'm gonna call it DB Dot migration so by default when the application starts and we have FlyAway enabled he will by default look for this DB dot migration folder and inside this db.migration folder we need to provide some SQL file so I'm going to create something like that just just to explain to you so this is what Flyway needs he needs SQL files here and then he will try to execute them one by one depending on the history or the Flyway migration history so this is something we will explain it in details also also we need to do some configuration in here we need to change a little bit our our application Properties or configuration and to do so to do so first of all think about removing this one and whether you put a tune on or you can just put it to verify and I would not verify sorry but it's validate and I would really recommend using validate instead because like that you can validate your schema or your Java schema within the database schema like the models or the Java models you have in your application within the data the tables we have in our database to be always consistent and always having the same the exact same representation so let's move on and add Flyway properties so here under spring we have Flyway and for example we have this property spring Flyway by Baseline version sorry not space line version I was Baseline on migration so this one this one let's let's see the documentation of this so here it says whether to automatically call Baseline when migrating it on empty schema so this property we need to set it to True let me just clean the code a bit so this one we need to set it to true and then also we can enable or disable Flyway directly from here so by default it's true so here sorry by default it's Yeah by default it's true the default value and here we can for example here I will leave it to true and for example if I want to disable the migration all I need to do is to put it or to set it to false so this also this is this is a property that we need and also Flyway needs the password and the login of the database like we provided here the username and password so we need to provide them also in here so we have the user this one and this one it's going to be empty for our case and we need the password and it's also empty for just for this case because as I mentioned before in the previous videos that for my database when I set it up at the beginning it was not I did not provide a username and password all right so now our our properties is almost or our configuration is almost done and what we need to provide right now as an extra property and here we have this for example Baseline description and this one is description to tag an existing schema with with applying a Baseline and here let's say or let's call it init for example we need another property or we can also provide another property which is the Baseline version and this one is used to version to tag an existing schema with the existing Baseline and let's give it for example the version 0 or the version 1. all right so by default it's one and we want to have to start from from zero all right so now we have our Properties or configuration for Flyway migration there is something else I want to tell you how to write or how to prepare your SQL migration files inside this inside this folder DB migration we need to put the SQL files that we want to use and this one or these files we cannot just name them whatever we want dot SQL files Flyway he has or Flyway has a specific pattern for that and here we are talking about a default pattern and this pattern is something that looks like this let me rename this file and here we have a v or like a version one two three and so on so forth so here let's say for example V1 and then two we need two underscores two underscores not only one it's two and as you can see here one two and then you can specify or precise your file name and here for example I will add it as init data database dot SQL all right I'm gonna refactor this and I will open this file and then I have the creation of the database the tables creation here I just copied it and I'm gonna put it in here so this is the old schema that we had or the old tables that we had and they were automatically generated by our application so I just generated the SQL code and just paste it here okay so now what we will do we will try to start our application and see what happens and I'm gonna explain exactly what's happening when we start or when Flyway try to migrate the database but before starting the application let me tell you a little bit how this works or how Flyway works when we start the application Fly Away the first thing he will go to he will try to connect to the database to the schema that we have and he will try to find or he will try to query his own table which is called Flyway migration and for this table he will try to see if we already have scripts and so on so forth and he will compare the scripts with of the script names the scripts names within the files we have in the database migration also if you change something in the file in here the hash will be changed and then we will have an exception we will have an exception and this and Flyway will not accept this so let's go ahead and click on start so the application is building and now we will see what will happen here so first of all we had an exception because let me explain this we have a flyweight initializer defined in class blah blah blah and let's go okay so here we have no possibly SQL username specified in startup packet this one you can fix it by just in my case as I mentioned because I don't have a username and password so I will just leave them empty or I will just comment out this username and password and now let's see what will happen again so here we have error creating bin entity manager and so on so forth why because we have a schema validation missing table address and here I'm let's let's go ahead and check our and check our database first Let me refresh this one here and see if we have the tables or not so here we only have this Flyway schema history and this is what I told you about that what I told you about that Flyway is gonna use so Flyway will use this V1 uh we'll use this database migration folder and inside it he will try to put all the history so let's let's check what what did we miss in here so the issue was that we have this when we created this folder we just named it DB dot migration and this is not like the packages when we create a DB dot like a name first one dot and then the second part he will not create the hierarchy for it so what to do is just rename the folder create the first folder called call it DB and then inside that one create a second folder and call it migration and here you can just go ahead and move this V1 in a database inside or into the migration folder and tdg will automatically display the name like that but if I try to rename as you can see here we I have only the migration folder so this was the issue and this I wanted to show you that if you don't provide the correct folder name the Flyway he will create only the Flyway history table and he will not be able to read these files because he doesn't know that we have this DB dot migration and as I mentioned by default he will try to look and to look for the SQL files in this under resources DB migration and then all the SQL files so let me now start the application and see the output of this one foreign and here we see we have six tables and we have the address Department employee Mission and so on so forth let's go ahead and have a look on this Flyway schema version so here we have the insta installed rank the version is the version one the description is in the database because this is the file name the type is SQL and the script is V1 in a database SQL and here we have a checksum the checksum is the content of the file so if I update or change this file and try to run again the application it will be failing and here this is installed by bualy and this is the database username and we have here the date and the execution time and if it was success or not so I will just stop the application and then update something in the V1 I will just um I don't know I'm I'm gonna call this one id id for example okay and then I will rerun the application so I'm gonna go ahead and click Start and let's see what will happen if this is gonna work or not and absolutely now let's check the exception and what we have as an issue so here we have error creating bin with name Flyway initializer and here we you can see that we have a migration check some mismatch for migration version one so here the one that which is already applied to the database is this one this checksum and here the result locally is this one so this means what this means that Flyway will check every time the content of the existing files and the one that they were applied so we already applied this this version or this init database but but we wanted to change something in this in this file but Flyway did not accept it because this version was already a was already updated or installed so what we need to do we need to create a separate file if for example we just want to rename this one so we need to create a second one this one we will do it just right now because if you remember before we had our hibernate sequence and also let me just remind you that here we have the stdl auto as validate and if we run the application with the validate you will check our hibernate will check if we have the correct schema and from the Java side compatible with with the database we have in the in our schema so let's go ahead and create a second version so I'm gonna call it V2 underscore underscore create hibernate sequence dot SQL of course so here it's so simple create sequence I'm gonna call it hibernate underscore sequence and this is this the default sequence name that hibernate uses I will restart my application and let's check the content of of this table so everything started fine the application is started and we have no exceptions Let me refresh this table and here we see let me let me also before going to the to the table and here we have or we see that we have our hibernate sequence and let's check what we have here so here we have a second line it's the installed rank this is the ID version 2 create hibernate sequence and you have also the checksum the user and the same information and here we have a success as true so for example let's go ahead and add something else and let's move on to the to the next topic all right so what we need to do now I will try for example to insert some data and here V3 under underscore for example in it employee data dot SQL and here I will just do an insert or let me show you how to generate an automatic insert query if I remember this correctly so here I will just right click SQL scripts and here we have insert rows into a table and we will have automatically generated SQL queries so I just can copy this one I can close this and put this in here so what we have here we have the ID so for the ID I can use the next uh sorry it's going to be our sequence no not not the sequence directly but we have a method called Next Val from hibernate sequence yeah exact so this is how we can insert a data here for the birthday let's say current date uh also we have an email I'm just gonna give you my email in case you need to contact me at gmail .com I will just online uh inline this so next one first name Ali last name but Ali I'm gonna try to make this a bit faster uh the role let's say for example roll admin of course I'm an admin and here let's check first name okay we have the identifier app after this one uh okay so this is the first wait so this one's the ID date okay so this is the date email first name and then I'm missing the identifier okay uh I'm just gonna call it like that and here we have Road admin and the address ID we can leave it empty and Department ID we can also leave it empty and we just need to remove them from here all right so this is our init script I will rerun the application and see if this is going to work or not so if this is fine or if this is going to work fine and also if our SQL query is correct we should see some data in our employee table and here we go here we go so here we see that we have the information of the data we just inserted so if you want to have for example insert some initial data into some tables and have this data you can do it in this way so so this is the easiest way or the simple way to use Flyway migration if you want guys if you want me to explain the more detailed and more advanced way of using Flyway migration like just an example changing the file patterns using Java for migration instead of SQL also changing the default configuration of Flyway using Java and annotations and so on and so forth Just Let Me just let me know in the comments and I will do this or I will create a specific video for for that so thanks a lot for watching and don't forget to hit the like button and subscribe if you are new to my channel and see you in the next video

Channel: Bouali Ali

Views: 15,657

Keywords: Spring, Java, Design pattern, Spring boot, Hibernate, Flyway, Database, Jpa, Spring data, Migration, Angular, Api, Restful, spring, jpa, data jpa, mapping, onetoone, one to one, spring data, many to one, manytoone, many to many, manytomany, class, entity, id, generatedvalue, generated value, column, persistence, repository, service, jparepository, jpa repository, crud repository, uml, class diagram, design, software, engineer, software engineer, java, jakarta, javax, spring boot, spring security

Id: 7uKynYx1eK0

Length: 25min 38sec (1538 seconds)

Published: Wed Nov 16 2022