• Flyway
• Team-based Development
• Database Versioning

Infrequently Asked Questions About Flyway

Recently, over lunch, I was idly scrolling through some Flyway Interview questions. “How can we integrate Spring Boot bu underscores Flyway?” Eh? well if one could understand the question, it would probably be easier to answer than “How is CRC32 calculated in Flyway?” (Answer: g(x) = x32 + x26 + x23 + x22 + x16 + x12 + x11 + x10 + x8 + x7 + x5 + x4 + x2 + x + 1).

Overall, I was surprised by how few of the questions even scratched the surface of what’s really required for commercial, team-based database development with Flyway. Inspired by this observation, I decided to collect a whole lot of questions and answers, from several years of experience, that I think will be a lot more relevant to the task of developing databases. I’ve organized the questions loosely into sections, so you can dig into the ones most relevant to the task at hand. Each answer includes one or more links to other articles I’ve written that describe the issue, and my proposed solution to it, in detail.

• Getting started with Flyway
• Builds versus migrations
• Multi-database management
• Database versioning
• Deployments
• Automation and workflow
• Database Testing/Quality
• Team-based development and CI

A few of the questions I’ve compiled here have also been tackled previously in Flyway How-tos: a User’s Perspective.

Getting started with Flyway

‘What does Flyway actually do?’ Asking for a friend.

Flyway is an open-source Java application that can be used to build a database to the version you want, using a chain of scripts, each of which changes the database to the next version. See Managing database changes using Flyway: an Overview

‘Is there an easy way to check out Flyway’s advantages?’

If you are developing for more than one database, and you don’t mind using PowerShell, try out Getting Started with Flyway CLI and any RDBMS

‘What are the benefits of adopting Flyway. It seems like effort.’

Well, probably the best way of looking at it is to view it as part of a development team’s move towards an approach that makes it realistic to deliver new functionality regularly. See Moving from ‘Chaotic’ to ‘Managed’ Database Development using Flyway.

‘Is Flyway wonderful?’

It is wonderful only in the sense that it does what a lot of developers need. It was developed as an open-source product with the active assistance of a crowd of users who were actually needing to develop databases and applications in synch and do it quickly. This knocked any rough edges off the product very quickly. Like any database tool, it has a few features that may trip the unwary as I explain here in Flyway Gotchas.

‘What is the easiest way to get started with Flyway scripting?’

I like to learn new development tools by using them with practice scripts, data and applications. My Flyway Teamwork PowerShell framework is designed to help get you started quickly with scripting Flyway migrations for a range of database systems. It introduces a PowerShell task library to help with the scripting of repetitive chores and to generate some of the ‘build artifacts’ that are often required during team development work. See What is the Flyway Teamwork Framework?

It works so far with Oracle, SQL Server, PostgreSQL, MySQL, MariaDB, and SQLite. See Flyway with SQLite for Those of a Nervous Disposition, Flyway with MariaDB for Those of a Nervous Disposition, Getting Started with Flyway Migrations on PostgreSQL, Getting Started with Flyway and SQL Server and Getting Started with Flyway Migrations on Oracle.

Builds versus Migrations

‘Do you have to drop the idea of doing a build if you adopt Flyway?’

Flyway aims to ease you into the convenience of a migration-based approach. You can still do builds with Flyway if you need to. See Building a Database with Flyway. You can even build from an object-level source used by Flyway Teamwork or Flyway Desktop (See The Uses of Dependency Information in Database Development and Building a Database Directly from Object-level Source Scripts).

‘I have a fundamental problem with a migrations approach. With a build script, you can make comments and documentation in the script, and they are visible and preserved. With a migration-based approach you can’t.’

Hang on: even with a build script, documentation nowadays soon gets lost because most IDEs have tools that allow you to use ER Diagramming or visual table editing tools. It is actually a generic problem. The RDBMSs should have allowed developers to add comments attached to the live objects so that, say, a mouseover in your favorite IDE will show an explanation of what the object does and explain it. Most RDBMSs have a way of storing table and column comments, but none of them insert these comments into generated build scripts. I address the problem of getting them in place in the tables and columns of the database here in Taming Database Documentation with Flyway and MySQL, and there is even a way of commenting build scripts from a JSON file full of comments (see Transferring Database Documentation into SQL DDL Scripts). I’ve added this to my Flyway Teamwork framework, for all the RDBMSs that it supports.

