Automated DB migrations for continuous delivery
At Phorest Salon Software we run our platform on AWS. That platform consists of many AWS resources (instances, databases) inside a VPC. Most of our services are running on docker containers which are managed by Amazon’s ECS.
Migration challenges
We have three dedicated environments; dev, staging, and production. Each of these has its own dedicated VPC, so resources in the VPC are not directly accessible from outside of it, which is great — usually.
The problem is that from time to time we need to modify the schema structure for some components, e.g. add a new table or column. In a Java-ish world we use Liquibase for managing DDL/DML changes . Again, usually that’s great… but how do you apply those changes to each different environment using continuous delivery?
Here are a few ways we came up with to solve this problem:
1.Execute on start-up
You can let your component to do that. For example you can configure your application to apply it on start-up. This is very simple in case of Spring Boot apps. You can achieve this by simply adding liquibase jar to the classpath and configuring a few configuration properties.
This approach may be fine for local development or dev environment, but what about staging or production? The issue is that your DB migration scripts might be slow, and it can take minutes, hours or even days to execute them.
That wouldn’t work well with ECS as when deploying a service there, you define a health check that ECS will use to verify the state of your component and in the case of executing DB migrations scripts on start up it would be considered unhealthy to do this until the migration has finished. But then ECS could kill your container in the middle of that migration. You could configure a grace period for your ECS service, but that might be hard to predict a value for, as it may vary for each migration.
2.Apply it manually
The other option is to SSH into an EC2 instance running in the same VPC as your service, and manually run your migration scripts from there (and you probably know how bad a practice this would be).
3.Use a docker container
One other option is to create a dedicated docker image to just run your migration scripts. We went with this option:
Define your docker image:
We used an open source base image that provides you a way to execute Liquibase changelogs. All we had to do was to define a few environment variables and copy our Liquibase changelogs to it. Those empty variables were left intentionally to explicitly show that they need to be provided when running the container.
We store theimage definition file together with our codebase, so it is versioned and tagged when releasing. With that in place, we updated our Jenkins job to build and push that new docker image as part of the job pipeline.
Next step was to update our Jenkins job to trigger an ECS task that uses our new image to migrate the DB. For that we use our own framework but you could use Terraform or K8s for it, or you could even run it manually (Please don’t do that in production!) with a command like:
docker run your-image-tag liquibase update
Benefits of this approach
Since we no longer run migrations on the container start up, we no longer have the problem related to long running migrations causing ECS health checks to fail. But the main benefit is that our migrations are now decoupled from our deployments (here is a great article explaining the benefits of separating db migrations from app deployments).
With that new approach we had to ensure that our migrations are always compatible with code that is currently running on production. It required a bit of additional work but now means that we can rollback our releases without any hassle.
There may be many other (perhaps better) approaches to solving this problem, but the latter one works well for us. If you have any ideas or thoughts on the topic feel free to reach out to Paweł Malon, John Doran or Paul Dailly
