The Sqlite.jsm
JavaScript code module is a promise-based wrapper around the Storage/SQLite interface. Sqlite.jsm
offers some compelling advantages over the low-level Storage XPCOM interfaces:
- Automatic statement management. Sqlite.jsm will create, manage, and destroy statement instances for you. You don't need to worry about caching created statement instances, destroying them when you are done, etc. This translates to fewer lines of code to talk to SQLite.
- All operations are asynchronous. Use of synchronous Storage APIs is discouraged because they block the main thread. All the functionality in
Sqlite.jsm
is asynchronous. - Easier memory management. Since
Sqlite.jsm
manages statements for you, it can perform intelligent actions like purging all cached statements not in use, freeing memory in the process. There is even ashrinkMemory
API that will minimize memory usage of the connection automatically. - Simpler transactions. Sqlite.jsm exposes a transaction API built on top of Task.jsm that allows transactions to be written as procedural JavaScript functions (as opposed to a series of callback driven operations). If the function throws, the transaction is automatically rolled back. This makes code easy to write and read.
- JavaScript-y API.
Sqlite.jsm
feels like a pure JavaScript module. The complexities of XPCOM are mostly hidden from view. JavaScript programmers should feel right at home usingSqlite.jsm
.
Sqlite.jsm
JavaScript code module can only be used from chrome -- that is, from within the application itself or an add-on.Before you can use this module, you need to import it into your scope:
Components.utils.import("resource://gre/modules/Sqlite.jsm")
Obtaining a Connection
Sqlite.jsm
exports the Sqlite
symbol. This symbol is an object with a single function: openConnection
. This function takes an object defining connection options:
- path
- (required) The database file to open. This can be an absolute or relative path. If a relative path is given, it is interpreted as relative to the current profile's directory. If the path does not exist, a new SQLite database will be created. The value typically ends with
.sqlite
. - sharedMemoryCache
- (optional) Boolean indicating whether multiple connections to the database share the same memory cache. Sharing the memory cache likely results in less memory utilization. However, sharing also requires connections to obtain a lock, possibly making database access slower. Defaults to true.
- shrinkMemoryOnConnectionIdleMS
- (optional) If defined, the connection will attempt to minimize its memory usage after this many milliseconds of connection idle. The connection is idle when no statements are executing. There is no default value which means no automatic memory minimization will occur. Please note that this is not a timer on the idle service and this could fire while the application is active.
openConnection(options)
returns a promise that resolves to an opened connection instance or is rejected if an error occurred while opening the database.
Here is an example:
Components.utils.import("resource://gre/modules/Sqlite.jsm"); try { const conn = await Sqlite.openConnection({ path: "myDatabase.sqlite", sharedMemoryCache: false }); // connection is the opened SQLite connection (see below for API). } catch (error) { // The connection could not be opened. error is an Error describing what went wrong. }
Working with Opened Connections
Opened connections are what you will interface most with in Sqlite.jsm
. The following sections detail the API of an opened connection instance.
Connection Management
These APIs are used to manage and inspect the state of the connection.
close()
Close this database connection. This must be called on every opened connection or else application shutdown will fail due to waiting on the opened connection to close (Gecko doesn't force close connections because it doesn't know that you are really done with them).
This function returns a promise that will be resolved when the database has closed.
If a transaction is in progress at the time this function is called, the transaction will be forcibly rolled back.
If statements are in progress at the time this function is called, they will be cancelled.
Callers should not attempt to use the connection after calling this method as the connection will be unusable.
It is possible to automatically close the connection when the browser is closed with the shutdown
hook:
const conn = await Sqlite.openConnection({ path: "myDatabase.sqlite", sharedMemoryCache: false }); try { Sqlite.shutdown.addBlocker("My connection closing", async () => await conn.close()); } catch (e) { // It's too late to block shutdown, just close the connection. await conn.close(); throw e; }
clone(readOnly)
This function returns a clone of the current connection-promise.
These functions receive the following arguments:
- readOnly
- (optional) If true the clone will be read-only, default is false. If the original connection is already read-only, the clone will be, regardless of this option. If the original connection is using the shared cache, this parameter will be ignored and the clone will be as privileged as the original connection.
transactionInProgress
This boolean property indicates whether a transaction is in progress. This is rarely needed by external callers.
shrinkMemory()
This function can be called to shrink the memory usage of the connection. This is a glorified wrapper around the PRAGMA shrink_memory
statement, which tells SQLite to shrink its memory usage (by clearing caches, etc).
While calling this has the benefit of shrinking memory, it can make your database slower, especially if you will be interacting with it shortly after calling this function. This is because SQLite will need to page the database back into memory from disk. Therefore, caution should be exercised before calling this function.
This returns a promise that is resolved when the operation completes.
discardCachedStatements()
This function is used to discard cached statement instances, freeing memory in the process. Active cached statements will not be discarded. Therefore, it is safe to call this any time.
This returns an integer count of the number of cached statements that were discarded.
Table and Schema Management
These APIs deal with management of tables and database schema.
getSchemaVersion()
The user-set version associated with the schema for the current database. If no schema version has been set, this will return the string "0".
setSchemaVersion(value)
Sets value
as the new version associated with the schema for the current database. This is a wrapper around the PRAGMA user_version
statement.
tableExists(name)
This function determines whether a table exists in the current database. It returns a promise that is resolved with a boolean indicating whether the table exists.
indexExists(name)
This functions determines whether a named index exists in the current database. It returns a promise that is resolved with a boolean indicating whether the index exists.
Statement Execution
These APIs facilitate execution of statements on the connection. These are arguably the most important APIs in this type.
executeCached(sql, params, onRow)
execute(sql, params, onRow)
These similar functions are used to execute a single SQL statement on the connection. As you might have guessed by the name, there are 2 flavors: cached and non-cached. Other than that, they behave identically.
These functions receive the following arguments:
- sql
- (required) String SQL statement to execute. The trailing semicolon is not required.
- params
- (optional) Parameters to bind to this statement. This can be an array or an object. See notes below.
- onRow
- (optional) Function that is called when a row has been received.
The return value is a promise that is resolved when the statement has finished execution.
When a statement is executed via executeCached()
, the prepared statement object is cached inside the opened connection. The next time this same SQL statement is executed (the sql
argument is identical to one passed in before), the old statement object is reused. This saves time associated with parsing the SQL statement and creating a new statement object. The downside is the cached statement object lingers in the opened connection, taking up memory.
When a statement is executed via execute()
, the underlying statement object is thrown away as soon as the statement finishes execution.
executeCached()
is recommended for statements that will be executed many times. execute()
is recommended for statements that will be executed seldomly or once.
Please note that callers don't need to prepare statements manually before execution. Just call executeCached()
and the statement will be prepared for you automatically.
Parameters can be bound to the statement by defining the params
argument. This argument can be an array of positional parameters or an object of named parameters. If the statement does not contain any bound parameters, this argument can be omitted or specified as null.
onRow
is not defined, the full results of the operation are buffered before the caller is notified of statement completion. For INSERT
, UPDATE
, and DELETE
statements, this is not relevant. However, it can have drastic implications for SELECT
statements. If your SELECT
statement could return lots of data, this buffering of returned rows could result in excessive memory usage. Therefore, it's recommended to use onRow
with SELECT
statements.StopIteration
is thrown during execution of an onRow
handler, the execution of the statement is immediately cancelled. Subsequent rows will not be processed and no more onRow
invocations will be made. The promise is resolved immediately.StopIteration
exception is thrown by the onRow
handler, the exception is logged and processing of subsequent rows occurs as if nothing happened. The promise is still resolved (not rejected).The return promise will be rejected with an Error
instance if the statement did not finish execution fully. The Error
may have an errors
property. If defined, it will be an Array of objects describing individual errors. Each object has the properties result
and message
. result
is a numeric error code and message
is a string description of the problem.
If onRow
is specified, the returned promise will be resolved with a boolean indicating whether the onRow handler was called. Else, the resolved value will be an array of mozIStorageRow
.
executeTransaction(func, type)
This function is used to execute a database transaction. A transaction is a series of related statements treated as one functional unit. If the transaction succeeds, all the statements contained within it are committed as one unit. If the transaction fails, the database is rolled back to its state before the transaction started.
This function receives the following arguments:
- func
- The function defining the transaction body.
- type
- The type of transaction to perform. This must be one of the TRANSACTION_* constants on the opened connection instance. Valid values are
TRANSACTION_DEFERRED
,TRANSACTION_IMMEDIATE
,TRANSACTION_EXCLUSIVE
. See the SQLite documentation for their meaning. The default isTRANSACTION_DEFERRED
.
The passed function is a Task.jsm compatible generator function. When called, the function receives as its argument the current connection instance. This generator function is expected to yield promises, likely those returned by calling executeCached()
and execute()
.
If we reach the end of the generator function without error, the transaction is committed. If an error occurs, the transaction is rolled up.
The returned value from this function is a promise that is resolved when the transaction has been committed or is rejected if the transaction was rolled back.
Examples
Open, Execute, Close
In this example, we open a connection, execute a simple statement, then close the connection.
const conn = await Sqlite.openConnection({path: "MyDB.sqlite"}); const result = await conn.execute("SELECT 1"); await conn.close(); alert("We are done!");
This isn't a terrific example because it doesn't include error handling.
Bound Parameters
Here are some examples demonstrating bound parameters. Assume we open an opened connection in the conn
variable.
let dataToInsert = [ ["foo", "bar"], ["biz", "baz"], {"yo", "ho"], ]; async function doInsert() { for (let data of dataToInsert) { await conn.executeCached("INSERT INTO myTable VALUES (?, ?)", data); } });
And the same thing with named parameters.
let dataToInsert = [ {paramA: "foo", paramB: "bar"}, {paramA: "biz", paramB: "baz"}, {paramA: "yo", paramB: "ho"}, ]; async function doInsert() { for (let data of dataToInsert) { await conn.executeCached("INSERT INTO myTable VALUES (:paramA, :paramB)", data); } });
Transactions
These examples demonstrate how transactions work.
conn.executeTransaction(async function simpleTransaction() { await conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]); await conn.execute("INSERT INTO myTable VALUES (?, ?)", ["biz", "baz"]); });
The above will result in 2 INSERT statements being committed in a deferred transaction (assuming the inserts proceed without error, of course).
Let's do an example where we want to force a transaction rollback.
conn.executeTransaction(async function complexTransaction() { await conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]); let data = await conn.execute("SELECT * FROM myTable"); if (data.length < 5) { throw new Error("We don't have the expected 5 rows to perform the next operation!"); } // ... });
Selecting and Returning Data
These examples demonstrate how to access the data that is returned.
This example shows multiple rows of a table being returned using the onRow
function parameter.
let accounts = []; let accountId, userName; let statement = "SELECT account_id, username FROM accounts ORDER BY username ASC"; try { const result = await conn.executeCached(statement, null, function(row) { accountId = row.getResultByName("account_id"); userName = row.getResultByName("username"); accounts.push({ accountId: accountId, userName: userName }); }); } catch (err) { // An error occurred. console.log(err); // Error, Oh noes! if (callback) { callback(err); } } // All accounts returned successfully, so do something with them. console.log(result); // It worked! if (callback) { callback(null, accounts); }
Note: the then
parameters can be anonymous functions (i.e. function()
), and only are labeled as onStatementComplete
and onError
for readability.
This example demonstrates retrieving a row without using the onRow
function parameter, and instead, using the conn.execute
result. This example also demonstrates retrieving the primary key row id of the last inserted row.
async function () { try { conn = await Sqlite.openConnection({ path: dbFile.path }); let statement = "INSERT INTO accounts (username, details) VALUES (:username, :details)" let params = { username:"LordBusiness", details: "All I'm asking for is total perfection." }; await conn.execute(statement,params); // Get accountId of the INSERT. statement = "SELECT last_insert_rowid() AS lastInsertRowID"; result = await conn.execute(statement); // Only one row is returned. let row = result[0]; let accountId = row.getResultByName("lastInsertRowID"); if (callback) { callback(null, accountId); } } catch (err) { if (callback) { callback(err); } } finally { conn.close(); } });
Note: The value returned by the last_insert_rowid() is per connection, so you may need to open separate connections when doing multiple INSERTs from different locations, to be sure the row id that is being returned is from the correct corresponding INSERT.