I’m sold on managing schema changes with migrations, but how do I manage the data we need for development and testing work?‘

Hmm. With Flyway, you can load static/enumeration data via INSERT statements because there isn’t, or rather shouldn’t be, much of it. See Managing Static Data in Flyway Database Development. For small quantities of test data, it’s also fine to use INSERT statements. Adding Test Data to Databases During Flyway Migrations describes an approach that generates test data on the fly, on SQL Server, using SQL Data Generator then uses SQL Data Compare to generate the INSERT script.

For larger volumes of development data, there are better, quicker ways. Firstly, it’s much better to leave the data out of the database metadata code, see Dealing with Database Data and Metadata in Flyway Developments. Secondly, you need to adopt an automated approach to building a database and then loading the data. Several RDBMSs provide special ways of inserting data in bulk. For example, here is an automated approach with SQL Server and BCP Getting Data In and Out of SQL Server Flyway Builds. With Flyway Teams you can use a callback script to do a more sophisticated version of this, described in Bulk Loading Data via a PowerShell Script in Flyway.

For a more generic way of exporting, deleting and loading data, using JSON, see Managing Datasets for Database Development Work using Flyway.

‘We just take a copy from a backup of the live database, make our changes, and then update the database with a Synchronization script. Why change to Flyway?’

This method is only usable with databases without any personal or financial information. It makes sense with a reference ‘Wiki’ site that needs sophisticated searches, but even here you actually don’t need to re-copy all the data unless a large proportion of the data changes. You just re-synch. It is just as easy to do this using Flyway. For databases with personal, financial or confidential data, you need to read Data Masking in Principle to find an answer.

Multi-database management

‘I looked at Flyway, but I just couldn’t see any way that it could help with my turnkey database that has to be configured differently for every customer’

Take a look at Flyway Teams and the Problem of Database Variants.

‘I looked at Flyway but thought that there was no way of coping with multi-database systems where there were dependencies between databases.

Flyway copes with them easily. See Finding External References in Database Deployments for finding the critters, and also Dealing with Mutually Dependent Databases in Flyway that explains an awkward build problem in a multi-database system. Also look at Dealing with Cross-Server Database Dependencies in Flyway Projects which deals with the knotty problem of cross-server dependencies.

Are you seriously suggesting that I run Flyway twenty times to update my twenty copies of our database in the test cell that run tests in parallel?

It is one of those tasks where Flyway really shines. Containers are crude over-engineering by comparison. I explain it all here: Automating Migrations for Multiple Databases using Flyway. If you are using Flyway Teams, there is the Baseline migration feature that ensures that you’d need to execute only one file on each database. See Getting In A State Using Flyway Baseline Migrations.

‘Can Flyway really deal effectively with any variety of relational database, and is it possible to write code that is easy to port between them?

There is built-in support for a range of relational databases, from SQLite to Oracle. If you have a good JDBC driver for your favorite database, you can install it into Flyway. There are some general principles to writing databases that are reasonably portable and a good starting point is a test harness that allows you to test code on a variety of databases. See How to Automate Cross-Platform Database Development. I describe a way of ironing out some of the syntax differences between databases here in One Flyway Migration Script for Diverse Database Systems.

Database versioning

‘We have an existing database that needs to be managed by Flyway, so we can move to a CI approach. How do we get started?’

Ah, that requires you to ‘baseline’ an existing database. It is all explained here in Flyway Baselines and Consolidations. I’d also take a look at Implementing a Database Versioning System, which explains how a version system works with a database.

‘My application needs to check the database version to ensure that it uses the correct interface. Can I use Flyway for this, somehow?’

Unfortunately, there is no simple way to do this. Even if you execute Flyway info, it checks all the migration files. If you just want to know what version the database is at, See Finding the Version of a Flyway-managed Database Using SQL to find out how.

