Compile a SQL query and return a Statement object.
This does not cache the compiled query and does not execute the query.
Under the hood, this calls sqlite3_prepare_v3.
Symbol
Database.prepare
prepare<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(sql: string, params?: ParamsType): Statement<ReturnType, ParamsType extends any[] ? ParamsType<ParamsType> : [ParamsType]>
@param sql
The SQL query to compile
@param params
Optional bindings for the query
@returns
A Statement instance
// compile the query
const stmt = db.query("SELECT * FROM foo WHERE bar = ?");
// run the query
stmt.all("baz");
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_stmtThis 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.
Execute the prepared statement and return all results as objects.
@param paramsoptional 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"}]Make get and all return an instance of the provided
Classinstead of the defaultObject.@param ClassA class to use
@returnsThe same statement instance, modified to return an instance of
ClassThis 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 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.Execute the prepared statement and return the first result.
If no result is returned, this returns
null.@param paramsoptional 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 type SQLite type stringTEXTnumberINTEGERorDECIMALbooleanINTEGER(1 or 0)Uint8ArrayBLOBBufferBLOBbigintINTEGERnullNULLExecute the prepared statement and return an
@param paramsoptional 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 paramsoptional 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"); // => undefinedThe following types can be used when binding parameters:
JavaScript type SQLite type stringTEXTnumberINTEGERorDECIMALbooleanINTEGER(1 or 0)Uint8ArrayBLOBBufferBLOBbigintINTEGERnullNULLReturn the expanded SQL string for the prepared statement.
Internally, this calls
sqlite3_expanded_sql()on the underlyingsqlite3_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'"Execute the prepared statement and return the results as an array of arrays.
In Bun v0.6.7 and earlier, this method returned
nullif there were no results instead of[]. This was changed in v0.6.8 to align more with what people expect.@param paramsoptional 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 type SQLite type stringTEXTnumberINTEGERorDECIMALbooleanINTEGER(1 or 0)Uint8ArrayBLOBBufferBLOBbigintINTEGERnullNULL