• Flyway
• Team-based Development
• Automation and workflow

The Journey to Team-based Development with Flyway

For many years, the developers and users of relational databases relied on the creators and vendors of these database systems, such as Oracle or SQL Server, to provide the tools needed to develop the databases. These tools compelled database developers to work in particular ways, and the experts in actually developing effective databases had only indirect influence on database development methods.

There are now several tools that are, instead, driven by the requirements of a community of database developers and their applications. The most interesting ones grow in response to development teams who want to use DevOps techniques to automate development processes and need to make them as appropriate and effective as possible. Flyway gains its strength from responding to years of such requests and suggestions from its users. Suggestions that are generally appropriate to database development are added to the free Flyway Community version. Those geared to supporting team development processes are added to Flyway Teams, and finally those aimed at Enterprise-level development, including support for advanced processes such as script generation and CI/CD, are added to Flyway Enterprise.

This article tackles the first leg of this journey, from Community to Teams.

Challenges of team-based database development

The organizations that rely on their databases are often frustrated by the amount of time and effort it takes to make, test and deploy changes to those databases. When the rate of delivery lags behind what the organization feels it needs to stay competitive, and remain aligned to the changing legislative framework, it can cause friction.

However, it is easy to be critical of the speed of delivery, without realizing why it is slow, or being aware of the unforeseen consequences of trying to cut out parts of the process that, on the surface, ‘do not seem to add much‘.

There are several problems that confront teams of database developers that, without the tools and processes to tackle them, will ensure that the delivery of the features that the organization need remains stubbornly slow:

1. Tracking and verifying database versions – the need to know precisely what is, or was, in any version of the database so as to be able to provision databases and deploy releases effectively.
2. Preserving data – a release to a server that is in production must not lose, or corrupt, the existing data.
3. Maintaining reliability – even small mistakes can be costly and damaging– a seemingly minor bug or security mistake in a database can end up losing the organization large sums of money and reputation. Rigorous testing is important.
4. Controlling the order in which new features are delivered – the order in which new but well-tested functionality or features is released ought, where possible, to reflect the requirements of the organization. This is hard to do without a reliable database branching and merging process.

What items 2, 3 and 4 in the list have in common is that they each rely on the first item, i.e., having a means to keep track of, and verify, each version of a database and all its development branches. In order to be able to test, double-check, or sign-off a release, the various specialized teams need a copy of the database at that release version that can be rapidly provisioned, and at a precise version. This is very important where tests or checks have to be run in parallel to reduce the overall time.

DevOps practices are effective because they make it more likely that a release candidate passes the required tests, but it doesn’t obviate the need for those tests.

How does Flyway help?

Flyway is a build and development system that imposes version-discipline across a large range of relational databases. It will track and automate the way process by which you update a database from one version to the next, by applying as series of versioned migration scripts.

It does this because it important to be able to know the version of any installation of a database and be able to update it to the version you wish. It doesn’t replace a version control system, but it does provide a way of ensuring that the version of the working database has identical metadata to the DDL code that defines that version, in the version control system.

Flyway is designed for a wide range of sizes of database, database teams and skill sets. The main advantages of using it are:

• Versioning When working with Flyway, you apply a version to your database schema changes, so as to keep track of changes over time, and easily roll back to a previous version if needed.
• Automation: Flyway is designed as a command-line tool in order to work in scripts, workflows and pipelines to automate the process of applying database migrations. This saves time, reduces tedium, ensures that the required parts of the process are done in the right order, and reduces the risk of errors that can occur when applying changes manually.
• Consistency: Flyway helps to ensure that your database schema is consistent for any version, across all the different parts of the development lifecycle, including testing, staging and production. It does so by applying the same migrations in each environment to provide or update a database at a specified version.
• Collaboration: Flyway makes it easier for several database developers to work on the same database schema at the same time without stepping on each other’s toes.
• Portability: Flyway supports a wide range of database platforms, so you can use it regardless of which database you are using.
• Security: All information about a database connection is held outside Flyway and Flyway supports several methods of ensuring that this information is secure

Versioning with Flyway

For any type of product development, you need to be able to track the progress of each component with version numbers. The larger the team, the more that any workflow relies on them. For the development of databases, this comes early on, when an application must be compatible with its database, so we must be able to assemble a release candidate of the database with all the correct features in place. From then on, it gets even more important.

The only industry-standard way to achieve a versioning system that can be used on any RDBMS is to apply change scripts, also known as migration scripts, in their correct order, which is exactly how Flyway does it. Each versioned migration changes the database from one version to another, with the versions being tracked in a special table in the database.

Every database change that alters the metadata requires a version change. Conversely, adding documentation, formatting SQL scripts, or changing the data must not affect the version. Changing a database setting that affects the way that the database runs, is a version change; any seasoned database developer will know how a single change to a setting can make a database behave wildly differently.

