What is a database migration (and why is it risky)?

A migration is a change to the structure of a database: adding a column, renaming a field, splitting one table in two. It is risky because the database already holds real people's data, and you cannot simply start over from scratch.

The analogy: rearranging a library that is open to the public

Picture a library full of books. You decide to change the system: add a children's section, shelve by author instead of by title. Do it badly and books get lost or end up in the wrong place. And if readers keep walking in during the works, they look for books that are no longer where they were.

A database is the same thing: tables (the shelves) filled with rows (the books). A migration reorganises the shelves without losing a single book.

What a migration looks like

It is a small file, written in the code and kept alongside it, that describes one precise, dated change. For example: "add a phone column to the customers table". Every environment (your computer, the test environment, production) replays these files in order and ends up with the same structure.

It is a big step up from changes made by hand in a tool: a migration can be reviewed, tested and replayed.

Why it is risky

• The data already exists. Adding an empty column is easy. Turning one field into two, or changing a type, means converting what is already stored. A badly thought-out conversion can truncate or erase values.
• The code and the database must agree. If the new version of the code expects a column the database does not have yet (or the reverse), the application breaks during the transition.
• Some changes are slow. On a large table, a change can lock the database for a while, and users wait.
• Going back is sometimes impossible. Dropping a column erases its contents for good.

The precautions that matter

1. A recent, verified backup before any migration. A backup you have never tried to restore is a hope, not a guarantee.
2. Try it elsewhere first, on a copy of the database: that is the job of the test environment, see Staging vs production: why we test somewhere else.
3. Move in small steps. Add the new column, make the code write to both, copy the old data, and only later remove the old one. Each step is safe on its own.
4. Plan the way back when possible, and know in advance what you will do when it is not.
5. Never edit the production database by hand, outside the code: nobody will know what was done.

Why it gets thought of late

In a project assembled quickly, the database often evolves through small tweaks made along the way, with no migration files. All is well until the day you must recreate an environment, or change something on real data: then you find nobody knows how the database got to where it is. See also Idempotence: tap twice, pay once, a useful habit for operations you may have to run again.

Key takeaway

A migration reorganises a database that is already full: you prepare it, try it on a copy, do it in small steps, and have a verified backup before you start.

And for your product?

If you are taking over a project whose database has evolved without a method, the first thing to do is understand its real state before touching it. That is a usual step of a project takeover: read the structure, check the backups, bring changes under control. If you would like to talk it through, we can have a 30-minute conversation.