When working locally, it's easy to forget that the database and the code are two separate things. I change a model, write a migration, run it, and start the application. If something doesn't work, I fix it and try again. By the end, the application finds exactly the database schema it expects.
In production, things don't always work that way. An application may have several instances, a deployment may update them one by one, and a background process might keep running the previous version of the code even after the release is complete. The database, however, is shared: a change to its schema becomes visible to everyone at once.
Even something as simple as renaming a column is enough to see the difference.
Renaming a column
Suppose we want to rename users.name to users.display_name. It seems straightforward: one migration, a few references to update in the code, and the job is done.
At least as long as we can afford to stop everything.
If the service needs to stay available, we have to decide in which order to deploy the changes. If we rename the column first, instances still running the old code will keep looking for name, which no longer exists. If we deploy the code first, the new instances will look for display_name while the database still only has name.
To avoid this, we spread the change across several releases. First, we add display_name without touching the original column. Then we update the application to write to both columns while continuing to read from name. Once every instance is running this version, we can copy the existing data into the new column. We still need to handle any writes that happen during the copy.
After checking that the data is in sync, we switch reads to display_name. The old column will be removed in a later release, once we're sure no process still uses it.
This approach is known as expand and contract: first, we expand the schema to maintain compatibility with existing versions, then remove what is no longer needed.
It takes more steps, but it allows different versions of the application to run alongside each other during an update. Not every project needs this approach. For an internal business application with just a few users, a maintenance window might make much more sense. We just need to remember that a deployment doesn't always offer the same conditions as our development environment.
Migrations have a history
Once a migration has been deployed, I don't go back and change it.
During initial development, I can rewrite it as often as I like, as long as it's only been applied to databases I can freely recreate. After the first release, though, I treat that file as immutable.
Migration tools keep track of which migrations have already been applied. If I modify an existing migration, databases that have already run it won't automatically run the modified version. Databases created from scratch, on the other hand, will start with the updated schema.
I could end up with two installations of the same software version running different database schemas. The code is identical, tests against a clean database pass, but the behavior depends on the installation's history. The problem is difficult to diagnose because the code I'm looking at isn't enough to explain it.
That's why, when a migration that's already been deployed contains a mistake, I add another one to fix it. If a column has the wrong name, I write a new migration to rename it; if an index needs changing, I add a migration to replace it.
The sequence of migration files should reflect the changes the database has actually been through. Rewriting that history would break the correspondence between what's in the repository and what really happened in existing installations.
Test the path, not just the result
The same care applies to testing. A test suite that always creates the database from scratch verifies that the final schema is valid, but tells us little about what will happen during an update.
Adding a NOT NULL column without a default value can work perfectly on an empty table and fail as soon as it encounters an existing row. A uniqueness constraint can run into duplicates nobody expected. Even a valid migration can hold a lock for too long when run against a table with millions of rows.
That's why I prefer to test migrations starting from the previous version's schema, using a database populated with realistic data. On projects that support multiple database backends, I repeat the test on each one, because PostgreSQL, MySQL, and SQLite don't always behave the same way when the schema changes.
You don't need elaborate infrastructure. An anonymized database dump or a script that generates representative data can already reveal problems that an empty database never will.
These tests have their limits, though: realistic data doesn't necessarily mean realistic load or concurrency. With larger tables, I also need to consider how long the operation will take and which queries might be blocked.
When we deploy a new version of an application, we can replace the old code. The database, however, retains its data, its schema, and the decisions made in previous releases. Migrations have to work with what's already there, including the intermediate stages when the old code is still running.