Java.DBMigrationTools.How do migration tools ensure migrations run in the correct order?

Core idea (common to all tools)

Migration tools ensure order by keeping a metadata table in the database itself.

That table records:

• which migrations ran
• in what order
• when
• with what checksum

So ordering is DB-driven, not “file-system magic”.

---

Flyway: how ordering works

1️⃣ Naming convention defines order

V1__init.sql
V2__add_user_table.sql
V3_1__add_index.sql

Rules:

• V<version>__<description>.sql
• Versions are compared numerically, not lexicographically
  • V2 < V10 (correct)

2️⃣ Flyway schema history table

Flyway creates:

flyway_schema_history

Each row contains:

• version
• description
• checksum
• installed_on
• success flag

Example:

| version | description        | success |
|--------|--------------------|---------|
| 1      | init               | true    |
| 2      | add_user_table     | true    |

3️⃣ Execution algorithm (simplified)

1. Read migration files from classpath
2. Sort them by version
3. Read flyway_schema_history
4. Execute only versions not yet applied
5. Insert a row per successful migration
6. Fail fast on first error

👉 If V3 fails:

• V4 never runs
• DB stays in known state

4️⃣ What prevents re-running?

• Once version V3 exists in history → Flyway skips it
• Checksum mismatch → startup fails

Liquibase: how ordering works

Liquibase is not version-based. It’s changeset-based.

---

1️⃣ Changesets have unique identity

<changeSet id="add-user-table" author="stanley">
    <createTable tableName="user"/>
</changeSet>

Uniqueness = (id + author + changelog file)

Order = file order, unless explicitly controlled.

2️⃣ Liquibase changelog table

DATABASECHANGELOG

Contains:

• id
• author
• filename
• checksum
• execution date

3️⃣ Execution algorithm

1. Read changelog file(s)
2. Parse changesets in order
3. For each changeset:
   • Check if (id, author, file) exists
   • If not → execute
4. Record checksum
5. Stop on failure

4️⃣ Liquibase can override order (dangerous)

Liquibase supports:

• runOrder
• contexts
• labels
• includes

Which gives power and foot-guns.

What really guarantees correct order?

Mechanism	Why it matters
DB metadata table	Single source of truth
Transaction per migration	Prevents partial apply
Checksum validation	Detects edited migrations
Fail-fast execution	Stops cascading corruption

---

Common interview traps

❌ “Migrations run in order of filenames only”

→ Wrong. Filenames are input, DB table is authority.

❌ “You can reorder migrations if needed”

→ Wrong. Once applied, order is locked.

❌ “You can delete history table if broken”

→ 🚨 Red flag. You just lost migration state.

---

Interview-perfect answer (concise)

Migration tools maintain a schema history table inside the database that records applied migrations and their checksums.
At startup, the tool compares available migrations with this table, sorts them deterministically, and executes only those not yet applied, failing fast on errors.
This guarantees consistent ordering even across multiple environments.

---

Senior-level add-on (optional flex)

This approach also prevents concurrent migration execution because the history table acts as a coordination point, usually protected by a lock.