As we have seen, data migrations have to be executed in several steps. Rollout as well as rollback scripts are executed right before the deployment. This implies that the application supports N-1 compatibility as well as that only one deployment is being executed at a time.

The migration process requires to perform several software releases, each of them consistent in application code and schema migration scripts. Engineers need to plan their commits accordingly. It's advisable to perform the full schema migration in a timely manner, to keep the database schema clean and to ensure that ongoing migrations aren't simply forgotten about.

It's in the nature of the corresponding model refactoring, whether existing data needs to be kept or can be discarded. Generally speaking, it is advisable to not throw away data. This means not to drop structures containing data that doesn't exist somewhere else.

As we have seen in the examples, the migrations will be applied in graceful steps; especially in regard to database constraints, such as not null or referential integrity constraints. Migration scripts should be resilient. For example, the migration should not fail when trying to create already existing columns. They could already exist from previous rollbacks. In general, it makes sense to think through and test different rollout and rollback scenarios upfront.

Engineers need to keep the update time in mind when updating table contents. Updating huge tables at once will take a non-negligible amount of time in which the data is potentially locked. This needs to be considered upfront; ideally, by testing the scripts in a separate database. For huge amount of data involved, the update steps can be executed in shards, for example, by partitioning the data by their IDs.

All rollout and rollback migration scripts should reside in the project repository. The database schema comprises a schema version that corresponds to the numbered migration scripts. This version is stored in the database as metadata together with the current schema state. Before every deployment, the database schema is migrated to its desired version. Right after that, the application with a corresponding version is deployed, making sure that the versions don't differ by more than one.

In a container orchestration framework this means that the database migration needs to be performed right before the new application version is deployed via rolling updates. Since there can be many replicas of pods, this process has to be idempotent. Executing the migration of a database schema to the same version twice, has to result in the same outcome. Kubernetes pods can define so-called init containers which execute one-shot processes before the actual containers start. Init containers run mutually exclusive. They have to exit successfully before the actual pod container process can be started.

The following code snippet shows an example of initContainer:

# ...
    spec:
      containers:
      - name: hello-cloud
        image: .../hello-cloud:1
      initContainers:
      - name: migrate-vehicle-db
        image: postgres
        command: ['/migrate.sh', '$VERSION']
# ...

The preceding example implies that the init container image contains the correct tooling to connect to the database instance as well as all recent migration scripts. In order to make this possible, this image is built as part of the pipeline, as well, including all migration scripts from the repository.

There are, however, many solutions to migrate database schemas. The important aspect here is that the idempotent migration needs to be executed upfront, while no second deployment action is being rolled out. The migration scripts of the corresponding versions would be executed in ascending or descending order, depending on whether the database schema version is upgraded or rolled back, until the version matches. After the scripts have been executed, the metadata version is updated in the database, as well.

The correlation between code and database versions can be tracked in the project repository. For example, the most recent rollout script contained in a commit version corresponds to the required database schema. The Build metadata section covers the topic of required metadata and where to store it in more depth.

Since the chosen migration solution highly depends on the project's technology, there is no silver bullet approach that can be shown here. The following example gives one possible solution on migration file structure and execution in pseudo code. It shows migration files for the example of changing the color column to chassis_color discussed earlier:

The preceding example shows the rollout and rollback scripts that migrate the database schema version to the desired state. Rollout script 004_remove_color.sql transposes the schema version to version 4 by removing the color column of the example shown earlier. The corresponding rollback script 003_add_color.sql rolls back the schema to version 3, where the color column still existed; in other words, version 3 contains the color column whereas version 4 doesn't, with these two migration files being able to roll back and forth.

The following shows the pseudo code of the script that performs the migrations. The desired version to migrate to is provided as an argument when invoking the script:

current_version = select the current schema version stored in the database

if current_version == desired_version
    exit, nothing to do

if current_version < desired_version
    folder = /rollouts/
    script_sequence = range from current_version + 1 to desired_version

if current_version > desired_version
    folder = /rollbacks/
    script_sequence = range from current_version - 1 to desired_version

for i in script_sequence
    execute script in folder/i_*.sql
    update schema version to i

This migration script is executed in the init container before the actual deployment.