I’m glad this was written out because I’ve deeply internalized that many queries => bad. Good to know the overhead for a query is that low
Well that's an advantage to using SQLite as your web app DB that I hadn't thought much about.
I wonder is there is some specific use case that makes this really powerful in comparison to Postgres. Something where you have lots of dependent queries perhaps?
In the article it describes the N+1 problem with an example of the fossil timeline.
In a system using Postgres, you would select all the timeline entries and then generate one big query to get the details of all the timeline items in one sql request.
With sqlite you can just iterate over the items and do the queries separately.
This makes handling this scenario much simpler.
Can confirm - we use SQLite queries as the primary business logic mechanism in our product now. In some contexts of use, several thousand queries will be executed based on user action (i.e. pressing a button). We have yet to see a case where this adds any perceptible latency to the UX.
I like to make many small queries and then merge results in code in any database. No one can really understand large complex queries but anyone can understand small selects and a couple of for loops that merge the results.
Since multiple queries is not a performance problem, would it be crazy to use something like mustache as an extension to SQLite and select your html components directly?
I was building my first web app with SQLite and wondering why reducing database hits wasn’t doing all that much to improve performance. Thanks for the article.