DailyRNG
Login
Advanced

Migrating data alongside the box

A new release of an app often means a new shape of the data it reads. The container can be perfectly correct and still be useless if the database on disk is in the old shape. Migrations are versioned artifacts that live next to the recipe, not inside the code that runs.

What you will see

The version matches the highest migration number. If it is behind, the app must refuse to start.

About 1 min of reading

Try It

Add a migration and gate it on the version

1

Create a migrations dir

mkdir -p migrations && printf 'ALTER notes ADD COLUMN pinned INTEGER DEFAULT 0\n' > migrations/001_pinned.sql

Before you run it, say out loud what it will print. Then open the answer.

What it prints
One migration file, numbered, that adds a column with a default so old rows stay valid.
2

Write the runner

printf 'import sqlite3, pathlib\ndef apply(db, cur):\n for f in sorted(pathlib.Path("migrations").glob("*.sql")):\n n = int(f.name.split("_")[0])\n if n > cur:\n db.execute("BEGIN"); db.execute_script(open(f).read()); db.execute_script("UPDATE _meta SET version = %d" % n); cur = n\n' > migrate.py

Before you run it, say out loud what it will print. Then open the answer.

What it prints
A runner that applies only migrations whose number is above the stored version.
3

Run it twice

python migrate.py && python migrate.py

Before you run it, say out loud what it will print. Then open the answer.

What it prints
The second run applies nothing. Idempotence comes from the stored version, not from checking whether the column exists.
4

Gate the release on it

python -c "import sqlite3; c=sqlite3.connect('app.db'); print(c.execute('SELECT version FROM _meta').fetchone())"

Before you run it, say out loud what it will print. Then open the answer.

What it prints
The version matches the highest migration number. If it is behind, the app must refuse to start.
Never edit a migration file after it has been applied on any machine. Add a new numbered file instead; the old file is a record of what happened.

Data Flow

Old data to new data
1 Read the data version from disk
↓
2 Diff against what this release expects
↓
3 Apply the missing migrations in order
↓
4 Stamp the new version and start the app
Why this works
Where the migration runs

Never at import time and never lazily inside a request. Run it as an explicit command, once, before the app starts, so a failure is loud and the app never sees a half-migrated state.

The two-release window

For one release, support both the old and the new data shape. That window is what lets an older box still read a database that a newer box wrote. Close the window in the release after.

Examples

One file per step

migrations/004_add_columns.sql and 005_drop_legacy.sql, applied in numeric order. The number is the version of the data, and the file is the only thing that can change that version.

Forward only, in one direction

Write the forward migration and make the app read both shapes for one release. Writing a reliable reverse migration for every step is a bigger promise than you can keep.

The box reads the data version

At startup, read the stored version. If it is behind what this release expects, run the migrations it is missing, or refuse. Refusing is safer than auto-migrating on a live database.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Checking a container before you ship it
Previous Lesson
Checking a container before you ship it
Next Lesson
Dependency ranges in a lock file
Dependency ranges in a lock file →