Pieter Levels (levelsio) of Nomadlist famously gets mocked for using a single index.php and sqlite but does 5 figure MRR. https://nomadlist.com/open

He used to get a lot of flack for it and i never understood why since you use whatever gets the job done. Too many people doing the "gatekeeper" thing.

There has always been a case for server-side embedded databases for app servers. It can be an in-process library like SQLite or Apache Derby, but databases like SQL Anywhere and SQL Server also have local IPC protocols that achieve the same effect.

App Embedded databases are not the main use case for non-SQL transactional engines like Berkeley DB and Microsoft ESE (used for ActiveDirectory and Exchange Server).

SQLite is a better server-side engine than the critics think but it is ill suited with the default options. WAL mode and Shared Cache mode must be enabled for decent performance. The popular runtime libraries for SQLite don’t expose the required C API calls nor even the enhanced functionality of the FILE URL optional connection string.

Even when configured properly, concurrency in SQLite is limited for apps that have a hot row or page. Row level locking or snapshot isolation are sometimes a huge help but fast local reads go a long way in alleviating the need. The main obstacle, however, is that the popular web frameworks assume a wire protocol SQL engine running on a separate tier like Heroku’s 12-Factor App architecture.

SQLite is totally capable of delivering a good performance. The main problem is that you have to take care of efficiently opening and closing the file yourself. Naively doing it for every user/transaction will quickly put an end to your scalability plans and in fact, it is something you get with a DB server applications for free.

That said, it's strength is its simplicity and not its scalability. So if you plan to run a database server cluster, you probably want to look for other technologies. But as long as you are on a single machine, your SQLite performance problems are probably a case of 'you are doing it wrong' ;-)

So when you are talking about server-side, you probably don't want to limit your technology choice to a database engine that can not be clustered. However, if you are offering software which should be simple to install on a server (e.g. Nextcloud), providing an SQLite option is probably a good idea, because nobody has to care about DB-Server administration and connection setup.

I was about to ask what the use-case would be, since I assumed server SQL databases would use Paxos or Raft or something, but apparently I was wrong! I guess durability is done through persisted volumes, storing tables in files and being deliberate about journal structure and fsync?

In that kind of world I guess sqlite is as good a choice as any if you don't need a SQL endpoint.

If you do mostly reads, it works great. I use it on http://www.wikdict.com for years without problems. I for e have very much traffic, though.

SQLite is for use cases where a dedicated DB is overkill/impossible. Considering the limitations it has, it probably isn't usable for most production DBs.