FDE Times dataFDE jobs open 881▼8 Change in open roles over 7 days, like-for-like: roles from companies we started tracking this week are left outNew in 7 days 55Companies hiring 185Remote-friendly 25%Median US pay $201kTop hirer Databricks 98
中文VI

The newspaper of the Forward Deployed Engineer

Tools

Why you must never edit a migration that already ran for a client

Flyway, Rails and Liquibase all keep a record of every migration they apply, so the right fix is always a new file, never an edit.

ShareLinkedInFacebookX

In brief

  • Each time it runs a migration, Flyway stores a CRC32 checksum in the schema history table. The validate command fails if the file on disk no longer matches.
  • An applied migration should not be edited. To make a change, restore the original file and write a new migration to roll forward.
  • Rails and Liquibase follow the same principle. Manual changes made outside migrations, however, are invisible to checksums.
Two ways of handling a change of the weight column from int to bigint, side by side. On the left, V002 is edited after it has already run (shown in red, labelled "edited"): staging still has int, while a freshly built database has bigint, and the validate command reports a checksum mismatch. On the right, V002 is restored to its original and a V004 is added (shown in orange): staging and the new database both have bigint, so all environments match.
Editing a migration that has already run leaves a customer's database out of step with the repo. Adding a new migration puts every environment through the same steps. Source: Flyway documentation, following the example in the article.

Change a single int to bigint in a migration file that has already run, and Flyway can halt the whole pipeline. The tool is not being fussy for the sake of it. It is telling you that the client’s database and the file in your repo now tell two different stories.

You bring a new feature to a client, and that feature needs an extra column or table. The same change has to run on your machine, in the test environment and on the client’s database, where you may have access for only a few hours.

Flyway is a Redgate product, which the company presents as a database change management solution. Its core is open source under the Apache 2.0 licence, maintained by Redgate together with many contributors. Understanding why Flyway forbids editing old files is also understanding how it keeps environments from drifting apart.

Each file is a version, and versions must not repeat

Versioned migrations follow a naming convention that the Flyway documentation illustrates with the example V001.002__NewTwitterColumn.sql: the letter V, a version number, two underscores, then a description. Each versioned migration must have a unique version.

When you run migrate, Flyway first applies pending versioned migrations in version order, then any repeatable migrations that have changed. The folder for a logistics company’s order system might look like this:

sql/
  V001__create_orders.sql
  V002__add_weight_column.sql
  V003__add_delivery_window.sql

If the client’s database has already run up to V002 and you bring V003, only V003 is pending. The documentation’s example uses numbers with leading zeros. It is worth keeping that numbering from day one, so anyone opening the folder sees the files in the right order too.

Checksums remember what has run

Each time it executes a migration, Flyway stores its checksum; for SQL files this is CRC32. The schema history table in the target database records the applied migrations along with each file’s checksum. The validate command fails if a migration’s name, type or checksum differs from what was applied.

Now go back to the opening scenario. V002 creates a weight column of type int and has already run on the client’s staging environment. An engineer decides the column should be bigint, opens V002 directly, edits it and commits.

Staging still has an int column, while any database built fresh from the repo will have bigint. Nobody notices the two databases have diverged until validate runs and reports that the versioned migration’s checksum does not match the record in schema history. That error stops you before the divergence reaches production.

The right fix: restore the original, then keep going

The Flyway documentation treats not editing applied migrations as best practice, and recommends a new migration to roll forward. In the example above, you return V002 to its exact original content, then add a file V004__widen_weight_to_bigint.sql containing an ALTER statement that changes the column type.

From then on every environment goes through the same steps. Staging runs only V004. A new database runs the original V002, then on through V004, so the end result is identical and the history records exactly what happened.

Not just Flyway

This principle is not one tool’s quirk. Rails tracks migrations that have run through the schema_migrations table, so an applied file that is later edited will not be run again. That is why the Rails guides advise writing a new migration rather than editing one already committed.

Liquibase recalculates a changeset’s checksum each time it runs or checks it, and compares it with the stored value. The fix Liquibase suggests almost mirrors the example above: restore the original changeset, then add a new changeset to change the column from int to bigint.

So on any client project, whatever the stack, ask two questions in the first week. Which table does the migration tool store its history in, and does it compare checksums?

What can checksums not catch?

Checksums protect only the files. If someone on the client side opens a SQL client and adds an index by hand, no file changes, so validate has nothing to report.

That is where a drift report helps. The Flyway Check tutorial for SQL Server uses the -drift option to produce a report listing schema objects that were not created by any applied migration.

Imagine running this report on the client’s staging environment at the start of an engagement: if that hand-added index shows up, you ask the database owner whether it should stay, then capture it in a new migration so the repo and the database match again.

There is one more limit: correct ordering does not make a bad SQL statement safe. A migration that locks a large table at peak hours still locks it, however properly it is versioned. Writing migrations that run against the client’s real data is still your job.

What to learn first, and what to put on your CV

Learn in this order: the naming convention, how migrate picks pending files, then how to read the schema history table. Leave validate and drift reports for last, because only once you understand what the tool records can you read its errors correctly.

On your CV, do not just write “knows Flyway”.

When a job posting mentions client databases, have a story like this ready for the interview.

You will not be the last person to touch the client’s database. A chain of migrations that nobody has quietly edited is the most trustworthy thing you can leave for whoever comes next.

Was this article useful?

Use with your AI assistantAsk Claude ↗Ask ChatGPT ↗
9 sources
Read next on the roadmap · Stage 2: Broad engineeringThe client's experts, not engineers, should build your golden datasetLabel Studio is open source and runs from one Docker container, so legal staff and doctors can label data inside the client's own infrastructure.