Sqldelight migrations turn schema history into executable SQL. A correct fresh database does not prove that existing installations can upgrade safely. The migration process must preserve user data, apply changes in order and leave the same final structure that a new installation creates.
Use one ordered change per migration file
Store migration statements in numbered .sqm files under the configured database source folder. Keep each file focused so reviewers can understand what changes at that schema version and why.
Prefer explicit data transformations
When a column changes meaning or type, write the transformation clearly. For complex SQLite changes, a safe pattern may involve creating a new table, copying converted data, dropping the old table and renaming the replacement.
CREATE TABLE note_new (
id INTEGER NOT NULL PRIMARY KEY,
title TEXT NOT NULL,
archived INTEGER NOT NULL DEFAULT 0
);
INSERT INTO note_new(id, title)
SELECT id, title FROM note;
DROP TABLE note;
ALTER TABLE note_new RENAME TO note;Save old schema fixtures
Maintain representative databases or schema snapshots for each supported upgrade starting point. Include data that exercises null values, long text, foreign keys, custom adapters and duplicate cases that a new constraint might reject.
Compare upgraded and fresh schemas
After all migrations run, compare the upgraded structure with a database created from the current schema. Differences in indexes, defaults or constraints can remain hidden until a later query depends on them.
Coordinate generated API changes
A migration and a query edit can change generated Kotlin types. Review database SQL and application call sites in the same change so an old column is not removed before data and code stop using it.
Production review checklist
- Every supported old version upgrades successfully.
- Important user data survives with correct values.
- The final schema matches a fresh database.
- Queries and adapters work after the upgrade.
- The application can reopen the upgraded database.
- Backup and rollback procedures are documented for the release.