- Managing DB Migrations for Downloadable and Updatable Applications -
1. Introduction
As you develop an application, the database schema also continues to change along with feature changes. You may add new tables, change the types of existing columns, or need to move existing data to a new structure while changing the data structure itself.
For a typical web service, these database changes can be managed as part of the deployment pipeline. If the development team manages both the server and database environments, it can also execute the required SQL along with the application deployment.
However, the Vizend Platform I am working on has slightly different requirements.
Vizend's Application is not operated only on a single central server; users can download and install it in their own environments. After installation, they can download or update the Application again whenever a new version is released.
In other words, the Application is Downloadableand at the same time Updatable.
In this structure, updating only the Application code is not enough. If the new version of the code requires a new Database Schema, the Database Schema must also be changed as part of the Application update process.
For example, suppose that version 7.1 of the Application uses the following table.
user
├─ id
└─ name
If a new feature is added in version 7.2 and the Application is changed to use a status column, the following Schema is required.
user
├─ id
├─ name
└─ status
If only the Application is updated to version 7.2 while the Database remains in the state of version 7.1, the Application cannot operate normally even if it starts successfully.
Ultimately, for us, an Application Update meant the following two things together.
Application Update
│
├─ Application Version Update
│
└─ Database Schema Update
More importantly, developers cannot directly access the Databases in every environment where users have installed the Application and execute SQL.
Therefore, if an Application can update itself, the Database Schema required by that Application must also be able to update automatically at Runtime.
To solve this problem, we introduced Flyway and included Database Migration in the Application Runtime.
In this article, rather than focusing on how to use Flyway itself, I will focus on the problems we encountered while applying Flyway to an actual project and the Migration principles we organized throughout that process.
2. Introducing Flyway
When we first applied Flyway, we expected its role to be relatively simple.
We would write Migration Scripts that assigned a Version to each Database change, and when the Application started, it would automatically apply Migrations that had not yet been executed.
For example, we write Migration Scripts as follows.
V7_1_1__Create_User.sql
V7_1_2__Modify_User.sql
V7_1_3__Create_Position.sqlFlyway checks the Database's flyway_schema_history to distinguish between Migrations that have already been executed and those that have not.
Therefore, when a new version of the Application starts, the required Schema changes can be applied at the same time.
In our Spring Boot Application, we enabled Flyway and configured Hibernate to only validate the Schema without directly changing it.
spring:
flyway:
enabled: true
jpa:
hibernate:
ddl-auto: validateThis way, Flyway is responsible for changing the Database Schema, while Hibernate verifies the consistency between the Entities and the actual Database Schema.
Initially, we thought this alone would solve most DB Migration problems.
However, as we developed the actual service and carried out multiple Version Updates, we learned that it is difficult to create a safe Migration based solely on “automatically executing SQL in order”.
Throughout this process, we now consider the following three points the most important when writing a Migration.
Is it safe if the same Migration is executed again?
If a problem occurs, can it be rolled back to the previous state?
Is the data required to return to the previous state preserved?
Each Idempotency, Rollback, Backupis a problem concerning
3. The First Principle of Migration: Idempotency
Flyway's Versioned Migration does not execute the same Migration again once it has completed successfully.
This may raise the question of whether it is really necessary to write Migration SQL in an idempotent manner.
In actual production environments, it is difficult to assume that Flyway will always run from a perfectly consistent state.
An error may occur after some SQL statements have been executed during the Migration process, or a specific SQL statement may need to be executed manually again while recovering from an incident. In addition, due to the nature of a Downloadable Application, we also had to consider the possibility that the Database in different installation environments might be in a state that differed slightly from what was expected.
If another error occurs when the same SQL is executed again in such situations, the recovery process becomes even more difficult.
Therefore, for all Migrations wherever possible, we established guaranteeing idempotency as a basic principle.
The most basic methods we use are IF EXISTS and IF NOT EXISTS.
When creating a table, we write it as follows.
CREATE TABLE IF NOT EXISTS user_history (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL
);When adding a column, we also write it as follows whenever possible.
ALTER TABLE user
ADD COLUMN IF NOT EXISTS status VARCHAR(20);For deletion operations, we use IF EXISTS instead.
DROP TABLE IF EXISTS user_history;For DDL statements where the Database does not directly provide IF EXISTS or IF NOT EXISTS, we created separate Functions so that they could be handled in the same way.
However, we also learned one thing to be careful about during implementation.
The presence of IF NOT EXISTS does not completely guarantee the idempotency of a Migration.
For example, suppose we have the following SQL.
ALTER TABLE user
ADD COLUMN IF NOT EXISTS status VARCHAR(20);If the status column already exists in the Database, the SQL terminates without an error.
However, the actual column may have been created as follows.
기대 상태
status VARCHAR(20)
실제 상태
status INTEGERThe SQL succeeded, but the Database is not in the state expected by the Application.
Therefore, we do not consider idempotency to mean merely that no error occurs when the SQL is executed twice.
Whether the final Database state is identicalregardless of whether the Migration is executed once or multiple times
is the criterion we use for evaluation. IF EXISTS and IF NOT EXISTS are tools for implementing this.
4. Forward Migration Alone Was Not Sufficient
When we first began writing Migrations, we focused primarily on how to change the Database to a new version.
However, when operating an Application, we must also consider cases where the new version has a problem and needs to be rolled back to a previous version.
If only the Application is reverted to the previous version while the Database Schema remains in the state of the new version, the previous Application may not function properly.
Therefore, writing only a Forward Migration was not sufficient.
Currently, when writing an Incremental Migration, we follow the principle of writing the corresponding Rollback Script at the same time.
For example, if we have a Migration like the following,
postgresql/V7_1/
└─ V7_1_3__Modify_User.sqlwe manage the corresponding Rollback Script in a separate directory as well.
rollback/postgresql/V7_1/
└─ V7_1_3__Modify_User_Rollback.sqlA Rollback Script is not something that Flyway executes automatically.
It is managed separately so that the person in charge can review and execute the changes when an Application Rollback or incident recovery is required.
For a simple column addition, we can write a Forward Migration like the following.
ALTER TABLE user
ADD COLUMN IF NOT EXISTS status VARCHAR(20);In the Rollback Script, we remove the column instead.
ALTER TABLE user
DROP COLUMN IF EXISTS status;At first, this may seem sufficient preparation for a Rollback.
However, with Migrations that modify actual data, reverting only the Schema does not return the system to its previous state.
5. Consider Backup Before Creating a Rollback
One of the most important principles we came to establish is to consider Backup and Rollback as a single operation.
For example, suppose we deleted an existing column.
ALTER TABLE user
DROP COLUMN legacy_code;If a problem occurs in the Application and we need to Rollback to the previous version, we can recreate the column as follows.
ALTER TABLE user
ADD COLUMN legacy_code VARCHAR(100);Looking only at the Database Schema, it appears to have returned to its original state.
However, the data stored in the existing legacy_code column has already been lost.
In other words,
Schema Rollback and Data Rollback are separate problems.
Because of this experience, we decided to always consider Backup together with any Migration that could modify or delete existing data.
For example, if an existing column needs to be changed, you can leave the existing column as a Backup column instead of deleting it immediately.
ALTER TABLE sample
RENAME COLUMN A TO A_7_1_3;
ALTER TABLE sample
ADD COLUMN A VARCHAR(255);
UPDATE sample
SET A = A_7_1_3;The existing A column is preserved under the name A_7_1_3.
If a problem occurs in the new version, remove the newly created column and restore the existing column.
ALTER TABLE sample
DROP COLUMN IF EXISTS A;
ALTER TABLE sample
RENAME COLUMN A_7_1_3 TO A;If a Backup of the entire table is needed, create a Backup Table.
CREATE TABLE user_7_1_3 AS
SELECT *
FROM user;During Rollback, you can use it to restore the original table.
DROP TABLE IF EXISTS user;
ALTER TABLE user_7_1_3
RENAME TO user;We also use a rule that includes the Migration Version in the name of the Backup Object.
<column>_<major>_<minor>_<patch>
<table>_<major>_<minor>_<patch>
For example, it is as follows.
email_7_1_3
user_7_1_3This makes it possible to identify which Migration the Backup was created for.
Ultimately, we currently view a single Migration task as consisting of the following three parts.
Migration
│
├─ Backup
├─ Forward Migration
└─ Rollback
In particular, it was important to design Rollback and Backup together when writing the Migration, rather than creating the Forward Migration first and then considering how to Rollback
This is because if you only start considering how to Rollback after completing the Migration, the required data may already have been deleted.
The current guide also requires Backup to be written for operations such as changing column names, changing data types, modifying data, changing table structures, and deleting data.
6. First Trial and Error: Managing the Baseline
Downloadable Application must support not only updating existing environments but also fresh installation in new environments.
The problem is that as the Application continues to operate over time, the number of Migration Scripts keeps increasing.
For example, dozens of Migrations may exist as follows.
V7_0_1
V7_0_2
V7_0_3
...
V7_1_1
V7_1_2
V7_1_3
...When installing a new environment, running all Migrations sequentially from the initial version involves a lot of unnecessary work.
In one Migration, a table may be created and then deleted just a few versions later. In a new installation, even though the final structure is already known, all of these historical changes must still be applied.
At first, to solve this problem, we managed separate Baseline SQL files representing the final Schema for each Minor Version.
baseline/
└─ postgresql/
├─ 7.0.sql
└─ 7.1.sql
In the Baseline, we defined the final Schema at that Minor Version, including all Tables, Indexes, Constraints, and so on.
In other words, we managed one additional Snapshot separate from the Flyway Migration.
At first, this had the advantage of simplifying new installations.
However, over time, its problems also became clear.
The biggest problem was that we effectively had to manage the same Database Schema in two places.
Incremental Migration
→ How the existing DB changes
Baseline
→ What state the new DB is created in
Problems arise if a developer writes a new Migration but does not modify the Baseline as well.
The result of updating an existing Database and the result of installing a new Database may differ.
Ultimately, this structure required people to continuously maintain consistency between the actual Migration history and the Snapshot Baseline.
While automating with Flyway, we had effectively created another element that had to be managed manually.
7. Changed the Baseline Structure to an Initial Migration
To improve this issue, we reorganized the Baseline structure.
We removed the separate Baseline Snapshot files and Changed the process so that the first Migration for each Minor Version includes the complete Schema DDL for that version.
For example, for 7.1, the following file must exist.
V7_1_0__Initial.sqlUse Patch Version 0 as the Initial Migration for the corresponding Minor Version.
The overall structure is as follows.
postgresql/
├─ V7_1/
│ ├─ V7_1_0__Initial.sql
│ ├─ V7_1_1__Create_User.sql
│ └─ V7_1_2__Modify_User.sql
│
└─ V7_2/
├─ V7_2_0__Initial.sql
├─ V7_2_1__Create_History.sql
└─ V7_2_2__Modify_User.sql
Write the complete Schema DDL required to install version 7.1 from scratch in V7_1_0__Initial.sql.
When Incremental Migrations are added during 7.1 development and development of 7.2 begins, write V7_2_0__Initial.sql based on the final Database Schema of 7.1.
V7_1_0__Initial
↓
V7_1_1
↓
V7_1_2
↓
7.1 Final Schema
↓
V7_2_0__Initial
This allows each Minor Version to have an independent starting point for a new installation.
It also reduces the need to manage consistency between a separate Baseline Snapshot and Incremental Migrations.
Of course, when updating an existing 7.1 Database to 7.2, you must not re-execute the entire V7_2_0__Initial.sql against the existing Schema.
Therefore, a separate strategy is required to distinguish which files should be executed for new installations and existing-environment Updates.
This part also showed that Migration design is not complete simply by creating SQL files; it was a case that demonstrated the need to consider both the new installation path and the Update path at the same time.
8. Second Trial and Error: When Multiple Developers Write Migrations
Another issue that occurred more frequently than expected was Migration Version management.
If one person writes all Migrations sequentially, managing the Versions is not difficult.
However, in actual projects, multiple developers work simultaneously on different Feature Branches.
For example, suppose the latest Migration is currently as follows.
V7_2_2Developer A and Developer B simultaneously modify the Schema in their respective Branches.
Developer A creates the following Migration.
V7_2_3__Create_User.sqlDeveloper B also creates the following Migration because the latest Version at the time they started working was V7_2_2.
V7_2_3__Create_Position.sqlNo problems occur in either Branch.
The problem occurs when the two Branches are merged into the Main Branch.
V7_2_3__Create_User.sql
V7_2_3__Create_Position.sqlThere are now two Migrations with the same Version.
If the Migration has not yet been executed in any environment, it can be resolved relatively easily.
After obtaining the latest state of the Main Branch, change the Version of the Migration that is merged later.
However, if the Migration has already been executed in a development or shared environment, a simple Rename may not be sufficient.
This is because Flyway records the Migration Version and execution result in flyway_schema_history.
After experiencing this problem, we established the following rule: Migration Versions must be checked again not only when the Script is written but also when it is merged into the Main Branch.
Currently, we manage them according to the following process.
Feature development
↓
Writing the Migration
↓
Updating the Main Branch
↓
Checking the latest Migration
↓
Checking for duplicate Versions
↓
Readjusting the Version if necessary
↓
Main Merge
9. The Problem of Higher Versions Being Executed First
In addition to conflicts involving identical Versions, we also encountered cases where the execution order of Migrations became tangled.
Let us assume that there are two Migrations.
V7_2_7
V7_2_8Judging by the Versions alone, we would naturally expect the following order.
V7_2_7
↓
V7_2_8
However, when the two Migrations are developed and deployed from different Branches, the actual Merge or Release order may differ.
A situation may arise in which the higher Version, V7_2_8, is applied to a particular environment first.
V7_2_6
↓
V7_2_8
If V7_2_7 is then added to the Repository, the Migration order expected by the Repository differs from the execution history recorded in the actual Database.
It is dangerous to resolve this situation simply by renaming files or modifying the History.
This is because Migrations that have already been executed are connected to actual Database Schema changes.
Through this experience, I learned that Migration Versions should not be viewed merely as a file-sorting criterion, but rather as operational information representing the actual order in which the Database Schema changes.
Therefore, we currently manage them according to the following principles.
-
We check the latest Migration Version before merging a Branch that includes a Migration.
-
We check whether an identical Version exists.
-
We ensure that a higher Version is not deployed first while a lower Version has not yet been deployed.
-
We do not arbitrarily modify the Version or contents of a Migration that has already been executed.
-
We check Pending Migrations and their execution order before deployment.
-
When a problem occurs, we do not use repair merely to organize Versions.
While using Flyway, this was an experience that made us realize that, in addition to writing technical SQL, Git Branches and the Release Process are also part of Database Migration.
10. Migration Principles Established After Implementation
When we first introduced Flyway, we thought as follows.
Migration
=
Application 실행 시 자동으로 수행할 SQLAfter actually updating multiple Versions and going through trial and error, I now think somewhat differently.
Migration
=
How to change the Database
+ Safety for repeated execution
+ How to preserve data
+ Recovery Methods in Case of Failure
+ Version and Execution Order Management
When writing a Migration, I currently check the following items.
New Installation
-
Check whether an Initial Migration exists for the Minor Version.
-
The default Schema for that version must be creatable using only the Initial Migration.
-
The Application must run normally after a new installation.
Incremental Migration
-
Write it based on the state of the existing production Database.
-
Ensure idempotency whenever possible.
-
Do not modify a Migration that has already been executed.
-
Check whether existing data is affected by the Database changes.
Backup and Rollback
-
When writing a Forward Migration, write the Rollback Script together with it.
-
If data will be changed or deleted, determine the Backup method first.
-
Check whether not only the Schema but also the data can be restored after Rollback.
Version Management
-
Check the latest Migration Version before merging into the Main Branch.
-
Check for duplicate Versions.
-
Check whether the actual deployment order matches the Migration Version order.
-
Do not arbitrarily change a Migration Version that has already been executed.
In the current project, we have organized these items into a guide for writing and verifying Migrations and use it accordingly.
11. Conclusion
The direct reason for introducing Flyway was the nature of the Vizend Application.
Because users can download, install, and update the Application to a new version themselves, the Database Schema also had to be updated automatically along with the Application Update.
Flyway provided a good foundation for implementing these requirements.
However, what I learned during the actual implementation was that using Flyway does not automatically make Database Migration safe.
Flyway records which Migrations have been executed and runs the Migrations that have not yet been applied in a predetermined order.
However, Flyway does not determine on your behalf whether a Migration is safe to run repeatedly, whether changed data can be recovered, or whether a Rollback method is in place.
In addition, in an environment where multiple developers write Migrations simultaneously, we also had to consider the Merge order of Git Branches and the Release order.
In the end, what was most important for using Flyway reliably in an actual project was not the tool's capabilities themselves, but the team's rules for how to write and manage Migrations.
When writing a Migration, the first three things I currently check are the following.
Is it safe to run again?
Can it be reverted if a problem occurs?
Is the data required for reverting still available?
At first, we introduced Flyway to run SQL automatically, but through the experience of actually applying it, I learned that Database changes, just like Application code, are something that must be designed to include not only how to make changes, but also how to handle failures and recovery.
References
The Flyway Migration writing and operation rules introduced in this article were organized based on an internal guide currently used in an actual project.
-
Flyway-based DB Migration Guide
https://vizend.notion.site/Flyway-DB-Migration-2e635bc54c138022a9d0f6c6c3b47695?pvs=74
David