Ability to return an iterator on a "many" query

[encendre]

Hi, when a query return a myriad of objects, the slice based solution is not very efficient, it would be great if it was possible to have a way to return some kind of iterator on the rows, something like sql.Rows but type safe.

The first solution that comes to mind is to add a new type of function in the sql queries builder. Instead of writing -- name: Name :many we would write -- name: Name :rows or another relevant keyword

and for the generated code, it would look something like this

CREATE TABLE pilots (
          id integer NOT NULL,
          name text NOT NULL
);

ALTER TABLE pilots ADD CONSTRAINT pilot_pkey PRIMARY KEY (id);

-- name: ListPilots :rows
SELECT * FROM pilots;

type Pilot struct {
    ID   int32
    Name string
}

type PilotRows struct {
    rows *sql.Rows
}

func (p *PilotRows) Close() error {
    return p.rows.Close()
}
func (p *PilotRows) Next() bool {
    return p.rows.Next()
}
func (p *PilotRows) Scan(dest *Pilot) error {
    return p.rows.Scan(dest.ID, dest.Name)
}
func (p *PilotRows) Err() error {
    return p.rows.Err()
}

func (q *Queries) ListPilots(ctx context.Context) (*PilotRows, error) {
    rows, err := q.query(ctx, q.listPilotsStmtn, listPilots)
    if err != nil {
        return nil, err
    }
    return &PilotRows{rows: rows}, nil
}

[kyleconroy]

@encendre thanks for the feature request. You're suggestion fixes a few of the issues we discussed when this first came up in #155. I'd ask you the same question I asked in that thread: can you use some type of paging to work through this query? It's not usually a good idea to request thousands of rows from a single query.

[eivindr]

@encendre @kyleconroy In #155 I linked to a poc for streaming via an iterator func. It's used in a project that needs to stream out alot of entries. Paging should oc be preferred, but sometimes its not an option.

[pcriv]

My team and I are considering using sqlc for our services and having this feature would be a huge pro for going for sqlc. Anyway, how could we help with this?

Edit: We also need this for streaming records

[pcriv]

@eivindr Did you find a nice way to extend sqlc to support the iterator?

[eivindr]

@pcriv I changed the generator to produce code like https://github.com/kyleconroy/sqlc/issues/155#issuecomment-653754008 - so you pass in a iterator func that handles the streaming. If the func returns error the stream is aborted. You tell me if its a nice way or not :P

[pcriv]

That one looks good! but I assume you are using your fork for now. I was hoping there was a nice way of extending sqlc without having to rely on a fork 😅

[eivindr]

Yes, I'm just using the fork. I could create a PR if @kyleconroy pre-approves the fundamentals in the approach. The PR would also need to have some documentation, and maybe also generate similar for Kotlin..? :sweat_smile:

[pcriv]

Having this in sqlc would be a huge win for us :) If I can help somehow please let me know

[pcriv]

@eivindr maybe you can just open the PR and we can continue the discussion there?

[kyleconroy]

We're planning on adding support for this if the "range over func" proposal is accepted: https://github.com/golang/go/issues/61405

[dskarataev]

Hi @kyleconroy , the mentioned proposal has been accepted already. Would it be possible to go ahead with the requested feature? We also really need it for the case of exporting data in the streaming mode and where it is not convenient to chunk the data by running multiple queries.

Thanks a lot in advance!

[kyleconroy]

Range over func will be enabled by default on Go 1.23, so we're now unblocked from the Go side of things.

[tgmendes]

Hi @kyleconroy 👋

We're also quite interested in start using the range over func for these queries - do you know if they'll be available any time soon? Or if there's a workaround to configure the generator to use these?

Thanks!

[viewsharp]

Hi @kyleconroy Can I help you with implementation of this feature?

For me, the following implementation of iteration is preferable:

type IterCitiesRows struct{
    rows *sql.Rows
    err  error
}

func (r *IterCitiesRows) Iterate() iter.Seq[City] {
    if r.rows == nil {
        return func(yield func(City) bool) {}
    }

    return func(yield func(City) bool) {
        for r.rows.Next() {
            var i City
            err := r.rows.Scan(&i.Slug, &i.Name)
            if err != nil {
                r.err = err
                return
            }

            if !yield(i) {
                break
            }
        }

        if err = r.rows.Close(); err != nil {
            r.err = err
        }
    }
}

func (r *IterCitiesRows) Err() error {
    if r.err != nil {
        return r.err
    }
    return r.rows.Err()
}

func (q *Queries) IterCities(ctx context.Context) IterCitiesRows {
    rows, err := q.query(ctx, q.listCitiesStmt, listCities)
    if err != nil {
        return IterCitiesRows{err: err}
    }
    return IterCitiesRows{rows: rows}
}

func example(ctx context.Context, q *Queries) {
    rows := q.IterCities(ctx)
    for city := range rows.Iterate() {
        // do something with city
    }
    if err := rows.Err(); err != nil {
        // handle error
    }
}

[kyleconroy]

I found this article really helpful in understanding how to range over functions in Go 1.23 https://eli.thegreenplace.net/2024/ranging-over-functions-in-go-123/