Once a version of a database deployed, the table holding the version number ought to remain unchanged by, and preferably invisible to, the database. This version number is used to ensure that subsequent changes can be rehearsed and are therefore unlikely to fail. For this, we also need a way to verify that a database remains at its stated version, i.e., to detect ‘drift’. To do so, we can compare the database, at its stated version, with a ‘model’ of the database that was created from a database at that version and then stored in the version control system.

Once we have both a controlled way to change the version of a database and a means to verify that a database is at the version it claims to be, we have a robust system that will minimize the chance of any embarrassing mistake that will derail a deployment. When one is spotted, you add a migration ‘patch’ to correct it and increment the version for both production and development.

The obvious advantages to being able to track and verify versions include:

• Fewer deployment errors – if you know the version of the production system and can verify that it hasn’t had uncontrolled changes, and you know the version of the release candidate, then you’ll be far less prone to deployment errors, caused by ‘unexpected’, and therefore untested, changes in the release, or undetected ‘drift’ in the production version.
• More thorough testing of deployment scripts – if we can have confidence that a database is at a particular version, then a migration script, or set of scripts, that change a database from the current production version to the release candidate’s version can be tested well in advance of staging. It will thereby be much more likely to succeed in deploying those changes, preserving all existing data correctly and avoiding other potentially costly mistakes and errors. You can also create and test a rollback script much more reliably.
• Better database testing – test runs are wasted if they are performed on the wrong version, the cause of much wasted effort and frustration.
• Branch-based development – giving developers have more freedom to experiment, think creatively and select the best way of devising new functionality. Without versioning, merge operations rely on ‘synchronization’, are chaotic and prone to collisions and conflict.

Automation with Flyway

The attraction of Flyway is that it has been refined and honed, in the light of the sometimes gritty reality of database development under pressure, with a community version that has had any rough edges smoothed off against the grindstone of experience. From the start, it has been designed as an automation tool, slotting easily into workflow pipelines and application build systems.

Its design is geared very much towards simplicity. There are only seven ‘core’ commands and once you’ve got things set up, it ‘runs on rails’. The way it works is intuitive. It is like building a tower. The only way is upwards, version by version, one story on the next. You work from a strong design. Each migration takes the database from one version to the next.

The way that you use Flyway depends on the sort of platform you are developing with. If you’re a JVM (Java Virtual Machine) developer then you can use the Java API for migrating the database on application startup or use a JVM build tool with a Flyway plugin, such as the Gradle or Apache Maven. There are plugins available for libraries and frameworks such as Ant, Griffon, Spring Boot, Grunt, Dropwizard, Grails, Play, SBT, or Ninja.

The added value of Flyway Teams

The free, open-source Flyway Community is ideal for the smaller databases that have just a single developer working on it. Flyway Teams is a commercial version of Flyway. It has everything available to the community edition, but also includes all those extras that are required for the database that has more than one developer working on it concurrently, or for the larger database that has a single developer but requires a means of branching and merging.

The extra features within Flyway Teams are provided to allow a team to make changes to a production database more quickly and safely. It has:

• Better workflow and automation features – including the facility for running callbacks and migration scripts that can be scripted by PowerShell, DOS batch or Bash scripts.
• Tracking, reporting and monitoring of changes – features that allow auditing and tracking of changes between versions and make it easier for others to review and verify the changes in a release candidate.
• Branch based development and testing – features that make it easier to create multiple copies of a database at any version, and to experiment without disrupting the work of others.

I’ll only discuss those Flyway Teams features that enhance team development, but there are other Flyway Teams features that benefit the organization, such as more advanced authentication and authorization options, and commercial support.

Workflow and Automation features

To minimize the risk of database change, we need to automate as many as possible of the tasks involved in the development and release of a new database version, including the tests and checks that are required to ‘sign off’ on a release candidate.

Creating and testing a database isn’t just about writing and migrations; there are many other tasks to do, such as loading data, updating documentation, logging changes, preparing reports and so on.

Scripted callbacks

Although Flyway Community allows Java callbacks, the scripted callbacks of the ‘Teams’ version are the key to advanced automation of the system, because they allow far more to be achieved in a script.

Scripted callbacks can be executed before or after either migrations or migration runs. In fact, there are many events such as repairs, info, cleans or baselines which have ‘before’ or ‘after’ callbacks. It means that any automated process can be executed at the appropriate point in the process without intervention.

I’ve provided plenty of examples of the use of PowerShell callbacks to automate a range of tasks, on successful migration to a new version, such as migration auditing, updating documentation, bulk loading development data, or running tests.

Scripted migrations

Flyway Teams also allows you to use a file-based interpretive scripting language such as PowerShell, Batch, Bash, and Python for migrations. The only difference is that the filetype denotes the type of script.

