>Digital-flame

By default, Flyway fails migration when it detects a version gap, because it assumes a migration is missing.

📌 One-liner:

“A version gap is treated as a missing migration and causes validation to fail.”

What Flyway considers a “version gap”

Example migrations:

V1__init.sql
V3__add_index.sql

Missing:

V2__something.sql

Flyway expects monotonic version ordering:

• Versions must increase without gaps
• Every version is intentional and traceable

---

What Flyway actually does

During validation (validateOnMigrate=true by default):

• Flyway scans all available migrations
• Detects missing versions
• Throws an error before executing anything

Typical error:

Validate failed: Detected resolved migration not applied to database: 2

➡️ Migration is aborted

Why Flyway is strict about this

A version gap usually means one of these:

• Someone deleted a migration file
• A migration wasn’t committed
• Two branches were merged incorrectly
• Different environments have diverged histories

Interview phrase:

“Flyway fails fast to prevent silent schema drift.”

How to resolve a version gap (correct ways)

✅ Option 1: Restore the missing migration (BEST)

• Re-add V2__...
• Re-run migrate

---

✅ Option 2: Baseline or repair (carefully)

If the gap is intentional and historical:

• Use flyway baseline (one-time)
• Or mark missing migration as deleted via repair

Only if:

• You fully understand the DB state
• The schema already includes the change

---

❌ Option 3: Disable validation (red flag)

flyway.validateOnMigrate=false

This hides problems and is not acceptable in prod.

---

outOfOrder — related but NOT the same

Interviewers love this trap.

flyway.outOfOrder=true

Allows:

V1 → V3 → later V2

But:

• It still requires all migrations to exist
• It does not allow gaps
• It allows late execution, not missing files

📌 Key sentence:

“Out-of-order allows late migrations, not missing ones.”

---

Repeatable migrations don’t count

R__ migrations:

• Have no version
• Don’t participate in gap detection
• Are unaffected by version gaps

---

Real-world example (production)

“Branch A added V2, Branch B added V3. Branch B got deployed first.”

Outcome:

• Prod has V3 applied
• V2 arrives later
• With outOfOrder=true → V2 runs
• Without it → Flyway fails

But if V2 never existed at all → gap → failure.

---

Interview-ready answer (perfect)

2–3 sentences:

“If Flyway detects a version gap, it treats it as a missing migration and fails validation, aborting the migrate operation. This is a safety mechanism to prevent untracked or deleted migrations from causing schema drift. The correct fix is to restore the missing migration or intentionally baseline or repair, not to disable validation.”

Placeholders in Flyway are variables that let you parameterize SQL migrations so the same script can run in different environments with different values.

📌 One-liner:

“Placeholders let you write one migration and inject environment-specific values at runtime.”

---

How placeholders work (mechanics)

In a migration SQL file:

CREATE SCHEMA ${schema_name};

GRANT SELECT ON ALL TABLES IN SCHEMA ${schema_name} TO ${readonly_user};

In configuration:

flyway.placeholders.schema_name=public
flyway.placeholders.readonly_user=app_ro

Flyway:

1. Loads the migration
2. Replaces ${...} with configured values
3. Executes the resulting SQL

➡️ Replacement happens before execution, not in the database.

Where placeholders are configured

You can define them in multiple ways:

Application config

flyway.placeholders.env=prod

Command line

-Dflyway.placeholders.env=dev

CI/CD secrets

• DB names
• Users
• Schemas
• Feature flags

Interview phrase:

“Placeholders are resolved by Flyway, not by the database.”

Typical real-world use cases (important)

✅ Environment-specific schema names

CREATE TABLE ${schema}.users (...);
schema=public in prod

schema=test_schema in tests

✅ Different DB users / roles

GRANT INSERT ON users TO ${app_user};

✅ Conditional naming (indexes, partitions)

CREATE INDEX idx_${env}_users_email ON users(email);

✅ Feature toggles in migrations (use carefully)

-- Only enabled in some environments
INSERT INTO config(key, value)
VALUES ('feature_x', '${feature_x_enabled}');

What placeholders should NOT be used for

This is where senior engineers stand out.

❌ Schema logic changes

Don’t do:

${create_users_table_sql}

That makes migrations:

• non-reviewable
• non-deterministic
• dangerous

📌 Strong interview sentence:

“Placeholders should parametrize values, not behavior.”

