Comment by jitl

I think this is an interesting way to generate migration scripts at design time, but I wouldn't trust auto-migration code in production that migrates from arbitrary schema to arbitrary schema. Take migrations that require copying the entire table - this could be fine for small tables, but extremely expensive for large tables. If disk space is a concern, this will also use 2x disk space! I at least want any such operations to be obviously visible at code review time, so the team can discuss any performance implications before we ship such a migration.

Our team uses a rigid workflow to maintain the SQLite schemas for our mobile and desktop apps:

1. Generate a new migration. This creates ${SCHEMA_VERSION}-migration-name.sql, where SCHEMA_VESION will be the PRAGMA user_version after migration. We also generate a matching ${SCHEMA_VERSION}-migration-name.test.ts.

2. The developer updates the migration file to do the thing. This part we could replace with declarative table diffing as in the article, which would be cool! The developer also fills in the test. Our tests typically write some rows to the DB at SCHEMA_VERSION-1, perform the migration, and then make assertions about how the rows look after the migration.

3. When the changes are ready for review, the developer runs a command that packs up all the migrations into generated allMigrations.{json,ts} files, along with an auto-generated "fast-forward" migration that is just the final database SQL dump, and a JSON description of the expected end result schema.

4. On end-user devices, at app boot we read PRAGMA user_version and apply the migrations transactionally in order, to the latest migration. At the end, we assert that the schema in the database matches the expected schema. If the database is new, like on a fresh install, we apply the fast-forward migration instead.