It's actually commonly used in production for certain types of databases. SQLite is not a replacement for something such as postgresql but it certainly has it's use cases.
Every iPhone, and Android phone have SQLite running on them, which easily makes it the most used production database. It's use case is typically very low traffic websites or as client side storage.
It is interesting how much penetration SQLlite has on the client, though.
At any rate, I know better than to not be extremely specific so I brought this on myself.
This all started when someone trashed RDBMS for being "heavy", implying that Mongo is good because it's light.
Why would you care how heavy a DB is if it's not on the client side? The kind of memory and storage you need for even the heaviest popular RDBMS (MySQL?) is still low enough that's nearly free to create a small app.