❌ Hiding incompatible migrations

Using placeholders to avoid running parts of migrations in prod/test is a red flag.

Safety & reproducibility concerns

Because placeholders change the executed SQL:

• The same migration version may run different SQL
• This slightly weakens reproducibility guarantees

Best practice:

• Keep placeholder usage minimal
• Use them only for names and identifiers
• Never for structural logic

---

Defaults & escaping (small but nice detail)

• Default placeholder prefix: ${
• Default suffix: }
• You can disable placeholders entirely:

flyway.placeholderReplacement=false

You can also escape:

SELECT '${not_a_placeholder}';

Placeholders vs repeatable migrations (nice comparison)

| Feature      | Placeholders     | Repeatable        |
| ------------ | ---------------- | ----------------- |
| Purpose      | Parametrize SQL  | Re-run on change  |
| Re-execution | No               | Yes               |
| Env-specific | Yes              | Sometimes         |
| Risk         | Divergent schema | Accidental re-run |

Interview-ready answer (perfect)

2–3 sentences:

“Placeholders in Flyway allow parameterizing SQL migrations so the same migration can be reused across environments with different values, like schema names or users. Flyway replaces placeholders before executing the SQL. They should be used sparingly for identifiers and configuration values, not to change migration logic.”

That answer sounds confident and senior.

Flyway integrates with Maven and Gradle via official plugins that expose Flyway commands as build goals/tasks (migrate, validate, info, baseline, repair, clean), and by letting you configure Flyway the same way you would in app runtime: URL/user/password, locations, schemas, placeholders, config files, etc.

---

Maven integration (Flyway Maven Plugin)

How you use it

• Add the Flyway plugin to pom.xml
• Run goals like:
  • mvn flyway:migrate
  • mvn flyway:validate
  • mvn flyway:info
  • mvn flyway:baseline
  • mvn flyway:repair

The plugin is designed so configuration can come from multiple places with a clear precedence order (system properties/env vars/config files/Maven properties/plugin config/settings.xml creds/~/.flyway.conf).

Typical Maven setup (conceptually)

• Put SQL migrations under the usual default: src/main/resources/db/migration (or configure locations)
• Configure DB connection in plugin config or via properties (often from CI secrets)
• Call flyway:migrate in CI, or during a dedicated pipeline stage (not necessarily on every local build)

---

Gradle integration (Flyway Gradle Plugin)

How you use it

• Apply plugin org.flywaydb.flyway (from Gradle Plugin Portal)
• Run tasks like:
  • gradle flywayMigrate
  • gradle flywayValidate
  • gradle flywayInfo
  • gradle flywayBaseline
  • gradle flywayRepair
  • gradle flywayClean (dangerous outside local/dev)

Config and config files

• You can configure via flyway { ... } in build.gradle(.kts) or use config files.
• The Gradle plugin supports ~/.flyway.conf and can load additional config files via -Dflyway.configFiles=....

---

Common patterns in real projects

1) “CI runs migrations” (most common for services)

• CI stage: validate → migrate
• App startup doesn’t need to run migrations (or you keep it enabled, but you must be intentional about it).

2) “App runs migrations on startup” (common with Spring Boot)

• Add flyway-core dependency and Flyway runs automatically at startup when configured.
• Still keep the plugin for local/CI tooling if useful.

3) Separate DB user for migrations

• Use elevated “migration user” for Flyway runs, limited “app user” at runtime.

Short definition (what you say first)

A Flyway baseline marks an existing database schema as a starting point so Flyway can begin managing migrations without reapplying old ones.

📌 One-liner:

“Baseline tells Flyway: assume the schema up to this version already exists.”

---

The problem baseline solves

Imagine this situation:

• You have a legacy database
• Tables already exist
• No Flyway history table
• You want to start using Flyway without dropping anything

If you run flyway migrate directly:

• Flyway will try to run V1__create_tables.sql
• ❌ Boom — tables already exist

👉 Baseline prevents that

---

What flyway baseline actually does

When you run:

flyway baseline

Flyway:

1. Creates flyway_schema_history
2. Inserts a baseline marker row
3. Marks all migrations below the baseline version as applied
4. Does NOT execute any SQL

Important:

• No schema changes
• Metadata only

---

Baseline version & description

Defaults:

• baselineVersion = 1
• baselineDescription = << Flyway Baseline >>

