class

sqlite.Statement

class Statement<ReturnType = unknown, ParamsType extends SQLQueryBindings[] = any[]>

A prepared statement.

This is returned by Database.prepare and Database.query.

const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");
stmt.all("baz");
// => [{bar: "baz"}]
  • readonly columnNames: string[]

    The names of the columns returned by the prepared statement.

    const stmt = db.prepare("SELECT bar FROM foo WHERE bar = ?");

console.log(stmt.columnNames);
// => ["bar"]
  • readonly columnTypes: null | 'TEXT' | 'INTEGER' | 'FLOAT' | 'BLOB' | 'NULL'[]

    The actual SQLite column types from the first row of the result set, as reported by sqlite3_column_type(). Useful for expressions and computed columns, which are not covered by declaredTypes.

    Returns an array of SQLite type constants as uppercase strings:

    • "INTEGER" for integer values
    • "FLOAT" for floating-point values
    • "TEXT" for text values
    • "BLOB" for binary data
    • "NULL" for null values
    • null for unknown/unsupported types

    Only available for read-only statements (SELECT queries). For other statements, accessing this property throws an error.

    const stmt = db.prepare("SELECT id, name, age FROM users WHERE id = 1");

console.log(stmt.columnTypes);
// => ["INTEGER", "TEXT", "INTEGER"]

// For expressions:
const exprStmt = db.prepare("SELECT length('bun') AS str_length");
console.log(exprStmt.columnTypes);
// => ["INTEGER"]
  • readonly declaredTypes: null | string[]

    The declared column types from the table schema, as reported by sqlite3_column_decltype().

    Returns an array of:

    • The exact type string declared in the CREATE TABLE statement
    • null for columns without declared types, such as expressions and computed columns

    Available for both read-only and read-write statements, before and after they run.

    // For table columns:
const stmt = db.prepare("SELECT id, name, weight FROM products");
console.log(stmt.declaredTypes);
// => ["INTEGER", "TEXT", "REAL"]

// For expressions (no declared types):
const exprStmt = db.prepare("SELECT length('bun') AS str_length");
console.log(exprStmt.declaredTypes);
// => [null]
  • readonly isFinalized: boolean

    Whether finalize was called, or the Database finalized the statement when it closed.

  • readonly native: any

    Native object representing the underlying sqlite3_stmt

    This is left untyped because the ABI of the native bindings may change at any time.

    For stable, typed access to statement metadata, use the typed properties on the Statement class:

    • columnNames for column names
    • paramsCount for parameter count
    • columnTypes for actual data types from the first row
    • declaredTypes for schema-declared column types
  • readonly paramsCount: number

    The number of parameters expected in the prepared statement.

    const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");
console.log(stmt.paramsCount);
// => 1
  • readonly reader: boolean

    Whether the prepared statement returns rows: it has at least one column.

    true for SELECT, for INSERT ... RETURNING and for a PRAGMA that reports a value.

  • readonly readonly: boolean

    Whether the prepared statement makes no direct change to the database file, as reported by sqlite3_stmt_readonly().

    BEGIN, COMMIT and SAVEPOINT are read-only in this sense.

  • Calls finalize if it wasn't already called.

  • [Symbol.iterator](): IterableIterator<ReturnType>;
  • ...params: ParamsType
    ): ReturnType[];

    Execute the prepared statement and return all results as objects.

    @param params

    optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

    const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");

stmt.all();
// => [] (nothing is bound yet)

stmt.all("baz");
// => [{bar: "baz"}]

stmt.all();
// => [{bar: "baz"}] (the last bound value)

stmt.all("foo");
// => [{bar: "foo"}]
  • as<T = unknown>(
    Class: new (...args: any[]) => T
    ): Statement<T, ParamsType>;

    Make get and all return an instance of the provided Class instead of the default Object, so the returned objects can have methods, getters, and setters.

    For performance reasons, class constructors are not called: initializers do not run and private fields are not accessible.

    @param Class

    The class to return rows as

    @returns

    The same statement instance, modified to return an instance of Class

    Custom class#

    class User {
   rawBirthdate: string;
   get birthdate() {
     return new Date(this.rawBirthdate);
   }
}

const db = new Database(":memory:");
db.run("CREATE TABLE users (id INTEGER PRIMARY KEY, rawBirthdate TEXT)");
db.run("INSERT INTO users (rawBirthdate) VALUES ('1995-12-19')");
const query = db.query("SELECT * FROM users");
query.as(User);
const user = query.get();
console.log(user.birthdate);
// => Date(1995, 12, 19)
  • finalize(): void;

    Finalize the prepared statement, freeing the resources used by the statement and preventing it from being executed again.

    This is called automatically when the prepared statement is garbage collected.

    It is safe to call this multiple times. Calling this on a finalized statement has no effect.

    Internally, this calls sqlite3_finalize.

  • ...params: ParamsType
    ): null | ReturnType;

    Execute the prepared statement and return the first result.

    If no result is returned, this returns null.

    @param params

    optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

    const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");

