Cookbook

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

Upload and Download Databases

A commonly-useful capability is uploading and downloading databases.

Downloading (exporting) a Database

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

Upoading (importing) a Database

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.

Uploading a database into the browser is trickier 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. First, 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);
});

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

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

Another alternative is to write the database to storage in a VFS-specific way...