For the embedded use case I think it’s a slam dunk, but there are many interesting use cases for server side but they all seem to be toyish.
The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect
> The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect
SQLite with WAL and synchronous configured appropriately will insert a row in ~50uS on NVMe hardware. This is completely serialized throughput (i.e. the highest isolation level available in SQL Server, et. al.). At scale, this can be reasonably described as "inserting a billion rows per day".
I have yet to witness a database engine with latency that can touch SQLite. For some applications, this is a go/no-go difference. Why eschew the majesty of SQL because you can't wait for a network hop? Bring that engine in process to help solve your tricky data problems. I'd much rather write SQL than LINQ if I have to join more than 2 tables.
We've been exclusively using SQLite in production for a long time. Big, multi-user databases. Looking at migrating to SQL Server Hyperscale, but not because SQLite is slow or causing any technical troubles. We want to consolidate our installs into one physical place so we can keep a better eye on them as we grow.
Fun fact: SQL Server "Hyperscale" is capped at 100MB/s on its transaction log. I have written to SQLite databases at rates far exceeding this.
[0] And I would be very careful about the security angle, because this is effectively a very dangerous back door into the whole system. Useful, but something to be careful about.
[1] Dump live activity stats, performance info, ability to run arbitrary queries against any database(s), ability to run arbitrary code in the app itself if available in a reasonable way (ex. if the app is in a dynamic language, just an eval(), possibly embed tcl/lua if not).
Admittedly, not high traffic like you asked, but one of my favorite uses of SQLite hands down.
If you need high write volume, then yes, the locking behavior means SQLite is not a good fit.
I think performance can be good, as long as a competent schema design is in place. Allowing ad-hoc queries from less trusted users will surely tank performance.
That being said; even if it weren't usable in a web service space, does that make it any less reasonable of a database? That whole mentality sounds like a web developer centric one. Berkeley DB was used for decades for application and system databases, a field that SQLite largely replaced it in. And one that MySQL, Postgres, Oracle, etc are generally completely unsuited for. It's the same reason Microsoft offered MS Access for so long alongside MSSQL, until MSSQL had it's own decent embeddable option to deprecate it.
The other use case I'm eager to explore is as persistence of status data in a long-running process. Occasionally the task blows up halfway and although we can recover the functional changes, we lose the reporting data for the original run. If we save it in a SQLite database instead of just in-script data structures, we don't have to try to reverse engineer it anymore.
In both cases, I like the idea of "everything's one file and we nuke it when we're done" rather than "deal with the hostile operations team to spin up MySQL infrastructure."
Because SQLite is run in many different environments and scenarios, and what's terrible in one scenario is perfect for another. There are no defaults that will work for everyone. This also applies to MySQL, PostgreSQL, etc. but the range of scenarios for those is more limited (no embedded for example) so the defaults are a bit more tuned to what's suitable for your scenario.
An old product I worked on (which is still around) used (still uses?) SQLite for storing filesystem blocks that came from the paired backup software. A single database only contained the blocks backed up from a single disk (based on its disk UUID.) So, this is a perfect scenario since there's only one writer at a time because there's only one device and one backup on that device at a time. We could write blocks to SQLite fast enough to saturate the IOPS of the underlying device and filesystem containing the database. This was over 10 years ago. Very practical and durable and way better than anything we could do in house.
Today, you can definitely do GB/s on NVMe class hardware with the right pragma settings and the right workload. So for certain classes of multi-tenant solutions I think it's not so bad, you can just naturally have a single writer to a single data store (or can otherwise linearize writes) and the raw performance will be excellent and more than enough.