stmt.get();
// => null (nothing is bound yet)

stmt.get("baz");
// => {bar: "baz"}

stmt.get();
// => {bar: "baz"} (the last bound value)

stmt.get("foo");
// => {bar: "foo"}

    The following types can be used when binding parameters:

    JavaScript typeSQLite type
    stringTEXT
    numberINTEGER or REAL
    booleanINTEGER (1 or 0)
    Uint8ArrayBLOB
    BufferBLOB
    bigintINTEGER
    nullNULL
  • ...params: ParamsType
    ): IterableIterator<ReturnType>;

    Execute the prepared statement and return an iterator over the results.

    A statement has one cursor, and its newest run owns it: after another call of get, all, values, raw, run or iterate on the same Statement, the next step of this iterator throws. Use Database.prepare to get a statement with a cursor of its own.

    @param params

    optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

  • ...params: ParamsType
    ): null | Uint8Array<ArrayBufferLike>[][];

    Execute the prepared statement and return all results as arrays of Uint8Arrays.

    This is similar to values but returns every value as a Uint8Array, regardless of its original SQLite type.

    @param params

    optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

    const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");

stmt.raw("baz");
// => [[Uint8Array(24)]]

stmt.raw();
// => [[Uint8Array(24)]]
  • ...params: ParamsType

    Execute the prepared statement.

    @param params

    optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

    @returns

    A Changes object with changes and lastInsertRowid properties

    const insert = db.prepare("INSERT INTO users (name) VALUES (?)");
insert.run("Alice");
// => { changes: 1, lastInsertRowid: 1 }
insert.run("Bob");
// => { changes: 1, lastInsertRowid: 2 }

const update = db.prepare("UPDATE users SET name = ? WHERE id = ?");
update.run("Charlie", 1);
// => { changes: 1, lastInsertRowid: 2 }

    The following types can be used when binding parameters:

    JavaScript typeSQLite type
    stringTEXT
    numberINTEGER or REAL
    booleanINTEGER (1 or 0)
    Uint8ArrayBLOB
    BufferBLOB
    bigintINTEGER
    nullNULL
  • enabled: boolean
    ): this;

    Set whether this statement returns integers as bigint. The default is the safeIntegers option of the Database.

    With false, integers are returned as number, and integers beyond 53 bits are rounded to the nearest number. The setting also decides the type of lastInsertRowid in the result of run.

    Database.query returns the same Statement for the same SQL, so the setting stays for its next caller.

    @returns

    The same statement

    db.run("CREATE TABLE foo (id INTEGER PRIMARY KEY, big INTEGER)");
db.run("INSERT INTO foo (big) VALUES (?)", BigInt(Number.MAX_SAFE_INTEGER) + 10n);
const stmt = db.prepare("SELECT * FROM foo");

stmt.all();
// => [{ id: 1, big: 9007199254741000 }]

stmt.safeIntegers(true).all();
// => [{ id: 1n, big: 9007199254741001n }]
    safeIntegers(): boolean;

    Whether this statement returns integers as bigint.

  • toJSON(): { columnNames: string[]; isFinalized: boolean; paramsCount: number; sql: string };

    What JSON.stringify() shows for the statement.

  • toString(): string;

    Return the expanded SQL string for the prepared statement.

    Internally, this calls sqlite3_expanded_sql() on the underlying sqlite3_stmt.

    const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?", "baz");
console.log(stmt.toString());
// => "SELECT * FROM foo WHERE bar = 'baz'"
console.log(stmt);
// => "SELECT * FROM foo WHERE bar = 'baz'"
  • ...params: ParamsType
    ): null | string | number | bigint | Uint8Array<ArrayBufferLike>[][];

    Execute the prepared statement and return the results as an array of arrays.

    If there are no results, returns an empty array.

    @param params

    optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

    const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");

stmt.values("baz");
// => [['baz']]

stmt.values();
// => [['baz']]

stmt.values("foo");
// => [['foo']]

stmt.values("not-found");
// => []

    The following types can be used when binding parameters:

    JavaScript typeSQLite type
    stringTEXT
    numberINTEGER or REAL
    booleanINTEGER (1 or 0)
    Uint8ArrayBLOB
    BufferBLOB
    bigintINTEGER
    nullNULL