{"id":15135,"date":"2017-12-05T15:01:13","date_gmt":"2017-12-05T14:01:13","guid":{"rendered":"https:\/\/blog.trifork.com\/?p=15135"},"modified":"2017-12-05T15:01:13","modified_gmt":"2017-12-05T14:01:13","slug":"how-to-do-database-migration-with-flyway","status":"publish","type":"post","link":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/","title":{"rendered":"How to manage Database Migrations with Flyway?"},"content":{"rendered":"

Joris Kuipers, CTO at Trifork, presented a webinar on some usage patterns for Flyway<\/a>. You can find the recording on our Trifork YouTube channel<\/a>.<\/p>\n

Tools like Flyway address a common concern for many people, which quickly leads to questions on how to pick a tool and then apply it in the best manner for one’s particular situation.<\/p>\n

In this blog post, Joris has summarized the Q&A session – he provides the readers with his answers and ideas for managing database migrations.<\/p>\n

1. How does Flyway compare to Liquibase?<\/strong><\/p>\n

When I was choosing a DB schema migration tool 4 or 5 years ago, I\u2019ve looked at both Liquibase<\/a> and Flyway. In general, I\u2019d say that Flyway is a bit more light-weight: Liquibase addresses some requirements that Flyway explicitly chooses not to support. These include support to define migrations declaratively (e.g. in XML) and then generate the correct SQL DDL statements for your particular DBMS and the support to generate migration rollbacks<\/em> (countering operations).
\n
\nFlyway did suffice for my needs, so I’ve chosen that and have been very happy with the results. I recommend that you have a look at both to see which one fits your particular requirements best, and definitely choosing either one will be a huge improvement over not having any DB versioning tool!<\/p>\n

2. Do repeatable scripts get included in the transaction spanning in all migrations or it is just versioned scripts?<\/strong><\/p>\n

I didn\u2019t cover repeatable scripts in the presentation, but a repeatable migration<\/a> is a migration whose name starts with an \u2018R\u2019 instead of a \u2018V\u2019. It doesn\u2019t have a version number, but is applied whenever its checksum has changed (hence the \u2018repeatable\u2019). They will be applied after your versioned migrations, but AFAIK that will still be in a single transaction if you\u2019ve chosen to enable that feature<\/a>.<\/p>\n

3. Flyway has a checksum for each upgrade script. Then how to handle the case when a developer finds an error in the previous script in a development environment? Do you have to write a new script to overwrite the old one?<\/strong><\/p>\n

If the script has only been applied to a development environment you don\u2019t necessarily need to write an additional script to fix the error. However, changing a scripts that was applied already will cause a checksum mismatch, obviously, if you don\u2019t regenerate your dev DB schema from scratch. In that case it\u2019s often easy to ensure manually that the fix is applied to the dev DB and then updated the checksum of the applied migration. You can do this yourself, or you can use Flyway\u2019s \u2018Repair<\/a>\u2019 command.<\/p>\n

4. What is the size of largest database you can handle with Flyway?<\/strong><\/p>\n

Flyway isn\u2019t restricted by the DB size, but applying certain migrations to a large DB table might take a long time (also depends on your DBMS). For those cases, ensure that you haven\u2019t configured a connection timeout that would be too short and understand that if you embed Flyway in your application, starting the application might take a long time when long-running migrations are included.<\/p>\n

5. If a change gets applied outside of Flyway for whatever reason, I could create a migration script after the effect that describes the change, but how can I mark this as \u2018applied\u2019 in the Flyway version table? Or is there another way I should be addressing changes that happen outside the process?<\/strong><\/p>\n

Ideally you’d prevent this from happening, of course: all changes should be applied through Flyway, so that you can always reliably recreate your DB schema. That said, if something like this does happen, there\u2019s a number of ways to deal with it. In your migration you could use SQL that won\u2019t fail if the change has been applied already, e.g. by using IF NOT EXISTS. In that scenario the migration will simply succeed. Another approach I\u2019ve used in these situations with MySQL is that I let the migration fail and then simply mark it as successful in Flyway\u2019s SCHEMA_VERSION table myself and then restart. If your database supports transactional DDL statements that won\u2019t work, because Flyway won\u2019t record a failed migration then: it will simply roll back the entire transaction, so there\u2019ll be no entry in the SCHEMA_VERSION table at all. In that case, you could copy the checksum from another environment and insert the row yourself to indicate that Flyway should treat the migration as successfully applied.<\/p>\n

6. How is the performance? Like over time, when you have gathered a lot of upgrade scripts, and you deploy to, as an example, a test environment with an initial DB, all these updated scripts are run. What is your experience? How do you prevent it from taking up too much time?<\/strong><\/p>\n

Creating the schema is usually quite fast, even with a lot of migrations. Of course this fully depends on your DBMS setup and what you\u2019re doing in your migrations. That said, I do find it cumbersome to have hundreds of migrations lying around: for that reason I developed the squashing pattern that I covered in the webinar<\/a>. Squashing will also improve the speed of applying all migrations against an empty schema, as you don\u2019t need to alter things all the time. If you use a tool like mysqldump it typically disables constraint checking in the beginning of the script to re-enable it at the end, which might speed things up even further.<\/p>\n

7. Would you recommend having the first schema version table name with the date from the offset to allow squashing in future?<\/strong><\/p>\n

No, you can choose to apply the squashing pattern with a new schema version table name in any case, you don\u2019t need to start with a custom name from the get-go.<\/p>\n

8. What about running automated integration tests, do you rebuild the DB then as well using the scripts or are there other patterns advised?<\/strong><\/p>\n

It depends – one thing that influences this decision is if you\u2019re using the same DBMS for integration tests as you do for normal environments, or if you\u2019re running against an in-memory DB. For the former case I\u2019d definitely use Flyway, for the latter it depends on if you\u2019ve written Flyway migrations to define the schema for your in-memory DB or if you\u2019re relying on something else like your JPA provider then to set up the schema. In general, having your tests running against the same DBMS helps to catch DBMS-specific issues, and nowadays with tools like TestContainers<\/a> this is easier than it used to be.<\/p>\n

9. How does squashing work when you also have Java classes defining migrations?<\/strong><\/p>\n

Depending on what the Java migrations do, they can either simply be removed (if they do something that\u2019s already captured in the new squashed migrations) or be squashed themselves to a single new Java migration.<\/p>\n

10. What\u2019s the best practice for managing repeatable objects such as VIEWS and stored procedures that are subject to removal? <\/strong><\/p>\n

I haven\u2019t used repeatable migrations myself yet, but I’d say that initially you could simply replace the contents of the repeatable migration with a comment and then later when you apply the squashing pattern you can just remove the migration completely, without Flyway complaining about a missing migration.<\/p>\n\n\n

\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"

Joris Kuipers, CTO at Trifork, presented a webinar on some usage patterns for Flyway. You can find the recording on our Trifork YouTube channel. Tools like Flyway address a common concern for many people, which quickly leads to questions on how to pick a tool and then apply it in the best manner for one’s particular […]<\/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":[441,400,442,443,444,445],"class_list":["post-15135","post","type-post","status-publish","format-standard","hentry","category-axon","category-development","tag-database-migration","tag-flyway","tag-repeatable-migration","tag-sql","tag-squashing","tag-webinar"],"acf":[],"yoast_head":"\nHow to manage Database Migrations with Flyway? - 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\/how-to-do-database-migration-with-flyway\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to manage Database Migrations with Flyway? - Trifork Blog\" \/>\n<meta property=\"og:description\" content=\"Joris Kuipers, CTO at Trifork, presented a webinar on some usage patterns for Flyway. You can find the recording on our Trifork YouTube channel. Tools like Flyway address a common concern for many people, which quickly leads to questions on how to pick a tool and then apply it in the best manner for one’s particular […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/\" \/>\n<meta property=\"og:site_name\" content=\"Trifork Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-12-05T14:01:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\" \/>\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=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/\",\"url\":\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/\",\"name\":\"How to manage Database Migrations with Flyway? - Trifork Blog\",\"isPartOf\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\",\"datePublished\":\"2017-12-05T14:01:13+00:00\",\"author\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/265bd41e503f7176742258a927de598b\"},\"breadcrumb\":{\"@id\":\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#primaryimage\",\"url\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\",\"contentUrl\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/trifork.nl\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to manage Database Migrations with Flyway?\"}]},{\"@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":"How to manage Database Migrations with Flyway? - 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\/how-to-do-database-migration-with-flyway\/","og_locale":"en_US","og_type":"article","og_title":"How to manage Database Migrations with Flyway? - Trifork Blog","og_description":"Joris Kuipers, CTO at Trifork, presented a webinar on some usage patterns for Flyway. You can find the recording on our Trifork YouTube channel. Tools like Flyway address a common concern for many people, which quickly leads to questions on how to pick a tool and then apply it in the best manner for one’s particular […]","og_url":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/","og_site_name":"Trifork Blog","article_published_time":"2017-12-05T14:01:13+00:00","og_image":[{"url":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png","type":"","width":"","height":""}],"author":"Joris Kuipers","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Joris Kuipers","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/","url":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/","name":"How to manage Database Migrations with Flyway? - Trifork Blog","isPartOf":{"@id":"https:\/\/trifork.nl\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#primaryimage"},"image":{"@id":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#primaryimage"},"thumbnailUrl":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png","datePublished":"2017-12-05T14:01:13+00:00","author":{"@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/265bd41e503f7176742258a927de598b"},"breadcrumb":{"@id":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#primaryimage","url":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png","contentUrl":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png"},{"@type":"BreadcrumbList","@id":"https:\/\/trifork.nl\/blog\/how-to-do-database-migration-with-flyway\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/trifork.nl\/blog\/"},{"@type":"ListItem","position":2,"name":"How to manage Database Migrations with Flyway?"}]},{"@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\/15135","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=15135"}],"version-history":[{"count":0,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/15135\/revisions"}],"wp:attachment":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/media?parent=15135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/categories?post=15135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/tags?post=15135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}