Phil Factor

21 January 2022

• Flyway
• Team-based Development
• Database Versioning

Moving from ‘Chaotic’ to ‘Managed’ Database Development using Flyway

The primary objective of moving to a ‘managed’ system of database development is to make it easier and safer for development teams to release new database functionality into production. In a subsequent article, Moving to Team-Based Database Development with Flyway, I explain how to build on this system further to allow teams to use task-based branches, where each commit to the parent branch can, if you wish, trigger an automated process to prepare and test a potential database release.

Adopting Flyway

In the dark past, the ‘chaotic’ model of database development was common. No versioning was done, and little in the way of source control was attempted (younger readers may gasp in disbelief). This model can be effective for speed of development and creativity if you are lucky, but fortune never smiles on the deployment process for the ‘chaotic’ model. The closer you get to release, the harder it gets.

Flyway, with its disciplined versioning system, can help you enforce a measure of control, consistency, and automation. Unfortunately, however, it is difficult to describe a ‘royal road’ to adopting Flyway, with step-by-step instructions to its use, because there are many ways of developing databases. Many of these methods, such as the disconnected model, are devised to overcome difficulties that don’t happen in a Flyway development.

For the SQL Server or Oracle user, you can change to Flyway relatively easily even if you have an existing development. To do so, you will need to use SQL Compare, or an equivalent schema comparison tool, equivalent system for creating migration scripts and object-level scripts.

Source control and versioning

To move to a Managed system, the first step is to create a baseline migration (build) script for current production version, plus the object-level source. I do this using SQL Compare, but you cna do the same thing with Flyway Desktop. The steps are as follows:

1. Decide what version number applies to the current production server.
2. Make a read-only object-level source of the current production version of the database using SQL Compare or SQL Change Automation.
   1. Set the production database (or a reference copy of it) as the source, and an empty folder as the target compare, and ‘deploy’.
3. Archive a copy of the development data as bulk insert files
4. Create a ‘baseline’ script from the production server and save it to the Migrations folder of the Flyway development project. You can create this script using SQL Compare, setting appropriate options that the script does NOT:
   1. Create the schemas – it should assume they are already present because Flyway creates these beforehand. (/exclude:schema, do not specify the IncludeDependencies in the options)
   2. Use transactions or error handling (“/options:NoTransactions, NoErrorHandling“)
   3. Attempt to create or alter the flyway_Schema_history table ("/exclude:table:flyway_schema_history“)
5. Prevent any further direct alterations of the production database by means of the necessary access control.

At this point, you should have a “baseline” script for your production system as the first and only Flyway migration. It should be given a filename that tells Flyway that it is a ‘V’ (Versioning) file, followed by the version number you decided in step 1 above, followed by a description (for example, V1.0.0__ProductionBaseline.sql).

Validate this script by using it to run a Flyway migration on an empty database. If you get build errors, see You just Build It don’t you? 12 Common Database Build Blockers.

Once it’s working, you can bulk-insert the development data. The final step is to compare it to the production database using SQL Compare, to ensure that it is identical. Use the SQL Compare Options to ignore aspects such as User Access that need to be different in Production and are applied only in Staging. If you can now build your current production database in Flyway, you are on the yellow brick road, heading in the right direction.

Developing and releasing database changes

If your aim is to allow a managed approach to database development, you will now want to make changes to the database. Your production server already has, if you followed the last section, a fake creation myth, a birth-to-production odyssey in one migration file. This forms the basis for your production branch.

Ensure that you have saved all materials in source control. Where you update one or more files for a new version, such as an object-level source of the current version, you can use a tag or version file so that changes can be monitored

Creating the production branch

Create a ‘production’ directory in the Flyway project and within it a Scripts folder containing the production baseline file (from step 4 above). Test out the plumbing by creating a copy of the production database as a reference, and for testing patches.

‘Version’ the production database (and Staging, if required) using the ‘baseline’ command in Flyway. This command introduces Flyway to existing databases by baselining them at a specific version, in this case at whatever version you assigned to the baseline migration script that forms the basis for your production branch. This stamps the production server at the correct version and ensures that the production ‘creation myth’ script you created is never accidentally re-applied to the production server.

Creating the development branch

You will now need to create a ‘develop’ directory in the Flyway project and copy into its migration script folder the production baseline file. With this done, you are ready to start development.

You might well be using a shared development server. I’m also assuming that you have in your team a single developer, or a few developers carefully coordinating their work to prevent overlapping or conflicting changes, as far as possible. In the next article, I’ll explain how to scale up from the managed level, to allow use of feature branches and continuous integration.

When you’re ready to deploy a new feature, you can compare the development server (source) with the database that you’ve already built from the production database (target), as represented by the migration in the production branch production. You will see the differences and can even create a first cut of the migration script. However, if you’re on a shared server, there is a good chance that there will be some partially completed work, and a few bits that will never see the light of day, alongside the completed work that is ready for release .

To get around this problem, when you run the comparison, you can select just the objects that are ready for release, and their dependencies, but ensuring that none of them have dependencies on the uncompleted work. The unfinished work will go into a separate migration script, assuming dependencies can be disentangled. This separate file will eventually become the basis for the subsequent development migration file. For the time being we just leave it to one side or, if we’re feeling ambitious, we can create a feature branch from it.

Once you’ve managed to disentangle the completed work, hit “deploy”, in SQL Compare, to generate the migration script. SQL Compare will warn you if there are dependencies that you haven’t included and will offer to include them. If you acquiesce to this, be careful to edit out the Schema-creation DDL that will appear in the script. Since we want Flyway to be able to run this script, we use the Compare options to turn off transactions, building the schemas, and explicit error handling.

Having dealt with any alerts or issues, such as those related to the preservation of existing data, save the script, giving the file the V prefix, a new version number and description.

Test this out by placing the development migration script in with the production migration script and running the two files on a separate test database (with a clean/migrate cycle). When the result passes all unit and integration tests, put it through your release process.

Verifying the process with trial deployments through to Staging

We then do a ‘dress rehearsal’, perhaps involving the wider team, by deploying through to staging, using Flyway, and the migration files in the production branch of the project. Flyway will ignore any scripts with a version at of lower than the baseline version and then apply all subsequent scripts. At this point, the focus, beyond the normal tests performed in staging, is to ensure that no data in Staging is lost.

Perform the release to production

If the database passes all the tests that accompany a release to Staging, then you can be more confident of a trouble-free release to production. If you are unlucky enough to get an error of some sort after the end of the actual migration, you’ll need to run a subsequent Flyway migration to apply a patch to fix the problem.

If, for some reason, you are not allowed to run Flyway in Staging or Production, the script for migration can be generated by a Flyway Teams ‘dry-run’. This creates a migration script that includes the code to update the Flyway schema history table, so you can execute it independently of flyway and still maintain the correct versioning sequence for the staging and production systems.

This process can be repeated on every production release until the system is bedded down, and you see no errors.

Summary

Flyway and SQL Compare work well together to ease a database development towards a versioned system that pushes back into development the processes that are needed for successful deployment.

The process of adapting a Flyway-based system should be a gradual one that starts with the deployment process and works back into the database development process. SQL Compare brings to the process the freedom to do the actual development work using a range of techniques just so long as the ‘deliverable’ is a migration, just as it always is in any deployment to an existing database.

Flyway has existed and evolved for a long time in the light of feedback from its users, and the system is very adaptable to support a range of database development requirements. If your use of Flyway is ‘hurting’ or restricting the way that you need to work as a developer, you may not be using it the best way for your purposes.