Great question! This is a super common scenario when using Flyway in production—you need to run one-off data fixes, maintenance tasks, or ad-hoc DML changes that don’t belong in your core DDL version control. Let’s break down the established patterns and built-in features to handle this cleanly:

1. Repeatable Migrations (For Re-Runnable Maintenance Tasks) #

If your DML script needs to be executed multiple times (e.g., monthly data cleanup, reindexing, or syncing reference data), Flyway’s repeatable migrations are perfect. These are scripts that Flyway will re-run whenever their content changes, and they’re not tied to a version number.

• Naming convention: Start with R__ (e.g., R__clean_old_logs.sql, R__refresh_product_catalog.sql)
• How it works: Flyway calculates a checksum of the script each time it runs. If the checksum differs from the last recorded value in the schema_history table, it executes the script again.
• Use case: Ideal for maintenance tasks that need to be updated and re-run periodically, without cluttering your versioned DDL migration history.

2. One-Off Ad-Hoc Fixes (Without Cluttering Version History) #

For one-time DML fixes (e.g., correcting a batch of bad user data, updating a configuration value), you have a couple of options depending on whether you want to track the execution in Flyway’s history:

Option A: Track the Fix with a "Patch" Migration #

If you want to keep a record of the fix in schema_history but don’t want it to be part of your core application’s versioned DDL, you can use a versioned migration with a special naming convention to mark it as a patch. For example:

• V1.2.3__patch_fix_user_email_data.sql
• This way, it’s executed in order with other migrations, and you have an audit trail of the fix. Just make sure to communicate with your team that these are one-off patches, not part of the regular application schema evolution.

Option B: Execute Without Tracking (Flyway Teams Edition) #

If you don’t want the script to appear in schema_history at all, Flyway Teams Edition includes the flyway execute command. This lets you run a single script directly, without adding it to the migration history.

• Command example: flyway execute -sqlScript=path/to/your/fix_script.sql
• Use case: Quick, temporary fixes where you don’t need a permanent audit trail in Flyway (though note that database logs will still record the changes).

Option C: Manual Execution (Community Edition Workaround) #

If you’re using Flyway Community Edition and don’t want to track the script, you can execute it directly via your database client (e.g., psql, SQL Server Management Studio). Just make sure to:

• Document the fix thoroughly (including why it was needed, when it was run, and who executed it)
• Test the script in a staging environment first to avoid production issues

3. Callbacks (For Automated Maintenance Tasks) #

Flyway Teams Edition also supports callbacks, which let you run scripts automatically before or after specific Flyway events (e.g., before migrate, after baseline). This is useful for automated maintenance tasks that need to run alongside your regular migrations.

• Example: A post-migrate callback that updates statistics or refreshes materialized views after new DDL is applied.
• You can define callbacks in the flyway.conf file or via command-line arguments.

Key Best Practices #

• Always test scripts in staging first: Whether it’s a repeatable migration or a one-off fix, never run untested DML in production.
• Document everything: For one-off fixes, keep a log outside Flyway (e.g., in your issue tracker or internal docs) explaining the context and impact.
• Separate concerns: Keep your core versioned DDL migrations focused on schema evolution, and use repeatable migrations or ad-hoc scripts for data changes.

内容的提问来源于stack exchange，提问作者Michael Remijan