An interesting consequence of this characteristic of SQLite is that it makes it a really good fit for GraphQL.
GraphQL has the N+1 queries problem pretty much baked into it. You can work around it to a certain extent with a lot of effort implementing data loader patterns, but someone will still likely be able to cow up with a query that triggers a cascade of SELECTs.
Or, because "many small queries are efficient in SQLite", you could use that instead.
I built a Datasette plugin that exposes a GraphQL API to an existing SQLite database and it works so well that I switched from being a GraphQL skeptic to a GraphQL advocate.
GraphQL has the N+1 queries problem pretty much baked into it. You can work around it to a certain extent with a lot of effort implementing data loader patterns, but someone will still likely be able to cow up with a query that triggers a cascade of SELECTs.
Or, because "many small queries are efficient in SQLite", you could use that instead.
I built a Datasette plugin that exposes a GraphQL API to an existing SQLite database and it works so well that I switched from being a GraphQL skeptic to a GraphQL advocate.
https://simonwillison.net/2020/Aug/7/datasette-graphql/
Here's an example nested GraphQL query that executes 32 select queries. It's pretty snappy: https://datasette-graphql-demo.datasette.io/graphql?query=%7...