I'm seeing an increasing trend of pushback against this norm. An early example was David Crawshaw's one-process programming notes [1]. Running the database in the same process as the application server, using SQLite, is getting more popular with the rise of Litestream [2]. Earlier this year, I found the post "One machine can go pretty far if you build things properly" [3] quite refreshing.
Most of us can ignore FAANG-scale problems and keep right on using POSIX on a handful of machines.
As someone who uses SQLite a lot, I'm suspicious of this claim. Litestream is strictly a backup tool, or, as its author puts it, disaster recovery tool. It gives you a bit more peace of mind than good old periodic snapshots, but it does not give you actual usable replication,* so I doubt it meaningfully increased SQLite adoption in the RDBMS space (compared to the application data format space where it has always done well).
* There was a live read replica beta which has since been dropped. Author did mention a separate tool they're working on which will include live replication. https://github.com/benbjohnson/litestream/issues/8#issuecomm...