The migration tools I work with have this already automated and (having been involved in the design of said automation) it's really not that difficult to add.
Other DBMSes often have to do complete table rewrites for certain types of ALTER anyway - so while, yes, it's faintly annoying to have to do that in userspace rather than having the db engine do it for you, it's not nearly as big a disadvantage as it first appears.
Depending on the situation, it can be well worth it to have your test suite run against SQLite while doing active development to be able to iterate faster and then run it again against the target database before pushing the branch.
Where possible I much prefer to spin up a version of my target database in a tempdir but "faster test cycles" is sometimes worth accepting the trade-offs.
I suppose it was a long time coming. SQLite was very convenient for me while the product was in a very early development stage, with everything changing rapidly.
Why not work around the expectation and simply migrate offline? (eg. dump DB, hack dumpfile/stream, load new CSV?) While you may lose instantaneous constraint validation, it would almost certainly be faster and allow you to work with known and well tested tools. Conforms to the Unix design philosophy: "Store data in flat text files." / "Write programs to handle text streams, because that is a universal interface." http://github.com/globalcitizen/taoup
Since you were nominally optimizing for migration, a zoom-out perspective may be to note that upgrading SQLite3 versions vs. upgrading major RDBMS versions is trivial/fast, relatively rarely required, also cohabitation of multiple versions works a lot easier, any kind of CI/CD process is going to be orders of magnitude faster and use much less CPU/memory/disk space, which means smaller build artifacts and thus faster transfer/download.
So in essence it's a lot of work for a small functionality gain?
There are some things that are inconvenient to do without window functions, but my primary use for them is simple optimization. Most of the cases where I've used window function in postgres, the query would be trivial to rewrite to use a subquery instead. Window functions always led to a much faster query that scaled much better over large query sets.
Reading through the responses just screams: not worth the effort. I know it’s an open source volunteer product and even with a commercial arm the lack of window functions (aren’t they part of the SQL standard?) is laughable.