The biggest thorn I found working with sqlite was the lack of ability to modify columns with ALTER TABLE which was a real pain.
Doesn't look like this is fixed in sqlite4 though...
Doesn't look like this is fixed in sqlite4 though...
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.
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.
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.
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.