what is the right way to read large number of results in stream mode in batches (not all at once)?

[KrishnaPG]

How to use the stream reader object to read batched rows? The readUntil and readAll seems to return all records in memory at once, which is not efficient when you have large number of rows returned from sql;

This below code:

  async *query<T extends Row<T> = Record<string, DuckDBValue>>(
    queryOrStrings: TSQLString | TemplateStringsArray,
    ...params: DuckDBValue[]
  ): AsyncGenerator<T[]> {
    const { sql, values } = this.buildQueryParams(queryOrStrings, params);
    const reader = await this.streamAndRead(sql, values);

    let readTill = 10;
    // Stream results in chunks for memory efficiency
    while (!reader.done) {
      await reader.readUntil(readTill); // Read in chunks of 2048 rows
      const rows = reader.getRowObjects() as T[]; 
      yield rows;
      readTill += rows.length; 
    }
  }

This above code tries to read rows in batches using readUntil, but the returns rows object keeps accumulating over the calls, instead of giving batches of results;

What I am expecting is:

• return first n results, then
• return next n results, then
• return next n results

where as with readUntil we get:

• return first n results, then
• return first n + next n results (holding both in same array), then
• return first n + next n + next n results (all in one array in memory)

What I need is:
i.e. never all the returned rows should be in memory at any given point, which is not what is happening with readAll or readUntil methods currently.

[jraymakers]

The reader, which is returned by all the result methods with "read" in the name, is designed to do what you describe: fetch chunks and store them as you go. If you'd like to fetch chunks and discard them as you go, you should avoid the reader, and use the lower-level methods that fetch one chunk at a time, such as result.fetchChunk(). To ensure DuckDB doesn't fully materialize the result in memory, use connection.stream() instead of connection.run() to run the SQL and get the result object.

To make this even easier, the async iterator pattern is implemented on the result object. So, you can fetch one chunk at a time (without storing them) using an async loop like:

for await (const chunk of result) {
  // ...
}

There are also convenience methods that leverage this async loop to yield the result one row at a time, in various formats:

for await (const row of result.yieldRows()) {
  // ...
}

You can use the variants yieldRowObjects, yieldRowsJs, yieldRowObjectsJs, yieldRowsJson, or yieldRowObjectsJson to get the rows in different formats.

See the tests in this PR for more detailed examples.