Anyway to save SQL DB to IndexedDB without calling export() for persistence?

[LeeRob97]

I am currently having to call export every time I need to save to indexedDB for persistence. The problem is export greatly increases the save time taken and I cannot save just the SQLJS object due to the new SQL.database constructor not being able to handle that? Is there any way around this or could this be a possible enhancement.

[theSherwood]

I'm also interested in this. Anyone have an idea what the barriers to implementation might be? I'm not familiar with sql.js but I'd be interested in using it if this problem can be sorted out. If someone could give me a heads up as to the what they think might be the best route for fixing this, I'd probably take a swing at it. Cheers!

[lovasoa]

Yes, or should not be too hard to implement this feature using

https://emscripten.org/docs/api_reference/Filesystem-API.html

If you are ready to work on a PR, I can assign you to this, @theSherwood !

[theSherwood]

I can give it a try. Just a heads up: I'm well outside my wheelhouse here. Maybe we should talk design. What kind of granularity would be right? Would every row in sqlLite be a document in IndexedDB? That sounds like the best way to reduce write volume for a database that needs to be persisted frequently. But to my mind, it also means coopting the run and exec commands to also write to indexedDB, which may not be ideal/may be better as a wrapper library.

@lovasoa, what are your thoughts on this? How would you go about it?

[lovasoa]

We should just use IDBFS from emscripten; you can read the documentation I linked to in my previous message. It shouldn't be hard to implement.

We could simply add an optional options argument to the Database constructor containing an optional database name. When it is provided, we don't an IDBDS and create a file with the given name in it.

[theSherwood]

Maybe I'm misunderstanding the way IDBFS works. Does it save each file as a separate document in IndexedDB? Or does it handle chunking out of the box?

[lovasoa]

no, it does not chunk files.

see https://github.com/emscripten-core/emscripten/blob/master/src/library_idbfs.js

[theSherwood]

Then if sql.js is treating the entire database as a single file, won't that defeat the purpose? It sounds like IDBFS would take the database and save it as a single document, which can also simply be done by using export and storing it in indexedDB manually? Or am I missing something?

[lovasoa]

no, you are getting it right

[theSherwood]

But how can that be done incrementally so as to not be saving the entire database to indexedDB at once, but only updating the records that changed and persisting each row in the sql.js database as a document in indexedDB? Do you have any idea on an approach for that?

[lovasoa]

This would be much more complex to implement. In practice, exporting the whole database and persisting it never takes more than a few seconds for huge databases, and it can be done in the background. Did you find a case where persisting the database faster would have a positive impact on the user ?

[kaizhu256]

i agree with lovasoa its probably not feasible to re-invent new mechanism to persist individual sqlite-records to indexeddb. what we really need are native file-api's like "seek" exposed in browser to exploit sqlite's native file-persistence mechanism (like wal).

it appears google is working on nativeio filesystem for browsers @ https://github.com/fivedots/nativeio-explainer. they have an experimental sqlite-wasm implementation @ https://github.com/jabolopes/sqlite-wasm

i managed to compile the demo @ https://kaizhu256.github.io/sqlite-wasm/dist/index.html, but couldn't get the nativeio benchmark working.

[theSherwood]

@lovasoa The application I have in mind would be attempting to backup user-generated changes almost constantly so as to prevent the user from losing any work. A few seconds for each backup could be problematic.

@kaizhu256 Thanks for the links. I'll be sure to keep on eye on that!

[lovasoa]

This means users would lose at most a few seconds of work, if their database were really huge. Would that be a problem?

[theSherwood]

Probably not a big problem initially, no. I don't want the user to have to think about any of that. So it isn't ideal, but not a huge problem.

[kaizhu256]

The largest sqlite db I've managed to persist in indexeddb is ~235mb. Chrome doesn't allow indexeddb objects larger than that.

For applications with many persisting writes, I had to redesign db and break large tables into smaller 5mb tables that could be quickly persisted as individual indexeddb blobs.

[kegsay]

I'd like to do this because I cannot rely on export as it closes the database and nukes prepared statements (which according to 1ee5a18 was to flush data correctly).

I'm guessing I would need to specify IDBFS at compile time https://emscripten.org/docs/api_reference/Filesystem-API.html#file-systems with -lidbfs.js and add it to the Makefile https://github.com/sql-js/sql.js/blob/master/Makefile#L31 but I'm wondering:

• Should this be optional?
• How should this be enabled?

Do I need to do any additional steps in my code to write to IDB? There's some talk of FS.syncfs(true, function (err) { ... }) but I'm unsure. Any pointers @lovasoa ?

