aconcan

A single command to roll back applied migrations with Flyway Community Edition, no license, no undo command, no crying in the club. If you’ve ever priced Flyway Teams just for the undo command, you know what I’m talking about. Managed rollback functionality is the most delectable morsel that lives behind the Flyway paywall, and it’s the one feature everyone wants at 2am when a migration hits the fan in prod. The Community edition gives you migrate, info, validate, repair, and stubs its cigarette out in your eye when you ask it to go backwards. A solution you ask? It turns out you don’t need the paid undo, you just need to be a little bit devious about what “rollback” actually is. The following rollback implementation is achieved entirely via Flyway commands, without running any SQL directly against the database via a separate driver (#minimalism). Here’s how it works, buckle up girls. The Core Concept: Rollback is just Migration in a Wig Here’s the mental unlock. Flyway Community will happily run any versioned migration you hand it. It doesn’t care whether that migration creates a table or drops one, SQL is SQL. There are however, two things Flyway is very passionate about, and this solution is built around them: version numbers must always go up and the schema history table is bible. So instead of asking Flyway to reverse V2, we write a new, higher-versioned migration whose body happens to be the reverse of V2, and we ask Flyway to migrate forward into it. Basically an “undo” expressed as migrate, and the database ends up back where it started. Then we clean up the paper trail of the migrations, and their respective “undo” migrations, in the flyway_schema_history table so it lines back up with the database state, and the files on disk. What you need on disk Two directories per scope. Your intended migrations, and a parallel set of “down” scripts that reverse the logic applied by these migrations. Something like: migrations/ V1__create_customers.sql rollbacks/ V1__create_customers.down.sql The naming convention is load-bearing. “down” scripts should be matched to their initial migration by the V<version>__ prefix. As part of the rollback the down script versions will be incremented, so Flyway sees them as new migrations to be applied (we’ll get to this shortly). I’d recommend validating that there is equivalent “down” script for every migration ready to go up front, so nothing is found to be missing mid-rollback: def _down_file(rollbacks: Path, version: str) -> Path: matches = sorted(rollbacks.glob(f"V{version}__*.down.sql")) if not matches: raise RollbackError(f"no down script for V{version} in {rollbacks}") return matches[0] What are we actually rolling back? An important question: when someone runs rollback, what exactly are they rolling back? The last migration? The last batch? What if the last migrate applied three files at once? This is solved by recording the batch of successfully applied migrations whenever flyway migrate is run. This is done by getting a diff the applied versions before and after the migrate, and storing the applied versions in a JSON file. Basically, whatever versions are new is “the batch”. def migrate(config: PgConfig, scope: str) -> int: # Get currently applied versions using info -outputType=json before = set(flyway.applied_versions(config)) # Run migrate code = flyway.migrate(config, [config.migrations]) if code != 0: return code # Get diff of the migrations applied before and after `migrate` after = flyway.applied_versions(config) batch = [v for v in after if v not in before] # Store to a JSON statefile if batch: state.write_batch(scope, batch) return 0 Write it to a path in your container that will survive the separate migrate and rollback invocations if you’re doing this via a process driven CLI approach. There’s a quiet piece of defensiveness in if batch:. If you run migrate twice by accident, the second run applies nothing new, batch is empty, and the recorded batch isn’t overwritten with []. Staging the rollback scripts Now that we’ve persisted a record of what was applied on the last migrate invocation, we know exactly what we’re rolling back. We can start to stage the “down” migrations with modified version prepends to satisfy the Flyway condition of “versions must always go up”. Given a batch like [1, 2], we build a temp directory full of synthetic migrations, purely for Flyways consumption: def _stage(config: PgConfig, batch: list[str], staging: Path) -> None: # Get the highest version applied highest = max(int(v) for v in flyway.applied_versions(config)) # Reverse the order and increment sequentially from the highest of the currently applied versions undo_versions = [] for offset, version in enumerate(reversed(batch), start=1): new_version = str(highest + offset) undo_versions.append(new_version) sql = _down_file(config, version).read_text() (staging / f"V{new_version}__undo_V{version}.sql").write_text(sql) Now we have all the “down” files that will be used in the rollback in a format that Flyway will readily apply. Two things worth noting: 1. We reverse the batch. If V1 was applied and then V2, V2 must be undone before V1. So the last-applied migration is undone first, and gets the lowest new version number. Batch [1, 2] with a current high-water mark of 2 becomes: V3__undo_V2.sql V4__undo_V1.sql 2. Version numbers keep climbing. We never reuse 1 or 2. Flyway’s cardinal rule is that versions only go up, so undo scripts are numbered above the current maximum. This is what lets us sneak the reversal past Community edition as an ordinary forward migrate.

When the dust settles, the history table has no memory that any of this ever happened. The customers table is gone, V1__create_customers.sql is still sitting on disk, and flyway info cheerfully reports it as Pending again, ready to re-apply, as if you’d never migrated. afterMigrate runs inside Flyway’s own execution, after the versioned migrations succeed. It fires only if the migration succeeded. Making it atomic The whole reason you want rollback functionality is that things go wrong. So the rollback itself had better not leave you half-reversed. This is where we lean on a Flyway flag: def migrate(config: PgConfig, locations: list[str]) -> int: # Run as a single db transaction.