Forest Gregg

Hey thanks for the suggestion! Definitely want to add pagination support and reduce the excessive network calls. A few notes:

1. I know this always doesnt solve the problem, but you can up that 1k row limit with datasette with the "--setting max_returned_rows 99999" flag https://docs.datasette.io/en/stable/settings.html#max-returned-rows

2. Instead of parsing CSV (which adds D3 as a dependency), I'd rather we still use the JSON endpoint, but without the `&_shape=array` params that are currently there. I did that bc it made getting it in "tidy" format easy, but if we instead use the default JSON API shape, we get significantly smaller payloads, as show in these two URLs:

~56KB: https://ds.agarcia.dev/congress/congress-members.json?sql=select+*+from+members
~130KB: https://ds.agarcia.dev/congress/congress-members.json?sql=select+*+from+members&_shape=array

We'd still need to do some manually transformations to get it into "tidy" data from the original rows/columns array, but definitely would be an improvement

3. You're totally right on the 'dont use with random' point. It also wont work if data gets inserted/deleted inbetween requests (which normally doesnt happen with datasette, but is technically possible). I'll definitely add more docs about caveats with this - also if the query already has LIMIT or OFFSET, then it won't work 
4. The original datasette client had a warning on the table view saying "Results possibly truncated" with a query limit was reached, but the current version of Observable's SQL cells don't offer a way to add custom warnings/messages to a table. I'll file a feature request for that! I think that alone would solve most of our headaches, if the message simply pointed people to the "--setting max_returned_rows" flag

Alex Garcia

all sounds good! 

one note, this approach *will* work if the original query has a limit or offset 'select * from (select * from example limit 10 offset 10) limit 5 offset 5' works.

another potential improvement:

instead of iterating through the queries and stopping when we get to a "page" that is empty, we could start with a count query. that would let us know all the queries we would need, and we could dispatch those asynch.

I switched to json and the previous url fetch method. it also makes the code a lot nicer because we get a 'truncated' flag back in the json response