Sqlite.jsm

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 a shrinkMemory 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 using Sqlite.jsm.

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");

Sqlite.openConnection({ path: "myDatabase.sqlite", sharedMemoryCache: false }).then(
  function onConnection(connection) {
    // connection is the opened SQLite connection (see below for API).
  },
  function onError(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.

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

Sqlite.openConnection({path: "MyDB.sqlite"}).then(
  function onOpen(conn) {
    conn.execute("SELECT 1").then(
      function onStatementComplete(result) {
        conn.close().then(
          function onClose() {
            alert("We are done!");
          }
        )
      }
    )
  }
)

Task.spawn(function* demoDatabase() {
  let conn = yield Sqlite.openConnection({path: "MyDB.sqlite"});

  try {
    let result = yield conn.execute("SELECT 1");
  } finally {
    yield conn.close();
  }
});

let dataToInsert = [
  ["foo", "bar"],
  ["biz", "baz"],
  {"yo", "ho"],
];

Task.spawn(function* doInsert() {
  for (let data of dataToInsert) {
    yield 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"},
];

Task.spawn(function* doInsert() {
  for (let data of dataToInsert) {
    yield conn.executeCached("INSERT INTO myTable VALUES (:paramA, :paramB)", data);
  }
});

Transactions

These examples demonstrate how transactions work.

conn.executeTransaction(function* simpleTransaction() {
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  yield 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(function* complexTransaction() {
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  let data = yield 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";
    
conn.executeCached(statement, null, function(row) {
  accountId = row.getResultByName("account_id");
  userName = row.getResultByName("username");
  accounts.push({ accountId: accountId, userName: userName });
}).then(function onStatementComplete(result) {
  // All accounts returned successfully, so do something with them.
  console.log(result); // It worked!
  if (callback) {
    callback(null, accounts);
  }
},
function onError(err) {
  // An error occurred.
  console.log(err); // Error, Oh noes!
  if (callback) {
    callback(err);
  }
});

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.

Task.spawn(function* () {
  try {
    conn = yield 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." };

    yield conn.execute(statement,params);

    // Get accountId of the INSERT.
    statement = "SELECT last_insert_rowid() AS lastInsertRowID";
    result = yield 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();
  }
});

See also

• Sqlite.jsm source on DXR