Flyway: A real solution to database migrations

When I started working at a company they had a production database. They had no database schema defined. They didn’t even have a normal development environment. They would attempt to clean production data and populate the development database. One of the biggest issues was they would make database schema changes in development and then have no idea what they were missing in production. They were so far away from a modern day CI/CD engineering process. I looked around for different ways to solve this issue.

At one point in time I was contemplating building a solution from the ground up. The first approach I had been taking was to simply have a git repository that held the database DDL files. The challenge was dealing with possibly dirty systems that had been manually changed. Identifying the current state of the DB in question and if/how to update it to the newest version.

Typically I would have a production environment, a development environment, and a build/test environment. Depending on the size of the engineering team I would like each engineer to have their own development environment. The build/test environment is used when a build is occurring. I need to be able to update an existing environment, as well as create a new DB on the fly. Sometimes I will need to not only create or alter the database structure. At times fairly complex migrations are needed to adapt older data to the new structure. This can be a fickle and tricky process that cannot allow for error. Flyway has its community (free as in speech & beer) edition, as well as its professional edition which has a modest price tag attached to it. Two of the most appealing paid features are “undo” and “dry-run”. The undo feature does exactly what it sounds like. It can undo one or more steps of a migration. Generally if a migration will fail during a transaction the flyway can simply rollback the transaction. However, I’ve had many circumstances where the migration was successful but something else was not functioning as expected. Now I need to revert the code as well as the database.

I am most likely using ECS Fargate, or Elastic Container Service on AWS. With a CloudFormation template describing the environment and infrastructure. Whenever I would have an update of my Spring Boot application I would use the Git hash from the commit and tag my Docker container with it. This provides an easy way to directly link a commit with its built image. I would push the Docker images to ECR (Elastic Container Registry) which houses the Docker images. The CloudFormation stack would be updated with the new image hash. ECS is designed to ensure that the new version of the service is stable and healthy prior to standing down the previous version and directing traffic to the new instance. One big issue that I have yet to effectively deal with, and have a number of very simple ways to handle. In my SDLC there are no releases, when code has been fully tested it goes out to production. That often means that it may be rolled out during business hours. In many instances the product I would be dealing with is not limited to business hours, rather an all time expectation. You of course can have maintenance periods but that is less than ideal. What do you do with an updated database which corresponds to an updated POJO that maps the database schema to a object. With statically typed languages and preferred enterprise approach you are using type-safe domain objects. Now during this grace period of a few minutes you should have the old version functional that will gracefully switch over to the new version. What may end up happening is that the old or former version becomes invalid due to the database migration. Now you essentially need to

This has become especially important

I believe a reference to both Flyway and Liquibase I found Flyway and was extremely impressed. The other major contender was Liquibase.