We've been using SQLite in production (B2B setting) for almost a decade. Concurrent, multi-user systems too. Sometimes thousands of concurrent users will be hitting the same databases all at the same time. Literally not once have we had an incident related to SQLite/IO corruption or performance.

SQLite could handle a billion daily users with just one decent NVMe drive. You don't even have to do anything crazy to make this work. Just pick one of the top 10 techempower web frontends, tack on SQLite and set journal mode to WAL. Oh - try to use the same connection throughout as well. That one trips up a lot of developers used to the hosted SQL patterns.

Oh but what about high availability? DQLite, RQLite, et. al. are now ready to take you to the next level. You can also address this in your application logic instead of deferring to your RDBMS vendor (this is our path).

This SQLite page actually tells you when it’s appropriate or inappropriate to use SQLite for a website.

https://www.sqlite.org/whentouse.html

SQLite supports high read concurrency but has to queue concurrent writers. If your writes are small and concurrency low, you may be able to get away with it but ymmv.

A couple of things come to mind. Scaling, specifically horizontally (though there are some solutions for that) and being write heavy like storing analytics. For many cases though it should work just fine for your average read heavy site.

Seems to me, it can work great if your use-cases match its strengths well. But if they ever don't, then it'll be a big mess with probably no solution but to fully switch over to a conventional RDBMS.

IMHO, going production also requires scalability, high availability, and tooling ecosystem (backup/restore/replication), etc, besides functionalities and performance.