SQLite is one of the unfortunate victims of reddit/hackernews circlejerk (the other common victims in this space are MySQL, Postgres, Mongo).
I use and love SQLite for what it is. But it's not a silver bullet for every use case.
I have an anecdote - few months ago a problem surfaced with SQLite - relatively simple query (3-4 joins) was working completely fine for a long time, but suddenly started taking very long time. It turned out it stopped using correct indexes - probably some heuristic threshold(s) have been crossed and suddenly query planner started returning very inefficient plan which was 100 times slower. It was very easy fix - force use of correct indexes, but it would be very hard to debug if I didn't have access to the database (as is often the case with embedded databases). This is the price for the abstraction of only defining what you want declaratively and you hope the database will do the right thing to execute your request.
I use and love SQLite for what it is. But it's not a silver bullet for every use case.
I have an anecdote - few months ago a problem surfaced with SQLite - relatively simple query (3-4 joins) was working completely fine for a long time, but suddenly started taking very long time. It turned out it stopped using correct indexes - probably some heuristic threshold(s) have been crossed and suddenly query planner started returning very inefficient plan which was 100 times slower. It was very easy fix - force use of correct indexes, but it would be very hard to debug if I didn't have access to the database (as is often the case with embedded databases). This is the price for the abstraction of only defining what you want declaratively and you hope the database will do the right thing to execute your request.