Skip to content
Tutorials Center
My learning

Topics

Node.js Reference ยท node:sqlite and Runtime Services

Node.js statement.iterate() method

Reference for statement.iterate() in node:sqlite: read a query row by row with for...of, stop early, avoid building big arrays and avoid invalidated iterators.

statement.iterate(...params) runs a prepared query and returns an iterator that produces one row at a time. Rows are fetched from SQLite as you ask for them, so a result with millions of rows never has to exist in memory all at once. That is the difference from all(), which builds the complete array before it returns.

The iterator works with for...of, spread, Array.from() and manual next() calls. Because the whole module is synchronous, iterating blocks the event loop while your loop body runs, so keep the body quick.

NODE
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE t (x); INSERT INTO t VALUES (1), (2), (3)");

for (const row of db.prepare("SELECT x FROM t").iterate()) {
  console.log(row.x);
}

Try it

Node.js Row by row, with an early exit

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Node.js One live iterator per statement

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Syntax

NODE
statement.iterate([namedParameters][, ...anonymousParameters])
Parameter Description
namedParameters Optional object binding :name, @name or $name placeholders.
anonymousParameters Values for ? placeholders, bound in order.

Returns. An iterator. Each step is an object { value, done }: while rows remain, value is a row (an object, or an array after setReturnArrays(true)) and done is false; at the end, done is true and value is null. The iterator is its own iterable, so it can be passed straight to for...of. Calling return() ends the query early, and leaving a for...of loop with break, return or an exception does so automatically.

Errors and edge cases.

  • A statement can have only one active iterator. Calling iterate() again on the same statement while an earlier iterator is unfinished invalidates the earlier one; the next call on it throws ERR_INVALID_STATE ("iterator was invalidated").
  • Errors inside SQLite while stepping (for example a runtime error from a custom function) are thrown from next(), not from iterate().
  • Integer values too large for a number throw ERR_OUT_OF_RANGE unless setReadBigInts(true) was called.

Usage notes

  • Use iterate() for exports, reports and migrations that touch many rows. Use all() when you need the whole list anyway, because it is simpler and a little faster for small results.
  • Finish or abandon an iterator before running the same statement again. Giving each concurrent loop its own prepared statement avoids the invalidation error.
  • Writing to a table while you are reading it with an iterator is allowed by SQLite, but whether the loop sees the new rows is not something to rely on. Collect the keys first and then write, or write in a separate pass.
  • A break out of the loop releases the statement for reuse. An iterator that you keep open, but never finish, keeps the read transaction open and can block checkpoints in WAL mode.
  • The loop body runs on the main thread between row fetches, and if you await inside the loop, the query stays open and other code can run against the same connection in the meantime. Keep the body short and synchronous.
  • Iterating does not make the query itself faster. An ORDER BY without a matching index still sorts everything before the first row appears.
  • To process in batches asynchronously, read keys in pages with LIMIT and all(), then await between pages.