How to Use Flyway with an Existing Database

hi I'm Kendra little from red gates advocate team I'm gonna give you a demo of how you can use Flyway to get an existing database into version control and start running migrations against that database what I've done already is I have set up two databases one of them is a Postgres database that is meant to act as my development environment so it has named PG demo - dev and I have another Postgres database that is representing our production environment it is named PG demo - prod these databases happen to be hosted in Azure you can't have them anywhere you want to run through a similar scenario you see that in my production database if i refresh here we have some existing tables we've got badges comments link types all sorts of tables in there what I've done is I've taken the schema from an older version of Stack Overflow and I've adapted that to Postgres sequel so I've created this fake production environment there but my development environment if i refresh my tables there it is currently empty I've downloaded and installed flyways pro version and I've configured the fly way installs that it's default commands will run against my development database because most of the time I'm going to want to be testing my commands against development and then when I'm ready to deploy production I can change things up and deploy to production I only have one migration script that I've created so far in the flyweight config file I said hey look for migrations in this folder i've named migrations and i created a file in there named v1 underscore underscore base underscore version dot sequel in here what i've done is i've scripted out that set of tables i have in production the starting point here i have an existing database so here is what the commands are to recreate the production schema i'm not going to run these commands against production but it's helpful at different times to be able to in development create my database from scratch say hey I want to clean things out I want to lay down my baseline my production baseline where I started doing development and then I want to test all of my migration scripts on top of that and that is what this base version represents we are now going to open up a terminal here and I'm going to run my first flyweight command here clear out my terminal first and then I'm gonna run flyweight info I have configured that default flyweight dot-com file to say I want to run against my dev database and it helpfully points out here it prints out the database that it's running against flyweight info takes a look and it sees there is one script in that migration folder that you configured to run from it is the base version it's version 1 and it's pending we haven't run this against the development environment yet the development environment is empty to run commands against the development environment I can deploy this by running flyweight with the migrate command flyweight migrate says hey take these migrations and actually run them against target there and it looks at that database the development database it says this is empty you haven't run commands against this I need to run that base version and it has now successfully applied that migration so now when I expand my development database here and I refresh these tables we can now see that and we now have badges comment we also have a table named flyweight schema history where it is tracking what are the migrations that I've run against this database now there's more that we can do over time I may want to as I said run against an empty database so I may periodically want to run the command flyway clean I'm gonna be very very careful with the clean commands though don't ever wanna run this against production because what its gonna do is clean out my development database and get rid of all of those tables so it is successfully dropped a lot of objects here and what that means is that I can run Flyway migrate again and we're on everything from scratch so if I'm testing out a bunch of migration scripts it'll rerun that baseline and run every migration script after that which is very very useful against a development environment not useful against a production environment generally if we want to look at that table in the development environment we are still connected to our development database up there I can query that fly wave schema history table and I can see now that it is run the v1 base version script well let's run an incremental migration now here I have some migration scripts that have been prepared and I've just had these stored in a different folder right now I'm now going to copy these into my migrations folder I have just added in one file named v2 dot one add new table it creates badly named table and because it's named v2 this is a roll forward script with flyways naming convention I've added a second file which is named u2 this is an undo script for that same version for that same upgrade it will drop table badly named table this is the pro undo feature that lets you for roll forward migrations or up scripts create a down script or an undo script that goes along with it so I've added those now into my migrations folder now when I open up my terminal again let's do it clear here can I say Flyway info it'll look at my develop database check what has been running and it says oh you have a migration in that folder which hasn't been run yet you've run the base version but now you have the add new table migration which I have given the version 2.1 you control that with a file name and that has not been run yet it is undoable to deploy that to my development database I run Flyway migrate and that will now run the up script for that against my development database it is adding badly named tables so if we go back to our development environment here and refresh and expand our tables here hey we've got a badly named table it just has one column in it it's not a very fancy bad leading table what if we want to do that undo script to run that undo script against our development database run a command flyweight undo that finds the undo script that we have for the last migration that we ran and it undoes that flyweight info will show us the status of the database it does keep track that we ran version 2.1 but it's state is that it has been undone because we successfully ran the undo script for that change it lets us know that if we do want to rerun 2.1 that migration script is still there and it is still pending when we do an undo we need to figure out what needs to happen next there if I go back to and just check in my database here here we haven't refreshed this yet if I now refresh my table view here the badly named table is gone it was dropped by my undo script so we have tested our rollback maybe perhaps I want to roll forward again to roll forward again I run Flyway migrate and that will once again run the roll forward script there and add that table back to my development environment at this point if I'm ready to run this against production production is an existing database and let's say this is our first deployment we have not yet run a deployment against it I am now going to run a command and specify a different configuration file in this configuration file I've specified the production database name and connection information and I'm just first going to run flyweight info while specifying that config file name it says we have two migration scripts here the base version and add new table we haven't run either of these they're both pending only one of these has an undo script well it doesn't seem right to run fly away migrate here because we don't want to run that base version but just to show you what happens if we do I have an existing production database and I try to run fly away migrate against it well it says the public schema is not empty there is no schema history table against this this doesn't look right I shouldn't run this baseline schema it suggests that I use the baseline command when starting against an existing production database the baseline command says ok we are starting from this point forward we are the objects in this database they exist now and we are going to build upon them and this lets flyway know that it shouldn't run that base version script against this target environment it says ok we've run our baseline yet and it creates the flyweight schema history table so that now it can track and now that I have baseline I can now run migrate against that production database this lets it know hey we shouldn't lay down that baseline we only need to run the migration scripts after that baseline and so it only runs the script to add new table if I look at flyweight info now it shows me that we have you know baseline to the database at this point in time and after that we have run the migration to add the new table it is undoable if we need to run that and do script against production thanks for joining me for this quick tour of how to use flyweight with an existing database I'll see you again soon

Channel: Flyway

Views: 3,190

Keywords:

Id: CJDXAr1LOsk

Length: 11min 39sec (699 seconds)

Published: Fri May 01 2020