Streaming Results

Sometimes a query returns more rows than you want to hold in memory at once: an export, a nightly job, a report over millions of records. Eager methods like get() and select() fetch and materialize the whole result set, which is fine for normal queries but wasteful for huge ones. That is what stream() is for.

When to stream

Reach for streaming when the result set is large enough that loading it all would strain a worker's memory. For everyday queries, stick with the eager methods; they are simpler and there is no reason to stream a handful of rows.

A stream keeps only one bounded batch in the worker at a time. As you iterate, Fast fetches the next batch behind the scenes, so peak memory stays flat no matter how many rows you process.

The three ways to stream

Streaming is available at every layer of the database API, and each yields a slightly different shape:

// Raw SQL: yields associative arrays.
foreach (db()->stream('SELECT * FROM events ORDER BY id', [/* params */]) as $row) {
	process($row);
}

// Query builder: also yields associative arrays.
foreach (table('events')->where('kind', 'sale')->stream(250) as $row) {
	process($row);
}

// Models: hydrates typed objects lazily, one at a time.
foreach (stream(User::class) as $user) {
	process($user);
}

The number you pass, like stream(250), is the batch size: how many rows Fast pulls per round trip. Larger batches mean fewer round trips but more memory per batch. The default is a reasonable middle ground.

Streams are forward-only and single-use

A stream is a one-way cursor. You iterate it once, front to back, and then it is done. You cannot rewind it or iterate it twice. This is what lets it stay cheap.

Crucially, a stream reserves a database resource for its whole lifetime, so you want to consume it promptly and let it finish:

An async standalone stream reserves one executor from the read pool for its entire lifetime. An idle stream is force-closed after cursorIdleTimeout seconds, but you should not rely on that. Do not open a stream and then wander off to do unrelated work while it sits there holding a handle.

How streams release their cursor

The good news is that the cursor is released automatically in every normal case. Any of these frees it:

• iterating to exhaustion (the loop finishes naturally);
• breaking out of the loop early;
• an exception propagating out;
• the stream object being garbage collected;
• calling $stream->close() explicitly.

So the common foreach usage just works. If you break early on purpose, the cursor is released the moment you leave the loop.

Streaming inside a transaction

Inside a transaction, use the transaction session's own stream() method, not db(). While a transaction stream is open, no other query may run on that session until the cursor is drained or closed. The transaction wrapper will force-close a lingering cursor before it commits or rolls back, but it is cleaner to close it yourself when you are done.

db()->transaction(function (Fast\Database\Session $tx): void {
	foreach ($tx->stream('SELECT * FROM ledger ORDER BY id') as $entry) {
		reconcile($entry);
	}
	// cursor is drained here; the transaction can now commit
});

Driver behavior

Different databases stream differently under the hood, and Fast picks the right mechanism for each: SQLite fetches incrementally, MySQL uses an unbuffered query for the cursor's lifetime, and PostgreSQL uses a true server-side cursor. Other PDO drivers fall back to incremental fetches, though whether the native client buffers internally is driver-specific.

That completes the database tour. Next we shift to protecting your app, starting with Sessions.