Database migrations with Flyway, Spring Boot and Kotlin and Postgres

hey hi hello and welcome to the next video in which I will show you what exactly Flyway is and how to configure it when working with spring Boot and cotl we will see why do we need it in our project how to configure it and customize Its Behavior lastly if you enjoy my content then don't forget to subscribe to my channel okay so without any further Ado let's figure out what is Flyway and what problems does it solve to put it simply Flyway is a database version tool just like G is a version control system for our codebase Flyway takes care about database schema management let's imagine that we've started a spring boot project which communicates with POG SQL date baites we've been working on our own implementing new features adding new tables and changing the schema many times at some point a new programmer joins our team so we simply export our local all remote database and send him the script so that he will be able to set up a local environment so for so good but what will happen when more people join our team or we would like to create more testing environments of course we could save the script and update it each time we change anything but this can introduce plenty of problems for example when multiple people are editing schema asynchronously how can we make make sure that all their scripts were applied in a specific environment or how can we be sure that all developers have exactly the same or a specific version on their local or how can we determine the correct order of script so that nothing breaks when applying them well and if those three points do not convince you just imagine working with G when cooperating with others and exchanging written code manually technically doable nevertheless definitely not recommended of course Flyway is not the only tool we could use with spring boot however it is definitely easy to work with and worth a try with all of that said as the first step let's prepare the database for our project today so I will be doing that with Docker and the first thing I need to do let's open up Docker Hub I'm working on Windows um this green indicator right here uh indicates that engine is running we can see that I had some mongodb container and the next thing I would like to do will be to run pgra SQL database container so I can show you how to do that manually let's open up our favorite web browser and navigate to dockerhub hub. do.com wonderful let's accept all cookies and let's search for PA SQL pogress wonderful let's click right here and let's scroll down a bit I'll will show you the command that we can run in order to fetch pogress Docker run we specify the name for our container we can set the postgress password as the environment variable we run that in a detached mode and we also need to uh provide the name of the image so let me copy this comment from here control+ C and as I'm working on Windows I will run Powershell excellent control+ V and I would like to adjust two things firstly I would like to set the password as password um if we do not specify the default username it will be pogress - D let's leave it and the postgress version I would like to use will be 15 because at this point this is the last supported uh Flyway uh last supported postgress version by Flyway we can see unable to find image pogress 15 locally uh it may take a while for um Docker to fetch the necessary things when it finishes we can see downloaded newer image for pul grass and in here we can see the hash of our container so when I specify Docker PS to uh show Ry containers we can see pogress similarly in here we can see some pogress added uh we can see it rying actually I need to stop that because we need to do one more thing so let me delete that remove this time it will be way faster let's run this command once again but this time I need to EXP the port of our container to our Local Host so whenever we will be connecting to Local Host DP 5432 5432 so whenever I will be reaching Local Host 5432 it will be forwarded to 5432 of our container again let's run this time we can see that the image is downloaded already so it doesn't take that much time let's verify Yep this time we can see that the port is exposed so following let's open up uh the tool for database management I will be using Deaver which is free Tool uh feel free to use whatever you want in here let's click new database connection and we are using pgra SQL so let me click on pgra SQL next so the host will be Local Host this is correct Port will be the default postgre SQL Port 5432 database will be postgress because this is the uh default database of course this can be configured ASW when running the container but we didn't do that username will be postgress and password password Let's test the connection we can see that we are connected so let's hit okay finish and on the left side we can see our connection we can see that we have one database called postgress so as the first thing I would like to do here create a new database I'll call it sdb because usually that's what you should do uh in your real life scenarios so with that done at this point that's pretty much everything we need to do here so following I would like to set up a new spring boot project and the first thing we need to do and I suggest you to do is to navigate to start. spring.io if you are using intell Ultimate Edition you can do that from your IDE spring excellent sorry start. spring iio excellent uh the project I would like to use Gradle with cotl DSL so I need to specify this one language will be cin as well spring boot version I will select the latest table coder C artifact Flyway description let me delete that package name that's okay jar whatever for the dependencies I will use spring data jdbc PR this data in SQL stores with plain jdbc excellent at this point I can hit generate it's downloaded and let me copy that to the desired directory documents project I have one example project and technically I've extracted Flyway project in here so with that done the next thing I need to do will be to Simply uh specify new intellig project let's click open in here we have project example Flyway let me click on Flyway and we need to wait some some time for Gradle to build our project when it finishes let's open a build Gradle kts and this is where we need to put our dependencies to work with Flyway we will need three dependencies postra SQL driver Flyway core and Flyway pogre SQL previously I remember we didn't need that but from some version they have modularized uh Flyway uh a bit and Flyway postra SQL is necessary as well so again let's open up our browser and this time navigate to Maven repository. comom Maven repository. comom excellent the first thing I would like to search for post SQL hit enter and this is post SQL jdbc driver the dependency that we are interested in I'll select the latest version control+ C get back to intellig and put it right here with that done the next thing I would like to search for Flyway core Flyway core let's hit enter and we have Flyway core dependency exactly the same let's copy that and open up intellig control+ V wonderful as I mentioned previously the last thing Flyway post grow SQL so let's get back Flyway post gql and this is the dependency that we need contrl plus C let's copy that get back here runtime only excellent let's reload that again Gradle will be fetching the necessary libraries dependences depending on your machine it may take some time but when it finishes let's go to main cotl Flyway application and let's try to run that let see what's going on in logs okay so we can see that application failed to start and Fade to configure data source so URL attribute is not specified and no embedded data source could be configured so the next thing we need to do will be to navigate to resources and configure data source by default we have application properties I prefer working with yam files so let me change the extension and right here spring application name let's leave it Flyway however the next thing we need to configure will be the data source so data source and the first thing URL will be jdbc this is the driver we'll be using post gr SQL and the puff Local Host 5432 if you remember correctly this is the default port for POG SQL SLS DB to specify the database we would like to connect to the one that we created previously for the username we'll use username postgress and passw password I hope that I don't need to remind you that but you should never specify your username password as a plain text within the Y it should be rather injector for example through environment variable to your container rank in uh environment and you must do that to avoid any leakage of those things uh when you push that to remote as a source code so with that done let's rerun and let's see whether anything changed and this time we can see our application finished and let's take a look at logs so first thing we can see schema key Story table public Flyway schema history does not exist yet we can see that Flyway successfully validated zero migration that's correct as well we don't have any no migrations found are your locations set up correctly well not yet moreover we can see creating schema history table public Flyway schema history and blah blah blah other things the important thing right here is fly with schema history so let me actually get back to De Beaver open up some DB let's hit here schemas public tables and we can see a table Flyway schema history when I take a look uh long story short this is the way Flyway manages our schema history so it creates a new table in our database which will later be populated whenever we add uh a new migration there will be version description what we can see type script uh will be the puff of the script and check some so if we apply some migration if we create some file it will be applied and later with edit that well Flyway will know about that but with that done let's get back to intellig and let's add the migrations directory right Mouse button on resources new directory DB once again right Mouse button new directory migrations and this is the def old uh Flyway location for migrations of course we can customize that and we'll learn how to do that later nevertheless let's add our first migration right Mouse button new file let's call it V1 double underscore create user table and this will be SQL script let's take a moment to figure out this strange name well this is not the mistake this is the way we need to name things when working with Flyway so first of all our name consist of a prefix V for version migrations U for undo R for repeatable and B for Basel so we can see that this script will be a version migrations secondly we need to specify the migration number and in our case this is one if we would like to uh it be to be 1. one then we should start the name with one underscore 1 so in the end this will be translated to 1.1 never this I don't want to change that nextly we have the double underscore that separates version from migration description and we have the migration description create user table so let's see it in practice pleas let me create a new uh let's insert some script create table if not exists up user let's open up rocket ID serial not uh primary key name text not null semicolon shift plus F10 and let's see what's going on in logs um no migrations found are your locations set up correctly so I guess they are not let me close this one DB migrations sorry this should be singular and now once again shift plus F1 to rerun the application and we can see successfully validated one migration curent version of schema empty schema which means that we didn't have anything and Flyway is migrating schema public to version one create user table and you can see it successfully splitted the version from the description let's take a look whether this is true so let's open up the beaver data the first thing I would like to check will be Flyway schema history f52 refresh and we can see that Flyway populated this table we have the description create user table we have the type SQL we have the script name V1 and check some as well if you're interested in that let's get back to intellig and let's change the script a bit for example um let's add address text not n as well let's take a look now shift plus after and we can see that this does not work right now error creating Bean with name Flyway initializer migration check some mismatch for migration version one apply to database so this is the value that we have in a a Flyway uh table and resolved locally and this is a great proof that Flyway will help you not only to add new migrations change your schema but additionally will guard you from changing that or anyone in your project from changing existing schemas well if you would like to apply this once again well then you need to uh manually edit that I do not recommend doing so so if you would like to change the uh last migration then you would need to simply delete this record and manually revert your table to the version from the migration and then you could technically rerun because fly will not be aware of the version anyway do not recommend that however Life Is Life and sometimes we need to think outside of the box so let me bring back that actually to the previous version shift plus F10 to see if I it still matches no it does not so maybe Control Plus Z can I do that shift plus of 10 let's take a look look okay let me revert that then we'll see that in action I am deleting up user I am deleting this record hit save get back to our project shift plus F1 H okay this is something I should not put shift plus F1 yeah that's cotl uh cotl thing to leave trailing commas successfully update one migration to schema public and that indicates everything is working fine so with that done let's see how we can customize Flyway with application y file and the first thing I would like to show you will be to enable disable Flyway so let's go here spring Flyway enabled and by default this is true we can set that to false for example you have various environments uh you want to you may want to set that from environment variable and this time you can see actually we cannot see any logs from Flyway and that indicates everything working fine let's get back Flyway enabled set to through wonderful uh sometimes if your migration project if your migrations count becomes higher then you may want to have multiple um multiple dire directories to organize that better how we can achieve that well let me introduce another new directory and let's call it another directory in here I would like to put another another uh migration control+ V plus C on the first one control+ V in here and let's call it add address column okay and right now I would like to edit the script so alter table if exists app user let me check if this was the correct one app user and add address um text not null semicolon in the end wonderful so we have two migrations and let's go back to application do yam Flyway enabled through and we can use the locations key to specify our locations locations and in here we can have multiple locations so we can instruct Flyway to search various directories um and it will combine them together so that it will then later it will treat them as One Directory again this might be useful uh to better organize your migrations within the code class Puff the first one will be DB migration and the second class path will be another directory control+ s let me take a look one again DB migration another directory when I rerun I expect that it will detect both migrations okay let's take a look what's going on here um okay we can see that our script at address column failed syntax error at or near is let's take a look what I did here alter table if exist shift plus F10 right now and this time let's take a look so we can see that Flyway successfully validated two migrations current version of the schema public one so it first reads our table Flyway table from the database checks what is the current schema what version We would like to apply and we can see migrating schema to public to version two at address column again let's open up Deb let's take a look at tables refresh F5 up user and we can see we have ID we have name and we have address so again this approach May really help you to organize your migrations better so as the next step let's take a look at one more thing I'll add Resources directory and some directory and inside it new directory I'll call it psql I would like to show you that Flyway is able to detect the vendor on the fly so let's add V3 and we'll drop this column V3 drop address column let's add it we have it in postgre SQL alter table if exist app user and right now drop column and address I would like to drop this column wonderful let's get back to application. yam and this will be the third directory I would like to specify here so class puff some directory slash and vendor so with this I expect Flyway to detect vendor as SP SQL and to resolve that on the fly if I rerun and everything was set up correctly we should see that well migrating schema to version three so we can see that Flyway thetic is detected it successfully to prove that let's refresh our appuse we can see that the column was successfully removed the last thing I would like to show you will be the Java codling configuration so SQL files are not the only way to uh configure Flyway we can do that in the code and sometimes this may be better whenever you would like to uh automate something or fetch some data Maybe from external resource from some file and it may be quite hard to do or not doable within SQL well in such a case we can simply implement the Java migration interface and annotate that as a component in our code and again we can mix that but in real life scenarios you should be quite cautious about that because it may be hard to understand later or for other people within your project nevertheless with that said let me close all of this close all tabs hit on this package new package I'll call it config and again right Mouse button new I'll call it Flyway within it I would like to introduce a new class and the class name will be matching this style the Flyway style of naming convention class V4 and for example well add uh address column again let's hit enter as I mentioned this must be annotated as a component or in any other way to instantiate being within our spring context and as I mentioned uh we can Implement a Java migration interface or extend the base Java migration base Java migration this is a class in intellig control+ E to implement member and we can see we need to overwrite the migrate uh function the first thing I would like to do will be context and let's make context not nullable nextly I would like to instantiate a new uh statement update equals context do connection. create statement wonderful and the next thing we can invoke the execute method on it so update. execute and in here we can see that I can specify a simple SQL script as a string value again sometimes you may want to use this approach to fet some informations from I don't know environment variables another API even well that might be a good place to start with and again this time I will simply copy add address column once again so V3 ctrl+ C let's get back here alter table if exists app user add address text not null well let's run this shift plus F10 and excellent we can see that Flyway validated for migrations and migrated public to version for address column again last one look hit F5 and we can see that this column was added successfully and well we can see that we can work not only with SQL files we can mix those things but in real life scenarios you should be cautious about that because it may be quite hard to understand later if something is happening in multiple directories sometimes in a SQL sometimes uh within the uh code and yeah this might be kind kind of hard to understand both for you in the future and for other team members and basically that's all and that's all for this video about Flyway with spring Boot and cotlin let me know what you think about it in the comment section below did I encourage you to use Flyway or any dat based Version Control tool thank you for watching and see you in the next videos bye

Channel: Codersee- Kotlin on the backend

Views: 460

Keywords: database schema migrations, flyway migrations, database migrations, database schema versioning tools, spring boot database migrations

Id: KOvykFIEoYc

Length: 30min 27sec (1827 seconds)

Published: Mon May 20 2024