Using Flyway for Database Version Management: Principles, Configuration, and Best Practices

Background

Traditional database update processes often suffer from missed updates, duplicate executions, wrong order, and poor traceability, requiring complex manual steps such as version.txt maintenance, script reviews, backups, and ad‑hoc fixes.

Flyway Working Principle

Flyway treats each update as a migration . When a migration runs, Flyway creates (or uses) a table named flyway_schema_history (pre‑v5.0 called schema_version ) to record the version, description, script name, executor, execution time and success flag. On subsequent runs, only migrations not yet recorded are applied, strictly following version order.

Supported Relational Databases

Flyway works with major RDBMS such as MySQL, PostgreSQL, Oracle, SQL Server, DB2, H2, and others (illustrated in the original image).

Directory Structure

-/flyway/sql/
|-- database_01/
|   |-- V1__initial.sql
|   |-- V2__first_changes.sql
|   |-- V3__add_tables.sql
|   |-- V4__init_data.sql
|-- database_02/
|   |-- V1__initial.sql
|   |-- V2__modify_columns.sql
|   |-- V3__add_user_tables.sql
|   |-- V4__init_data_for_user.sql
... # additional databases can be added similarly

SQL File Naming Convention

File names must follow the pattern V[Version]__[Description].sql where:

Version is a numeric (or dotted) sequence without letters, e.g., 1, 2, 3.1, 3.2.2.

Description is a short, hyphen‑free identifier extracted for the migration description.

The two underscores "__" separate version and description.

V1__initial.sql
V2.1__first_changes.sql
V3.1.1__add_tables.sql

Practical Project Guidance (MySQL)

Key operational recommendations:

Commit SQL files together with service version files, respecting the directory and naming rules.

Do not include USE db statements; the target database is defined by Flyway configuration.

Avoid DROP/CREATE cycles; prefer incremental changes to reduce data‑loss risk.

Once a SQL file has been applied to any game server, never modify or delete it.

Keep each migration transaction small to simplify rollback on failure.

SRE Operation Guide

Installation (example version 5.2.4):

version=5.2.4
cd /home/xxxx
wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/${version}/flyway-commandline-${version}-linux-x64.tar.gz | tar xvz
sudo ln -s `pwd`/flyway-${version}/flyway /usr/local/bin

Default configuration ( flyway.conf ) example:

flyway.url=jdbc:mysql://host:port/dbname?useSSL=false
flyway.user=xxxxx
flyway.password=xxxxxxxxx

Running Migrations

Two equivalent approaches:

Synchronize SQL files to Flyway’s default directory and run:

# sync files
rsync -azL --delete ${updating_dir}/ /home/${project_user}/flyway/sql/${database}/
# migrate
flyway -baselineOnMigrate=true -baselineVersion=0 -schemas="${database}" -user="${user}" -password="${password}" -url="jdbc:mysql://${host}" migrate

or directly specify the location (recommended):

flyway -baselineOnMigrate=true -baselineVersion=0 -user="${user}" -password="${password}" -url="jdbc:mysql://${host}" -schemas=${database} -locations=filesystem:/home/xxx/server/database/htdocs_local/db/alter/stg/ migrate

Important parameters:

-baselineOnMigrate=true : treat existing DB as baseline if Flyway has not been used before.

-baselineVersion=0 : set baseline version.

-schemas : target database name.

-locations : directory containing migration scripts.

migrate : execute pending migrations.

Typical Update Flow

1. Developers add new versioned SQL files following Flyway conventions.
2. Submit the change through the Aladdin deployment pipeline.
3. Backup the target DB on the client machine.
4. Pull the new SQL files.
5. Run Flyway migrate.
6. If errors occur, fix and repeat.

Troubleshooting

Common issues and remedies:

Checksum mismatch : run flyway repair after correcting the script.

Description mismatch : manually update the description column in flyway_schema_history .

Failed migration : address the error message, optionally delete the offending row from flyway_schema_history and re‑run.

Missing migration : use -ignoreMissingMigrations=true or remove the stale record.

Duplicate index warning : safe to ignore.

Conclusion

Standardizing database change management with Flyway eliminates the traditional pain points of manual updates, provides reliable version tracking, and enables SRE teams to perform high‑quality, low‑risk schema changes across multiple projects.