Flyway Desktop: Working with PostgreSQL

hello my name is grant fritschy i work for red gate software today i want to talk to you about flyway desktop and its support for postgres sql and let's just go ahead and get started with it now we could work on existing projects happy to look show you the one of those but instead what we're going to do is walk through the entire process of creating a new project so this could be a brownfield or a greenfield project it almost doesn't matter what we're going to do is go ahead and get started and first we need to select a folder where we're going to place our stuff now i'm absolutely going to put it into github and post it online so we'll select this folder to get started this is our location for all my github stuff and now we're going to give our new project a name this is hamshack radio and we're going to get going with that now we get to choose our database engine we currently support sql server oracle and postgres we are working on getting all of the rest of these databases supported fully through flyway desktop but right now the full support is just with sql server oracle and postgresql let's go ahead and do this create our project and get going now that's created the project and that's great but what we need to do is actually link to a database this is all about doing development this is all about making changes controlling those changes getting them into source control and tracking them over time so the first thing we're going to do is link up with our development database so i'm going to choose to go into here now i've got my local host and on the default port now the database is going to be hsr dev hamshack radio development the schema we're going to go after we'll go after public for now we can look at those later i've got a simple setup right now for security and we can walk through that obviously you can change the way it stores that information i'm going to go ahead and save it here or you could just post in directly the full jdbc connection yourself and edit that now let's go ahead and test that validate that it's working it succeeded so we're good to go what we're going to do is say test and save and so now it's starting a comparison project so what it's doing is it's comparing what i have currently in my source control system which is nothing because it's a blank folder to the existing set of structures already in my database and so i have four tables that we've been working on a couple of sequences and one foreign key as you see there so we've got a full-blown database already under development so this would effectively be a brownfield project we're not doing something new we're doing something existing now we can select all of these and once they're selected then we get to save them to our project and basically this is one of several views into the way everything is managed within flyway desktop now we've got the schema model where where we've got our actual schemas we've got the ability to generate migrations we have the ability to look at the migrations we've generated and then we have the ability to control our version control on our source control connections and everything else now to start with i want to save this to my project because we've got to get started someplace and this is the best place to start because like i said we've already got an existing database so we will now save that to project it is retrieving everything into a local set of repositories and we can take even take a look at that what was done was these files were created so we've now got those locally and we can commit these changes or we can start looking at generating migrations now we're not ready for migrations yet the whole idea of migrations is for when you're ready to do cross system deployments we're ready to move to the qa to division we're ready to move into production and so you don't generate migrations when you're working when you're working you stick within the schema model and make all of your changes there so now we've got all this set up we're good to go we don't have to do anything else but what if we wanted to what if we wanted to generate some migrations well let's go ahead and say you know we're going to generate the migrations now the first thing the migrations needs is a shadow database a place to run those migrations to validate that things are going to work so what we're going to need to do is set up a shadow database now before we do that we need to have a database so let me switch back over here i'm going to run create database for hsr shadow first for hamshack radio shadow database switch back to flyway desktop and now we can set it up so again it's localhost with the default port the database that we're looking at is the one we just created hamshack radio and again i'm going to use the existing credentials that i have i'm going to go ahead and test the connection again just because you know i'm a little paranoid want to make sure we get it right okay good it worked now we can say is it okay to erase data inside this database there's no need in this case but you could want to do that i'm going to leave it off for now oh well actually i'm going to say okay because we actually need to do the full test there is no data so there's no fear here so now it's starting the comparison between what i have in source control and what is on my existing database now the fact is there's nothing on my database so if we're going to generate migrations this is the set of migrations that we would want to generate and again we could click on them click on them as individuals say oh well what if we only wanted to generate for the radios table for example and then we could generate the scripts right over here on that button but what we're going to do instead is we're not going to select any of these instead you'll notice we have a prompt we've not yet created a baseline a start point for all of the deployment and development that we're going to do so first let's create a baseline so let's go ahead and connect up to a database we'll do the same thing as before localhost default port i have a database already called ham check radio this is not production it is a clean copy of my production database stored locally so we're protecting our production environment and let's make sure i didn't fat finger the password cool that's all good let's hit test and save and now let's establish a baseline so what it's doing is it's comparing the existing database to the scripts that we have and it's found that it's all the same it's ready to go this is the migration script that would be for uh our baseline for any new databases that we create so let's go ahead and hit save and so now we have a baseline database and that's great so we're all set up we can even take a look at it in the migrations and you see that it's version one it's got the date time stamp on it by default we could change that if we wanted to and but this is all set up and ready to go and it's let us know that it is a baseline it gave us a description already and so we're ready to go now from here we could start talking about migration adding to new databases where we would like to do deployments we're not ready for that let's just go back to schema model now the thing is is that this is all about development so what if we were developing well let's go back over to here let's say we want to create a new table and we'll just call it my new table and we'll just put a single column into it and that's it just for example purposes we're going to create a new table on our database now we could do this any number of ways you could you know however you want to run it however you want to run your edits creating things we just need to make sure that we're in the right database development and now we can run this and we've created a new table inside the database now let's go back over to flyway desktop and we've got the schema model let's refresh our schema so it's again it's running a comparison between what we already have locally in our source control and it's showing you the path that exists and then it's showing you the new thing that we've created and we created that new table my new table id and four all set up exactly where we expected now the thing to notice is we could save this to project right now and it's ready to go we can click on it again individually or as a group if there's more than one change and then we can commit that to our project or let's say this was created by accident or there's an error or better still let's say that we're working on a different branch of code and we want to make pull from um the branch code and put this into our database we could instead apply to the database so we where we have a table in the database this would then remove it from that database that's not what i want to do right now instead i am going to go ahead and save it to the project so we will select this we will save the project that then has put it into our our project we're good to go um we can take a look at and generate migrations for this new thing let's go ahead and close the existing stuff so it's going to run again the comparison validating what's in the shadow database with what's in our source control system and it arrives at the same place again we've got a couple of things that are different um we had a one table that got created that wasn't there before and a table that was missing from our shadow database okay let's go ahead and generate scripts for all of them there we go now here's where we can decide if we want to use the existing schema naming or if we want to say hey you know what putting my name in there is not actually descriptive let's put in something else let's put in you know fixing missing tables so we've got tables that we're missing and we're making sure that they get added back in now you can of course add some underscores in here if you want to it will change the way the documentation is done within flyway um it's all up to you how you guys want to manage this stuff you can do it any way you like but the important part here here is right here in ensuring that you've got a good schema versioning number and that's all set so now if i hit save that migration is now committed i can hit close or i can go to the folder where that's at so let's um let's go take a look it opens up the folder and we can see each of those if we back out a little bit we can take a look and see our migrations and so there are the two migrations that we've created the baseline itself and then the vo2 so they're all in there all set up the way we would expect to see them we've also got the json and the configuration for flyway for it to be able to do the stuff and it's already even set up git ignore so that some of our information that shouldn't go into our source control system is not available now from here what we could do is um initialize get so now we've created this as a git repository so if we go back to to flyway desktop if we take a look at version control it's not going to think that this is version control so what we're going to do is close this project and reopen it because we did initialize source control underneath the project so now we can come back in here it's it's again running the comparison to see if anything has been changed nothing has we obviously with nothing changed we can't generate migrations but now if we take a look at version control sure enough all the stuff that we've created all the information that we've walked through as we've done this session are all there now and so what we can do is just say initialize project in flyway desktop with that everything is selected we can now commit and now all of that stuff is committed into the local git repository and so as we make our changes they're all committed locally and when we're ready um if we need to we can branch um push pull from other branches whatever you needed to do you've got a way to control it through here and so this gives you a complete way to set up control generate scripts for all of the things that you need to do within your postgresql database it's pretty exciting stuff we're really moving into a whole new multi-platform world and i'm excited to see postgres sql as part of it that's it for today my name is grant fritchie i work for red gate software

Channel: Redgate Software

Views: 4,697

Keywords: flyway, flyway migration, flyway data migration, redgate flyway, flyaway database, flyaway db, Redgate software, database migration tool, PostgreSQL, postgres tutorial, postgres

Id: XQ2leB8dtko

Length: 14min 25sec (865 seconds)

Published: Wed Mar 23 2022