ComputeSphereLearn
Console
Learning paths / Application foundations / Databases in an app

Migrations: changing a schema safely

Reading · 6 min · Module 3, lesson 2 of 638 min left in this module

Module 3 · Databases in an appLesson 2 of 6

Goal: Explain why schema changes are written as versioned migrations that run in order in every environment.

3:55 · captions and chapters · narrated with an AI-generated voice
Transcript

Narration uses an AI-generated voice.

[00:00] Where we're going

By the end of this video, you'll know how to change a database schema the same way in every environment. And how to change it without a single request failing along the way.

[00:11] Why not by hand

You could open a SQL console on production, and add a column by hand. But then your laptop, staging and production each end up a little different. On release day, the new code expects a due date column. Production doesn't have it. So every request that touches tasks fails.

[00:32] Numbered files

The fix is a migration: a small, numbered file that changes the schema by one step. It lives in git, right next to your code. Zero zero one creates the tasks table. Zero zero two adds a due date column to it. So order matters. Two needs the table that one creates.

Each migration has two halves. The up makes the change. Here, it adds the column. The down undoes it, and drops the column again.

[01:05] What has run

The database keeps a record of what has run. A table, often called schema migrations, lists every migration that has run. The migration tool compares it with the files, and runs only what's missing. Run it twice, and the second time there's nothing to do. Where the database allows it, each one runs in a transaction. It all happens, or none of it does.

And the same files run everywhere, in the same order. A migration is reviewed in the same pull request as the code that needs it.

[01:41] When they run

So when do they run? Once per release, before the new code needs them. And from one place: a release step, or a one-off job. Not from every copy of the app as it starts. Several copies would race to change the same table.

[01:57] Down isn't a backup

Now, a warning about the down. A down that drops a column deletes the data in it. And running up again won't bring the data back. So take a backup before any migration that removes something. Treat the down as a tool for development.

[02:14] Two versions at once

During a release, the old and new versions of your app run side by side, against the same database. So every schema change must work for both. Say you rename title to name, in one step. The old version still reads title, so its requests fail.

[02:34] Expand, then contract

Instead: expand, then contract, in four steps. First, expand. A migration adds name. The code writes both columns, and still reads title. Second, backfill. Copy title into name, in batches if the table is large. Third, switch. The code reads and writes only name. Fourth, contract. In a later release, once no running version reads title, a migration drops it. It takes more releases. In return, no request fails along the way.

[03:13] Recap

So, to recap. Migrations are numbered files, with an up and a down. The database records what has run. Back up before removing anything. And to avoid downtime, expand before you contract.

[03:28] Check yourself

Here's a question to check yourself. You rename a column in one migration, and release. For about a minute, some requests fail. Why? The old version was still running, and it read the old name. Expand, then contract, avoids this. Later in this module, you'll run these migrations yourself.

Key idea

A migration is a small, numbered file that changes the schema by one step. Migrations live in git next to the code, run in order, and are recorded in the database, so every environment reaches the same schema by running the same files.

Why not just change the database?

You could open a SQL console on production and add the column by hand. Then your laptop, staging and production each end up slightly different, and nobody can say which changes production has.

The bill arrives on release day: code that expects due_date ships to a database without it, and every request that touches tasks fails.

How migrations work

  • Numbered files. 001_create_tasks, then 002_add_due_date. Order matters: 002 changes a table that 001 creates.
  • Up and down. Each migration has an up, which makes the change, and a down, which undoes it.
  • A record in the database. A table, often called schema_migrations, lists what has run. The tool compares it with the files and runs only what's missing, so running it twice changes nothing.
  • One step at a time. Where the database allows it, each migration runs in a transaction, a group of changes that all happen or none do, so a failed one leaves nothing half-done.
  • The same files everywhere. Laptop, staging and production run the same migrations in the same order. A migration is reviewed in the same pull request (a proposed change others review before it's merged, Module 7) as the code that needs it.

When they run

Run migrations once per release, before the new code needs them, from one place: a release step (a command your deploy runs once before starting the new version) or a one-off job. Running them from every copy of the app as it starts means several copies race to change the same table.

Down isn't a backup

A down that drops a column deletes the data in it, and running up again won't bring the data back. Take a backup before any migration that removes something. Treat down as a tool for development, and for undoing a step that has only just gone out.

Changing a schema with no downtime

During a release, the old and new versions of your app briefly run side by side against the same database, so every schema change must work for both. The pattern is expand, then contract: add the new column first, move the code over, and remove the old column in a later release.

Expand and contract, step by step

Say you want to rename title to name. A single rename breaks the old version, which still reads title while the new one starts. Instead:

  1. Expand. A migration adds name. The code writes both columns and still reads title.
  2. Backfill. Copy title into name for existing rows, in batches if the table is large.
  3. Switch. The code reads and writes only name.
  4. Contract. In a later release, once no running version reads title, a migration drops it.

Each release works with the schema before and after its own migration. It takes more releases, and in return no request fails along the way. Lesson 5.7.2 shows the same rule for rolling updates on ComputeSphere.

Check yourself

You write migration 003. Staging has already run 001 and 002. What happens when you run migrations on staging?
You rename a column in one migration and release. For about a minute, some requests fail. Why?