{"id":15037,"date":"2018-08-17T15:44:44","date_gmt":"2018-08-17T13:44:44","guid":{"rendered":"https:\/\/blog.trifork.com\/?p=15037"},"modified":"2018-08-17T15:44:44","modified_gmt":"2018-08-17T13:44:44","slug":"come-fly-with-me-flyway-usage-patterns-part-i","status":"publish","type":"post","link":"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/","title":{"rendered":"Come Fly With Me: Flyway usage patterns part I"},"content":{"rendered":"<p><a href=\"https:\/\/flywaydb.org\/\" target=\"_blank\" rel=\"noopener\">Flyway<\/a> is a tool for managing your relational database schema as it evolves while developing a project. It\u2019s not restricted to just performing schema migrations: it can execute any type of SQL script, or even JDBC code, that you\u2019d like to run before or at startup as part of deploying a new version of your application.<br \/>\nAn <a href=\"https:\/\/blog.trifork.com\/2014\/12\/09\/integrating-flywaydb-in-a-spring-framework-application\/\" target=\"_blank\" rel=\"noopener\">older blog<\/a> provides a nice overview of the framework, and the project provides comprehensive documentation.<br \/>\nI\u2019ve been using Flyway as a database schema management tool in a handful of projects over the last few years, to great success. However, what I\u2019ve noticed is that it isn\u2019t always clear to newcomers how to best apply it to a project. Also, it depends on a number of factors on how you can in fact best apply Flyway. In this new blog post series I\u2019ll describe some common usage patterns that I\u2019ve encountered or developed myself.<\/p>\n<p>To kick off, let\u2019s talk about some patterns for using version numbers for your migration scripts.<\/p>\n<h2>Migration version numbering<\/h2>\n<p>The basic idea behind version numbers for Flyway migrations is pretty simple: you provide versions in the form of one or more numeric parts separated by dots or underscores, and then Flyway executes the migrations in order from lowest to highest. Leading zeros are stripped, which can help to make alphabetical sorting match numeric sorting.<br \/>\nBy default you make this part of the filename of a SQL migration, or of a class name in the case of a Java migration. You could have a list of migrations like this, which have been ordered in the same way that Flyway would:<\/p>\n<ul>\n<li>\n<pre>V001__Initial_schema.sql<\/pre>\n<\/li>\n<li>\n<pre>V002__Initial_reference_data.sql<\/pre>\n<\/li>\n<li>\n<pre>V003__Create_relation_tables.sql<\/pre>\n<\/li>\n<li>\n<pre>V004__Create_order_tables.sql<\/pre>\n<\/li>\n<\/ul>\n<p>In this example, we\u2019ve used a simple sequential versioning scheme where most versions are just a single number, padded out with zeroes so that (at least the first thousand) migrations will be ordered correctly when sorted alphabetically.<\/p>\n<p>This is an easy way to get started, but the approach has some limitations.<\/p>\n<h3>Dealing with maintenance releases<\/h3>\n<p>A common approach to development, especially when developing products with predictable release cycles, is to have regular feature releases combined with more frequent maintenance\/bugfix releases. That means that you could have a product with version 2.1 running in production with a 2.2 release candidate being tested in a user acceptance environment, while development teams are busy working on the upcoming 3.0 release. This usually means that you have two active branches in your version control system (if you\u2019re working on a product that\u2019s hosted on-premise it might be even more, depending on how many versions you need to support in parallel).<\/p>\n<p>In this situation, maintenance work performed on a version that has already been released is typically merged back to the main development branch to ensure it\u2019s contained in the new major release as well.<\/p>\n<p>Let\u2019s say that the the version of our product running in production (the maintenance version) is currently using version 3 of the DB schema (meaning it has some migration with version 3 applied as the last migration). Now as part of implementing some maintenance fix we want to add a Flyway migration. On the maintenance branch <code>V003__some_migration.sql<\/code> is the latest migration, so we must add a file using a higher version number. However, the master branch might very well already contain a <code>V004__some_other_migration.sql<\/code>. Adding something like a <code>V004__fix_column_lengths.sql<\/code> to the maintenance branch won\u2019t work: you could release that to production just fine initially, but when merging that migration to the development branch you now end up with two V004 migrations. This is obviously a problem: Flyway will throw an error as it requires unique migrations for every version. Also, we need to ensure that the new maintenance migration will be logically ordered before any new migrations contained only on the master branch, since that\u2019s the order that migrations will need to be applied with as you\u2019re first releasing one or more maintenance releases followed later by a new major release.<\/p>\n<p>The thing is that even though you might have multiple branches, your DB schema can only evolve linearly.<\/p>\n<p>An easy fix for this is to say that after creating a maintenance branch, no Flyway migrations should be created on that branch with new major version number. Instead, use a new minor version number. In our example, you could simply add a <code>V003_01__fix_column_lengths.sql<\/code> instead and merge that to the development branch as well.<br \/>\nThe effect of this is that your maintenance branch will contain migrations numbered 1, 2, 3 and 3.1, while your development branch will additionally contain 4 and maybe higher as well.<\/p>\n<p>You can release as many maintenance versions as required, and when the time comes to release a new major version of your product it will simply run the migrations that were only present in the master branch because they\u2019re ordered after the maintenance migrations.<br \/>\nThis will also make it obvious just by looking at the version number if a migration was developed as part of regular develop work on the main branch, or as part of a maintenance release on the maintenance branch.<\/p>\n<p>This approach works well if your development process follows this pattern where you\u2019re deploying maintenance releases into production while in the meantime working on a new upcoming major release. You\u2019ll still have strictly incrementing version numbers on any given branch, so Flyway can simply enforce a strict ordering and run any new migrations that it finds.<\/p>\n<h3>Developing with feature branches<\/h3>\n<p>A popular approach to development nowadays is to use feature branches, especially since distributed source control systems like Git make them so easy and fast to use. In this approach, features are developed on a separate branch and merged back to the main branch only when they\u2019re sort-of done.<br \/>\nThere is a lot of debate at the moment about the pros and cons of this approach versus mainline development with feature toggles, but that\u2019s for another blog: it\u2019s simply a given that many teams develop using feature branches.<\/p>\n<p>In that approach, using simple incrementing version numbers for migrations like 1,2, 3, etc. will quickly become a problem: let\u2019s say that version 3 is the latest migration on master, and two new feature branches are created. Both teams working on those features decide that they need a database migration, so both teams might decide that they\u2019ll create a version 4 migration. Now the first team to merge back their feature branch wins, but the other team will have to change the version number(s) of their migration(s) after merging and (hopefully) realizing that they created a conflicting migration.<br \/>\nThis might be OK when not using feature branches a lot and only having a small team of people, but this approach causes lots of problems when scaling up.<\/p>\n<p>The approach that I\u2019m following for projects heavily using feature branches is to forego Flyway\u2019s default guarantee that migrations will always be applied strictly in incrementing order and to adopt version numbers that are basically timestamps.<\/p>\n<p>What this means is that a migration file created on July 8 at 11:10 AM will look like this:<br \/>\n<code>V2017_07_08_11_10__fix_column_lengths.sql<\/code>.<\/p>\n<p>This will prevent version number conflicts from happening (I\u2019m assuming including hours and minutes suffices for this) but creates another problem: versions will not be guaranteed to be merged back to master in order.<br \/>\nThat means that Flyway could run against a database to apply a migration 2017.7.7.12.10 like above, but then in a later release finds that there\u2019s now a new migration 2017.7.6.12.12 that hasn\u2019t been applied yet but has a version that\u2019s lower than the latest migration that has been applied already.<\/p>\n<p>By default Flyway will consider this an error and your migration will fail. However, you can simply tell Flyway that this is all expected and that it should simply execute all migrations that haven\u2019t been applied yet by setting its <code>outOfOrder<\/code> property to true.<\/p>\n<p>The only requirement for this to work is that migrations created on separate branches can be run independently from each other, i.e. that there\u2019s no dependency in the order in which they are applied. In my experience, this is something that\u2019s typically the case already and really doesn\u2019t need any additional work or processes to be put in place.<\/p>\n<p>That means that this approach works really well when using feature branches.<\/p>\n<h3>Naming conventions for migrations<\/h3>\n<p>Apart from choosing a version strategy, you\u2019ll also need to decide on the description part of your migration file names. Flyway will use those parts to create a textual description of the migration (replacing underscores with spaces) and stores in a column of its schema_version table.<br \/>\nIn general these description should be, well, descriptive in indicating what they do. A migration called <code>V034__Oops_fixing_a_mistake.sql<\/code> won\u2019t help in understanding what it contains in terms of SQL. What I often find to work well is to adopt a convention that the description should start with the issue number that the migration is related to. Let\u2019s say you\u2019re using Jira and your project code there is PROJ, a migration might be called something like <code>V2017_07_07_12_30__PROJ-1234_add_indices_for_relations.sql<\/code>. Sure, your version control will allow you to see this as well (assuming you\u2019re including issue numbers in your commit messages), but if someone is reading the migrations as stored by Flyway in its schema_version table they probably don\u2019t a cloned repository available to quickly check the context of the issue requiring a particular migration.<\/p>\n<p>A nice thing about conventions like this is that people will start following them automatically once you have a couple of migrations: it\u2019s just a matter of copying what you\u2019re seeing, rather than having to document and then enforce some process.<\/p>\n<h3>Learn To Fly: What&#8217;s coming up<\/h3>\n<p>This concludes the first part of this Flyway blog series. Some topics that I\u2019m planning to cover in upcoming entries are:<\/p>\n<ul>\n<li>introducing Flyway when you have an existing database already;<\/li>\n<li>how to use Flyway while you\u2019re in the process of splitting up your monolithic application into multiple services that might not yet have their own database;<\/li>\n<li>how to use dynamic migration locations to deal with different databases or different types of migrations like schema changes vs. data fixes;<\/li>\n<li>how to periodically collapse your ever-growing list of database migration files so that you don\u2019t actually hit that V999\u2026<\/li>\n<\/ul>\n<p>If you have suggestions for Flyway usage patterns that you like to see covered, then please let us know!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Flyway is a tool for managing your relational database schema as it evolves while developing a project. It\u2019s not restricted to just performing schema migrations: it can execute any type of SQL script, or even JDBC code, that you\u2019d like to run before or at startup as part of deploying a new version of your [&hellip;]<\/p>\n","protected":false},"author":62,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":""},"categories":[113,10],"tags":[400],"class_list":["post-15037","post","type-post","status-publish","format-standard","hentry","category-axon","category-development","tag-flyway"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Come Fly With Me: Flyway usage patterns part I - Trifork Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Come Fly With Me: Flyway usage patterns part I - Trifork Blog\" \/>\n<meta property=\"og:description\" content=\"Flyway is a tool for managing your relational database schema as it evolves while developing a project. It\u2019s not restricted to just performing schema migrations: it can execute any type of SQL script, or even JDBC code, that you\u2019d like to run before or at startup as part of deploying a new version of your [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/\" \/>\n<meta property=\"og:site_name\" content=\"Trifork Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-17T13:44:44+00:00\" \/>\n<meta name=\"author\" content=\"Joris Kuipers\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joris Kuipers\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/\",\"url\":\"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/\",\"name\":\"Come Fly With Me: Flyway usage patterns part I - Trifork Blog\",\"isPartOf\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#website\"},\"datePublished\":\"2018-08-17T13:44:44+00:00\",\"author\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/265bd41e503f7176742258a927de598b\"},\"breadcrumb\":{\"@id\":\"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/trifork.nl\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Come Fly With Me: Flyway usage patterns part I\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/trifork.nl\/blog\/#website\",\"url\":\"https:\/\/trifork.nl\/blog\/\",\"name\":\"Trifork Blog\",\"description\":\"Keep updated on the technical solutions Trifork is working on!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/trifork.nl\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/265bd41e503f7176742258a927de598b\",\"name\":\"Joris Kuipers\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/9ab8da0d60582bad84342d4602d23dbd?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/9ab8da0d60582bad84342d4602d23dbd?s=96&d=mm&r=g\",\"caption\":\"Joris Kuipers\"},\"sameAs\":[\"http:\/\/www.trifork.nl\"],\"url\":\"https:\/\/trifork.nl\/blog\/author\/jorisk\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Come Fly With Me: Flyway usage patterns part I - Trifork Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/","og_locale":"en_US","og_type":"article","og_title":"Come Fly With Me: Flyway usage patterns part I - Trifork Blog","og_description":"Flyway is a tool for managing your relational database schema as it evolves while developing a project. It\u2019s not restricted to just performing schema migrations: it can execute any type of SQL script, or even JDBC code, that you\u2019d like to run before or at startup as part of deploying a new version of your [&hellip;]","og_url":"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/","og_site_name":"Trifork Blog","article_published_time":"2018-08-17T13:44:44+00:00","author":"Joris Kuipers","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Joris Kuipers","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/","url":"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/","name":"Come Fly With Me: Flyway usage patterns part I - Trifork Blog","isPartOf":{"@id":"https:\/\/trifork.nl\/blog\/#website"},"datePublished":"2018-08-17T13:44:44+00:00","author":{"@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/265bd41e503f7176742258a927de598b"},"breadcrumb":{"@id":"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/trifork.nl\/blog\/come-fly-with-me-flyway-usage-patterns-part-i\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/trifork.nl\/blog\/"},{"@type":"ListItem","position":2,"name":"Come Fly With Me: Flyway usage patterns part I"}]},{"@type":"WebSite","@id":"https:\/\/trifork.nl\/blog\/#website","url":"https:\/\/trifork.nl\/blog\/","name":"Trifork Blog","description":"Keep updated on the technical solutions Trifork is working on!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/trifork.nl\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/265bd41e503f7176742258a927de598b","name":"Joris Kuipers","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/9ab8da0d60582bad84342d4602d23dbd?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/9ab8da0d60582bad84342d4602d23dbd?s=96&d=mm&r=g","caption":"Joris Kuipers"},"sameAs":["http:\/\/www.trifork.nl"],"url":"https:\/\/trifork.nl\/blog\/author\/jorisk\/"}]}},"_links":{"self":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/15037","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/users\/62"}],"replies":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/comments?post=15037"}],"version-history":[{"count":0,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/15037\/revisions"}],"wp:attachment":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/media?parent=15037"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/categories?post=15037"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/tags?post=15037"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}