I use it in several of my personal and professional projects, especially in cases where data can be sharded well. The one thing that is a bit annoying though is the locking mechanism in SQLite, which prevents simultaneous writes to the database and locks it for reading while a write transaction is underway. If they could find a way to improve that behavior I think the use cases of SQLite would grow way beyond embedded application databases. Whether or not this is a good thing is of course a different question, but having a zero-configuration "plug-and-play" database engine could be a very attractive proposition to many people.

BerkeleyDB for example, which handles concurrent access much better than SQLite and also comes in a high-availability version is used in many production environments today, most notably as the backend of Amazon's DynamoDB key/value store.