René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Proposal: generate low‑level query prepare and row‑binding helpers for streaming `:many` results","articleBody":"### What do you want to change?\n\n`sqlc` generates methods for `:many` queries that read all rows into a slice. For example, the parameterised `ListAuthorsByIDs` query executes:\n\n```sql\n-- name: ListAuthorsByIDs :many\nSELECT id, bio, birth_year FROM authors\nWHERE id = ANY($1::int[]);\n```\n\nand the generated Go method allocates a `[]Author`, iterates over `rows.Next()`, scans each row into a struct and appends it to the slice before returning. This is convenient but can be inefficient for large result sets because it requires storing all rows in memory.\n\n\u003cdetails\u003e\n\u003csummary\u003eStandard :many generated code\u003c/summary\u003e\n\n```go\nconst listAuthors = `-- name: ListAuthors :many\nSELECT id, name, bio FROM authors\nORDER BY name\n`\n\nfunc (q *Queries) ListAuthors(ctx context.Context) ([]Author, error) {\n\trows, err := q.db.QueryContext(ctx, listAuthors)\n\tif err != nil {\n\t\treturn nil, err\n\t}\n\tdefer rows.Close()\n\tvar items []Author\n\tfor rows.Next() {\n\t\tvar i Author\n\t\tif err := rows.Scan(\u0026i.ID, \u0026i.Name, \u0026i.Bio); err != nil {\n\t\t\treturn nil, err\n\t\t}\n\t\titems = append(items, i)\n\t}\n\tif err := rows.Close(); err != nil {\n\t\treturn nil, err\n\t}\n\tif err := rows.Err(); err != nil {\n\t\treturn nil, err\n\t}\n\treturn items, nil\n}\n```\n\u003c/details\u003e\n\n\nThere have been discussions about adding streaming APIs (e.g. via a `:stream` or `:callback` annotation) [1](https://github.com/sqlc-dev/sqlc/discussions/1229) and a long‑running PR to support iterators [2](https://github.com/sqlc-dev/sqlc/pull/3631). A maintainer noted that the forthcoming Go `iter` package may influence how such a feature should be implemented, and no built‑in solution has been decided on yet.\n\n### Proposal\n\nTo avoid committing to a particular iterator/channel API, I propose generating two low‑level helper functions per `:many` query when a new `emit_stream_helpers` option is enabled. These helpers decouple executing the query from scanning rows, enabling callers to implement their own streaming or pooling logic while relying on `sqlc`’s type‑safe scanning.\n\n#### Example using `ListAuthorsByIDs`\n\nToday `sqlc` generates a `ListAuthorsByIDs` method that returns `([]Author, error)` and reads all rows into a slice. With `emit_stream_helpers: true`, `sqlc` could additionally generate helpers like these:\n\n```go\nconst listAuthorsByIDs = `-- name: ListAuthorsByIDs :many\nSELECT id, bio, birth_year FROM authors\nWHERE id = ANY($1::int[])`\n\n// ListAuthorsByIDsPrepare executes the query and returns *sql.Rows.\n// It accepts the same parameters as the current ListAuthorsByIDs.\nfunc (q *Queries) ListAuthorsByIDsPrepare(ctx context.Context, ids []int64) (*sql.Rows, error) {\n    return q.db.QueryContext(ctx, listAuthorsByIDs, pq.Array(ids))\n}\n\n// ListAuthorsByIDsBindRow scans the current row into dest.\n// The caller is responsible for allocating dest and can reuse it.\nfunc (q *Queries) ListAuthorsByIDsBindRow(rows *sql.Rows, dest *Author) error {\n    return rows.Scan(\u0026dest.ID, \u0026dest.Bio, \u0026dest.BirthYear)\n}\n```\n\nA caller can then stream results without materialising a slice:\n\n```go\nctx := context.Background()\nrows, err := q.ListAuthorsByIDsPrepare(ctx, []int64{1, 2, 3})\nif err != nil { return err }\ndefer rows.Close()\n\nbuf := \u0026Author{} // could come from sync.Pool\nfor rows.Next() {\n    if err := q.ListAuthorsByIDsBindRow(rows, buf); err != nil {\n        return err\n    }\n    // process buf (e.g. write to a gRPC stream)\n}\nif err := rows.Err(); err != nil { return err }\n```\n\n### Benefits\n\n* **Constant memory usage:** The caller controls when each row is read; no slice is allocated.\n* **Memory pooling:** Because `BindRow` accepts a pointer, callers can reuse a struct from a `sync.Pool` to minimise allocations.\n* **Flexibility:** Low‑level helpers let users wrap them in a channel, iterator, or callback abstraction, and leave room for future integration with Go’s `iter` package.\n* **Consistency with existing patterns:** `sqlc` already supports prepared queries (`emit_prepared_queries`); this proposal applies a similar pattern at the per‑query level.\n\n### Open questions / feedback\n\n* Should helpers be generated by default or behind a new `emit_stream_helpers` flag?\n* What naming best communicates their purpose (e.g. `Prepare`/`BindRow`, `Rows`/`ScanRow`)?\n* Should `Prepare` return `*sql.Rows` (executing the query) or `*sql.Stmt` (requiring the caller to execute)?\n* Would returning a boolean from `BindRow` (e.g. `(bool, error)`) be useful for early termination?\n\nI plan to contribute an implementation for this feature. Before beginning, I’d appreciate feedback from maintainers and the community on whether this approach fits the project’s direction and how the API should be designed.\n\n\n### What database engines need to be changed?\n\nPostgreSQL\n\n### What programming language backends need to be changed?\n\nGo","author":{"url":"https://github.com/benjaco","@type":"Person","name":"benjaco"},"datePublished":"2025-09-15T13:24:44.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/4108/sqlc/issues/4108"}