method

sqlite.Database.query

query<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
sql: string
): Statement<ReturnType, ParamsType extends any[] ? ParamsType<ParamsType> : [ParamsType]>;

Compile a SQL query and return a Statement object. This is the same as prepare except that it caches the compiled query if possible.

This does not execute the query; it prepares it for later execution.

Internally, this calls sqlite3_prepare_v3.

@param sql

The SQL query to compile

@returns

A Statement instance

// compile the query
const stmt = db.query("SELECT * FROM foo WHERE bar = ?");
// run the query
stmt.all("baz");

// run the query again
stmt.all();

Referenced types

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

    The statement must be executed at least once before accessing this property. Available for both read-only and read-write statements.

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

// For expressions (no declared types):
const exprStmt = db.prepare("SELECT length('bun') AS str_length");
exprStmt.get();
console.log(exprStmt.declaredTypes);
// => [null]
  • 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

  • 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("baz");
// => [{bar: "baz"}]

stmt.all();
// => []

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.exec("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("baz");
// => {bar: "baz"}

stmt.get();
// => null

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

    The following types can be used when binding parameters:

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

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

    @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 DECIMAL
    booleanINTEGER (1 or 0)
    Uint8ArrayBLOB
    BufferBLOB
    bigintINTEGER
    nullNULL
  • 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
    ): string | number | bigint | boolean | 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 DECIMAL
    booleanINTEGER (1 or 0)
    Uint8ArrayBLOB
    BufferBLOB
    bigintINTEGER
    nullNULL