Configurable:

flyway.baselineVersion=5
flyway.baselineDescription=legacy schema

Result:

• Flyway will only apply migrations V6+

📌 Interview phrase:

“Baseline defines the first migration Flyway is allowed to execute.”

---

Typical real-world use cases (this matters)

✅ 1. Introducing Flyway to an existing database

Most common case.

• DB already in production
• Schema managed manually or by scripts
• You want Flyway from now on

---

✅ 2. Brownfield systems

• Old monolith
• Years of unmanaged schema drift
• Baseline at “current reality”

---

✅ 3. Environment alignment

• Prod already exists
• New environments start clean
• Baseline ensures consistent starting point

---

What baseline is NOT

This is where people fail interviews.

❌ Not a migration
❌ Not a rollback
❌ Not a schema sync
❌ Not something you run repeatedly

📌 Strong sentence:

“Baseline is a one-time operation per database.”

---

baselineOnMigrate (important variant)

Config option:

flyway.baselineOnMigrate=true

Behavior:

• If schema exists and
• flyway_schema_history is missing
• Flyway automatically baselines before migrate

⚠️ Use carefully:

• Safe for controlled environments
• Dangerous if used blindly in prod

Senior wording:

“baselineOnMigrate trades safety for convenience.”

Risks & best practices

⚠️ Risks

• You can lie to Flyway about schema state
• Past migrations are no longer reproducible
• Drift before baseline is untracked forever

✅ Best practices

• Baseline at a clear, documented version
• Commit migrations starting after baseline
• Never baseline repeatedly
• Never baseline to hide problems

---

Interview-ready answer (perfect)

2–3 sentences:

“A Flyway baseline marks an existing database schema as the starting point for migration tracking without executing historical migrations. It’s typically used when introducing Flyway to a legacy or already-populated database. After baselining, Flyway will only apply migrations newer than the baseline version.”

Short answer (what you say first)

Java-based migrations allow you to implement migrations that are too complex, conditional, or data-driven for plain SQL, while still being versioned and transactional under Flyway control.

📌 Interview one-liner:

“They’re for logic-heavy or DB-aware migrations where SQL becomes unsafe or unreadable.”

---

What Java-based migrations actually are

Instead of:

V5__update_accounts.sql

You write:

public class V5__UpdateAccounts extends BaseJavaMigration {
    @Override
    public void migrate(Context context) throws Exception {
        // Java logic here
    }
}

Key properties:

• Versioned (V5__)
• Ordered like SQL migrations
• Recorded in flyway_schema_history
• Can participate in transactions (DB-dependent)

Real benefits (this is the important part)

1️⃣ Complex data migrations (main reason)

Java lets you:

• Loop over rows
• Batch updates
• Apply conditional logic
• Handle edge cases explicitly

Example:

• Migrating data based on multiple columns
• Transforming JSON fields
• Backfilling data with rules

SQL for this becomes:

• unreadable
• error-prone
• impossible to test properly

📌 Interview phrase:

“Java migrations are safer for non-trivial data transformations.”

2️⃣ Conditional behavior based on environment or state

In Java you can:

• Detect DB version
• Check schema state
• React to data presence
• Feature-flag logic

Example:

if (tableExists(context, "users")) {
    migrateUsers();
}

This is hard or ugly in SQL.

3️⃣ Reuse application-level logic

You can reuse:

• Parsers
• Validators
• Mappers
• Business-safe transformations

Example:

• Reusing a JSON deserializer
• Applying the same normalization logic as the app

This reduces logic drift.

---

4️⃣ Better testing & debuggability

Java migrations:

• Can be unit-tested
• Can be debugged in IDE
• Have proper logging
• Allow structured error handling

SQL migrations:

• Fail at runtime
• Are harder to introspect

Interview bonus line:

“Java migrations are easier to reason about under failure.”

---

5️⃣ Database portability (sometimes)

Java migrations can:

• Branch by DB vendor
• Avoid vendor-specific SQL
• Share logic across engines

⚠️ Still limited by JDBC + SQL underneath, but more flexible.

---

What Java migrations are NOT good for

This is where candidates fail interviews.

❌ Simple schema changes

Bad use:

• CREATE TABLE
• ADD COLUMN
• ADD INDEX

SQL is:

• clearer
• reviewable
• auditable

Interview phrase:

