Cursors & Streaming

By default, execute() fetches up to fetchRows rows (default 100, see Executing Queries) and returns them all at once as QueryResult.rows. Pass cursor: true to instead get back a unidirectional Cursor that fetches rows from the database on demand.

const result = await client.execute(Select().from('customers'), { cursor: true });
const cursor = result.cursor!;

caution

A cursor keeps its underlying connection open until cursor.close() is called (or all rows have been read). If you obtained the cursor through client.execute(), the connection is retain()-ed for you and released automatically once the cursor closes; if you obtained it through connection.execute() on a connection you're managing yourself, make sure the cursor is closed (or fully consumed) before you release that connection.

Iterating a cursor

let row;
while ((row = await cursor.next())) {
  console.log(row);
}

cursor.next() moves forward by one row and returns it, or undefined once the cursor is exhausted (at which point it also closes itself and emits close).

Cursor API

Member	Description
connection	The SqbConnection this cursor was created on.
fields	FieldInfoMap describing the result's columns.
row	The current row.
rowNum	The current row number (0 before the first row).
isBof	true before the first row has been fetched.
isEof	true once the cursor has been fully consumed and you've stepped past the last row.
isClosed	true once the cursor has been closed.
fetchedRows	Total number of rows fetched from the database so far.
next()	Moves forward one row, returns it.
prev()	Moves back one row (requires caching, see below).
seek(step)	Moves forward (or backward, with caching) by step rows.
moveTo(rowNum)	Moves to an absolute row number (requires caching to move backward).
cached()	Enables an internal cache so the cursor can move backward and be re-read; must be called before any row has been fetched.
reset()	Rewinds to before the first row (requires caching).
fetchAll()	Fetches every remaining row into the cache and returns the count fetched (requires caching); after this, you can safely close() the cursor and keep reading from the in-memory cache.
close()	Closes the underlying adapter cursor.
toStream(options?)	Wraps the cursor in a CursorStream (see below).

Events: move, fetch, eof, reset, close, error.

Rows are fetched from the adapter in batches — the batch size is the query's fetchRows (default 100) — and buffered internally, so calling next() repeatedly doesn't round-trip to the database on every call.

Moving backward with cached()

By default a cursor is forward-only: prev(), moveTo() to an earlier row, or seek() with a negative step all throw unless you've called cursor.cached() first (and before any row has been fetched). With caching enabled, every fetched row is kept in memory so the cursor can move freely in both directions.

cursor.cached();
await cursor.seek(10);
await cursor.moveTo(3);
const row = cursor.row;

Streaming with CursorStream

cursor.toStream(options?) wraps the cursor in a CursorStream, a Node.js Readable:

const stream = cursor.toStream({ objectMode: true });
stream.on('data', row => console.log(row));
stream.on('end', () => console.log('done'));

Option	Type	Default	Description
objectMode	boolean	false	When true, the stream is in Node object mode and emits one row object per data event. When false (the default), the stream emits chunks of text that together form a single JSON array ([, each row as JSON.stringify(row), ,-separated, then ]) — pipe it straight to an HTTP response to stream a JSON array to a client.
limit	number	unlimited	Stops the stream after this many rows (closing the JSON array or ending object-mode output cleanly), without necessarily exhausting the underlying cursor.

CursorStream.isClosed mirrors the underlying cursor's isClosed. Closing the stream (reaching 'end', or calling stream.close() yourself) closes the underlying cursor and, if the cursor was retaining the connection, releases it.

Next steps

• Transactions
• Schemas