Node.js Reference ยท node:sqlite and Runtime Services
Node.js statement.get() and statement.all()
Reference for statement.get() and statement.all() in node:sqlite: fetch one row or every row, the shape of result objects, empty results and RETURNING clauses.
statement.get(...params) runs a prepared query and returns the first row. statement.all(...params) runs it and returns every row in an array. They take the same parameters as run(), so one prepared statement can be used with either method.
Use get() when you expect at most one row, for example a lookup by primary key or an aggregate such as count(*). Use all() when you want a list and know it is small enough to hold in memory. For large results, stream with iterate().
import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync(":memory:"); db.exec("CREATE TABLE t (x); INSERT INTO t VALUES (1), (2)"); console.log(db.prepare("SELECT x FROM t").all());
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.get([namedParameters][, ...anonymousParameters]) // row | undefined statement.all([namedParameters][, ...anonymousParameters]) // row[]
| Name | Description |
|---|---|
namedParameters |
Optional object that binds :name, @name or $name placeholders. |
anonymousParameters |
Values for ? placeholders, bound in order. |
Returns. get() returns the first row as an object, or undefined when the query returns no rows. all() returns an array of row objects; the array is empty when nothing matches. With setReturnArrays(true) each row is an array of values in column order.
A row object has a null prototype. Its keys are the column names (or aliases) and its values follow the SQLite-to-JavaScript mapping: integers become number (or bigint), reals become number, text becomes string, blobs become Uint8Array and NULL becomes null.
Errors and edge cases.
- If an integer cannot be represented exactly as a
number, reading it throws anERR_OUT_OF_RANGEerror. Callstatement.setReadBigInts(true)to read it asbigint. - When two columns have the same name, the later one overwrites the earlier one in an object row. Alias them, or use
setReturnArrays(true). - Too many parameters throw "column index out of range"; missing ones bind
NULL. - Calling either method after
statement.close()throwsERR_INVALID_STATE.
Usage notes
get()does not addLIMIT 1for you. SQLite stops after the first row, but the query may still sort or scan a lot first, so writeLIMIT 1yourself on large tables.all()builds the whole array before returning. A table with millions of rows can exhaust memory; page withLIMITandOFFSET(or a keyset condition) or useiterate().- Because
get()returnsundefinedfor no match and an object otherwise, test withif (row)rather than assuming a value. - Row objects do not have
Object.prototypemethods.JSON.stringify(row)works;row.hasOwnProperty("x")does not. INSERT,UPDATEandDELETEstatements with aRETURNINGclause behave like queries: useget()orall()to receive the returned rows.- Both methods run the statement to completion in one synchronous call, so a slow query blocks the event loop. Move heavy reads to a worker thread.