SQLite is one of the world's most popular databases in production (I heard it's the most deployed database in the world, but I'm not entirely convinced) so it surely can't be awful at least..
For webapps, it's a mixed bag. I've run a few sites with low 6 figure pageviews per month (i.e. as big as probably 90% of all sites) on it without problems but in situations with high levels of concurrent writes, it's classically been advised against. If your app or framework of choice likes to timestamp every database record it touches or logs heavily to the database, be careful ^1.
(^1 - SQLite 3.7, which I've not played with yet, supports a write-ahead log mode which means writers and readers don't block each other. This could resolve a lot of potential performance snafus.)
> I heard it's the most deployed database in the world, but I'm not entirely convinced
That's absolutely true but not really a fair comparison. sqlite is the go-to method for storing structured data in desktop and mobile applications (Android, iOS, Windows, Chrome, Firefox, ...), accounting for many billions of installations, whereas the other things that people usually call "databases" (e.g. MySQL) obviously aren't meant for that use case.
For webapps, it's a mixed bag. I've run a few sites with low 6 figure pageviews per month (i.e. as big as probably 90% of all sites) on it without problems but in situations with high levels of concurrent writes, it's classically been advised against. If your app or framework of choice likes to timestamp every database record it touches or logs heavily to the database, be careful ^1.
(^1 - SQLite 3.7, which I've not played with yet, supports a write-ahead log mode which means writers and readers don't block each other. This could resolve a lot of potential performance snafus.)