Skip to content
Tutorials Center
My learning

Topics

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

Node.js statement.run() method

Reference for statement.run() in node:sqlite: execute writes, read changes and lastInsertRowid, bind parameters, use BigInt results and handle constraint errors.

statement.run(...params) executes a prepared statement and reports what it changed. It is the method for INSERT, UPDATE, DELETE, and any other statement where you care about the effect rather than about rows coming back, such as CREATE TABLE or REPLACE.

It returns a small object with two numbers: changes, the number of rows the statement inserted, updated or deleted, and lastInsertRowid, the row id given to the most recent insert on the connection. Any parameters you pass are bound to the placeholders before the statement runs.

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

const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)");
const info = db.prepare("INSERT INTO t (v) VALUES (?)").run("hello");
console.log(info); // { changes: 1, lastInsertRowid: 1 }

Try it

Node.js Reading changes and lastInsertRowid

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 Conflicts, BigInt and errors

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.run([namedParameters][, ...anonymousParameters])
Parameter Description
namedParameters An optional object whose keys match the named placeholders (:name, @name, $name).
anonymousParameters Zero or more values bound to ? placeholders in order.

Accepted value types are null, number, bigint, string, Buffer, TypedArray and DataView. See SQLite value types.

Returns. An object { changes, lastInsertRowid }. Both are numbers by default and both become bigint after statement.setReadBigInts(true).

Field Meaning
changes Rows inserted, updated or deleted by the most recent write on the connection. It is 0 when that write matched nothing.
lastInsertRowid The row id of the most recent successful insert on this connection.

Errors. Constraint violations (UNIQUE, NOT NULL, CHECK, FOREIGN KEY) throw an Error with code: "ERR_SQLITE_ERROR", a numeric errcode and an errstr. More parameters than the statement has placeholders throws "column index out of range". A value of an unsupported type throws ERR_INVALID_ARG_TYPE or a similar error.

Usage notes

  • changes counts only rows changed directly by the statement. Rows changed by triggers are not included.
  • lastInsertRowid is a property of the connection, not of the statement. After an UPDATE or DELETE it still shows the last insert. Do not read it unless you have just run an insert.
  • Use INSERT ... RETURNING id with get() when you want the new row's columns, not just its id.
  • Missing parameters are not an error: an unbound placeholder is NULL. A forgotten argument can therefore insert a NULL silently (or raise a NOT NULL error), so check column constraints.
  • A statement that fails midway through a multi-row change may leave some rows changed unless a transaction wraps it. Wrap related writes in BEGIN and COMMIT; see Transactions.
  • JavaScript integers beyond 2^53 lose precision as plain numbers. Bind them as bigint, as in the example.
  • Calling run() on a SELECT runs the query and discards the rows, and the returned numbers are leftovers from the last write on the connection. Use all() or get() instead.