‘Does Flyway and its versioning prevent database drift?’

It prevents accidental drift from what should and shouldn’t be in a particular database version because its checksum system means it will detect retrospective changes in a chain of migration files. Unfortunately, there are lots of other ways that drift can happen. I explain the details here Flyway Database Drift and How it Happens. Any system can be ‘gamed’, and there is always a dev who adds an index and forgets to create a migration from the patch. To prevent all forms of database drift, you need an external process to check the target database against a ‘model’ of what should be in the database at that version. I show how to do this for any RDBMS supported by Flyway Teamwork in Cross-RDBMS Version Checks in Flyway

‘I dislike the idea of having a special schema-history table for holding the version and the means by which the database got to that version. It gets confused with the database I’m developing in that it appears in reports and is difficult to exclude from other schema objects’

It can be awkward, but Flyway is designed to be used with any RDBMS and there is no standard way they shared of storing a version of a database. The table or view are the only usable common database structures and so it was an easy decision. You can change the schema and name of the flyway_schema_history table to something more convenient for your project, but it means configuring Flyway appropriately. I explain how in Exploring the Flyway Schema History Table.

Deployments

‘Our staging environment will only accept scripts and cannot use Flyway. Are we stuck?

Nope. See Dry Runs for Database Migrations using Flyway Teams for a full explanation of how to do this.

‘We are awash with migration files, some of which go back to the times of our ancestors. We’re never likely to be interested in them and it’s slowing down deployments, but Flyway springs an error on us if we even lose just one. Help!’

There are several ways of dealing with this, using Flyway but the simplest approach is outlined here in Flyway Baselines and Consolidations in the section of consolidating.

‘I’m using a database system (MySQL) that can’t roll back a migration within a transaction after the script errors-out. It means I have to restore from a backup when this happens. It takes the joy out of using Flyway.’

You have my sympathy. I’m far more familiar with the chores of backing up, deleting and restoring databases than I want to be, so I wrote Rollbacks, Undos and Undonts and Dealing with Failed SQL Migrations in MariaDB or MySQL. I like to use idempotent code that cleans up any prior attempts at a migration at the start. I describe this here: Creating Idempotent DDL Scripts for Database Migrations.

‘I looked at Flyway, but our development, test and staging environments all require different code and settings and so our database has to be configured differently for every database environment’

You might find Complex Production Database Deployments and Flyway useful for those tricky problems where you have complex deployments with additional aspects such as payment gateways, interfaces with other production databases, audit, production access systems. Also, take a look at Flyway Teams and the Problem of Database Variants.

I’m using Redgate’s SQL Change Automation PowerShell Cmdlets. I don’t want to lose all that work. What do you suggest?’

Well, SCA is SQL Server only, and doesn’t do either a build or a migration, but instead creates a synchronization script to make the target database identical to the source. SCA is better thought-of as a scripted way of using SQL Compare, but with the added value of the NuGet package. Flyway adds the migration approach with versioning, that removes the need for a synchronization script for any Flyway database. However, SCA still has its uses when used with a SQL Server migration, as I demonstrate here Using Flyway Teams with SQL Change Automation PowerShell Cmdlets

‘I looked at Flyway, but I could find no way of supporting a separately maintained schema to provide support general monitoring and reporting services for the database’

A lot of databases have these utility schemas. Flyway works only with the schemas that you specify in the project, so it is easy to have two independent projects in the same database. I’ve described a way of doing utility schemas here, in Maintaining a Utilities Schema in a Flyway Project

Automation and workflow

‘Do you need to key-in lots of stuff at the command-line just as our forefathers once did?’

No. You can do so if you want, but it is a bit like wearing braces and ‘Peaky-Blinder’ hats. The idea is to keep your databases up to date via automated batch processes or via scripted processes using PowerShell or Python. I show you how to do this in PowerShell with various flavors of Relational databases such as SQL Server, Oracle, PostgreSQL and MySQL. If you like using the command line, I describe ways of saving you a lot of typing in Flyway Without the Typing. Many processes can be done via DOS batches or Bash scripts.

