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 open statements 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. 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 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 as the callback property of the options object (optionally called xFunc in order to align with the C API documentation). 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 (see below for details).

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.

Design note: 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 set in the options object differ for scalar and aggregate functions:

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

Sidebar: 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 all SQL statements in the string provided to it. Its arguments must be one of:

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.

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. If the SQL contains no statements, an SQLite3Error is thrown.

Design note: 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.

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.

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

transaction()

any transaction(callback)

Starts a transaction, calls the given callback, and then either rolls back or commits the savepoint, 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().

The Stmt Class

Prepared statements are created solely through the DB.prepare() method. Calling their 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(...)

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

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 the bind() API.

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 finalizes. Returns undefined. 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 names after the columns of the result set.

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 with 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 passed a truthy argument then this.clearBindings() is also called, otherwise any existing bindings, along with any memory allocated for them, are retained.

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 finalizes this statement immediately after stepping unless the step cannot be performed because the statement is locked. Throws on error, but any error other than the statement-is-locked case will also trigger finalization of this statement.

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:

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

This subclass does not currently add any features beyond the base class, but such additions are a future possibility.

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.