Object Oriented API #1 (a.k.a. oo1)

Jump to...

When sqlite3.js is loaded and operated from the same thread, the primary API which JavaScript clients are likely to interact with is the high-level object-oriented API (colloquially known as oo1). It provides a flexible, reasonably feature-rich interface and can be used in conjunction with the lower-level C-style API when needed.

Its APIs are documented in detail in its source file and a demonstration of how it is used can be found in demo-123.

When sqlite3.js is loaded from one thread and operated from another, clients must instead communicate with the database via passing of Worker-style messages. One such API wrapper can be found in the Worker1 API, and clients are of course free to create their own based on either the oo1 API or the C-style API.

The oo1 Namespace

The sqlite3.oo1 object acts as a namespace for the oo1 functionality. The following docs elide that part for brevity's sake.

Exceptions

These APIs exclusively use exceptions to report errors, and always throw SQLite3Error objects unless they propagate an error from another API (e.g. Stmt.getJSON() might do that). Any docs which say "throws on error" specifically means that class unless noted otherwise.

The DB Class

Each instance of the DB class corresponds to one sqlite3* created using sqlite3_open() or its equivalent. It's as simple as:

const db = new sqlite3.oo1.DB();
try {
  db.exec([
    "create table t(a);",
    "insert into t(a) ",
    "values(10),(20),(30)"
  ]);
} finally {
  db.close();
}

A complete demo can be found in demo-123.js, demonstrated in demo-123.md.

DB Constructor

For the second form, the object may contain any of:

{
   filename: db filename,
   flags: open-mode flags,
   vfs: name of the sqlite3_vfs to use
}

The given db filename must be resolvable using whatever filesystem layer (virtual or otherwise) is set up for the default sqlite3 VFS.

Note that the special sqlite3 db names ":memory:" and "" (temporary db) have their normal special meanings here.

The second argument specifies the open/create mode for the database. It must be string containing a sequence of letters (in any order, but case sensitive) specifying the mode:

If "w" is not provided, the db is implicitly read-only, noting that "rc" is meaningless

Any other letters are currently ignored. The default is "c". These modes are ignored for the special ":memory:" and "" names and may be ignored by specific VFSes.

The final argument is analogous to the final argument of sqlite3_open_v2(): the name of an sqlite3 VFS. Pass a falsy value, or none at all, to use the default. If passed a value, it must be the string name of a VFS

The filename and vfs arguments may be either JS strings or C-strings allocated via WASM.

For purposes of passing a DB instance to C-style sqlite3 functions, the DB object's read-only pointer property holds its sqlite3* pointer value. That property can also be used to check whether this DB instance is still open.

In the main window thread, the filenames ":localStorage:" and ":sessionStorage:" are special: they cause the db to use either localStorage or sessionStorage for storing the database using the kvvfs. If one of these names are used, they trump any VFS name set in the arguments.

The constructor throws an SQLite3Error on error.

DB Class Properties and Methods

checkRc(db,resultCode)

Expects to be given a DB instance or an sqlite3* pointer (may be null) and an sqlite3 API result code. If the result code is not falsy, this function throws an SQLite3Error with an error message from sqlite3_errmsg(), using the given db handle, or sqlite3_errstr() if the db handle is falsy or is a close()ed DB instance.

Note that if it's passed a non-error code like SQLITE_ROW or SQLITE_DONE, it will still throw but the error string might be "Not an error." The various non-0 non-error codes need to be checked for in client code where they are expected.

If it does not throw, it returns its db argument (this, if called as a member function).

DB Non-method Properties

DB Methods

The instance methods of this class are described below in alphabetical order.

affirmOpen()

Throws if this given DB has been closed, else returns this.

changes()

int changes(total=false,sixtyFour=false)