‘Flyway isn’t for me. I use Windows, I don’t have Java, and our company frowns on PowerShell scripting. If only one could just use DOS scripting. Nothing to install.

My sympathies. I’m a reformed DOS freak. I’m the type who collects old bicycles and likes staring at engines on station platforms. I’ve explained how to get started here with a script that executes any number of database migration tasks across a range of servers and databases, using DOS batch scripts and Flyway. Batch Processing using Flyway

‘Why would anyone want to use Callbacks? No other build system for databases has them.’

Callbacks are very useful for any sort of task that has to be done before or after a migration run, or even in response to an error. I explain them here and illustrate the points with an example A Flyway Teams Callback Script for Auditing SQL Migrations.

‘Placeholders are intriguing but it would seem to be rather difficult to debug code that uses placeholders.’

Well, if you have Flyway Teams, you have the ‘Dry Run’ feature that allows you to inspect the code that would have been run, and to execute parts of it that are causing trouble. See also Debugging Flyway Callbacks and Migrations that Use Placeholders for a way of debugging various types of scripts in SSMS, using SQL Server.

‘Wow. I run a Flyway migration and gets screeds of apparently useless data scrolling up my command window, so much that I lose the thread of what I’m doing. How can I be rid of it all?’

You can opt for JSON output that is sent to STDOUT. This can be diverted to a file. In addition, you can write the warnings and information out to a file. It is all explained here. Using Flyway JSON Output in PowerShell for Reporting. The problem, of course, is dealing with serious warnings such as when Oracle merely warns you that some procedures couldn’t be compiled. I talk about getting around this here Piping, Filtering and Using Flyway Output in PowerShell

‘Flyway configuration is pretty intimidating. It isn’t always easy to work out what is finally used by Flyway because of the many different ways of providing it’

Every means of configuring Flyway is there for a purpose. It is probably easier to understand why each method is there and what its primary purpose may be. This may allow you to keep it all simple and use just one approach, or it may convince you to use several of them at once for the sheer convenience of doing so. See A Programmer’s Guide to Flyway Configuration.

Database Testing and Quality

‘How can we be sure, Using Flyway, that the code that gets into a migration is of sufficient quality to meet the standards we have in our organization?

Good point. For a general introduction see Managing Database Code Quality in a Flyway Development. There are aspects to code quality one of the most important is making it to document it properly. I use a system like this, described in Taming Database Documentation with Flyway and MySQL. Another aspect is inspecting the code for obvious mistakes, omissions, or deviations from coding standards. This article, Running SQL Code Analysis during Flyway Migrations, describes how to get started with code quality checks, using a code analysis CLI application. Flyway Teams has built-in, albeit rudimentary, multi-RDBMS code analysis engine, which I describe here Reviewing SQL Migration Files Before a Flyway Migration.

It’s best to run your code quality checks before the migration is executed in the development branch because you can’t subsequently alter a migration easily without ‘fiddling the system’ because Flyway will detect a change and refuse to do the command. You can’t even reformat the code without Flyway detecting it but fortunately, you can, of course, ensure that your SQL Server code is formatted nicely before a migration using a callback: see Bulk Formatting of the SQL Server SQL Files.

‘I need a way of ensuring that basic mistakes in SQL DDL aren’t being made. You know the thing: tables without indexes, or tables that are unrelated to any other table’

A lot of checks can’t be made until after a migration run or build, when it then becomes possible to query the live database. Each RDBMS has its own way of checking for these things. As an example of building these checks into the system after each migration run, I’ve created an example script here, for SQL Server, in this article Running Dynamic Code Quality Checks on Flyway Databases: Table Smells, I provide some example code and a list of obvious things to look for.

‘I want to be able to test migration scripts to see if they will work but I can’t, because once I have used a migration file in a Flyway migration, I then can’t modify it.’

This seems like a paradox, but it isn’t, just as long as you are using a relational database system that allows you to roll back transactions that include DDL code. I explain the technique here in Try Before You Commit in Flyway and Testing Flyway Migrations Using Transactions. If you are using a system that doesn’t allow this, you’ll have to open the wallet and get the Teams Edition which allows you to use undo files. See Secrets of Flyway Teams Undo. With undo files you can re-engineer migration files as much as you like to find the best approach to implementing a feature.

