Query Result Iterator

Hello sqlc folks, this package does really give a peace of mind, thanks!! 👍

I noticed that you are tagging query return cardinality: :exec (aka none), :one, :many. In the latter, :many, have you ever considered the case in which the whole result doesn't fit in memory? Apart from using DB.QueryContex is there any typed work-around?

Keep up the great work,
Enrico Bonetti Vieno

I haven't tackled the case where query results don't fit into memory. If I was writing the queries, I'd fix it by doing paging myself with filtering and limits. However, I get that there are times when you'd rather just stream the results.

I could imagine a world where tag a query :iter and generate code like this:

-- name: StreamAuthors :iter
SELECT * from authors;

package db

const listAuthors = `-- name: StreamAuthors :many
SELECT id, name, bio FROM authors
ORDER BY name
`

type AuthorStream struct {
  rows *sql.Rows
}

func (s *AuthorStream) Next() bool {
    return s.rows.Next()
}

func (s *AuthorStream) Close() error {
    return s.rows.Close()
}

func (s *AuthorStream) Err() error {
    return s.rows.Err()
}

func (s *AuthorStream) Scan() (Author, error) {
    var i Author
    return i, rows.Scan(&i.ID, &i.Name, &i.Bio)
}

func (q *Queries) StreamAuthors(ctx context.Context) (*AuthorStream, error) {
    rows, err := q.db.QueryContext(ctx, listAuthors)
    if err != nil {
        return nil, err
    }
    return &AuthorStream{rows}, nil
}

Paging, I didn't think about it.. 🤔 As for our parallel world, maybe even a closure iterator could work:

package db

const streamAuthors = `-- name: StreamAuthors :iter
SELECT id, name, bio FROM authors
ORDER BY name
`

func (q *Queries) StreamAuthors(ctx context.Context) func() (Author, error) {
	rows, queryErr := q.db.QueryContext(ctx, streamAuthors)
	if queryErr != nil {
		return func() (Author, error) {
			return Author{}, queryErr
		}
	}

	return func() (a Author, err error) {
		if !rows.Next() {
			return Author{}, rows.Err()
		}

		return a, rows.Scan(&a.ID, &a.Name, &a.Bio)
	}
}

The closure iterator won't work as written since you need to expose a Close method. Maybe you could return two funcs?

Actually you're right, to close prematurely I would use the context: to me this context use feels right since the closure is a way to iteratively explore the context call results. How do you feel about it?

package db

const streamAuthors = `-- name: StreamAuthors :iter
SELECT id, name, bio FROM authors
ORDER BY name
`

func (q *Queries) StreamAuthors(ctx context.Context) func() (Author, error) {
	rows, queryErr := q.db.QueryContext(ctx, streamAuthors)
	if queryErr != nil {
		return func() (Author, error) {
			return Author{}, queryErr
		}
	}

	ctx, cancel := context.WithCancel(ctx)
	go func() {
		<-ctx.Done()
		rows.Close() //Close is safe for concurrent use.
	}()

	return func() (a Author, err error) {
		if !rows.Next() {
			//Cancel used to terminate child context, Close already called by Next.
			cancel()
			return Author{}, rows.Err()
		}

		return a, rows.Scan(&a.ID, &a.Name, &a.Bio)
	}
}

The above code snippet still has concurrency issues. Since the cancel function isn't returned, a caller would need to continue to call the returned function until rows.Next returns false. The complexity of the returned closure leads me to believe sqlc shouldn't generate this type of code right now.

If more people need this functionality, we can revisit this in the future.

Someone more knowledgeable than me noticed that " QueryContext plumbs its ctx all the way down to rows.initContextClose, which arranges for rows.close to be called when ctx is canceled". So the code can be simplified back to the first revision:

package db

const streamAuthors = `-- name: StreamAuthors :iter
SELECT id, name, bio FROM authors
ORDER BY name
`

func (q *Queries) StreamAuthors(ctx context.Context) func() (Author, error) {
	rows, queryErr := q.db.QueryContext(ctx, streamAuthors)
	if queryErr != nil {
		return func() (Author, error) {
			return Author{}, queryErr
		}
	}

	return func() (a Author, err error) {
		if !rows.Next() {
			return Author{}, rows.Err()
		}

		return a, rows.Scan(&a.ID, &a.Name, &a.Bio)
	}
}

Sure a Close would be more apparent, still this code should now work like a charm:

	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()

	next := q.StreamAuthors(ctx)

	var a Author
	var err error
	for a, err = next(); err != nil; a, err = next() {
		//Do something with a
		if stopCondition {
			break
		}
	}

Keep up the great work,
Enrico Bonetti Vieno

Hi, sorry for bumping a closed issue. What if :iter generated something like

const streamAuthors = `-- name: StreamAuthors :iter
SELECT id, name, bio FROM authors
ORDER BY name
`

type streamAuthorsIter func(author Author) error

func (q *Queries) StreamAuthors(ctx context.Context, iter streamAuthorsIter) error {
	rows, err := q.db.QueryContext(ctx, streamAuthors)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var a Author
		if err := rows.Scan(
			&a.ID,
			&a.Name,
			&a.Bio,
		); err != nil {
			return err
		}

		err = iter(a)
		if err != nil {
			return err
		}
	}
	if err := rows.Close(); err != nil {
		return err
	}
	if err := rows.Err(); err != nil {
		return err
	}
	return nil
}

Compared to :many its basically adding a func type and calling the passed iter function instead of appending.

Pushed a quick and dirty poc - eivindr@64043b3

Navigation Menu

Repository navigation

Query Result Iterator #155

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Query Result Iterator #155

Description

Activity

kyleconroy commented on Dec 12, 2019

ebonetti commented on Dec 13, 2019

kyleconroy commented on Dec 13, 2019

ebonetti commented on Dec 14, 2019

kyleconroy commented on Dec 17, 2019

ebonetti commented on Dec 18, 2019

eivindr commented on Jul 4, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions