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.
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
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.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
changescounts only rows changed directly by the statement. Rows changed by triggers are not included.lastInsertRowidis a property of the connection, not of the statement. After anUPDATEorDELETEit still shows the last insert. Do not read it unless you have just run an insert.- Use
INSERT ... RETURNING idwith 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 aNULLsilently (or raise aNOT NULLerror), 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
BEGINandCOMMIT; see Transactions. - JavaScript integers beyond 2^53 lose precision as plain numbers. Bind them as
bigint, as in the example. - Calling
run()on aSELECTruns the query and discards the rows, and the returned numbers are leftovers from the last write on the connection. Useall()orget()instead.