Returns the number of changes, as per sqlite3_changes() (if the first argument is false) or sqlite3_total_changes() (if it's true). If the 2nd argument is true, it uses sqlite3_changes64() or sqlite3_total_changes64(), which will trigger an exception if this build does not have BigInt support enabled.

checkRc()

mixed checkRc(resultCode)

db.checkRc(resultCode) is equivalent to DB.checkRc(db,resultCode).

close()

Finalizes all still-open statements which were opened by this object and closes this database connection. This is a no-op if the db has already been closed. After calling close(), this.pointer will resolve to undefined, so that can be used to check whether the db instance is still opened.

If this.onclose.before is a function then it is called before any close-related cleanup.

If this.onclose.after is a function then it is called after the db is closed but before auxiliary state like this.filename is cleared.

Both onclose handlers are passed this object as their only argument. If this db is not opened, neither of the handlers are called. Any exceptions the handlers throw are ignored because "destructors must not throw."

Note that garbage collection of a db handle, if it happens at all, will never trigger close(), so onclose handlers are not a reliable way to implement close-time cleanup or maintenance of a db.

createFunction()

Creates a new scalar, aggregate, or window UDF (User-Defined Function) which is accessible via SQL code. This function may be called in any of the following forms:

In the final two cases, the function must be defined in the options object, as described below. In the final case, the function's name must be the name property.

The first two call forms can only be used for creating scalar functions. Creating an aggregate or window function requires the options-object form, as described below.

UDFs cannot currently be removed from a DB handle after they're added. More correctly, they can be removed as documented for sqlite3_create_function_v2(), but doing so will "leak" the JS-created WASM binding of those functions.

Such a leak may prove to be unavoidable at the level of this API, as there are multiple paths for performing the JS-to-WASM function conversion, not all of them in the sqlite3 API's control, and there is no central authority to keep track of those conversions. Because the function conversions' memory is managed in WASM's infrastructure, invisible to sqlite3, the leak applies even after closing a database in which a UDF is created. In practice, however, uninstalling UDFs is rare, and this leak only affects clients who install and uninstall UDFs. This leak can be avoided via client-level code by manually performing and managing the JS-to-WASM function conversions as needed.

On success, returns this object. Throws on error.

When called from SQL, arguments to the UDF, and its result, will be converted between JS and SQL with as much fidelity as is feasible, triggering an exception if a type conversion cannot be determined. Some freedom is afforded to numeric conversions due to friction between the JS and C worlds: integers which are larger than 32 bits will be treated as doubles or BigInt values.

The values required by the options object differ depending on the type of function:

The options object may optionally have an xDestroy function-type property, as per sqlite3_create_function_v2(). Its argument will be the WASM-pointer-type value of the pApp property, and this function will throw if pApp is defined but is not null, undefined, or a numeric (WASM pointer) value. i.e. pApp, if set, must be value suitable for use as a WASM pointer argument, noting that null or undefined will translate to 0 for that purpose.

The options object may contain flags to modify how the function is defined:

The following options-object properties correspond to flags documented at:

www:/c3ref/create_function.html

The ability to add new WASM-accessible functions to the runtime requires that the WASM build is compiled with the equivalent functionality as that provided by Emscripten's -sALLOW_TABLE_GROWTH flag.

dbFilename()

string dbFilename(dbName='main')

This is simply a proxy for sqlite3_db_filename(), returning the filename associated with the given database name, defaulting to "main". The argument may be either a JS string or a pointer to a WASM-allocated C-string. Throws if this db is closed.

dbName()

string dbName(dbIndex=0)

Returns the name of the given 0-based db number, as documented for sqlite3_db_name(). Throws if this db is closed.

dbVfsName()

string dbVfsName(dbName=0)

Returns the name of the sqlite_vfs for the given database, defaulting to "main". The db name may be a JS or WASM C-string. Throws if this db is closed.

exec()

Executes SQL statements and optionally collects query results and/or calls a callback for each result row. Call forms:

In the latter case, optionsObject.sql must contain the SQL to execute. By default it returns this object, but that can be changed via the returnValue option described below. It throws on error.

If no SQL is provided, or a non-string is provided, an exception is triggered. Empty SQL, on the other hand, is simply a no-op.

The optional options object may contain any of the following properties:

The following options apply only to the first statement which has a non-zero result column count, regardless of whether the statement actually produces any result rows.

The first argument passed to the callback defaults to an array of values from the current result row but may be changed with...

Any other rowMode value triggers an exception.

isOpen()

Returns true if this db handle is open, else false.

openStatementCount()

Returns the number of currently-opened Stmt handles for this db handle, or 0 if this object is close()d. Note that only handles prepwared via this.prepare() are counted, and not handles prepared using capi.sqlite3_prepare_v3() (or equivalent).

prepare()

Compiles the given SQL and returns a prepared Stmt. This is the only way to create new Stmt objects. Throws on error.

The SQL argument may be any type described for flexible-string conversions. If the SQL contains no statements, an SQLite3Error is thrown.

The C API permits empty SQL, reporting it as a 0 result code and a NULL stmt pointer. Supporting that case here would cause extra work for all clients: any use of the Stmt API on such a statement will necessarily throw, so clients would be required to check stmt.pointer after calling prepare() in order to determine whether the Stmt instance is empty or not. Long-time practice with other sqlite3 script bindings suggests that the empty-prepare case is sufficiently rare that supporting it here would simply hurt overall usability.

savepoint()

any savepoint(callback)

This works similarly to transaction() but uses sqlite3's SAVEPOINT feature. This function starts a savepoint (with an unspecified name) and calls the given callback function, passing it this db object. If the callback returns, the savepoint is released (committed). If the callback throws, the savepoint is rolled back. If it does not throw, it returns the result of the callback.

selectArray()

mixed selectArray(SQL [,bind])

Prepares the given SQL, step()s it one time, and returns an array containing the values of the first result row. If it has no results, undefined is returned.

If passed a second argument other than undefined, it is treated like an argument to Stmt.bind(), so may be any type supported by that function.

Throws on error.

selectArrays()

mixed selectArrays(SQL [,bind])

Runs the given SQL and returns an array of all results, with each row represented as an array, as per the 'array' rowMode option to exec(). An empty result set resolves to an empty array. The second argument, if any, is treated as the bind option to a call to exec().

Throws on error.

selectObject()

mixed selectObject(SQL [,bind])

Prepares the given SQL, step()s it one time, and returns an object containing the key/value pairs of the first result row. If it has no results, undefined is returned.

Note that the order of returned object's keys is not guaranteed to be the same as the order of the fields in the query string.

If passed a second argument other than undefined, it is treated like an argument to Stmt.bind(), so may be any type supported by that function.

Throws on error.

selectObjects()

mixed selectObjects(SQL [,bind])

Works identically to selectArrays() except that each value in the returned array is an object, as per the "object" rowMode option to exec().

selectValue()

any selectValue(SQL [,bind [,asType]])

Prepares the given SQL, step()s the resulting Stmt one time, and returns the value of the first result column. If it has no results, undefined is returned.

If passed a second argument, it is treated like an argument to Stmt.bind(), so may be any type supported by that function. Passing the undefined value is the same as passing no value, which is useful when...

If passed a 3rd argument, it is expected to be one of the SQLITE_{typename} constants. Passing the undefined value is the same as not passing a value.

Throws on error (e.g. malformed SQL).

selectValues()

array selectValues(SQL [,bind [,asType]])

Runs the given query and returns an array of the values from the first result column of each row of the result set. The 2nd argument is an optional value for use in a single-argument call to Stmt.bind(). The 3rd argument may be any value suitable for use as the 2nd argument to Stmt.get(). If a 3rd argument is desired but no bind data are needed, pass undefined for the 2nd argument.

If there are no result rows, an empty array is returned.

transaction()

any transaction([beginQualifier,] callback)

Starts a transaction, calls the given callback, and then either rolls back or commits the transaction, depending on whether the callback throws. The callback is passed this object as its only argument. On success, returns the result of the callback. Throws on error.

Note that transactions may not be nested, so this will throw if it is called recursively. For nested transactions, use the savepoint() method or manually manage SAVEPOINTs using exec().

If called with 2 arguments, the first must be a keyword which is legal immediately after a BEGIN statement, e.g. one of "DEFERRED", "IMMEDIATE", or "EXCLUSIVE". Though the exact list of supported keywords is not hard-coded here, in order to be future-compatible, if the argument does not look like a single keyword then an exception is triggered with a description of the problem.

The Stmt Class

Prepared statements are created solely through the DB.prepare() method. Calling the constructor directly will trigger an exception.

It is important that statements be finalized in a timely manner, else clients risk introducing locking errors later on in their apps.

By and large, clients can avoid statement lifetime issues by using the DB.exec() method. For cases when more control or flexibility is needed, however, clients will need to prepare() statements and then ensure that their lifetimes are properly managed. The simplest way to do this is with a try/finally block, as in this example:

const stmt = myDb.prepare("...");
try {
  ... use the stmt object ...
} finally {
  stmt.finalize();
}

Stmt Non-method Properties

Stmt Methods

The instance methods of this class are described below in alphabetical order.

bind()

Stmt bind([ndx=1,] value)

Binds one or more values to its bindable parameters. It accepts 1 or 2 arguments:

If passed a single argument, it must be either an array, an object, or a value of a bindable type (see below). Its bind index is assumed to be 1.

If passed 2 arguments, the first one is the 1-based bind index or bindable parameter name and the second one must be a value of a bindable type.

Bindable value types:

If passed an array, each element of the array is bound at the parameter index equal to the array index plus 1 (because arrays are 0-based but binding is 1-based).

If passed an object, each object key is treated as a bindable parameter name. The object keys must match any bindable parameter names, including any $, @, or : prefix. Because $ is a legal identifier chararacter in JavaScript, that is the suggested prefix for bindable parameters: stmt.bind({$a: 1, $b: 2}).

It returns this object on success and throws on error. Errors include:

bindAsBlob()

Stmt bind([ndx=1,] value)

Special case of bind() which binds the given value using the BLOB binding mechanism instead of the default selected one for the value. The index may be a numbered or named bind index. The value must be of type string, null/undefined (both get treated as null), or a TypedArray of a type supported by bind().

If passed a single argument, a bind index of 1 is assumed and the first argument is the value.

clearBindings()

Stmt clearBindings()

Clears all bound values. Returns this object. Throws if this statement has been finalized.

finalize()

void finalize()

"Finalizes" this statement. This is a no-op if the statement has already been finalized. Returns the value of the underlying sqlite3_finalize() call (0 on success, non-0 on error) or undefined if the statement has already been finalized. It does not throw if sqlite3_finalize() returns non-0 because this function is effectively a destructor and "destructors do not throw." This function will throw if it is called while the statement is in active use via a DB.exec() callback.

Most methods in this class will throw if called after this is.

get()

any get(ndx [, asType])

Fetches the value from the given 0-based column index of the current data row, throwing if index is out of range.

Requires that step() has just returned a truthy value, else an exception is thrown.

By default it will determine the data type of the result automatically. If passed a second arugment, it must be one of the enumeration values for sqlite3 types, which are defined as members of the sqlite3 namespace: SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB. Any other value, except for undefined, will trigger an exception. Passing undefined is the same as not passing a value. It is legal to, e.g., fetch an integer value as a string, in which case sqlite3 will convert the value to a string.

If the index is an array, this function behaves a differently: it assigns the indexes of the array, from 0 to the number of result columns, to the values of the corresponding result column, and returns that array:

const values = stmt.get([]);

will return an array which contains one entry for each result column of the statement's current row..

If the index is a plain object, this function behaves even differentlier: it assigns the properties of the object to< the values of their corresponding result columns:

const values = stmt.get({});

returns an object with properties named after the columns of the result set. Be aware that the ordering of the properties is undefined. If their order is important, use the array form instead.

Blobs are returned as Uint8Array instances.

Special case handling of 64-bit integers: the Number type is used for both floating point numbers and integers which are small enough to fit into it without loss of precision. If a larger integer is fetched, it is returned as a BigInt if that support is enabled, else it will throw an exception. The range of integers supported by the Number class is defined as:

getBlob()

Uint8Array|null get(ndx)

Equivalent to get(ndx) but coerces the result to a Uint8Array.

getColumnName()

string getColumnName(ndx)

Returns the result column name of the given index, or throws if index is out of bounds or this statement has been finalized. This may be used without having run step() first.

getColumnNames()

array getColumnNames(target=[])

If this statement potentially has result columns, this function returns an array of all such names. If passed an array, it is used as the target and all names are appended to it. Returns the target array. Throws if this statement cannot have result columns. this.columnCount, set when the statement is prepared, holds the number of columns.

getFloat()

number|null get(ndx)

Equivalent to get(ndx) but coerces the result to a number.

getInt()

number|null get(ndx)

Equivalent to get(ndx) but coerces the result to a number.

getJSON()

any get(ndx)

Equivalent to getString(ndx) but returns passes the result of passing the fetched string string through JSON.parse(). If JSON parsing throws, that exception is propagated.

getParamIndex()

int getParamIndex(name)

If this statement has named bindable parameters and the given name matches one, its 1-based bind index is returned. If no match is found, 0 is returned. If it has no bindable parameters, the undefined value is returned.

getString()

string|null get(ndx)

Equivalent to get(ndx) but coerces the result to a string.

reset()

Stmt reset(alsoClearBinds=false)

Resets this statement so that it may be step()ed again from the beginning. Returns this. Throws if this statement has been finalized, if it may not legally be reset because it is currently being used from a DB.exec() callback, or (as of versions 3.42.1 and 3.43) if the underlying call to sqlite3_reset() returns non-0.

If passed a truthy argument then this.clearBindings() is also called, otherwise any existing bindings, along with any memory allocated for them, are retained.

In versions 3.42.0 and earlier, this function did not throw if sqlite3_reset() returns non-0, but it was discovered that throwing (or significant extra client-side code) is necessary in order to avoid certain silent failure scenarios, as discussed in the SQLite forum.

step()

bool step()

Steps the statement one time. If the result indicates that a row of data is available, a truthy value is returned. If no row of data is available, a falsy value is returned. Throws on error.

stepFinalize()

bool stepFinalize()

Functions like step() except that it calls finalize() on this statement immediately after stepping unless the step() throws.

On success, it returns true if the step indicated that a row of data was available, else it returns false.

This is intended to simplify use cases such as:

db.prepare("INSERT INTO foo(a) VALUES(?)").bind(123).stepFinalize();

stepReset()

Stmt stepReset()

Functions exactly like step() except that...

This is intended to simplify constructs like:

for(...) {
  stmt.bind(...).stepReset();
}

Note that the reset() call makes it illegal to call this.get() after the step.

JsStorageDb

When the sqlite3 API is installed in the main thread, the JsStorageDb class, a subclass of the DB class, is added, which simplifies usage of the kvvfs.

const db = new sqlite3.oo1.JsStorageDb('local' /* or 'session' */);
... use like any other db ...
if( db.storageSize() ) {
  db.clearStorage(); // empty it!
}
db.close();

The JsStorageDb object includes these class-level methods:

which are simply proxies for sqlite3_js_kvvfs_clear() and sqlite3_js_kvvfs_size(). The member functions with the same names only operate on their own storage object:

const db = new sqlite3.oo1.JsStorageDb('local');
console.log('db size =',db.storageSize()); // only localStorage size
db.clearStorage(); // clears only the localStorage db

OpfsDb class

The OpfsDb, a subclass of the DB class, is installed only if OPFS VFS support is active. It can be used like:

const db = new sqlite3.oo1.OpfsDb('/path/to/my/db','c');
// Or: new sqlite3.oo1.OpfsDb({filename: ..., flags: 'c'});

The arguments have the same meanings as for the DB constructor but the directory parts leading up to the file will be created if the open flags contain 'c' (create). It throws if the database cannot be opened. See the OPFS VFS docs for information about file locking, noting that any given DB is accessible to all browser tabs which are currently visiting the same HTTP origin.

This subclass adds one static method to the API:

OpfsDb.importDb()

(Added in 3.43.)

The class-level (static) importDb() method can (asynchronously) import a database into OPFS storage. It only works with database files and will throw if passed a different file type. Usage:

sqlite3.oo1.OpfsDb.importDb('filename', byteArray);

Notes:

It throws on error, resulting in a rejected promise. In such cases, it may leave a partially-written file in the filesystem.

As of version 3.44, if passed a function for its second argument then its behaviour changes to import its data in chunks fed to it by the given callback function. It calls the callback (which may be async) repeatedly, and expects a return value of either a Uint8Array or ArrayBuffer (to denote new input) or undefined (to denote EOF). For so long as the callback continues to return non-undefined, it will append incoming data to the given VFS-hosted database file.

  1. ^ Recall that the length of a function is the number of declared parameters it has.