A database migration may look like an ordinary SQL file. During early development, it is tempting to edit 0001_initial.sql, drop a local database, and start again.
That workflow stops being harmless once the application ships.
A user's database is not your disposable development database. SQLx records applied migration versions and checksums in _sqlx_migrations. Changing an already-applied migration changes the history the new binary claims to understand.
1. A shipped migration is immutable history
Imagine version 1.0 ships 0001_spaces.sql. A user installs it, and SQLx records the migration version and checksum.
Later, a developer edits the same file. The filename and version are unchanged, but the contents—and checksum—are different. When the newer binary compares its migration files with the database history, SQLx detects the mismatch and refuses to proceed.
That is a safety mechanism. Silently accepting a different definition for an already-applied version would make the database's history ambiguous.
Once a migration has shipped, treat its contents as immutable.
2. Add a new migration instead
If v1.0 has shipped, a schema change belongs in a new version:
spaces/db/migrations/ ├── 0001_spaces.sql └── 0002_add_space_color.sql For a simple additive change:
ALTER TABLE spaces ADD COLUMN color TEXT NOT NULL DEFAULT 'blue'; The default matters: existing rows should satisfy the new invariant as soon as migration finishes. Avoid leaving sentinel values that every reader must special-case.
In Knovum, Spaces uses an embedded SQLx migrator before its pool is exposed. Feature-owned databases keep their own migration histories rather than routing every product capability through a global database service.
3. SQLite table rebuilds need deliberate migrations
Some SQLite schema changes are not simple in-place operations. Changing a CHECK constraint or adding a foreign key can require a table rebuild.
A simplified shape:
BEGIN; CREATE TABLE spaces_new ( id TEXT PRIMARY KEY, title TEXT NOT NULL, color TEXT NOT NULL, created_at INTEGER NOT NULL ); INSERT INTO spaces_new (id, title, color, created_at) SELECT id, title, color, created_at FROM spaces; DROP TABLE spaces; ALTER TABLE spaces_new RENAME TO spaces; -- Recreate required indexes and triggers. COMMIT; This is an illustration, not a drop-in migration. A real rebuild must account for foreign keys, indexes, triggers, views, dependent objects, and data transformations.
Prefer additive tables, indexes, and columns with meaningful defaults. Backfill required values in the same migration. Treat dropping a table or column as a data-retention decision, not routine cleanup.
A forward migration should not silently reinterpret what users already saved.
4. Two failure modes should not become an automatic reset
A checksum mismatch means an already-applied migration differs from the file bundled with the current application. This is usually a developer or release-process bug.
An unknown newer migration means the database may have been opened by a newer application. An older binary must not pretend it understands that schema, attempt an implicit downgrade, or reset the database.
In both cases, preserve the original data and report a clear incompatibility. Do not fall back to deleting and recreating the database.
5. Fresh-database tests are not upgrade tests
This proves that the current schema can be created:
let pool = SqlitePoolOptions::new() .max_connections(1) .connect("sqlite::memory:") .await?; MIGRATOR.run(&pool).await?; It does not prove that a database created by the previous release can be upgraded safely.
An upgrade test should start at a previously shipped schema, insert representative rows, run the remaining migrations, and assert both the resulting schema and surviving data.
Knovum's src-tauri/src/spaces/db/tests/upgrades.rs follows this old-schema-first approach. It migrates to a shipped version, inserts representative records, applies the current migration history, and checks the final schema and retained data. Release acceptance also requires exercising the packaged upgrade path against a library created by the previous release.
Fresh in-memory creation alone is not upgrade evidence.
6. Keep SQL authority out of the WebView
Migration safety is also an ownership problem. If a Vue WebView can issue arbitrary SQL, persistence rules and transaction boundaries spread into presentation code.
The boundary used in Knovum is:
Vue 3 WebView -> typed Tauri commands -> Rust feature owner -> SQLx queries / transactions -> feature-owned SQLite database The renderer has no SQLite capability and no SQL plugin dependency. It sends domain-shaped requests; Rust owns reads, writes, transactions, migrations, and persisted-field parsing.
The UI asks to rename a Space, not to execute an arbitrary UPDATE.
If an invariant spans multiple SQL statements, keep them in one Rust SQLx transaction. Do not split a transaction across multiple IPC calls.
7. Connection policy is part of storage design
Knovum's shared SQLite connection policy currently includes foreign-key enforcement, WAL, synchronous=NORMAL, a five-second busy timeout, a bounded pool, cache/mmap/temp-store settings, and PRAGMA optimize after migration.
WAL + synchronous=NORMAL is a trade-off, not a zero-data-loss guarantee. It avoids waiting for a full fsync on every commit while protecting against ordinary process-crash corruption. Sudden power loss can still lose recent committed transactions, so the choice must be evaluated alongside backup and recovery.
These are the project's current settings, not universal defaults every SQLite application should copy.
8. A migration review checklist
- [ ] Did this change modify, delete, or rename a migration that has shipped?
- [ ] Is the change represented by a new, increasing migration version?
- [ ] Do existing rows satisfy the new constraints when migration completes?
- [ ] Have foreign keys, indexes, triggers, and views been reviewed?
- [ ] Is there an upgrade test starting from the previous shipped schema?
- [ ] Does it verify both retained data and its meaning?
- [ ] Does an older binary refuse an unknown newer schema without resetting it?
- [ ] Does release acceptance exercise a database created by the previous installer?
Conclusion
A migration is not just a deployment script. It is a long-lived compatibility promise between an application and the data people have entrusted to it.
Freeze shipped migrations. Evolve forward. Test upgrades from historical schemas. Keep SQL and transaction ownership in the feature that owns the data.
These practices come from the database ownership and migration rules I have been applying while building Knovum, a local-first desktop application. The source and engineering notes are at github.com/madebyzan/knovum.
Code snippets are simplified teaching examples, not verbatim copies of the repository. Check the current source and migration history before adapting them.
