 Well, hold on. I'm all messed up because my computer is busy rebooting. So I'm Scott Mead. I'm a senior architect with open SCG I've been using Postgres for about 12 years in different capacities. I'm a DBA. I've contributed code to the core I've I'm a developer. I do a lot of database migrations and So I've spent a lot of time in on around and next to Postgres. I'm by trade. I'm a systems administrator. I was a Nt4 and Solaris administrator way back in the day and When when we got sick and tired of using Oracle we I started to learn Postgres It was back in the seven days and It was for a school district. So we started keeping student records and things in Postgres and moving but we couldn't afford Oracle We were a small school So that's my background with Postgres But what I want to talk to you today about is schema management Not just with this tool that I love which is called flyway But kind of in general and we'll get to flyway and what it is and what it does. I've scared one off already It's alright in me some give give me one second to fire up the presentation here, that's the wrong Structure slides and schema management flyway. Yeah, you get a little bit All right, so I'm not going to get my My cool presenter mode, but we'll do it this way. So again, Scott meets in your architect open scg and big sequel So let's talk about schema management, right? So there's a lot of different strategies for managing your schema and not just Storing it, but how does it evolve? Alright schemas don't change a lot. Hopefully they don't but in the days of Developers in love with Cassandra and Mongo and schema lists. They kind of like to make changes here and there So in a relational database, it can be a lot harder to do. How do we manage those changes? So there's a few strategies for it, right? The simplest thing is like, hey, I'm just going to take my database schema I'll do like a PG dump dash s and I'll store it and get or mercurial or whatever You've also got application managed. These are really popular now developers want to manage the schema in their code So they're using ORM right object relational mappers And then there's also this thing that we're going to get to which is migration strategy So this is actually what flyway does and the migration strategies what I recommend But I'm going to show you why we get there and then why it's important that Migrations is is in use. So what is single file? Single file is pretty easy to implement If you've ever used PG dump, or if you've ever written a single SQL script, you are already doing this You just write your schema down you import it in the database and then you keep that schema file forever And then if you ever need to recreate it, you can just run it. That's great. It's easy One file everything's there the single import creates the entire database. It's really easy to track It's centrally managed. You've got one file to go look at for your schema. It's beautiful, right? What what could possibly be wrong? Well? Most schemas these days are very large So even small simple database schemas are many thousands of lines I just when I installed the open-source Xabix monitoring tool And it's got 6,000 lines in its default schema that it imports, right? So so there's a lot of stuff in there And it's just because you've got your your referential integrity your primary keys your permissions everything else They all have to be in there and and they get very large. So It sounds like a great idea. It's easy to manage But is 6,000 lines really something that you want to be dealing with Now probably not if you want to start making changes So the single file again if it's small it's easy, right? I have a small table I always use the people table. I call it in all my examples We've got an ID a first name and a last name right pretty simple schema How do we deploy it with p-sql or with whatever in the world you want that can execute sql against the database? It's great creates a table now your schema is up to date and you can deploy your app, right? So even the Xabix 6,000 line monster right when I download Xabix I can deploy that thing it might take it a minute But it'll deploy cleanly and I can go I can look in the log for errors It's simple But what about changes this is the biggest problem with the single file strategy, right? So if I have a single file and get let's say we want to make a change to our people table and just add one column I you know what? I'd really like to start storing people's email addresses. It's pretty simple thing There's also as you can see I did really silly things like the email addresses of our car 25 like That's a bad decision. I made a development in the future. I'm going to want to change that Well, what happens if I make a modification to my single file and try to deploy it? Sorry, you can't because the people table already exists, right? So now I have to go write the altar statement to to do it and then deploy that now I've got my single file plus. I've got all these altars these these extra scripts that are running and they're all floating around, right? When that's not a bad thing and that's actually what migrations about which we'll get to in back is keeping those delta scripts But here's the thing when your development organization or your DBA organization is thinking single file And I've done this a lot. You're not tracking those delta scripts as if they matter You're always going to be looking at that single file and your delta scripts are going to get lost or Maybe somebody goes and tweaks the upgrade from version 1 to version 2 So now version 2 is actually not version 2. It's version 2.1 And the master file might not get updated and you're always slightly out of sync and you never really know where you are Right, so so that's that's the big thing and that's kind of what this slide is talking about So so okay, so Single file sounds like it might not be a great idea. Wait a second though There's tools for this, right? There's there's got to be a tool like before we move on to application manage stuff Let's just take a look because I am Convinced that somewhere in the universe There is a tool that will allow me to do this. So there actually are there are multiple tools One is called PG quarrel and one is called a PG diff Both of these tools, which you'll find first of all, I don't recommend them And let me show you why this actually happened there is one of the Vivek and I were actually at a We worked together for a while and where we were we had a table called event log. Can you guys see this? Okay, by the way yeah, okay we had a table called event log and We wanted to add we we said, you know what we're purists. We're gonna use only natural keys We're not gonna use a auto incrementing column and after about six months. We said, yeah, we're purists But we want the auto incrementing column. So we wanted to add it So the developers went in to the master schema file They added the ID column to the front just like it should look so here's a question. I have for you. Is this table Identical to this table so What's different about it? Right the IDs at the end right that's because of the way Postgres does things and you add a column It depends it to the end now in my schema file It would make sense. I added it and I put the ID at the beginning. Well, guess what happens when you run a PG diff or PG quarrel They don't know that this is the same table because they see the columns are out of order So even though we manually applied it, you know what a PG diff says is wait a second in order to do this I am going to execute drop table event log and then create table event log in the right order and The customer dropped a 300 gig reporting table that had all their data That was unrecoverable because they didn't take backups and other things So the diff tools that are out there that will compare that full schema and look at your existing system and try to resolve the differences You have to be careful with them Don't let them just run the altar statements on your database because they're not they're not super sophisticated They're just kind of looking at some basic things and even though these tables actually are the same table It's the same columns the same types everything just because they're in a different order It's gonna mess up some of your automation. Okay, so when you do and I'm telling you, you know Yeah, you don't want to do the single file. That's that's my real reason why right there Okay Okay, so single file, you know a single file sounds like a great idea. It's really simple. It's easy It's kind of simple to manage But maybe not right so Let's take a look at application managed or ORMs So instead what the basic strategy is is that we're going to let the application manage the schema force It sounds kind of nice as a DBA. It's like hey man when you guys deploy a new version of the schema I hear a new version of the app or new version of schema. I don't touch it right no tickets There's no extra work to do the application boots and poof off it goes There's a couple of ways to do this right one of them is developers will do it manually So actually in the application they store the schema as a string or something and then they They deploy it. So this is an example. This is a small Python program And it's actually got its schema stored as a big string inside and in order to deploy it There's this code plus there's Let's see we got to go to the database and then there's this we have to connect we have to deploy So I've got 200 some odd lines of Python in addition to about a hundred and seven lines of Other utility code just to make that deployable and guess what if I tried to run two of these things at the same time They're going to bump into each other and screw each other up So it it doesn't really work all that well The other issue you run into is silly parser stuff because Python The Psycho PG2 doesn't take multiple statements. So in order to do this actually have to parse that string out on something I'm writing my own sequel parser Kind of stings so you can do that manually But it's a pain in the neck. So we say okay. I'm not going to do that I'm gonna use an aura right most of you now. I didn't do this before How many people in here consider themselves developers? And how many consider themselves DBAs and how many don't know how you got here This is a lot of DBAs don't know how they got here. That's an interesting mix So so ORMs are Kind of nice at when you first look at them Especially as a developer if you're not super familiar with databases because you're not writing database code What you're writing is objects in your application, right? So I want to write the people object and I want the system to automatically generate the database schema for me It's basically what this does Got a lot of examples of this hibernate sequel alchemy to Django Django active record gorm. There's one. There's 10 for every language So what do they actually look like? I actually have I had to reboot my computer So let's give me a second to pull it up But what I did is I wrote I like Python. So I wrote a simple little Simple little Python thing if I can find it here Let me just find it in my home director. I had to reboot find What's it called according to my slide? It's called simple or m.py simple And I know it's in here somewhere, but Essentially what I have is a small Python program. That's That defines my data model so I use sequel alchemy so I have simple or m.py if I go to here and alchemy There it is Cooperate to all right. So simple or m.py Yeah, anyways, okay, so I Import some sequel alchemy stuff. I created an engine here. I'm using sequel light so I didn't wire up the postgres But it's the same basic thing And then I just say I have an object or class. That's a person and it's got an ID It's gonna be an integer with a sequence and a first name and a last name column great There's no sequel there right as a developer. It's like BAM. I defined that thing and then I can run it so if I go we'll do a That's an actual file there, but if I just run The actual I run the script you can see right here. I ran it in debug mode It says okay, I'm gonna run this Python script the first time it runs it creates a database It says up that table doesn't exist it generated based on my class the create table statement That was valid for sequel light it ran it created the table and then I actually in another And then this other Python file here Which will recover Okay Instead of having to write an insert statement, but I didn't have to I just said you know add a new person I created a new person called Scott mead and I added that new person and it generated the insert statement for me So I didn't have to write a lick a sequel which is great, right? I'm a developer. That's great So very simply I can say okay. Well, we don't have to worry about schema management because now it's in my app It's beautiful So here's the run What's the problem with this though? It's actually the exact same problem that we have with a single file if I want to make a change to it now I have to either tell my framework to make the change Which is not straightforward sequel alchemy kind of does it but not super well and What they actually tell you is provide a delta script that is written in pure sequel that we will run for you So sequel alchemy and Django and all these guys want you to provide they call them migration scripts So how do I go from A to B? So again? We're back to this whole idea of migration, right? I want to manage it in code But I can't I still have to have a separate source of how to update and I have to have my my authoritative source So I've still got two sources of the truth and that I've never really liked So what does that come to is migration and this is the kind of the industry is moving towards The standard term essentially what we mean by migration is that your schema is going to be spread across a number of upgrade scripts That's really what it is You're gonna have version one and that's gonna be version one and that might be a thousand lines or six thousand lines in the Cases that is that's a big one the first one to deploy But every successive version beyond that is going to just be a few small lines of new code or it's it's You know drop this table alter this table add this do this and Basically, the idea is is you're gonna run them in order, right? So version in in my example that I used with people Let's say we create version one example that SQL we put our people table in there wonderful When we want to get to version two where the desired state is to add the email column All we're gonna do is write a script called v2 example dot SQL and it's just gonna have the alter table statement in it, right? When I want to go to v3, which you know what I don't want the email or separate columns for first name and last name I just want one full name as a big giant text blurb I can actually inside of v3 write a full migration script because I can't just do that if I actually want to save the data Here I can actually say create the table copy the create a temp table copy the data and then rename them, right? So I can actually write extremely complex actions that do the upgrade for me Inside of my migration scripts, and if they're all run in the proper order every single time Wonderful right now now I can build my schema any developer can pull get and they say hey I've got version two. I need to get to version three So I run this script or if I'm at version two I want to get to version 15 I have to run these 13 scripts and and and that's kind of the way it goes now That's the that's the crux of the issue though. It's like okay So we're moving away from this single file Which is one place where everything is and I kind of deal with these upgrade scripts And so instead of that I'm gonna say hey, you know what we're going to as an organization focus on these Delta scripts But now I don't know what my schema looks like right and it's a pain in the neck because if I want to go If I'm a developer, I just want to get to the latest. I have to run 12 scripts I have to run 13 scripts and you know what really stinks is that when you do something simple Let's see if I can get my terminal back here and again. I apologize. I had to reboot So that's my stuff isn't sitting where I normally like it So this is my directory where I have all of my schema scripts. I just do an LS And I order it and already we see the problem right now version 1.1 is Listed before version 1 so I can't even just do an LS and see them in the right order Developers, you know, I'm like I said, I came from a systems admin background and a DBA background So I'm I'm I've become a little bit of a developer, but I'm not a Developer so to all those you that I said we're developers. Let me apologize in advance But as a DBA, you know, I think this is fine most developers say wait a second I have no idea what the hell's going on you guys. This is ridiculous. We're not going to do this So how do we deal with this problem? Right? It's like I saying migrations are the right thing to do They are they simplify your life And if you think about it if you've got a team of five developers or ten developers all working agile and at the same time And you're storing them simply like this It's actually a lot easier to itemize the changes and see what the other guys and gals are up to Just by these Delta scripts, but it's a pain in the neck to run them all. That's where flyway comes in Okay, so flyway is essentially a very very lightweight tool That's going to run those bad boys in order and keep track of them for us So if you lay everything out you version it properly It just does the job. It's a Java based tool. It's patchy v2 license It's nice and easy to use what it's really doing is it adds some metadata It adds a schema version table and so when you run flyway, it's going to execute your migrations in order and It's going to track when they were applied what scheme of what file it came from how they were applied where they successful Did they fail did they roll back and it leaves you with a lot of nice flexibility in tracking? And it's great because it's simple developers can just download it run it on their machines You can run it in production and and away you go so What it does when it runs it actually looks at that schema version table and says hey I met version 2 of the schema. I noticed you have up to version 15 And it will run them for you in the proper order and if one fails it will stop It does what you would expect it to do and you don't have to run PC equal 13 times So it you've got a lot of flexibility if you don't want it to run in order you can do that You can say hey, I want to go from version 2 to version 3 instead of 15. Don't pick all of them Essentially it when start reading flyways website, which is pretty good pretty easy navigate I'll show you a little bit before we go, but It's really got some very six simple commands right migrate clean info validate baseline repair, okay? Migrate is just run a migration go from version whatever I am the version whatever is available And you've got different options in there clean is a great one for developers It's terrifying for DBAs because it it just drops everything in the database that it knows about and you start over Info looks at that schema version table, and it gives you some basic information about it We'll talk about validate in a second because it's not quite what you think yet So there is there is one catch to flyway you need an extra tool and I'll go into that in a bit So again migrate just applies everything gets you to a specific version. So what does a migration look like? So I have in here as you can see I've got version one version one point one two three four So what are these what are these actually doing? So? version one is My people table right and that the schema that I'm using for in these five scripts is slightly different than what I had in the rest of the slides just to to show you some some different things with flyway But I've got my version one now. What is version one point one? It's a table that references the people table. So I said, oh, okay, it's not quite version two I just want to add this little table in there so I made it a one point one instead of a full-blown two and You look at there's a lot of different numbering schemes you can use with flyway So you can read their website it gets a little complex But it just follows the order and the naming is important. They want you to have v a Number a version number and then two underscores and then the name of the sequel file. So how does this actually work? What I have is a database that I'm going to drop Called flyway demo We'll drop the oops drop deep. Oh first. I got to start postgres because I rebooted Okay, drop DB flyway demo Okay So I'm going to create an empty DB flyway Demo in my postgres 95 instance that I have here. I didn't use semicolon there. So My history is gone. So Flyway is in my path and as you can see there's a whole ton of different Different options and parameters here But what I'm going to do is I have a config file I wrote for my flyway in Comf so we'll take cat comp flyway configuration and what I have is Really the JDBC URL to the database that I wanted to manage. So JDBC postgres ql it includes the JDBC driver for postgres So you don't have to go download that separately which really made me happy This is just running on port 9520 on my local machine As the postgres user and I don't have a password that I'm requiring so I can do a flyway cop file, I think you have to bear with me for a second again my editor reboot and I need to provide the locations of the Things that I want the sequel files that I wanted to use got em flyway SQL migrate And I don't remember that Config file is the option I'm looking for Okay, so when I ran it by itself I pointed at the sequel directory. It said hey, okay cool. You've got an empty database It creates a schema version table and then it actually executed all of my schemas in the proper order, right? So did one then one point one then two three four five So if I ask it for some info It tells me okay, I ran all your versions here's I ran them all here's when I ran them and Here's the state. They were all successful if I had a failure. I would see that there was a failure there I could try running it again. Whatever the failure happened to be What is very simple for me to go from absolute zero to running all of those very very quickly Without worrying about it, right? So that's something you can do prod Dev whatever you want now The other the other important function here I'm gonna use clean which if you're in production. I would ever recommend basically just dumped everything in that in that schema But what I can do is say for my great I want a target of Version two So I don't want to go all the way to five I want to run to two and it's gonna run one one dot one and then two for me and then if I say Run it against to it's not gonna update anything and we're already at version two, right? I Want to move to three I can right pretty simple pretty straightforward It's just a nice tool that kind of manages your migrations for you Now there's a problem there's a problem that I'm skimming over here So, okay, great. We were applying all of our migrations Flyway has done it for us and it's showing us. Hey, yeah, you've migrated and we even have this thing called validate That one would expect Validates that the sequel that's in there has is actually at version three and it says, ah, you know I validated that The migrations all look good But it's not actually looking at the tables and validating that the structure is what it's supposed to be what this does This is an important thing, but what this is actually doing. It's looking at your sequel file So if I say Sequel and let me modify version three and I'm gonna add I don't know another So I full name text and then I'll add email text as well, right? So I add I modified one of the Delta scripts or one of my migration scripts And when I run validate it's not actually looking at the database what it's doing is saying I know that I ran script one one one two and three and then it looks at the file system It says wait a second, but the checksum of the script I ran and the checksum of what's on the file system are different There's danger. Somebody modified something you've already applied, right? That's a dangerous state So the good thing is it doesn't try to apply it What this is not doing is looking and saying, okay, remember from From back here From back here. It's not looking at my tables and it's not saying hey are the columns in the same order It's not looking to make sure that if I added the id column in version three It's not looking to make sure the id column exists flyway can't actually do that It doesn't have the ability to so you're kind of in this state now It's like, okay, we've moved we we have a tool to apply them But when flyway says I'm at version three am I actually at version three? so what happens if I go in and I'm a You know, I'm a rogue DBA fly by the seat of my pants tickets matter not to me and change control is nothing So I'm just going to look and say hey, you know what? I Noticed that that this addresses table, you know what I just don't like it anymore So when we do that and we run validate validates just saying hey You know what the file is out of date. It actually doesn't know about the table and if I modify my file If I modify version three to go back to the default we get rid of our email We're going to fool flyway into thinking that this is actually Validate successfully So so even though the database is missing an entire table, right the validate still comes true This is just a weakness in in flyway itself, but you can solve this problem. So See we'll go we did a migration talk about rollback in a second Okay, so we talked flyway validate right doesn't actually do what it needed to now if you remember a while ago I told you all these great tools there was something called PG coral or something called PG diff And what do they do remember from before when we're talking about single file they look at two databases and they apply Alterscripts right and I said not to use them. Don't let them run the altarscripts, and I still want you to do that however These tools are really great because you can run them against a known good copy of the database and run them against and you can Validate hey all the tables exist or I'm missing a table So what do I really recommend is you run you have your migrations, but you also create a gold master So what I do is I have a server that's called gold master It's running the same version of postgres I deploy and on that is every version of the full scheme I run flyway and I deploy it so I have version 1 version 1.1 is the database name and I can compare them at any time so I can run PG quarrels going even just use PG dump and Schema only and just kind of do a diff and make sure that the scheme all the tables all the objects everything's there And if something's wrong you can fix it yourself Unfortunately, it's just something we're stuck with you know We're always working in the postgres community to try and make this a little easier, but it's something that's missing So you actually really need to kind of do that validation yourself That's the that's the the deal with the flyway and migrations or any questions at this point Anybody have questions comments? Yeah Yeah, so so the question is about you know different branches of development right so if I've got Multiple developers and multiple branches and two guys take a claim of version five at the same time All right, what do I do or if they start adding each other's file it what it comes down to it's a little it's more mindset than anything else is The scheme is actually important All right So if you're gonna mess around with the schema Don't mess around with the schema do it, you know modify the schema And and I've run into you get developers at stake their claim No, I want version five no I want version five and what that comes down to is merge time So at the one of the ways to solve this is as DBAs or you know even as a developer That's kind of in control the schema is you have a either a version branch or you've got your master However, you're doing it, but you always have to merge somewhere Somebody who knows and cares about your schema has to be responsible for that right and at some point It's like okay, you know John and Bob I know you both want version five, but your big boys and I've decided that John gets version five and Bob gets version six Or you put them together, right? So you have to do that merge and the other thing to keep in mind is With these scripts is your Then developers don't necessarily know a lot about this But you've also got locking behavior to deal with you know my create table. I did add column big serial, right? That's great The problem is is that so when we do let's see let's pull up a terminal here and If we go into pcql and I create table test first name Text Oops, I'd have a lot of tables called test. I do this all the time So create table test first name text, right? So that's simple Hey, I want to add an ID column with a big serial to it So let me first we'll do insert into Test values and we'll do Chuck who is my dad We'll do Scott If I could spell today and we'll do Sally right Sally was my college sweetheart. So we select Star from test Okay, great. So we've got three rows in table now I want to add a big serial column some auto incrementing features. So I'll say alter table tests add column ID serial Right, wonderful. If I select star from test, what do I see? Everybody's got an ID number. That's great. Well, what happened underneath the covers, right? That was very simple But I locked the table from all concurrent access I added a column and then filled it with a number now It's great for a table with three rows But what if I've got a table that's got 10 million rows or a hundred million rows or a billion rows, right? And I'm 24 by 7 I Can't just do that. So my developers so when you know what I'm getting to with your question I know I'm you know way over here but Your question, you know when you've got developers that want to do something like that They might not necessarily know what they just did, right? They just brought production down for an unknown period of hours While they were adding that auto increment column. So what do I actually have to do as a DVS? Okay? We need to add the serial column. So in since I'm in charge of the schema. I actually need to put a Sequence and some to create a sequence in my migration script create sequence that I'm going to alter table and just add A nullable column that's empty and then I'm I'm going to put a trigger I'm going to bump the sequence to a million and then I'm going to let all new rows get that and then I'm going to write a Separate script that actually comes in later and back fills the other rows, right? So I couldn't just run it in one command because of locking I had to you know build this bridge out of duct tape and string and gum and everything else But that's how you get around that problem. So The reason that I come with your question, which was more about branching and tagging You've got multiple lines of development going on is you still need to merge it to a single point Not just for that reason, but for things like locking as well, right? And and a lot of developers the other thing they do is they just take create index not create index concurrently You know, so you have to be able to control that and get it to a single point so if you're not doing it already pick a database are or to database ours or Put the guys and gals in a room and let them beat each other up like whatever works organizationally You got to get it to that merge point and and I find that only deploying off of a merged or off of a certain branch As a rule usually works better Developers cry and scream because they always want control over it They want to be able to change it and that's it's like fine Do whatever you want and if you give them a tool like flyway Then they can do whatever they want and apply it and then clean it and then change it and apply it and clean it When it comes to merge time, then you're responsible for the final merge, right? So that the real thing about flyway is it's great for DBAs But it's actually a lot better for developers because it gives them the flexibility a lot of like that the flyway You saw me run was running against Postgres, but if you look at flyway it actually supports Flyway DB org It supports quite a few and Let's see where we are Documentation on the side. Here's here's all the tools, but if you look at supported databases Oracle SQL server The list goes on enterprise DB's advanced server as listed as one specifically Right, but it's all It's it's it's all stand they work with everybody anybody that's got a JDBC driver Typically will work with flyway all it's really doing is parsing it now There's one other thing one of the reasons I'm especially in love with flyway is I said before you know when you're when you're looking at writing your Very own parser right you it's a pain because you can't do functions You have to actually rewrite the po the PC equal function parser The cool thing about flyway is for Postgres. It actually supports functions natively dollar-quoted functions out of the box but it just works and That's that's how I use it now Because I came from that sys admin Developer side. I'm using flyway on the command line, right? So I download it. I install flyway. That's great Maybe you're in a shop. That's primarily developers to say, you know I kind of like flyway, but I'm still really married to the idea of doing everything in code And that's the next beautiful thing about flyway is that it's got a great API So you can actually manage this right inside your app. So if you are using Java of this is actually a Java they have Android versions it you can hook it to spring so you can wire it right up So when spring comes up, it'll automatically deploy your migrations for you So it's not you're not just something you're gonna have to run manually You can't actually wire this into the rest of your application and framework So you get that feel of hey when my app boots it auto deploys the right version of the schema auto migrates And it's done you don't have to write the migration logic your schema is a set of migrations And that's it and then flyway deals with the rest whether it's on the command line or inside the app itself and Let's see. I think that is let me just go through there's a couple things Ah, there's one of the one thing that typically comes up is rollback So flyway doesn't have a rollback You typically the way that you roll back is by following forward, right with databases, especially like you know I the couple of things is hey first of all if I've got a clustered app I might upgrade one app server, but I've got six others that are using that same app server So all of my changes have I can't break anything, right? So if I do break something then I have to I'm gonna have to roll it back manual I have to write a separate rollback script flyway won't do that for you Or instead I'm gonna write version six which is actually the rollback and I'm gonna upgrade to version six And then basically do the rollback script. So that's one strategy for doing that I Is a DBA I typically don't do rollback scripts. What I usually do is test So if your testing is in place if you know what sequel statements are You run it through some testing frameworks, but you know the reality is is you need to roll back from time to time So flyway doesn't really do that unless you again fall forward. So hey version five was a complete and utter failure I wrote version six and I upgraded to it and that rolled back the changes It's it's a weird way of doing it, but that's how you would use flyway in the rollback scenario Okay, great. So you've convinced me flyway sounds awesome And I want to use migrations and I never want to use a single file again And we're gonna move forward to the brave new world but My database has been in production for three years and it's a single file and how do I move forward? That's one of the flyway commands called baseline So what I can actually do is take a PG dump of an existing database Run flyway baseline and then from that point forward write migrations on it So it's very easy to kind of enroll existing databases into a migration based strategy with flyway The baseline tool will do that for you. So take a look and There's there's another kind of cool thing about this is you can actually they have a client who? Loves Java objects so much that they keep them in their database So they get the object they serialize it to binary form and stuff it in a large text field Siri thinks I'm talking to her So What's the problem with that is that hey if something's wrong in that blob? I can't deal with it or what if I want to take something out of that blob and convert it to a database field Right flyway actually you can run a Java class as part of your scheme so doesn't just have to be sequel you could write Java code that actually does it so if you wanted to Hey, I'm going to Have when I run version six. It's actually going to connect to the database. It's going to select some data It's going to make a web service call and based on the results from that It's going to change some of the data in my database you can do that You can do extremely come basically anything at that point that Java can do you can do now I personally don't like Java Never have but it's very flexible and it gives you the ability to kind of do that so That's that's what I've got for you done a couple of minutes early, but any questions Yeah, right well, you're you'll you'll still see that so if you're keeping your migration files and get you'd still see And I actually like it a little bit better because I say okay if I'm if I'm looking at these sequel files One of the ways to do it is a get diff and it'll show me the delta of the lines The other way is just say hey what changed in version five and there it is drop table test, right? So it's actually I think at least it's a little bit easier to itemize the changes And then if you're storing them and get you can still you know blame I yeah, I use example, but you know, I would use personally I use a name that everybody's going to use so one of the things Flyway does is it forces the V Number and the two underscores the example is up to you Bob's your uncle whatever pick a name, you know I would do it. I would do it for those scheme level I would say go with the large a very large granularity that way people don't change it and that way everybody's kind of in that same Yeah, and that that comes back to that that kind of database schemas are the individual that really owns that scheme Is responsible for merging it to that that branch and I'll even in in development and QA boxes I'll even force that hey you want this to you on it Well, we'll deploy to dev off of the dev branch But it has to be in the database dev branch and that's the only place we deploy it from you can deploy it in your laptop Or your own VM however you want But when you want us to deploy it to regression testing or to this farm of QA servers or whatever it happens to be That's going to come off this branch Yeah Yep, well, well kind of so so in and it's a little bit of workflows So what I found and I've actually seen developers do multiple things with this so some will in their local environments They'll write 50 files that are the scheme of you know for their migrations and they'll do all their little changes But at some point they've got to come to what they want and and that's where you know You basically say take what you have as a role. It's roll those 50 up and put them in one And then that's what we're going to put in the branch So the it's not necessarily going to be what what developers are iterating on very very quickly When it comes to the deployment branches the dev the test or the prod branch I roll those up into one update that's like hey, this is we're picking this day. We roll them up This is what we've got everybody signed off that this is a good scheme and we're going to go It there isn't auto managing of all those dependencies and everything so you do have to do it You got to be looking at it, but that again in my view that schema is typically The holy grail for most organizations. That's that's a serious thing So having somebody serious looking at that or a few individuals or committee that's caring about what's getting rolled in That's important a lot of times what I'll see is developers those those diff tools I was telling you about like a PG diff for PG quarrel Developers will actually if when you stand up a gold master server, they'll say okay I'm ready to go to the dev branch and test So they'll run their diff tool say give me version two I want to make version three out of my hundred changes Get me the ten itemized diffs and then put them in a migration and then put that in the deployment branch They produce an altar script now, I mean that being said, you know, let's see if I have it here So PG quarrel the very first time I ran PG quarrel. I ran it in debug mode. Yay. Everything's working It's diffing my databases. Oh Segfall So It's not perfect and most of the diff tools are gonna run into this issue So it's it's not perfect that that being said if you're Python developer or Java developer You know, these tools are not super hard to write Java metadata I think I've got a diff script somewhere in here I keep meaning to put in the bucket or get hub that's Python's about 80 lines of code that uses sequel alchemy dot reverse engineer and it gives you the database in a form That's kind of easy, but it the those diff tools are good for developers when you're getting ready to stage changes to To a release Any other questions? Yeah Yeah, you wouldn't do that. So you would you know v v1 v2 There's no v4. There's v3. So it might be v10 in a developer branch But when you merge it down, it's gonna be it's just gonna increment by one So whatever happens to get there first is the winner basically So you won't you don't want to skip Okay, terrifying and scary. Yeah, um, I like to do I do like to keep them separate Especially when like the example I use where we're adding a big serial column to a 10 million row table, right? Keeping those as separate pieces that way I can run them separately Is useful there are times when you know at that that where I run a trigger and then do the backfill And then I stand on my head and you know hope the Sun is in the right position all that kind of stuff That might be a separate script that might be an action script So I've stored those as actions next to schema upgrades Sometimes you have to run them sometimes you don't but usually when you're doing that a large database You want p sequel and you're gonna be watching it while you're monitoring and looking for locks and other stuff So, you know, we're executing that through fly away can be a little dangerous. I did commingle them But you know the idea of taking any of those important action scripts or those extra data massaging scripts that you're doing And running them separately is Is important to do that the other thing you could do with flyway It's nice is you could actually write it as a Java based Migration so you could write a Java class that actually does all that stuff and do as a migration That gives you the ability to open another connection to the database and check on state and do other things So you can get really sophisticated, but usually keeping as a separate script is good That's the last question. Thank you very much everybody. If you have any questions