It turns out the N+1 thing really is only an anti-pattern if you're dealing with significant overhead per query. If you don't need to worry about that you can write code that's much easier to write and maintain just by putting a few SQL queries in a loop!
Related: the N+1 problem is notorious in GraphQL world as one of the reasons building a high performance GraphQL API is really difficult.
Perhaps the overhead of chatty queries is diminished in this special use case.
But it doesn't change the fact that standalone database server processes are designed to support specific queries at lower frequencies. This is one of the main points of The SQL language is to load precisely the data that is needed in a single statement.
Relying on this is a design pattern would only scale in specific use cases and would hit hard walls in changing scenarios
https://sqlite.org/np1queryprob.html#the_need_for_over_200_s... talks about this in the context of Fossil, which uses hundreds of queries per page and loads extremely fast.
It turns out the N+1 thing really is only an anti-pattern if you're dealing with significant overhead per query. If you don't need to worry about that you can write code that's much easier to write and maintain just by putting a few SQL queries in a loop!
Related: the N+1 problem is notorious in GraphQL world as one of the reasons building a high performance GraphQL API is really difficult.
With SQLite you don't have to worry about that! I built https://datasette.io/plugins/datasette-graphql on SQLite and was delighted at how well it can handle deeply nested queries.