On-device migrations are a different problem

I had reached for an ORM in a shared data package, the layer that a set of mobile apps all sit on top of. The ORM had a proper migration tool: file-based, code-generated, the works. I did not use it. I wrote my own tiny migration runner instead.

That felt like reinventing a wheel until I remembered where this wheel had to roll.

A server migrates once, a phone migrates forever#

When you migrate a server database, you do it once, at deploy, under your own eye. If it goes wrong you are watching, and you can roll back.

On-device SQLite is nothing like that. The migration runs on every user’s phone, on whatever version they happened to install, whenever they next open the app, with no one watching. You cannot inspect the box. You cannot re-run by hand. It has to be boring and predictable to the point of dullness. Get it wrong and you do not get a bug report, you get a user whose app will not open and whose notes may be gone, on a phone you can never log into to fix.

So the runner was deliberately small. A versions table recording what had been applied. Migrations sorted by an integer version. Already-applied ones skipped. Each migration wrapped in its own explicit transaction, so a failure rolls that step back cleanly instead of leaving the schema half-changed on a stranger’s device.

The ORM stayed, but only for what it is good at#

I did not throw the ORM out. I demoted it. It stayed on as the query builder and as the source of inferred types, so application code still got autocomplete and type-checked queries.

But the schema itself, the DDL, was raw SQL:

CREATE TABLE IF NOT EXISTS note (
  id TEXT PRIMARY KEY,
  body TEXT NOT NULL,
  updated_at INTEGER NOT NULL
);

Raw DDL means the migration is exactly what I wrote, with no codegen tool between my intent and the phone. The integer version numbers decouple me from the tool’s file conventions entirely. I can read the whole migration history top to bottom and know what runs, in what order, on a device I will never touch.

On a server you migrate a box you can watch. On a phone you migrate a box you will never see again. The second one wants a runner you could explain to a stranger in a sentence.

Put the defaults where nothing can skip them#

Two more device-specific habits made it into this package, and both are about removing the chance to forget.

The first is a single database factory that applies a fixed set of pragmas on every launch: write-ahead logging on, foreign keys on, a set cache size. Every app in the monorepo opens its database through that one factory, so no app can quietly forget to turn foreign keys on. The correctness defaults live in one place, applied every time, not copy-pasted per app and drifting.

The second is unique-id generation. The obvious call is the web crypto UUID primitive, and it is right there in the browser. But the mobile runtime does not guarantee it exists. So id generation falls back to a manual implementation when the platform primitive is missing, rather than throwing on a phone at the worst moment.

That is the wider trap. A mobile runtime is not a browser. Web platform globals you lean on without thinking may simply not be there, and you find out in a crash report from a device you cannot reproduce.

The lesson#

On-device migrations carry constraints a deploy-time server migration never has: no supervision, no rollback, every version of the app in the wild at once. A small integer-versioned runner with a per-migration transaction is predictable and cuts your dependence on a codegen tool.

Put your correctness defaults, the pragmas, in one factory so nothing can skip them. And never assume a web platform primitive exists in a mobile runtime. Check, and keep a fallback for when it is not there.