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

• Upload and Download Databases

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

Uploading a database into the browser is trickier because the client has to know which sqlite3_vfs they want to use, as that will determine where the database is to be stored. 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 for a specific VFS. Using the the OPFS VFS as an example (noting that this VFS is not available in the main UI thread so the array buffer from the previous code would need to be transferred to the appropriate Worker thread using postMessage()):

sqlite3.capi.sqlite3_js_vfs_create_file("opfs", "my-db.db", arrayBuffer);
const db = new sqlite3.oo1.OpfsDb("my-db.db");

To import the database into the WASM environment's transient virtual filesystem, pass "unix" as the first argument (that's the name of the main VFS). Unfortunately, the internals of the "kvvfs" VFS are incompatible with this approach, so a db cannot be directly imported into localStorage or sessionStorage this way.