Skip to content
Tutorials Center
My learning

Topics

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().

NODE
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

Node.js One row, no row, many rows

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 RETURNING, array rows and duplicate names

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.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 an ERR_OUT_OF_RANGE error. Call statement.setReadBigInts(true) to read it as bigint.
  • 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() throws ERR_INVALID_STATE.

Usage notes

  • get() does not add LIMIT 1 for you. SQLite stops after the first row, but the query may still sort or scan a lot first, so write LIMIT 1 yourself on large tables.
  • all() builds the whole array before returning. A table with millions of rows can exhaust memory; page with LIMIT and OFFSET (or a keyset condition) or use iterate().
  • Because get() returns undefined for no match and an object otherwise, test with if (row) rather than assuming a value.
  • Row objects do not have Object.prototype methods. JSON.stringify(row) works; row.hasOwnProperty("x") does not.
  • INSERT, UPDATE and DELETE statements with a RETURNING clause behave like queries: use get() or all() 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.