Flyway for freeloaders, rollback implemented in Python

This article presents an ingenious Python-based solution to implement rollback functionality for Flyway Community Edition, a feature typically locked behind the more expensive Flyway Teams license. The author demonstrates how to achieve managed rollbacks without using Flyway's proprietary undo command, leveraging existing Flyway capabilities and a "devious" approach to migration management. The core idea is to treat "rollback" as just another "migration in a wig," satisfying Flyway's requirement that version numbers always increase.

• Rollback as Forward Migration: The fundamental insight is that Flyway Community will execute any versioned SQL script. Instead of attempting to reverse an existing migration (V2), the solution creates a new, higher-versioned migration (e.g., V3__undo_V2.sql) whose content reverses V2. This allows Flyway to process it as a regular forward migration.
• Directory Structure: The setup requires two parallel directories: migrations/ for standard upward migrations and rollbacks/ containing corresponding .down.sql scripts for each migration. The naming convention (V<version>__*.down.sql) is crucial for linking.
• Batch Tracking: To enable intelligent rollbacks, the system records the batch of migrations applied during the most recent flyway migrate command. This is achieved by comparing applied versions before and after a migration and storing the new versions in a JSON state file.
• Staging Rollback Scripts: When a rollback is initiated, the relevant .down.sql scripts from the recorded batch are staged in a temporary directory. Their versions are incremented sequentially above the current highest applied migration (e.g., V2's undo might become V3), ensuring Flyway's "versions must go up" rule is maintained, and the batch is reversed so the last migration applied is undone first.
• Schema History Alignment: A critical step is to reconcile Flyway's flyway_schema_history table. An afterMigrate.sql callback is staged alongside the "down" scripts. This callback runs after the undo migrations succeed and deletes the entries for both the original migrations and their undo counterparts from the history table, making it appear as if the original migrations never happened.
• Atomicity: The entire rollback process is made atomic by using Flyway's -group=true flag. This ensures that all "down" scripts and the history cleanup callback execute within a single database transaction, rolling back the entire operation if any part fails.
• Limitations: The author transparently notes limitations, including the lack of data loss accounting (as with paid Flyway), the need to manually write .down.sql scripts, reliance on the container's filesystem for state, and the "evil" act of modifying Flyway's flyway_schema_history table directly.
• Benefits: Despite its "devious" nature, the solution provides a rollback that runs through Flyway's engine with transactional guarantees. It leaves the schema history clean, presenting rolled-back migrations as pending again, ready for re-application or remediation, all without incurring licensing costs.

This method offers a clever, practical, and open-source alternative to Flyway Teams' rollback feature. By intelligently using Flyway's existing mechanics and carefully managing the schema history, developers can achieve robust, transactional rollbacks in production environments for free.