‘I’m used to being able to test out the SQL code I’ve written before committing it. I don’t see how I can do this with Flyway’s approach.

For an introduction into database testing see Database Testing in a Flyway Development. You can also run your Unit and Integration Tests automatically (see Running Unit and Integration Tests during Flyway Migrations) or do performance tests (Performance Testing Databases with Flyway and PowerShell). I also show how to provision a bank of test databases in Testing a Flyway Database Migration. For further information, check out these articles in the collection about database testing with Flyway.

Team-based development and CI

‘When I tried Flyway, I thought that it ‘sort of’ did everything required for a team-based development. Does it?’

A system that allows you to use a large number of different relational databases can’t do it all, built in. Many have tried in the past, and failed, because there is such a wide variety of perfectly valid approaches to team-based database development. However, it allows you to create a script that fits in with your particular team processes. See Scripting with Flyway Teams and PowerShell for an overview. For an example of automating team processes, using SQL Server and PowerShell, I’ve described an approach here. Creating Database Build Artifacts when Running Flyway Migrations. As an example of what is possible with Flyway and automation, see Automating Flyway Development Chores using Database Diagrams

‘With a migration approach it’s harder for the team to keep an eye on what’s in a table, or group of tables. With a database build, it is just a simple search for one create statement to work out what is in a table. How do we prevent mistakes creeping into the design?’

Firstly, it is a trivial task to generate a database build script whatever way you built the database, so nothing is being taken away. Besides, there are plenty of ways of Searching a Flyway Database.

Secondly, my Flyway teamwork framework will automatically generate a file-based (JSON) model of each version of the database, for any one of the major RDBMSs. You can do a lot of useful reporting using these models, to investigate the structure and design of the database. I give an example of what is possible here in Automating Flyway Development Chores using Database Diagrams and in Simple Reporting with Flyway and Database Models

‘How do we keep track of which objects were changed and how, and in which migration?’

Flyway provides a built-in feature in the Enterprise edition that reports on changes. I For other editions, I show how one can use a scripted approach to get a high-level “narrative of changes. See Discovering What’s Changed by Flyway Migrations . Here’s an example using Oracle: Recording What’s Changed when Running Oracle Migrations with Flyway. You can write a markdown report of the changes as I demonstrate in Reporting on Changes Made by Flyway Migration Scripts. You can even create an ER diagram to show the changes Increasing the Visibility of Database Changes in Flyway Development

If you write out an object-level directory or build script automatically at the end of every migration run, then you can use your source control system to give you forensic details (see Flyway and Simple Source Control).

‘When working in a team, I need a visual approach to progress in migrating a database. Does Flyway do this?’

Flyway is designed as a CLI tool that works with the user tools that your team prefers. As a demonstration of how you’d do this, see Reporting on the Progress of a Flyway Database Development Project

‘The UNDO file is a mystery. What does it undo?’

An undo file performs a backwards migration. It is rather like a time-travel movie where the hero goes back in time to deal with a villain, and thereby allows a happier and more tranquil future. They are ideal for use in branch-based development, because it gives each developer a simpler way to revert to a previous migration, fix a problem with the code, and redo a migration. We can even undo migrations that affect a group of tables: Undoing Actions on Groups of Database Tables. Just don’t try this outside a feature branch. It ends in tears.

The tested undo files can, with a few extra safeguards, be used to create a rollback script. See Rollbacks, Undos and Undonts.

‘How can I do sensible branching and Merging in Flyway?’

I describe the advantages, and difficulties of moving to a branching and merging approach to database development in Moving to Team-Based Database Development with Flyway. However, getting started is always difficult. I write about some of the general principles here in Branching and Merging in Database Development using Flyway and show practical approaches in A Simple Example of Flyway Development using GitHub Branching and Flyway Branching Walkthrough. You can see what is going on in branches using techniques I describe here in Database Development Visibility using Flyway and PowerShell. Merging can be a head-scratcher. I describe a way of dealing with this here in Database Branching and Merging without the Tears.

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more