Feature request: Iterator support for handling large amount of data

[vroad]

Could we add a new finisher method that wraps *sql.Rows, so that we could iterate over rows without loading all of them at once? I found that Query finisher returns *sql.Rows, but couldn't find its usage in the documentation.

Something like iterator can still be done with some boilerplate code, according to #294.

I'd love to be able to load columns without writing boiler code like this for all types. Hooks and eager loading of relationships are not handled with this code, which could be an issue depending on your use case.

rows, err := models.Tweets().Query(db)
columns, err := rows.Columns()
valueMapping, err := queries.BindMapping(tweetType, tweetMapping, columns)
tweetRows := &tweetRows{
	rows:         rows,
	valueMapping: valueMapping,
}

var tweetType = reflect.TypeOf(&models.Tweet{})
var tweetMapping = queries.MakeStructMapping(tweetType)

type TweetRows struct {
	rows         *sql.Rows
	valueMapping []uint64
}

func (r *TweetRows) Next() bool {
	return r.rows.Next()
}

func (r *TweetRows) Scan(tweet *models.Tweet) error {
	pointers := queries.PtrsFromMapping(reflect.Indirect(reflect.ValueOf(tweet)), r.valueMapping)
	return r.rows.Scan(pointers...)
}

[aarondl]

Would be cool to see an implementation of this 👍🏽

[michelepra]

I've done a wrapping to iterate over sql.Rows but i can't now test performance in according to https://github.com/volatiletech/sqlboiler/issues/294#issuecomment-394400940

func Iterator[A any](query *queries.Query, ctx context.Context, exec boil.ContextExecutor) (*SqlRowsIterator[A], error) {
	if sqlRows, err := query.QueryContext(ctx, exec); err == nil {
		iterator := &SqlRowsIterator[A]{sqlRows: sqlRows}
		if err := iterator.init(); err == nil {
			return iterator, nil
		} else {
			return iterator, err
		}
	} else {
		return nil, err
	}
}

type SqlRowsIterator[A any] struct {
	sqlRows *sql.Rows

	elementType reflect.Type
	element     *A

	error   error
	hasRows bool
}

func (rowsIterator *SqlRowsIterator[A]) init() error {
	rowsIterator.hasRows = true

	var element A
	rowsIterator.elementType = reflect.TypeOf(element)
	if rowsIterator.elementType.Kind() != reflect.Struct {
		return fmt.Errorf("SqlRowsIterator can manage only struct")
	}

	return nil
}

func (rowsIterator *SqlRowsIterator[A]) Next() bool {
	if rowsIterator.error == nil && rowsIterator.hasRows {
		element := reflect.New(rowsIterator.elementType).Elem().Interface().(A)

		rowsIterator.element = &element
		rowsIterator.error = queries.Bind(rowsIterator.sqlRows, rowsIterator.element)

		if rowsIterator.error == nil {
			return true
		} else {
			rowsIterator.element = nil
			if errors.Is(rowsIterator.error, sql.ErrNoRows) {
				rowsIterator.error = nil
				rowsIterator.hasRows = false
			}
		}
	}
	return false
}

func (rowsIterator *SqlRowsIterator[A]) Error() error {
	return rowsIterator.error
}

func (rowsIterator *SqlRowsIterator[A]) Get() *A {
	return rowsIterator.element
}

func (rowsIterator *SqlRowsIterator[A]) SqlRows() *sql.Rows {
	return rowsIterator.sqlRows
}

func (rowsIterator *SqlRowsIterator[A]) Close() error {
	return rowsIterator.sqlRows.Close()
}

It can be used like

func queryIterator[A any](ctx context.Context, dbConnection *sql.DB, recordHook func(record *A), query *queries.Query) error {
	// execute query
	if iterator, err := Iterator[A](query, boil.WithDebug(ctx, true), dbConnection); err == nil {
		// close must always be called
		defer iterator.Close()

		// iterate over sql.Rows
		for iterator.Next() {
			record := iterator.Get()
			recordHook(record)
		}

		// get any error encountered during iteration
		return iterator.Error()
	} else {
		return err
	}
}