“Schema DDL belongs in SQL migrations.”

---

❌ Long-running business jobs

• No retries
• No progress tracking
• Risky in deploy pipelines

---

❌ Hiding broken schema design

Java migrations are not a workaround for:

• bad modeling
• missing constraints
• poor planning

Transaction behavior (important!)

• Java migrations are transactional if the DB supports it
• Same rules as SQL migrations
• One connection per migration

So:

• Postgres → transactional
• MySQL (DDL) → often not

Interview-safe phrasing:

“Transactional behavior depends on the database, not the migration type.”

---

Trade-offs (show maturity)

Downsides:

• Harder to review than SQL
• Requires recompilation
• Ties migration to application code
• Can be abused as “logic dumping ground”

Senior comment:

“Java migrations should be rare and justified.”

---

When I’d explicitly choose Java migrations

Say something like this in an interview:

“I use Java-based migrations when I need complex, conditional, or data-heavy transformations that are unsafe or unreadable in SQL — especially when correctness matters more than simplicity. For pure schema evolution, I stick to SQL.”

That sentence alone sounds very senior.

---

Interview-ready final answer (perfect)

2–3 sentences version:

“Java-based migrations are useful when a migration requires complex logic, conditional behavior, or data transformations that are hard to express safely in SQL. They allow reuse of Java code, better error handling, and easier testing while still being versioned and tracked by Flyway. For simple schema changes, SQL migrations are usually the better choice.”

What are Flyway callbacks?

Callbacks are hooks that Flyway executes at specific points of the migration lifecycle.

They let you run custom logic:

• before / after migrations
• before validation
• after repair
• on success / on error

📌 Key idea:

“Callbacks allow you to react to migration lifecycle events.”

beforeMigrate: what exactly does it do?

beforeMigrate is executed:

• Right before migrate starts
• After validation
• Before any SQL migration is applied

Lifecycle order (simplified):

validate
→ beforeMigrate
→ migrate (V__ and R__)
→ afterMigrate

Important:

• If beforeMigrate fails → no migrations run
• It acts like a gatekeeper

How callbacks are implemented

Flyway supports two main ways:

1️⃣ SQL-based callbacks (most common)

File naming convention:

beforeMigrate.sql
afterMigrate.sql
beforeValidate.sql
afterRepair.sql

Placed in:

• the same locations as migrations
• or configured callback locations

Example:

-- beforeMigrate.sql
SELECT current_database();

Typical use:

• sanity checks
• logging
• lightweight validations

2️⃣ Java-based callbacks (advanced / enterprise)

Implement Flyway’s callback interface:

public class BeforeMigrateCallback implements Callback {
    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.BEFORE_MIGRATE;
    }

    @Override
    public void handle(Event event, Context context) {
        // custom logic
    }
}

Use cases:

• environment checks
• feature flags
• complex validations
• metrics / audit integration

📌 Interview note:

Java callbacks are evaluated inside the Flyway process, not the DB.

Typical real-world use cases for beforeMigrate

✅ Good uses

• Ensure correct schema / user / DB
• Check application version compatibility
• Validate prerequisites (extensions, roles, settings)
• Acquire advisory locks
• Emit audit / metrics events

Example (Postgres):

DO $$
BEGIN
  IF current_user != 'migration_user' THEN
    RAISE EXCEPTION 'Invalid DB user';
  END IF;
END $$;

❌ Bad uses (red flags)

• Schema changes
• Data migrations
• Fixing broken history
• Business logic

Interview phrase:

“Callbacks should validate or observe, not mutate schema.”

Failure behavior (important)

If beforeMigrate:

• throws an error
• raises an exception
• returns failure

➡️ Flyway aborts migration immediately

No partial execution.

This makes callbacks ideal for fail-fast safety checks.

---

Relation to other Flyway features

Feature	Purpose
validateOnMigrate	Detect schema drift
beforeMigrate	Enforce runtime safety
Versioned migrations	Apply irreversible changes
Repeatable migrations	Rebuild derived objects

Good interview sentence:

“Validation checks history, callbacks check runtime conditions.”

---

Common pitfalls

❌ Putting business logic in callbacks
❌ Relying on callbacks for data correctness
❌ Assuming callbacks are transactional
❌ Making callbacks environment-specific without guards

Remember:

• Callbacks may or may not be transactional, depending on DB & event
• Don’t rely on rollback semantics

---

Interview-ready answer (perfect)

Short answer (2–3 sentences):

“Flyway callbacks like beforeMigrate are lifecycle hooks executed at specific points during migration. beforeMigrate runs after validation and before any migrations are applied, and if it fails, migration is aborted. They’re typically used for safety checks, logging, or environment validation, not for schema or data changes.”

Flyway detects checksum mismatches during validation and fails migration to prevent schema drift.
A mismatch means an already-applied migration file was changed.

Key phrase:

“Checksum mismatches indicate a violation of migration immutability.”

How Flyway detects a checksum mismatch

Flyway stores, for each applied migration, in flyway_schema_history:

• version
• description
• checksum
• success

When you run flyway migrate (with validateOnMigrate=true, default):

1. Flyway recalculates the checksum of local migration files
2. Compares it to the stored checksum
3. If different → validation fails

Typical error:

Validate failed: Migration checksum mismatch for migration V3__add_index.sql

Why checksums change (important distinction)

🟡 Harmless causes

• Whitespace changes
• Line endings (CRLF ↔ LF)
• Comments
• SQL formatting

🔴 Dangerous causes

• Changed SQL logic
• Modified DDL
• Changed data migration
• Reordered statements

Flyway cannot tell the difference — it only sees the checksum.

---

What Flyway does when a mismatch occurs

By default:

• ❌ Migration is aborted
• ❌ No new migrations are applied
• ❌ Startup fails (in Spring Boot)

This is intentional and correct behavior.

Interview phrase:

“Flyway fails fast to prevent silent divergence.”

Correct ways to handle checksum mismatches

✅ Option 1: Restore the original migration (BEST)

If the migration was changed accidentally:

• Revert the file to its original state
• Re-run migrate

This preserves immutability.

---

✅ Option 2: Create a new migration (MOST COMMON)

If the change was intentional:

• Undo the modification
• Add a new migration:

V4__fix_index_definition.sql

This is the recommended production approach.

⚠️ Option 3: flyway repair (ONLY in safe cases)

Use repair only if:

• The change is formatting-only
• No logical behavior changed
• You are 100% sure the DB state is correct

What repair does:

• Updates stored checksum to match the file
• Makes validation pass

Interview-safe wording:

“Repair is acceptable only for non-functional changes.”

What NOT to do (big red flags)

❌ Disable validation:

flyway.validateOnMigrate=false

❌ Edit flyway_schema_history manually
❌ Ignore the error and push to prod
❌ Use repair to hide real schema problems

These answers fail interviews immediately.

Repeatable migrations: special case

For R__ migrations:

• Checksum changes are expected
• Flyway automatically re-runs them
• No failure occurs

That’s why:

• Repeatables are for regeneratable objects
• Versioned migrations must be immutable

Typical production incident (real-life)

“Someone reformatted an old migration, CI passed locally, but prod failed on startup.”

Correct response:

1. Identify the change
2. If formatting-only → repair
3. If logic → revert + new migration
4. Re-run pipeline

Interview-ready answer (perfect)

Short answer (2–3 sentences):

“Flyway detects checksum mismatches during validation by comparing applied migration checksums with local files and aborts migration if they differ. This protects against modifying applied migrations and prevents schema drift. The correct fix is usually to restore the original migration or create a new one; repair should be used only for non-functional changes.”

That answer is senior-level and safe.

validateOnMigrate tells Flyway to validate applied migrations against the local migration files before running migrate.
If validation fails → migration stops.

📌 One-liner:

“It ensures the database history matches the migration files before applying new migrations.”

What “validation” actually checks

When validateOnMigrate=true, Flyway compares:

1️⃣ Applied migrations vs local files

• Same versions
• Same descriptions
• Same checksums

2️⃣ Detects dangerous situations

Flyway will fail startup if it finds:

• ❌ Modified versioned migrations
• ❌ Missing migration files
• ❌ Out-of-order versions (unless explicitly allowed)
• ❌ Failed migrations in history
• ❌ Checksum mismatches

This happens before any SQL is executed.

What happens during migrate

Order of operations:

1. Validate (if validateOnMigrate=true)
2. Abort if validation fails
3. Apply pending migrations

So:

validate → migrate

Not:

migrate → hope for the best

Why this setting exists (real reason)

Without validation:

