I agree that SQLite is awesome for many use-cases. Running in the same process allows to have a very small latency. No additional server to manage is also a big plus. Concurrency can be an issue with writes though. Plus if you need to scale your backend you can't share the same database.
ORMs are very hit-and-miss for me. Had bad experiences with performance issues and colleagues who don't understand SQL itself, leading to many problems. Micro-ORMs that just do the mapping, reduce boilerplate and otherwise get out of your way are great though.
It really depends on the project. For a CRUD application? Sure. For an application that processes hundreds of events per second, stores them, and makes them wueryable using various filters? Can get tough real quick.
That's where SQLite really shines - used it in a massively scaled facial recognition system, with several hundred simultaneous cameras, a very large FR database with over 10K people, each having dozens of face entries, and all the events that generates from live video viewing large public venues. SQLite was never a bottleneck, not at all.
Single SQLite db embedded within the FR server, with some crazy huge database size of 75+ million faces too. This system is now deployed in a huge percentage of the western world's airports.
ORMs are very hit-and-miss for me. Had bad experiences with performance issues and colleagues who don't understand SQL itself, leading to many problems. Micro-ORMs that just do the mapping, reduce boilerplate and otherwise get out of your way are great though.