How to Integrate Flyway With Jenkins

how to integrate flyway with jenkins [Music] although you might have your application set up to use deployment automation your database is a completely different story every time that you release your application someone has to log in and run scripts to make changes to your database schema or even worse they have to go in and manually make changes to the database sometimes these changes can cause delays to deploying your application because that one person is the only person that has access to make those changes what if i told you it was possible for you to also automate your database changes along with your application deployment so that you no longer have to wait for someone else to make those changes for you in this video we're going to show you one way to do that using flyway and jenkins here's today's starting point i have a jenkins lts controller version 2.319.3 when this controller was installed it was installed using install suggested plugins and attached to this controller i have a linux based agent that has docker installed i also have a mariadb instance that already has an empty database called my underscore app throughout this video we're going to be creating a sample repository that by the end of this video you'll be able to go and take a look at so if you want to try it out for yourself it'll be there for you the link to that sample repository is down in the description as i said in the introduction i have a linux based agent with docker installed on it and we're going to be using the flyway container image to be able to run our flyway commands now if we go ahead and take a look at the flyway image on docker hub what we'll see at flyway flyway is that there is a latest image but i don't ever like using just the latest image but i can see the latest image is f6 af so if i look for f6 af and i go ahead and scroll down the rest of this page i can find it was eight okay that's still not good enough there's eight five might be good enough there's an eight five one that's the one i want to use so in the jenkins files that we create we're going to be using this version of flyway 8.5.1 so the first thing that i'm going to do is i'm going to create a jenkins file that i can use to verify that the flyway image is being created correctly and we can get a version out of it the first thing i'm going to do is i'm going to create a jenkins file in my repository in this case i'm actually going to call it jenkinsfile dash1 and here's the basic pipeline that we're going to run and we're just going to be running the container flyway calling out the tag 8.5.1 and we're just going to run version so that way i know that i can pull in the image start it up and get the version out of it that we're expecting so i'm going to go ahead and save this let's go ahead and push it up to the repository i'll call this initial and we'll go ahead and sync that up so now that we've created this jenkins file let's go ahead and go back over to our controller and let's create a new item we're going to call it flyway and create pipeline click ok let's scroll down to pipeline script from scm change this to git paste in our url we'll change our branch to main and our script path is jenkinsfile-1 because that's what we named our file click on save and click on build now and we can see here from the output from version that we have flyway community edition 8.5.1 next up let's take a look at the flyweight documentation and the link for this documentation is also down in the description and we're just taking a look specifically at the first steps for command line and the example that we're going to be creating is going to be based on what we see here on this page so we can see that we're going to need to create potentially a configuration file we will but we're going to do it exactly the same way that they're doing it here we're going to create this person table and then we'll migrate it so that the table is created in our database then we'll create another migration where we're actually inserting data into that table and then we'll run the migration again so let's go ahead and go back over to vs code and let's create that comp file but we're not going to create everything that was in that comp file that we saw i'm going to say new file let's say conf slash flyway.com i'm going to paste in just the url now my database is mariadb so i'm just using the mysql jdbc connection so i have my ip address my port and then my database name is my underscore app and we can go ahead and take a look at that in sql pro and we can see here that we already have a my app database and actually nothing has been created inside of it yet so that's okay so i'm just defining what the url is next up what i want to do is create the first migration which is going to be the creation of the person table so i'm going to say new file and we're going to name it sql v1 underscore create person table now how we name our files is actually important and you'll see that in a few moments so we'll go ahead and create this file and the contents of this file is just create table person an id and a name so very simple table to create so let's go ahead and save that the next thing up is we need to create our jenkins file to be able to run the migration so let's create a new file and let's name it jenkinsfile-2 i'll go ahead and paste this in and we'll walk through it line by line what we're going to need are some credentials to be able to connect to our database now the my app database has already been created and it has a credential that we're going to create in just a moment but we're going to be naming it db dash creds within jenkins so we'll run our version command again just to make sure we have the right version that we expect and then we're going to run our migration our validation and then we're going to run info at the end so let's go ahead and go back over to our controller let's create our credentials and we'll go here to dashboard manage jenkins manage credentials we're going to create a username and password and we'll look at why in just a moment the username that i've created previously is jenkins and the password is password1 and the id is gonna be db-creds and same for the description let's take a look again at our jenkinsfile2 file and if we take a look back over at vs code what we're going to be doing is a docker run we're going to be mounting in two volumes we're mounting in a volume that is workspace sql which is where we created our migration and it's being mapped into flyway sql if you take a look at the documentation for the flyway image it tells you where to go ahead and mount these and these are the two mounts first off is for sql the second mount that we have is comp which goes into flyway conf again all in the documentation for flyway we specify which image that we want to run and then we're specifying our user and password then we're running migrate validate and info and this db creds is being mapped back to our credential that's up here at line 4 which is being loaded in from the credentials helper into the db creds specifically the db underscore cred's environment variable which then gets expanded into db credits underscore usr and dbcred's underscore psw so we've saved everything let's go ahead and commit all of this up so we're going to say here migrate 1 and we'll save all and commit and we'll sync and then let's go back over to our controller and let's modify our job to set this to jenkins file dash 2. we'll click on save and then let's click on build now okay so i messed up let's go back and see what i did wrong if we take a look at my naming here i have a jenkinsfile-2 well that's correct and all the other files are correct all right so what did i do wrong here let's see what i did wrong so unable to find jenkins file dash 2 from that all right well let's click on this and see what's going on did i not push it up i guess i didn't all right here let's do that again so let's go back over here and let's sync the changes but i had done that i guess i didn't okay now let's go ahead and go back over to our controller and let's run this job one more time okay now that it's finished let's scroll back up and walk through what happened here here's our first call out to version so we see our version number again which is fine that's what we expect we run our migrate which it says flyway is up to date it's pointing to our database that we configured in our comp file in our flywheel.conf that's in the conf directory it also validated the migration for us which is good and it's creating the schema history so there's a schema history table that we're going to look at in just a moment it's creating the current version but then notice here migrating schema my app to version one dash create person table think back to how we named that file just a few moments ago what we have here is a v1 underscore underscore upper c create underscore person underscore table if you'll notice what happened is it took the v off in front of the one it turned the double underscore into space dash space and then removed the underscores for the rest of the phrase so how you name your sql file determines what the version name will look like then we successfully applied the migration we ran validate which it just ran the validation for us just because and then finally we ran our info and it gives us information about here's our database and these are the versions that have been applied in this case the only one we've had so far is number one which was create person table so let's flip over to sql pro and we can see here if i refresh this that we have two tables we have our person table which is what we expect with just two columns and then we have our flyway schema history which has just one entry in it next up let's create another migration which is going to insert data into that table so let's go back over to vs code inside of the sql directory we're going to create a new file the new file is going to be named v2 double underscore add underscore person then what we're going to be putting into this file is just three simple insert statements and this data will be inserted into the table when we run the migration so let's go ahead and commit this we'll call this v2 and this time i won't forget the sync and now let's go back over to our controller and let's run this job one more time you can see here as it finished up we run our version we run our migrate which in this case is just saying you're already at schema version one we're gonna go ahead and apply schema version two so we applied one migration to that now we're at version v2 we do a validate and we do an info and we can see from the info that we have our second migration that occurred which was add person and if we go back over to sql pro what we'll see here if we go ahead and go over to person we can see our three rows that were inserted automatically for us and just for consistency's sake if we take a look at the schema history we can see both version 1 and version 2. here are a few reasons why automating the deployment of your database schema changes are important if you typically have to wait for someone to run scripts for you this automation will eliminate that bottleneck by automating the schema changes you'll know that the schema changes are applied in the correct order and you'll minimize the risk of an incorrectly applied change by including your database schema changes as a stage within your deployment pipeline you'll be able to confirm that database changes are already in place prior to deploying your application hopefully eliminating the risk of having to roll back your application deployment if you have any questions or comments you can reach out to us on twitter at cloudbees if this video was helpful to you give us a thumbs up and if you haven't subscribed to cloudbees tv yet why not take a moment click on that subscribe button and ring that bell and you'll be notified anytime there's new content available on cloudbees tv thanks for watching and we will see you in the next video

Channel: CloudBeesTV

Views: 3,720

Keywords: darin pope, jenkins, jenkins tutorial, what is flyway, how to use flyway, flyway db, flyway docker, redgate flyway, flyway example, flyway jenkins, jenkins flyway, mysql flyway, flyway mariadb, flyway devops, flywaydb, how flyway works, flyway jenkins pipeline, flyway integration with jenkins

Id: Oy4v8HAbWCs

Length: 13min 20sec (800 seconds)

Published: Tue Mar 15 2022