The advantage of applying migrations as scripts rather than SQL is that it allows the loading of data from the local file system, or for installing a third-party application or CLR library. They are useful for cleaning up local files used in a migration such as those created by the Oracle’s SQL*Plus SPOOL command.

Error over-rides

Each task in an automated ‘pipeline’ must pass along its output in a form that is useful to the next task in the sequence. When the output of Flyway is piped to subsequent scripted processes, it becomes important that we can filter and control what information gets passed along and how.

Error overrides in Flyway Teams allows the output of individual errors, debug messages and warnings to be altered to make it easier to respond correctly to them. Their use is dependent on the relational database being used.

Tracking, reporting and monitoring of changes

We also need to use automation to increase the visibility of the development processes to the team, so that we can assess exactly what impact a new release might have on the production system. We need to check on the implications of change to other parts of the database, as early as possible, when alterations and fixes are easier to make.

Object level scripts (schema model)

For each new version of a database, Flyway Teams can produce a schema model, a set of object level scripts describing the state of each object, which we can commit directly to the version control system. This gives us a means both to track what has changed between versions and to check for any ‘drift’ in a target database.

Dry run scripts

It is likely that a set of migration steps needs to be reviewed or checked by the team before it is applied. A dry run script is simply a single script “aggregate” of all the migration that would need to be applied to deliver the requested new version. The dry run script will not make any changes to the database at that point but can make the changes if subsequently executed against the database and will update Flyway’s schema history table.

Dry runs are also a useful artifact for team-based code reviews, such as before a merge. The team can review the SQL code that is going to be executed, check for potential conflicts and possibly run various code quality checks.

Branch-based development and testing

Branch-based database development allows developers to work more efficiently, because it ensures the isolation of changes, allows collaboration and makes testing easier.

With Flyway, branch development can begin by provisioning a branch database at the current development version and loading it with development data. At the point of merge, we can use the schema comparison and object-level directories to review what work has been done on the feature branch, and also check for any potentially conflicting work that has been committed to the parent branch in the interim. A fully tested features can be merged in, at the appropriate version, as and when the organization schedules it for release.

Flyway teams also has a few other features that are particularly beneficial for branch-based development.

Undo scripts

Undo scripts revert a database from a specific version, back to a previous version. Every forward migration has an undo twin, created together (it isn’t possible to undo a repeatable migration). They are best used only in a branch, ideally where there is likely to be just one developer active at any time, because otherwise their use can be disruptive to others in the team. The ability to undo work, migrating back to an earlier version, allows freer and more experimental design work. You might, for example want to try out different strategies for the functionality you need to introduce to find the fastest or most effective.

To use the undo action in Flyway, you’ll need to specify a target otherwise you might well end up with an empty database. Flyway will try to undo all versioned migrations in reverse order, latest first, until it reaches the required target version, or hits a version missing its undo migration. If possible, Flyway will attempt to undo all these migrations within a single transaction.

If an undo file is idempotent, in that it only undoes what remains of a failed migration, then it can be used as a rollback script to clean up a failed deployment, which is especially useful for RDBMSs like MySQL, MariaDB, or SQLite that cannot roll back DDL code within a transaction. More generally, you’d want any rollback script intended for use on production to be idempotent, regardless of RDBMS, in case someone accidentally ran it on the wrong version.

Baseline migrations

These ‘super migrations’ are consolidations of a number of migration files. When, for example, the number of historic version files get too large to manage, you will sometimes need to consolidate them into just one or more baseline migration files that will build the database in a single leap, to the earliest version that is still relevant. It’s easy to do, but requires that you can also verify that the version produced by the single script is exactly the same as that produced from the run of migrations that it replaced (such as by using schema comparison to compare the database produced by the baseline migration to the object object-level directory that defines that version, in the version control system).

They are used for deploying a new database from scratch. They are especially helpful when creating test or branch databases in large projects, especially ones that involve setting up, and subsequently tearing down, many copies of the database to allow processes such as performance testing or pen-testing to be done in parallel. We can also use a baseline migration to allow a branch containing several migration files to be merged into the parent branch with a single migration, but again, only if the result can be verified.

Conclusions

Like choral singing, team database development looks easy until you have to do it. Database development and deployment has been bedeviled by the many IDEs, tools and build systems designed by experts with strong opinions about how to do it that are not fully tested against reality.

Flyway has evolved in the light of the many requests of developers who want to deliver changes more frequently and without the stress and dramas of the average traditional database deployment. This hasn’t been easy, because there is little in common between a corporate database system in its mid-life, and the rapidly evolving database of a start-up. To square the circle, Flyway is provided in different flavors for different tastes and requirements. After all the years of responding to suggestions from experienced developers, there is likely to be a flavor to suit you.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more