Flyway for Freeloaders — Rollback without Teams

find me @ aconcan.io

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.

Bringing the flyway_schema_history table into alignment

If we stopped here, after staging only the "down" scripts and running migrate, flyway info would show records for V3__undo_V2, V4__undo_V1, despite the fact that these migrations don't exist as files in our migrations directory (and despite the fact that they cancel out the logic of previous migrations in the database, returning it to initial state). Because these migrations don't exist as files, Flyway will now flag the history as out of sync and blow up on future validate and migrate commands.

So the last file we stage in our temporary directory along with the "down" scripts is an afterMigrate.sql callback, which deletes the round-trip rows from the history table:

    # Delete rows for both the original migrations and their respective "down" migrations
    versions = ", ".join(f"'{v}'" for v in batch + undo_versions)
    (staging / "afterMigrate.sql").write_text(
        f"DELETE FROM {config.schema}.flyway_schema_history "
        f"WHERE version IN ({versions});\n"
    )

We delete both sides of the round trip, the originals (1, 2) and the undo entries (3, 4). 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. If any pending migration fails, the whole batch rolls back.
    args = _base_args(config, locations) + ["-group=true", "migrate"]
    return subprocess.run(args).returncode

-group=true wraps all the pending migrations in a single transaction. Either every "down" script and the purge callback commit together, or Postgres rolls the whole thing back and your database is untouched. There's no state where V2 got undone but V1 didn't. (This does rely on your DDL being transactional)

And the assembled rollback puts it all together:

def rollback(config: PgConfig, scope: str) -> int:
    # Read the batch of just-applied migration versions from the state file
    batch = state.read_batch(scope)
    if not batch:
        raise RollbackError(f"no recorded migration batch to undo for {scope}")

    # Verify every down script exists
    for version in batch:
        _down_file(config, version)

    # Stage temporary flyway-versioned down scripts and callback, then call migrate
    with tempfile.TemporaryDirectory() as tmp:
        _stage(config, batch, Path(tmp))
        code = flyway.migrate(config, [config.migrations, tmp])

    # Clear the state file of migrations just applied (the initial batch has been rolled back)
    if code == 0:
        state.clear_batch(scope)
    return code

The ordering here is deliberate:

1. No batch? Hard fail Rolling back with nothing recorded is a mistake. If the rollback isn't occurring in the current session, it's likely rolling back previous migrations that have been in place for some time; in which case they should be fixed forward, not rolled back.
2. Check every "down" script exists before touching the database. If you're missing the .down.sql for even one version in the batch, it's time to fail. Much like an arrow to the knee, half a rollback is worse than none.
3. Migrate over both locations Notice Flyway needs the real migrations directory and the temp staging dir. Flyway needs the originals in its locations to reconcile history, while the temp dir supplies the new down versions.
4. Only clear state on success If the migrate returns non-zero, the batch file stays as is so you can fix the problem and retry.

The temp directory evaporates with the with block. Upon completion, the temporary directory and all of its contents are automatically deleted. The Flyway specific versions of the "down" files exist for exactly one Flyway invocation, and then they're gone.

Limitations

Honestly, I don't think there are many hairy parts in the solution, but it's worth mentioning some of the limitations to know where the edges lie.

• Nothing in this solution accounts for data loss: This leaves you in the same position you'd be in if you had actually paid for Flyway Teams, but I have to mention it anyway because transparency is my passion. If your "up" script does something irreversible like dropping a column that has data in it, I hope you didn't really need it.
• You write the "down" scripts by hand: There's no auto-generated inverse, but guess what, you also need to do this if you pay for Flyway teams.
• State lives on the container filesystem: Rollback context is tied to that filesystem's lifetime. Blow away the container between migrate and rollback and you've lost the batch. (This doesn't apply to you stateless enjoyers)
• We delete rows from flyway_schema_history: Flyway purists will feel a chill up their very erect spines. I'm comfortable with it because the deletion is scoped to exactly the versions in the round trip and runs inside the same transaction as the reversal. That said, you are editing Flyway's bookkeeping out from under it. Know that you're doing it.

Why this is actually nice

Even though this solution is 2 evil Flyway operations on top of eachother in a trenchcoat, the result is a rollback that's honest.

It runs your reversal SQL through the same engine, with the same transactional guarantees as your forward migrations. Modification of the flyway_schema_history table is done via a Flyway supported feature. You're pretty protected from weird half-jobs presuming you do use database transactions. When it's done, your schema history is clean, and the migrations you rolled back are simply pending again, ready to re-apply (or I assume remediate).

No license. No undo command. Just a temp directory, a version counter that only goes up, and one saucy DELETE. A story for the ages.