Add streaming of :many via chan and/or callback func

[cloneable]

I noticed that :many queries collect result objects in slices. That could be inefficient for large number of rows. I was wondering if you'd be willing to add/allow support for some sort of :stream annotation, which produces functions like :many, but they return a <-chan T instead. The channel can be read from in a different goroutine while the first goroutine does the row scan. It closes the channel when done. The channel should definitely be buffered, but I've got no idea how to set the size. Could be a field in Queries{}.

Another idea is a :callback annotation. A query function takes a func(T) callback as parameter, which handles the scanned T one by one. No separate goroutine, but also no buffering.

[cameronpm]

A :callback annotation is cleaner and if only one of the two approaches were chosen, much simpler when you want to read rows synchronously (while allowing you to implement :stream yourself). It also allows the caller an extra means of requesting cancellation by returning a bool (ie func(T) (ok bool)) as opposed to cancelling the context.

[cloneable]

True, the callback would allow one to implement streaming. I thought it's a bit much ceremony at the moment to do it in every callback, with selecting on chan<- and <-ctx.Done() and all. Needs to be done correctly and the code generator would be ideal. However, I realized with the upcoming support for generics a simple generic helper function could take care of this, too.

[PatrLind]

Today I was looking for a way to make sqlc to support streaming a large amount of data from a query to gRPC. I found the related issues and this discussion. To me a simple callback option seems like the best way to do it.
But now that generics have been out for a while, is there any more ideas around this topic?

[perbu]

I've just ran into this. I'm working on some maintenance work on a database and I don't know how many rows I get back. Could be 1K or 1M. I really love sqlc and the correctness it brings.

It would be really amazing to have a callback that would get fired off for each row.

[andrewmbenton]

Given the new iter package proposal (golang/go#61897) is likely to move forward, I think we won't want to add functionality until we know how to integrate with that package.

[Mxrk]

Hi, what do you think about the updates from go 1.23? (Found this discussion here via google, not sure if I should create a new one?)

[atomotic]

I see there is a PR for iterator #3631
Any plans on when this will be merged?

[DrBlury]

What's the status here?

[perbu]

imho, I think this ticket should be closed. Iterators is the obvious way forward and since there is quite good progress in a PR, we should direct ppl there to evaluate the PR.

[atomotic]

Since the iterator PR hasn't been merged yet, I'm curious about current best practices for handling large table queries.
What approaches are you using to select from huge tables? Is pagination the recommended solution, or are there other patterns the community has found effective?

[DrBlury]

I think pagination is the way to go for now.