Streaming results

Streaming results findAll() builds every row into memory before it returns. For a result set that does not fit — an export, a backfill, a migration of ten million rows — stream it instead. dialect.queryStream() returns a Node Readable in object mode, and the MapTransform / FilterTransform / BatchTransform / StreamIterator transforms compose on top of it. Because it is a standard Readable, pipeline(), pipe(), for await, backpressure and destroy() all behave the way you expect from Node streams. A failing query destroys the stream with the driver's error, so the for await loop rejects rather than ending silently. Two implementations Dialect | Mechanism | --- | --- | PostgreSQL, MySQL, CockroachDB | A native server-side cursor — true constant-memory streaming. | SQLite | better-sqlite3's Statement#iterate(). | Everything else | A paginated fallback: repeated pages of the query, one round trip per page. | The fallback wraps your statement and appends the dialect's own pagination clause: Each page is fetched only after the previous one has been drained by the consumer — real backpressure, not read-ahead buffering. It is slower than a cursor for very large sets, since every page is a fresh query, but it is correct everywhere and never loads the whole result at once. Oracle supplies its own page builder for its pagination syntax while reusing the same loop. Options Option | Default | Effect | --- | --- | --- | batchSize | 1000 | Rows fetched per page / cursor batch. | highWaterMark | 1000 | Stream buffer size. | model + mapToModel | — | Map each row to a model instance instead of a plain object. | transaction | — | Run the stream inside a transaction. | logging | — | Per-stream logging override. | A pointed caveat on the paginated fallback: it pages with OFFSET, so the underlying query must have a stable ORDER BY. Without one, rows can be skipped or repeated between pages. Order by the primary key. Cursor or pagination The fallback path is why a stable ORDER BY matters: paging with OFFSET against an unordered query can skip or repeat rows between pages. A cursor has no such problem. Streaming from a model There is no model-level streaming helper you can reach today. Use dialect.queryStream() with the SQL you want, or findInBatches(), which uses keyset pagination, works on every model, and yields real instances: src/models/model.ts contains a createModel() factory whose models do expose findAllStream() and findAllIterate(), built from FindOptions. That factory has no callers in the codebase and is not exported from the package — prorm.define(), prorm.addModel() and the decorator path all use a different implementation. Treat those two helpers as unreachable unless that parallel implementation is wired up or removed. The transforms All four are object-mode Transform streams with a default highWaterMark of 1000. MapTransform mapFn may be sync or async; a throw destroys the stream with that error. FilterTransform Filtering in the database is cheaper — use this only for predicates SQL cannot express, or that depend on something outside the database. BatchTransform Regroups a row stream into arrays, so downstream work happens per chunk rather than per row. The final partial batch is flushed at end-of-stream. StreamIterator Turns any readable into an async iterator, for stream sources that do not already support for await: A worked pipeline pipeline() propagates errors and destroys every stage, which is what you want for a long-running job. Choosing an approach Situation | Use | --- | --- | Result fits comfortably in memory | findAll() | Large result, need model instances and hooks | findInBatches() / findEach() | Very large result, plain rows, minimal overhead | dialect.queryStream() | Writing the result somewhere else | a pipeline() with the transforms above | Related reading Querying — findInBatches keyset iteration Bulk operations Raw queries Connection pooling — a long stream holds a connection