"SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time."
My question is, why do people keep advising against using it in production? How many websites have that many users? From what I just copied above from the documentation, Would it be bad to use it for a website with 15-20k users? I am confused and need someone to explain to me.
And for data analysis stuff it's absolutely brilliant!
So yes absolutely it's underrated!
If you use, say, PostgreSQL, any number of web server processes and batch processes can run at any time and connect to the database and the integrity of your data is ensured. If you want to do some administration or analysis just connect with psql or DBeaver.
SQLLite on the other hand is a library that runs inside a single program. If you want to add a table or do some analysis you have to shut your web server down!.
It is not the hits per day that matter so much as the analysis, administration and management requirements. If it is an application just a few people use you can shut it down to admin, but if it has 15-25k users you will certainly inconvenience some.