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.
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
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.
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
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 throwsERR_INVALID_STATE("iterator was invalidated"). - Errors inside SQLite while stepping (for example a runtime error from a custom function) are thrown from
next(), not fromiterate(). - Integer values too large for a
numberthrowERR_OUT_OF_RANGEunlesssetReadBigInts(true)was called.
Usage notes
- Use
iterate()for exports, reports and migrations that touch many rows. Useall()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
breakout 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
awaitinside 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 BYwithout a matching index still sorts everything before the first row appears. - To process in batches asynchronously, read keys in pages with
LIMITandall(), thenawaitbetween pages.