If you have multiple versions in the wild to support, there's nothing stopping you from supporting multiple upgrade paths, or even continuing to chain migration files, if you want to.
Moving data about is always going to be a manual process. This approach just helps you test it better.
> Moving data about is always going to be a manual process.
No, it's not always.
If moving data around within a migration isn't always a manual process, please show me the tool which automatically generates the correct statements.
When a user on random old version finally updates their app to latest, that latest code better be able to handle a migration correctly.
First, every developer has a development environment. If someone goes on holiday for a few weeks they will come back to a personal environment that's likely several steps behind everyone else.
Smart engineering orgs have one or (hopefully) more QA environments which differ from production by design - they're where you preview and test upcoming features that may involve schema changes.
At a certain scale individual teams may have their own dedicated QA environments, for testing in-development features without disrupting the work of other teams.
Then there are environments for running integration tests, hooking up to CI systems, load testing etc.
There may be only one production environment but there could be dozens or even hundreds of other environments that need to be able to reliably apply migrations up to a specific point.
The Django approach to migrations handles this really well.
If we're talking about deployed sqlite dbs running on client machines, expecting an upgrade to be a manual process is simply not acceptable.
No idea why this was downvoted, because this is a key truth.
You need to be able to migrate the production database to the latest version without losing any data.
You need to be able to replace any non-production database with the same schema as production. Ideally, with a (sanitised, subsetted, etc) copy of its data.
You don't need to be able to do anything else.
What about on-premises or just open-source software, where you any number of any version of your software may be installed around the universe, and those users need to upgrade to a newer version?