How do schema migrations work in a local-first app?

Every app that stores data locally has to change the shape of that data as features change. Mobile platforms have tools for this, and the ones below assume one database on one device moving from one version to the next.

• SQLite supports a limited set of direct changes: renaming a table, renaming a column, adding a column, and dropping a column. Any other change is done by creating a new table in the desired shape, copying the data across, dropping the old table and renaming the new one. SQLite also keeps a user_version integer in the database header that applications can use however they want, for example to record which schema version a file is at.
• Room on Android can generate automated migrations between database versions. If a change is ambiguous, such as deleting or renaming a table or column, the developer must describe it, and complex changes such as splitting one table into two need a hand-written migration.
• Core Data on Apple platforms can perform lightweight migration, inferring the mapping from the differences between the old and new models. It handles changes such as adding or removing an attribute, and renaming an entity or property when a renaming identifier is set. Anything beyond that needs a manual migration.

These tools also show the failure mode. If Room cannot find a migration path from the version on a device to the current one, it throws an exception, unless the app opts into destructive fallback, which recreates the tables and, in the words of the Room documentation, permanently deletes all data in them. That trade-off can be acceptable for a cache that a server can refill. In a local-first app, where the device holds the primary copy, there may be nothing to refill it from.

On a server, a migration runs once against the authoritative database. In a local-first app there is no single database to migrate. The Ink & Switch essay that set out local-first ideals names this as an open problem: collaborators may be running different versions of an application, and with no central database server there is no authoritative current schema for the data.

That creates two requirements, which the Ink & Switch Cambria project defines:

• Backward compatibility: new code can read data written by old code.
• Forward compatibility: old code can cope with data written by new code.

The second is the hard one. Cambria’s authors describe renaming a field from authors to contributors with a migration that copies the value across and deletes the old field. It works in a single-user test. But while any old copy of the app is still editing the document, that copy can write authors again, and the result can be lost data. They note that these sequencing problems are rare in centralised systems but common in decentralised ones.

• Add, do not rename or repurpose. A new optional field with a sensible default is safe in both directions. Old versions ignore it and new versions fill the default when it is missing.
• Read defensively. Treat every field as possibly absent or in an older form, and fill defaults in one place rather than scattering checks through the code.
• Keep what you do not understand. When an old version reads a record with extra fields, it should write them back untouched rather than dropping them.
• Version the data, not just the app. Record which schema version wrote each document, so a reader knows what it is looking at.
• Translate instead of rewriting. Cambria’s approach is to mark data with versions, relate versions through bidirectional translations called lenses, and apply a chain of them to convert between formats when data is read or edited. In its Automerge prototype the lenses are stored in the documents themselves, so old code can load them and translate a newer document into the shape it understands.
• Migrate local-only data normally. Indexes, caches and settings that never leave the device can use the platform tools above.

The data model matters too. Fields that merge independently are easier to evolve than one large value that is replaced as a whole, which is one more reason to split records into separate fields in a CRDT-based store.

Translation reduces the damage of a schema change but does not remove every conflict. Cambria’s authors found that when two versions represent data differently enough, interoperability means trading off between consistency (both sides see an equivalent view), conservation (neither side changes data it cannot see) and predictability (each operation keeps its local intent). Their example is an issue tracker where an old version allows one assignee and a new version allows several. The old version can only show the first, so removing that person is ambiguous: clear the list, remove one entry, or replace it. The authors present Cambria as research rather than a production-ready tool.

• Keep the schema history in version control. Room exports each version’s schema as JSON for this reason, so older versions can be recreated in tests.
• Test the upgrade path from every version still in use, not only the previous one.
• Test mixed versions: two devices on different releases editing the same record, then syncing, then one of them upgrading.
• Decide in advance the oldest version you will still sync with, and what the app shows a person whose peer is too old.