production data migration patterns in continuous delivery

Question

What are relational database (and schema) migration patterns on production in continuous delivery?

In many traditional developments the DBA arranges a big migration script out of the many smaller scripts created in the current release cycle. But in CD the developer may want to push the change now to production, not wait to compile them with other scripts.

I know on rails-migration but to me it looks more reasonable to use raw sql scripts.

I've also seen tools like flyway to manage migrations but I have not read of many people using them in production. This is why I wonder what are the common practices here.

Answer 1

Flyway works great for continuous delivery/deployment. Many clients use it across all environments, including production.

The single most important thing for cascading DB migrations across environments is to have a 3 step process:

Step 1

Old application code works together with old DB.

Step 2

New application code get deployed, and migrates DB on startup. This migration must be backwards-compatible so that old application code still works with the new DB. This is essential because:

• you can then do rolling upgrades, upgrading one node at a time until all nodes have the new application code
• rollback immediately to the old application code if the new one is broken

This step may involve compatibility views and triggers to do the job.

Step 3

After the changes have been proven to work, the next version of the application code gets deployed together with the necessary DB migrations to discard any remaining outdated (from step 1) and compatibility (from step 2) structures.

Answer 2

Implement changes to your database as single (raw) sql files, then use sqlpatch to build a migration script.

I usually have a single git repository for the database alone and a cd environment attached to it. I usually have a production and a development database that are automatically migrated when I push to corresponding branches.

This setup makes is very easy to setup another database for a feature branch and to experiment with it. Sqlpatch takes care of all the dependencies in the separate sql files so that I can easily merge a feature branch in another branch.