• A developer can modify an old migration
• CI might still pass
• Prod may silently diverge
• You lose schema reproducibility

With validation:

• Drift is detected early
• Startup fails loudly
• Teams are forced to fix history properly

📌 Interview phrase:

“It enforces immutability of applied migrations.”

Default value (important!)

• Flyway default: true
• You must explicitly disable it

flyway.validateOnMigrate=false

⚠️ Disabling it is usually a red flag

When would you ever disable it?

⚠️ Rare, controlled cases only

• Legacy databases with known drift
• Temporary migration cleanup phase
• One-time bootstrap of existing schema

Even then:

• Disable temporarily
• Re-enable ASAP

Good interview line:

“Disabling validation should be a short-lived exception, not a standard configuration.”

Relation to flyway repair

Very important distinction:

| Feature             | Purpose         |
| ------------------- | --------------- |
| `validateOnMigrate` | Detect problems |
| `flyway.repair()`   | Fix metadata    |

Workflow:

validate fails
→ investigate
→ fix scripts OR run repair
→ migrate

Never:

validate fails
→ disable validation
→ migrate anyway ❌

Typical failure example

If someone edits an old migration:

Validate failed: Migration checksum mismatch for version 3

Correct response:

• Restore original migration
• Or introduce a new migration
• Or (rarely) run repair after safe formatting changes

Interview-ready answer (perfect)

Short answer (2–3 sentences):

“validateOnMigrate makes Flyway validate the applied migrations in flyway_schema_history against the local migration files before running migrate. If it detects checksum mismatches, missing migrations, or failed entries, migration is aborted. It’s a safety mechanism that enforces immutability and prevents schema drift.”

That’s a strong senior answer.

Short definition (what interviewers expect first)

flyway.repair() is a maintenance command that fixes Flyway’s metadata table (flyway_schema_history) so migrations can proceed safely again.
It does not change the database schema — it only repairs Flyway’s internal state.

📌 Key phrase:

“Repair fixes metadata, not the schema.”

What exactly does flyway.repair() do?

1️⃣ Removes failed migrations

If a migration failed halfway:

• success = false remains in flyway_schema_history
• Flyway will block all future migrations

repair():

• Deletes failed rows
• Allows you to fix the SQL and re-run

✅ This is the most common real-world use case

2️⃣ Recalculates checksums

If someone:

• reformatted SQL
• changed line endings
• updated comments
• or fixed whitespace

➡️ The checksum no longer matches

repair():

• Recomputes checksums
• Updates flyway_schema_history

⚠️ Only safe if the change is logically equivalent

3️⃣ Marks missing migrations as deleted (optional behavior)

If a migration file:

• existed before
• was applied
• but later removed from the repo

Flyway sees it as missing

repair():

• Marks it as deleted = true
• Avoids startup failure

📌 This is common after refactoring or cleanup.

What flyway.repair() does NOT do (important!)

❌ It does NOT:

• Roll back migrations
• Fix broken schemas
• Re-run migrations
• Apply new migrations

This sentence is gold in interviews:

“Repair only adjusts Flyway’s bookkeeping; it never touches actual database objects.”

---

When should you use it?

✅ Correct scenarios

• A migration failed and you fixed the SQL
• Non-functional SQL changes broke checksums
• You removed obsolete migrations intentionally
• CI/CD pipeline is blocked due to metadata mismatch

❌ Dangerous scenarios (red flags)

• To “hide” a broken production migration
• To bypass a real schema inconsistency
• Without understanding why Flyway is failing

Interview trap:

“Repair is not a substitute for proper migrations.”

---

Typical production workflow

# 1. Migration fails
flyway migrate  ❌

# 2. Fix the migration script
# 3. Repair metadata
flyway repair

# 4. Retry
flyway migrate  ✅

Relation to repeatable migrations (bonus)

• Repeatable migrations (R__) rely on checksums
• repair() updates stored checksums
• Allows repeatables to re-run correctly

But:

• If you changed logic, not formatting → you must understand the impact

Interview-ready answer (perfect)

Short version (2–3 sentences):

“flyway.repair() fixes inconsistencies in the flyway_schema_history table by removing failed migration entries, recalculating checksums, and marking missing migrations as deleted. It does not change the database schema, only Flyway’s metadata. It’s typically used after fixing a failed migration or resolving checksum mismatches.”

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