DocsReferenceGuidesBlog
Discord logo
Discord
GitHub logo

query

Bun

Symbol

Database.query

query<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(): 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.

This does not execute the query, but instead prepares it for later execution and caches the compiled query if possible.

Under the hood, this calls sqlite3_prepare_v3.

@param sql

The SQL query to compile

@returns

Statment 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 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.

  • 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>;
  • all(): 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>(): Statement<T, ParamsType>;

    Make get and all return an instance of the provided Class instead of the default Object.

    @param Class

    A class to use

    @returns

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

    This lets you attach methods, getters, and setters to the returned objects.

    For performance reasons, constructors for classes are not called, which means initializers will not be called and private fields will not be accessible.

    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.

  • get(): 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
  • iterate(): IterableIterator<ReturnType>;

    Execute the prepared statement and return an

    @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.

  • Execute the prepared statement. This returns undefined.

    @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("UPDATE foo SET bar = ?");
stmt.run("baz");
// => undefined

stmt.run();
// => undefined

stmt.run("foo");
// => undefined

    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'"
  • values(): string | number | bigint | boolean | Uint8Array<ArrayBufferLike>[][];

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

    In Bun v0.6.7 and earlier, this method returned null if there were no results instead of []. This was changed in v0.6.8 to align more with what people expect.

    @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