Support larger >1GB sqlite databases

ArrayBuffer limitations

Currently, SQLite databases are loaded using arraybuffers:

```
response.arrayBuffer();
```

function loadDbFromArrayBuffer(buf) { … }

Arraybuffers have limited sizes:

Chrome 2145386496 bytes is 2.145386496 gb
Safari 4294967296 bytes is 4.294967296 gb
Firefox 8589934592 bytes is 8.589934592 gb

String limitations

Or by loading text:

```
return response.text();
```

Strings have limited sizes:

Chrome 1073741776 bytes is 1.073741776 gb
Safari 4294967294 bytes is 4.294967294 gb
Firefox 2147483644 bytes is 2.147483644 gb

Solutions

Streams API / Web Streams API

To handle larger files, it is possible to use Web Streams:

With fetch

// request
await fetch(url, {
body: new ReadableStream()
})
// response
const readable = (await fetch(url)).body

With the File System Access API

const writable = await fileHandle.createWritable();
const readable = (await fileHandle.getFile()).stream();

WebAssembly.Memory

A workaround to creating a larger array buffer is using new WebAssembly.Memory

However, I think using the Streams API is a better fit.

Other

I was looking at the implementation:

function loadDbFromArrayBuffer(buf) {
    const bytes = new Uint8Array(buf);
    const p = sqlite3.wasm.allocFromTypedArray(bytes);
    const db = new sqlite3.oo1.DB();
    sqlite3.capi.sqlite3_deserialize(
        db.pointer,
        "main",
        p,
        bytes.length,
        bytes.length,
        sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE
    );
    return db;
}

And I’m thinking that if OPFS VFS support is active, then we can stream the db file to the OPFS VFS, and then open the DB from the file we created.

Using OPFS requires cross-origin isolation (COOP and COEP), which can be added to GitHub pages with coi-serviceworker.

nalgeon / sqlime