Java.DBMigrationTools.How does Flyway handle repeatable migrations?

What is a repeatable migration in Flyway?

In Flyway, a repeatable migration is a migration that:

• Has no version number
• Can be re-executed automatically
• Is identified by a checksum, not a version

Filename format:

R__description.sql

Example:

R__create_views.sql
R__refresh_materialized_views.sql
R__update_permissions.sql

How Flyway decides when to re-run it

Flyway stores checksums of repeatable migrations in flyway_schema_history.

Execution rules:

Flyway re-runs a repeatable migration when:

1. The file changes (checksum changes)
2. A new versioned migration is applied after the last run
3. The migration failed previously

➡️ Flyway compares:

• stored checksum
• current file checksum

If different → re-execute

Execution order (important!)

Flyway executes migrations in this order:

1. Versioned migrations (V1__, V2__, …)
2. Repeatable migrations (R__)

This means:

• Repeatable migrations always run after the schema is up to date
• They can safely depend on the latest schema

📌 Interview phrase:

“Repeatable migrations are executed after all pending versioned migrations.”

---

Typical use cases (this is what interviewers want)

✅ Good use cases

Use repeatable migrations for derived or regeneratable objects:

• Views
• Materialized views
• Stored procedures / functions
• Triggers
• Grants / permissions
• Reference data that can be recalculated

Example:

CREATE OR REPLACE VIEW active_users AS
SELECT * FROM users WHERE active = true;

❌ Bad use cases (red flag)

Never use repeatable migrations for:

• Table creation
• Column changes
• Data migrations with irreversible side effects
• Anything that must run exactly once

❌ BAD:

INSERT INTO users (...) VALUES (...);

Why?

• It will re-run
• You risk duplicates or corruption

How Flyway stores repeatable migrations

In flyway_schema_history:

• version = NULL
• description = filename
• checksum = file hash
• success = true/false

This is why editing an applied repeatable migration is allowed — unlike versioned ones.

---

Comparison with Liquibase (interview bonus)

Flyway	Liquibase
R__ repeatable	runOnChange="true"
checksum-based	checksum-based
automatic re-run	automatic re-run
file-based	changeset-based

Key difference:

• Flyway → file-level
• Liquibase → changeset-level

---

Common pitfalls (real-world)

❌ Hidden data changes

Putting data updates in repeatables → accidental re-execution

❌ Non-idempotent SQL

Repeatables must be idempotent

Bad:

INSERT INTO roles VALUES ('ADMIN');

Good:

INSERT INTO roles (name)
SELECT 'ADMIN'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE name = 'ADMIN');

❌ Assuming repeatables run only once

They don’t. Ever.

---

How to explain this in an interview (perfect answer)

Short answer (2–3 sentences):

“Flyway repeatable migrations are identified by the R__ prefix and are re-executed whenever their checksum changes or when a new versioned migration is applied. They always run after versioned migrations and are typically used for views, procedures, and other regeneratable objects. They must be idempotent and should never contain one-time schema or data changes.”

That answer is senior-level.

---

If you want, next we can:

• Compare repeatable vs versioned migrations in edge cases
• Discuss how repeatables behave in CI/CD
• Simulate an interviewer follow-up trap question