An application's code is versioned with Git, reviewed and tested. The database schema, on the other hand, is still often changed by hand, with a command run directly on the production server. The result is that nobody knows for sure what structure each environment has, and an update can fail because production is missing a column that exists in development. Migrations solve this problem by treating schema changes as code.
What a migration is
A migration is a file describing a single change to the schema: creating a table, adding a column, creating an index. The files are numbered or dated and applied in order:
migrations/
0001_create_customers.sql
0002_create_orders.sql
0003_add_customer_phone.sql
0004_index_orders_date.sql
The database keeps a service table recording which migrations have already been applied. When you run the migration tool, it compares the list of files with the table and applies only the missing ones. This way every environment, from development to production, reaches the same schema by following the same steps.
Almost every web framework includes one, and there are language-independent tools such as Flyway or Liquibase. The principle is always the same.
The basic rules
- A migration that has been applied is never edited. If it contains a mistake, you write a new migration that fixes it. Editing a file that has already run elsewhere creates different environments with the same «version».
- Migrations live in the repository, alongside the code that depends on them, and go through review like any other change.
- Each migration does one thing, so it is easy to understand what changes and trace a problem if something goes wrong.
- They are tested on a realistic copy of production data, not just on an empty database: an operation that is instant on a hundred rows can take hours on a hundred million.
The problem with changes in production
When the application is in continuous use, a migration must take two factors into account.
Table locks
Some operations lock the table for their entire duration. Depending on the database and version, adding a column with a default value, changing a column's type or creating an index can prevent reads or writes until they finish. On a large table, that means a stalled application.
Many databases offer non-blocking variants. In PostgreSQL, for example, an index can be created without blocking writes:
CREATE INDEX CONCURRENTLY idx_orders_date ON orders (order_date);
It is slower and cannot run inside a transaction, but the application keeps working. Before running a migration on a large table, always check your database's documentation for the type of lock it requires.
Old code and new code together
During a release, the previous version of the application and the new one coexist for a while. If the migration renames a column, the previous version stops working the moment the migration is applied.
Expand, then contract
The most widely used technique for changes without downtime is called expand and contract: first you expand the schema in a way compatible with both versions, then, when the old version is no longer in use, you contract it.
Example: renaming the phone column to mobile.
| Step | Action | Compatibility |
|---|---|---|
| 1 | Add the mobile column, optional |
Old code ignores it |
| 2 | Release code that writes to both columns | Old and new both work |
| 3 | Copy historical data from phone to mobile, in batches |
No impact |
| 4 | Release code that reads and writes only mobile |
The old column is no longer needed |
| 5 | Drop the phone column |
Contraction complete |
That is five steps instead of one, but at no point does the application stop or lose data.
Copying data in batches
A single UPDATE on millions of rows creates an enormous transaction, holds locks for a long time and generates a large volume of log. It is better to proceed in batches:
UPDATE customers
SET mobile = phone
WHERE id BETWEEN 1 AND 10000 AND mobile IS NULL;
repeating the statement for the following ranges, with a short pause between batches.
What about rolling back?
Many tools let you write the reverse operation for each migration too. It is useful in development, but in production it should be used with care: the reverse of «drop column» cannot bring back the deleted data. Often the safest strategy is to roll forward, with a new corrective migration, and to take a backup before every destructive migration.
In short
- Migrations treat schema changes as versioned, ordered code.
- An applied migration is never edited: it is corrected with a new one.
- On large tables, check the locks and use non-blocking variants.
- Renames and type changes are done with the expand-and-contract technique.
- Data is copied in batches, and destructive migrations are preceded by a backup.
