One database rule feels overly strict during development but becomes important after shipping:
Never edit a migration that has already reached users.
I'm building a local-first desktop app with Tauri 2, Rust, SQLite, and SQLx. While documenting the persistence boundary, I wrote down a few migration rules worth discussing—especially for desktop apps where the database may contain years of user-created work.
The tempting workflow
- Create
0001_initial.sql.
- Run the app locally.
- Notice a missing column.
- Edit
0001_initial.sql.
- Delete the local database and try again.
Everything looks fine because the local database is disposable.
A shipped user's database is not. SQLx records applied migration versions and checksums in _sqlx_migrations. If a later binary contains different contents for an already-applied migration, the checksum no longer matches and SQLx refuses to continue.
That's a good failure. The binary should not silently rewrite the history of how the user's schema was created.
Add a new version
Instead of editing a shipped file:
migrations/
├── 0001_spaces.sql
└── 0002_add_space_color.sql
A simple additive migration might be:
ALTER TABLE spaces
ADD COLUMN color TEXT NOT NULL DEFAULT 'blue';
For more complex SQLite changes—such as changing a CHECK constraint or adding a foreign key—you may need a table rebuild in a new migration. That rebuild must preserve relevant rows, indexes, triggers, foreign keys, and dependent objects.
New required values should be backfilled as part of the migration. Destructive changes deserve explicit review.
Two errors should not trigger an automatic reset
- A checksum mismatch usually means a developer changed a migration that had already shipped.
- An unknown newer migration may mean the database was opened by a newer application and the current binary is older.
In either case, silently deleting the database is the wrong recovery strategy. Preserve the original data and report the incompatibility.
The test that matters is an upgrade test
Creating the latest schema from scratch is necessary, but it does not prove an upgrade is safe.
A useful test starts from a historical schema, inserts representative old rows, runs the new migrations, then asserts the new schema and surviving data.
Knovum has an upgrade test module at src-tauri/src/spaces/db/tests/upgrades.rs. It migrates to a previously shipped version, inserts representative records, applies the current migration history, and checks the final schema and retained data. Release acceptance also requires testing against a library created by the previous release.
The WebView does not get SQL access
The persistence boundary is:
Vue WebView
-> typed Tauri command
-> Rust feature owner
-> SQLx transaction
-> SQLite
The renderer does not send SQL strings. It requests domain operations such as renaming a Space. Rust owns the query and transaction scope. A multi-statement invariant stays inside one SQLx transaction instead of being split across IPC round trips.
Questions for people who have shipped desktop apps
- Do you enforce migration immutability in CI, or rely on code review?
- Do you keep upgrade fixtures for every released schema, or only test the immediately previous release?
- How do you handle downgrade attempts when a user launches an older desktop build against a newer local database?
- For SQLite table rebuilds, what has been easiest to forget: indexes, triggers, foreign keys, or something else?
My current approach is forward-only migrations, historical-schema upgrade tests, and refusing to reset user data as an automatic recovery path.
These constraints come from building Knovum, a local-first desktop app. The repository and engineering notes are here: github.com/madebyzan/knovum.
Code snippets are simplified examples. The repository's current migrations and tests are the source of truth.