Actually, with a good architecture, SQLite brings you very far. However, you need a solid architecture; otherwise, the journey ends very soon. So people with architectural wisdom can build great things with SQLite.

The ones who warn about it are often the ones who don't care about architecture and just plug stuff together.