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 Model.iterate() walks every matching row without loading them all, and takes the same options as findAll() — so this needs no SQL: Model.stream() gives the same rows as a Node Readable in object mode, for pipeline() and the transforms below: Both page through findAll() under a keyset cursor, exactly as findInBatches() does, which is what makes them work on all 27 dialects with no server-side cursor. Paging by the last id seen also cannot skip or repeat a row when the table is written to mid-scan, and stays fast at depth because the database never counts past rows. The trade is that they order by the primary key: a caller-supplied order is replaced rather than silently producing wrong pages. Backpressure is Node's own — stream() is Readable.from() over the generator, so the next page is fetched only when the consumer takes the previous one. A consumer that abandons the stream after three rows causes exactly one query. findInBatches() and findEach() remain for handler-style iteration: Note: dialect.queryStream() is still there and takes a SQL string. Prefer the model methods: they are typed, they respect scopes, paranoid filtering and hooks, and they require no SQL. Reach for queryStream() only when you want a native server-side cursor on PostgreSQL, MySQL or CockroachDB and are willing to write the query. 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() | Walking a large result | Model.iterate() | Piping it somewhere, with transforms | Model.stream() | Handler-style, a batch at a time | findInBatches() / findEach() | A native cursor, and you will write the SQL | dialect.queryStream() | Related reading Querying — findInBatches keyset iteration Bulk operations Raw queries Connection pooling — a long stream holds a connection