The upside is it doesn't need a central server. And 10 minutes to install the server (if you know how to do it) vs 0 minutes is a pretty big deal.
A project running on sqlite can be quickly taken to just about any box and run without any infrastructure dependencies.
You can easily run a hundred instances on a single machine, for dozens of simultaneous users, without any setup or coordination. Computing resources are only required for access, not for availability.
On the small projects, DB admin doesn't seem to be much more complex than using a SQLite DB. By the time you get the DB load high enough that you really want to pay attention to administrating it, SQLite has probably given up the ghost long ago.
Don't get me wrong, SQLite is great for what it does. I don't see the upside to it on this though. Even if you know for sure your site will never hit high traffic, it just isn't that hard to run a conventional DB. And if it does, it's a lot easier to pay somebody to set up your DB server right than to convert over to a conventional DB and then get it set up right.