Cookbook

This page collects "recipes" and tips for implementing common application-level features, such as...

Importing and Exporing Databases

A commonly-useful capability is uploading and downloading databases.

Exporting an in-browser Database to the local Filesystem

Downloading is straightforward and easy to implement given just an opened database handle. Getting the raw bytes of the database is the first (and easiest) step:

const byteArray = sqlite3.capi.sqlite3_js_db_export(myDb);

Then we need a good deal more code to export that byte array out of the browser. Here's one approach:

const blob = new Blob([byteArray.buffer],
                      {type:"application/x-sqlite3"});
const a = document.createElement('a');
document.body.appendChild(a);
a.href = window.URL.createObjectURL(blob);
a.download = (myDb.filename.split('/').pop() || "my.sqlite3");
a.addEventListener('click',function(){
  setTimeout(function(){
    console.log("Exported (possibly auto-downloaded) database");
    window.URL.revokeObjectURL(a.href);
    a.remove();
  },500);
});
a.click();

Importing a Database into the Browser

Forewarning: this will not work with WAL-mode databases. The WASM environment lacks the shared-memory APIs required by WASM, so the WASM build cannot read WAL-mode databases.

Importing a database into the browser is trickier than exporting because the client has to be able to store the database somewhere, and such storage is sqlite3_vfs-dependent. The first step, getting the database bytes, is fairly generic, and two approaches are demonstrated below.

First, we'll show how to upload a local database file into the browser. For that we need a UI element with which to fetch the database:

<input type='file' id='load-db'/>

Then we need to listen to events which will transfer uploaded files into our JS code. Ignoring such details as a load progress indicator, that looks something like...

const eUploadDb = document.querySelector('#load-db');
eUploadDb.addEventListener('change',function(){
  const f = this.files[0];
  if(!f) return;
  const r = new FileReader();
  r.addEventListener('load', function(){
    // this.result is an ArrayBuffer with the file's contents
  });
  r.readAsArrayBuffer(f);
});

The loading process includes optional events for things such as upload cancellation and upload status. More details about those can be found at MDN, noting that reporting upload status will likely be superfluous for all but the most unusual cases, as the transfer of the file content into the browser's memory is normally extremely quick.

Similarly, a database can be loaded using fetch(), either from the local web server or a remote one:

fetch( '...url to the db...' )
  .then(res=>res.arrayBuffer)
  .then(function(arrayBuffer){
    // the database's bytes are in arrayBuffer
  });

Once that ArrayBuffer is loaded, we need to decide what to do with it. We have at least two options. Most simply, we can load it into an in-memory database:

// assuming arrayBuffer contains the result of the above operation...
const p = sqlite3.wasm.allocFromTypedArray(arrayBuffer);
const db = new sqlite3.oo1.DB();
const rc = sqlite3.capi.sqlite3_deserialize(
  db.pointer, 'main', p, arrayBuffer.byteLength, arrayBuffer.byteLength,
  sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE
  // Optionally:
  // | sqlite3.capi.SQLITE_DESERIALIZE_RESIZEABLE
);
db.checkRc(rc);

The last argument to sqlite3_deserialize() is significant here for reasons discussed in the docs for sqlite3_deserialize().

A fetched database can be written to browser-side storage, but how exactly that is done varies depending on the VFS:

Replace the Library's Logging Routines

In order to help those who support the library gather details from users reporting problems, it may log a small amount of information to the JS dev console. It can be made completely silent by swapping out the logging routines with ones compatible with the console.log() family of functions. To install those before the library is loaded do:

globalThis.sqlite3ApiConfig = {
  // define any or all of these:
  warn: ()=>{},
  error: ()=>{},
  debug: ()=>{},
  log: ()=>{}
};
// Then load the library using your preferred approach, then
// delete the temporary config object:
delete globalThis.sqlite3ApiConfig /* automatically done as of 3.46.0 */;

The requirement of a global-scope value to configure the library is unfortunate but we have no other way to feed client-level configuration to the library's bootstrapping code because it's buried in a layer of Emscripten-related module-loading code. Custom builds which use only this project's API layer, without the Emscripten layer, can pass a config object directly to the library's bootstrapping functions.

The routines must be interface-compatible with console.log() but the library does not have any dependency on what they actually do with their arguments, so eliding them or sending them to alternate logging channels are both legal options. They must not throw any exceptions or the library may misbehave.

They can also be modified after the library is loaded, but they may have already emitted output during the library initialization phase, e.g. to warn if OPFS is not available, so that will be too late to squelch all possible output:

sqlite3.config.log =
  sqlite3.config.error =
  sqlite3.config.warn =
  sqlite3.config.debug = ()=>{};

Note that overridding all of them is not necessary. As of this writing, the library will emit a warning in some rare cases and an error in a few cases, but should never emit log() or debug() messages except during its own development.