I hit annoyances when using sqlite when dealing with some corner-cases. I wanted to implement a lock and share some small amount of data (who did what when) between two different linux users on the same machine.
I figured "sqlite is better than fopen, let's use that!", but between directory permissions, all the WAL files, probably the sqlite3 python lib and Diskcache (https://pypi.org/project/diskcache/) not helping things, it was a real pain, where regularly under different race conditions, we would get permission denied errors. I managed to paper it over with retries on each side, but I still wonder if there was a missing option or setting I should have used.
I feel you! I had an ASP.NET Core Web API using a SQLite file for data storage. It was working exceptionally fine in development. When we released it, as soon as more than 10 users were throwing request upon it, it got so deadly slow. Obviously caching and some tweaking helped a lot. But it was far from easy to find, understand and test (!) all required options and optimizations.
I figured "sqlite is better than fopen, let's use that!", but between directory permissions, all the WAL files, probably the sqlite3 python lib and Diskcache (https://pypi.org/project/diskcache/) not helping things, it was a real pain, where regularly under different race conditions, we would get permission denied errors. I managed to paper it over with retries on each side, but I still wonder if there was a missing option or setting I should have used.