[kegsay]

So I think I have it working insofar that it is writing to indexedDB, but without any actual files yet. I'm invoking this using:

FS.mkdir("/idb");
FS.mount(IDBFS, {}, "/idb");
FS.syncfs(true, function(err) { ... }); // load from indexeddb

// time goes by...
FS.syncfs(false, function(err) { ... }); // save to indexeddb

As per the docs for syncfs:

The populate flag is used to control the intended direction of the underlying synchronization between Emscripten`s internal data, and the file system’s persistent data.

populate (bool) – true to initialize Emscripten’s file system data with the data from the file system’s persistent source, and false to save Emscripten`s file system data to the file system’s persistent source.

I can't use "/" as the root FS because of a mysterious errno: 20 when doing the mkdir for /, and if I skip the mkdir and just do FS.mount(IDBFS, {}, "/"); I get errno: 10 which looks to be because it doesn't like the root mount being mounted again:

if (root && FS.root) {
    throw new FS.ErrnoError(10);
}

Trying to unmount results in errno: 28, likely because of:

if (!FS.isMountpoint(lookup.node)) {
    throw new FS.ErrnoError(28);
}

So I'm a little stuck atm. I think I need to be mounted at / as mounting anywhere else doesn't pick up the .db files (and reasonably so). Looking at the constructor for Database suggests they are written to /:

        this.filename = "dbfile_" + (0xffffffff * Math.random() >>> 0);
        if (data != null) {
            FS.createDataFile("/", this.filename, data, true, true);
        }

I think staticInit is screwing me over because it sets MEMFS before I can get to it:

staticInit:function() {
        FS.ensureErrnoError();
  
        FS.nameTable = new Array(4096);
  
        FS.mount(MEMFS, {}, '/');
  
        FS.createDefaultDirectories();
        FS.createDefaultDevices();
        FS.createSpecialDirectories();
  
        FS.filesystems = {
          'MEMFS': MEMFS,
          'IDBFS': IDBFS,
        };
      }

[kegsay]

So it seems mounting straight to / for IDBFS doesn't work, it produces EPERM errors. Looking at the docs:

File data in Emscripten is partitioned by mounted file systems. Several file systems are provided. An instance of MEMFS is mounted to / by default. The subdirectories /home/web_user and /tmp are also created automatically, in addition to several other special devices and streams (e.g. /dev/null, /dev/random, /dev/stdin, /proc/self/fd); see FS.staticInit() in the FS library for full details. Instances of NODEFS and IDBFS can be mounted to other directories if your application needs to persist data.

This indicates that other filesystems were probably not designed to be mounted at /, which explains why things are unhappy. I guess I'll have to add a filename arg to new Database and see how that goes.

[kegsay]

#397

[cepm-nate]

Curious if anyone's tackled this again. In #397 it looks like the consensus was to use export(), but tell it to just flush cache without closing the DB. #400 seems to relate to SQLite knowing it's storing data in memory or storing on disk (filesystem) when told to do an export(), with the last comment on Jul 16th.

With my DB size over 400MB, chunking is necessary in my case. Has there been any other progress in this area? (chunking & saving to indexedDB, or saving via a filesystem)

[kaizhu256]

chrome-canary recently shipped with experimental native-filesystem support for webassembly (with database use-case specifically in mind). they have a tutorial to compile wasm's with native-filesystem-support here.

note there's some conflict between chrome's experimental feature and w3c's competing file-system-access standard [1], [2].

[rhashimoto]

Unfortunately the suitability of the File System Access API for SQLite seems to be going nowhere, despite its recent support in Safari. First of all, Firefox seems pretty strongly opposed to it.

In addition, it looks like Chrome's implementation for writes copies the file, writes to the copy, scans the copy for malware, and renames the copy over the original. So changing 1 block of a 1GB SQLite database might cost over 2GB of I/O (1 for the copy, 1 for the scan) unless your filesystem has some kind of block-level deduplication. That's probably worse than export(). Not sure what Safari does.

[fdietze]

Have a look at https://github.com/jlongster/absurd-sql, it uses indexedDb as a file system for sqlite

[delaneyj]

Also @jlongster has forked SQL.js to add the supported hooks to allow for custom filesystems. Would be great to see these incorporated into SQL.js mainline.

EDIT: didn't see there is already a PR #481

[Kar-Tarak]

I am also having the same requirement. Any suggestion? What approach should I take?

[zwass]

https://github.com/rhashimoto/wa-sqlite seems to have support for this :)