Loading and saving to a sqlite.db file

[jlarmstrongiv]

I would like to add a save and load database functions to @vlcn.io/crsqlite-wasm.

I found that package depends on wa-sqlite, and I think the addition would be best suited for it.

I found a previous discussion which describes:

There is currently no example code in this repo for this and I have no plans to add it. I would probably approach it by instantiating a VFS and calling its methods - xOpen, xLock, xFileSize, xSectorSize, xRead, xUnlock, xClose - to read data in sector sized chunks...but that requires an understanding of both the SQLite VFS interface and how wa-sqlite adapts it to Javascript. A less general but probably easier way to do it would be to copy the code that opens the IndexedDB database and then read the blocks directly with the IndexedDB API.

I also found that sql.js supports loading a database via:

/* loading */
dbFileElm.onchange = () => {
  const f = dbFileElm.files[0];
  const r = new FileReader();
  r.onload = function() {
    const Uints = new Uint8Array(r.result);
    db = new SQL.Database(Uints);
  }
  r.readAsArrayBuffer(f);
}

const sqlPromise = initSqlJs({
  locateFile: file => `https://path/to/your/dist/folder/dist/${file}`
});
const dataPromise = fetch("/path/to/database.sqlite").then(res => res.arrayBuffer());
const [SQL, buf] = await Promise.all([sqlPromise, dataPromise])
const db = new SQL.Database(new Uint8Array(buf));

/* saving */
const fs = require("fs");
// [...] (create the database)
const data = db.export();
const buffer = Buffer.from(data);
fs.writeFileSync("filename.sqlite", buffer);

Would you be open to adding save and load functions to your package? Do you have suggestions on the best way for me to implement those?

[rhashimoto]

I guess I do have a few thoughts and suggestions...

As you excavated from the other thread, I would instantiate a VFS and make calls to its methods defined in the VFS base class. It might be instructive to uncomment the browser debug console logging in the VFS that you use, e.g. here for IDBBatchAtomicVFS, and open your application or the demo to watch SQLite make calls because you'll need to mimic that to some degree. I think you can do what you need with only xOpen, xRead, xWrite, and xClose, but I'm not 100% sure across all the VFS classes.

One critically important thing to know is the IndexedDB VFS classes store each write as a separate IndexedDB object, and the VFS assumes that 1 object = 1 page in a database file (except during a VACUUM which you won't encounter) and reads will never cross a page boundary, because that's what SQLite does (and there are advantages for that). That's a key constraint on how you perform I/O with the VFS. In addition, page size is not a known constant value (it can be changed with SQL) so you'll need to read the page size from the file header first.

Also the API for VFS classes is changing so you'll almost certainly want to write to the new API.

I would recommend that the interface to your code stream file data if possible (e.g. with the Streams API), to allow for the possibility of managing databases larger than available memory. I don't know how well this is supported yet, though - I do know you can't use fetch to upload a stream.

You're welcome to develop this in your own repo. As for adding it to this project, it might make sense to have this in examples/ and a demo page in demos/. Integration with the main demo might be nice, too, but be aware it has been rewritten in the same branch with the new APIs. This would be the first substantive code submission, so you might have to bear with me while I figure out how I want to manage PRs, could be a little annoying for you as I make things up on the fly. 🤪 (but not entirely kidding)

[jlarmstrongiv]

@rhashimoto thank you for the thoughts and suggestions! 😄

I will enable logging with IDBBatchAtomicVFS and try that out first. My goal is to get it working in the browser and that’s a great place to start.

I’m familiar with byte parsing with restructure, so I’ll be able to jump right in to get the page size from the header.

Also the API for VFS classes is changing so you'll almost certainly want to write to the #76.

Would you make an alpha release so that I can try the latest API? Definitely would like to work with the Uint8Arrays

I’m familiar with the web streams apis, and I’ll definitely need to use those to support larger databases like you said.

Once I have something working for both reading and writing to SQLite files in the browser, I’ll work with you to add it to the repo 👍

[rhashimoto]

Would you make an alpha release so that I can try the latest API? Definitely would like to work with the Uint8Arrays

You can develop against the breaking-changes branch until it gets merged to the trunk. The changes I'm still making in the branch shouldn't affect you.

I’m familiar with byte parsing with restructure, so I’ll be able to jump right in to get the page size from the header.

One warning - getting me to accept a new package dependency is a really high bar if you eventually want to submit a PR. In this specific case of extracting a couple fields from the header, you should just use DataView.

[jlarmstrongiv]

You can develop against the breaking-changes branch until it gets merged to the trunk. The changes I'm still making in the branch shouldn't affect you.

Alrighty, I’m currently installing the dependencies to build it locally

One warning - getting me to accept a new package dependency is a really high bar if you eventually want to submit a PR. In this specific case of extracting a couple fields from the header, you should just use DataView.

DataView it is! Thanks for the heads up

[rhashimoto]

The breaking-changes branch has pre-built WASM artifacts in dist/, so you shouldn't need to do an Emscripten build for this unless you want to.

[jlarmstrongiv]

Fantastic 🙌 that’s exactly what I was looking for. Thank you!

[sgbeal]

I think you can do what you need with only xOpen, xRead, xWrite, and xClose, but I'm not 100% sure across all the VFS classes.

A potential gotcha: some VFSes require that the lengths given to xWrite and xRead be a legal db page size (a power of 2 between 512 and 64k). The library internals only ever pass such sizes to those functions. Not all VFSes are picky about that, though.

[rhashimoto]

Yes, that's what I was trying to get across but perhaps it wasn't clear. The wa-sqlite IndexedDB VFS classes expect 1 write = 1 IndexedDB object = 1 page, not just a legal page size but the actual page size. Reads don't have to be a full page but can't straddle a page boundary.

[jlarmstrongiv]

Thank you both! That‘s a great tip to be aware of

[tantaman]

To summarize and ensure I understand, the basic idea for importing a db file is to:

1. Read the db header (defined here: https://www.sqlite.org/fileformat.html)
2. Pull the page size from that header
3. Pull the page count from the header
4. Starting at byte index 100, read PAGE_SIZE chunks up until PAGE_COUNT
5. For each chunk, write it to the vfs via xWrite

[rhashimoto]

You'll need to surround your file accesses with xOpen and xClose.

Pages start at the beginning of the file so (4) is not correct. The header is part of the first page.

[jlarmstrongiv]

This big-picture overview and tip helped a lot! For writing to a db file, I assume the steps are:

• reading every page for the given DB
• concatenate/stream all the bytes together

[rhashimoto]

Again:

The wa-sqlite IndexedDB VFS classes expect 1 write = 1 IndexedDB object = 1 page, not just a legal page size but the actual page size.

[AntonOfTheWoods]

  const res = await fetch("http://localhost/mytest.db");
  if (!res.ok) throw new Error("bad response");

  const buffer = new Uint8Array(await res.arrayBuffer());
  const pOut = new Value();
  const vfs = new AccessHandlePoolVFS('/a_directory');
  await vfs.isReady;
  const FILE_ID = 0xdeadbeef;
  const filename = 'a_filename';
  vfs.xOpen(filename, FILE_ID, VFS.SQLITE_OPEN_CREATE | VFS.SQLITE_OPEN_READWRITE  | VFS.SQLITE_OPEN_MAIN_DB, pOut.pass());
  vfs.xWrite(FILE_ID, buffer, 0);
  vfs.xSync(FILE_ID);
  vfs.xClose(FILE_ID);
  await vfs.close();

The above looks suspiciously like it works... I get the contents of the tables I'm expecting and a PRAGMA integrity_check; returns 'ok'. I got inspiration from the tests - I have no idea what the pOut.pass() is doing... Is it doing something I should know about? If my (currently anyway) only use case is importing files like the above - is there anything else I am missing? If I were to have a list of chunks of binary file, is there anything stopping me from just writing them in chunks (in order of course!)?

EDIT: Added the missing VFS.SQLITE_OPEN_MAIN_DB flag, otherwise the imported db gets deleted on worker reload...

[AntonOfTheWoods]

Nevermind... I can't work out what I was doing before but the above code actually does work... it is very slightly different to what I had (I cleaned up to put here) and this cleaned up version actually does produce a valid db. Sigh...

Thanks!

[AntonOfTheWoods]

BUT BUT BUT!!!!!!!!!!!!!!!!!!!

You MUST NOT have a significant amount of time between the xWrites. I'm picking it's got to do with the flushing but if you don't basically get the entire database and do everything in a single loop then the above WILL NOT WORK. I was getting chunks of my db from the network and xWriteing them as they arrived and this doesn't work, at least not with the 31 10MB blocks I was trying to do it with. I spent several hours trying to work out what was going on - I got down to the fact that md5s of the chunks were identical with getting the entire file and splitting, then doing in chunks... the only thing different between downloading chunks from downloading the entire file and splitting was the timing... And adding a short sleep in between iterations proved you can definitely produce a corrupt DB by taking more than a couple of seconds (3000ms?) to import the entire file from the first xWrite to the last.

EDIT: It appears that you also MUST have a sufficient amount of time after the xOpen (or maybe VFS object creation) before the writes start, possibly also around 3000ms... And even then, it appears to quite regularly create corrupt dbs...

Is there a way to guarantee it does what it should, or should I just go with SQL inserts?

[rhashimoto]

And adding a short sleep in between iterations proved you can definitely produce a corrupt DB by taking more than a couple of seconds (3000ms?) to import the entire file from the first xWrite to the last.

Oh, I see (I think). Chrome (at least, possibly other browsers, too) automatically aborts an IndexedDB transaction after a minute or so. IDBBatchAtomicVFS.xWrite is usually synchronous, but if it's always synchronous then a long SQLite transaction will cause an IndexedDB transaction timeout. To work around that there's this code which switches from synchronous to asynchronous every...3 seconds (bing!). But this is complicated because of the way Asyncify makes two calls for each asynchronous call. You're not calling via Asyncify so you only make one call, and I thought the code would handle that but it certainly sounds like it doesn't.

I'll take a deeper look but it might not be all that soon. You could try to patch IDBBatchAtomicVFS and make #xWriteHelper a public method and call that instead of xWrite. If you do that and your database loads can take a significant fraction of a minute, then you should also make Javascript start a new task every once in a while so the current IndexedDB transaction can commit.

The required delay after xOpen is still a mystery to me, not sure what would explain that.

[AntonOfTheWoods]

Thanks again for the help.

Ok, so I kept looking and it actually doesn't appear to be strictly related to those flushes but rather MAX_TRANSACTION_LIFETIME_MILLIS - so 5000ms. If there is not 5000ms between the xOpen and the first xWrite (or less if I reduce that const - at 2000ms it is also 100% reproducible), then it fails. I tried pretty much everything - it looked like I could just force a

  async forceCommit() {
    this.#tx?.commit();
    await new Promise((resolve) => setTimeout(resolve));
    this.#tx = null;
  }

after the xOpen in IDBContext, but that makes no difference whatsoever... So there is obviously something else that is necessary...

Interestingly, in the actual xWrite loop, 2-3 await new Promise((resolve) => setTimeout(resolve, 0)) will cause db corruption but if there are none (or anything else that allows the next task to execute), or just 1-2 await new Promise((resolve) => setTimeout(resolve, 0)), then it executes fine. This happens both whether MAX_TRANSACTION_LIFETIME_MILLIS is 5000ms or 2000ms, so I'm not sure what's going on.

So basically, you MUST wait MAX_TRANSACTION_LIFETIME_MILLIS after xOpen and you MUST NOT let another transaction happen between the start of the xWrite and the vfs.xSync(FILE_ID, 0)/vfs.xClose(FILE_ID). If both these are true, it now seems pretty reliable that a valid db is produced.

[rhashimoto]

Thanks for documenting all that behavior! It's unexpected and strange. I'll see if I can reproduce your findings and determine what's different from how SQLite makes these calls.

#110

[tantaman]

@AntonOfTheWoods - did you end up writing an exporter to export the DB File from the IndexedDBBatchAtomic VFS?

[AntonOfTheWoods]

I didn't @tantaman , and it's not on the radar either, as for me that VFS is only used in a chrome ext, and users will always have the website where they will be able to download from the same DB using AHP (or at least they will when that gets implemented!).

[rhashimoto]

If you're building your own SQLite (as @tantaman does), on any platform (not just for the browser), one way to enable database export (but not import) is with the SQLITE_DBPAGE virtual table. This module is bundled in the SQLite source and is activated with a compile-time flag. If included, you can fetch raw database file pages using SQL to avoid messing with the VFS directly.

[rhashimoto]

I created a demo page, online here (source) to demonstrate database file import (local file to wa-sqlite) and export (wa-sqlite to local file). The demo uses direct calls to IDBBatchAtomicVFS, as suggested earlier in this thread. I think it should work with other VFS classes, but I have not tried any.

I built this to investigate Issue #110 (now resolved), based on racy behavior mentioned earlier in this thread. I have not reproduced any of that behavior, but that bug remains open. It is possible that my code doesn't produce those issues and all is fine, or that it will and I just haven't seen it yet. I did try uploading and downloading database files between 12 KB and 6.3 GB, and transfers in both directions pass PRAGMA integrity_check.

The implementation streams data using ReadableStream in both import and export, which allows handling database files larger than available memory. Note that the only cross-browser way (that I know of) to stream an export to a local file is with a service worker, so that's what I did. I wrote my service worker as an ES6 module, so it would need to be bundled to work on Firefox (which doesn't yet support ES6 module service workers).

[rhashimoto]

I think it should work with other VFS classes, but I have not tried any.

Since the demo exports using a service worker, drop-in replacement of course won't work with a VFS that uses OPFS synchronous access handles (i.e. every OPFS VFS) because those aren't available in a service worker. It shouldn't be difficult to work around this with the File interface instead, though, just need a way to get the FileSystemFileHandle. Alternatively, you could proxy the VFS method calls to a dedicated Worker but that sounds harder.

Note that the only cross-browser way (that I know of) to stream an export to a local file is with a service worker, so that's what I did.

You can use URL.createObjectURL() to stream a File to a local file, so that would not require a service worker for a database file in OPFS. However, that won't work for an AccessHandlePoolVFS database, because that implementation prepends a sector of VFS metadata to the database file. In retrospect, that was an unfortunate design decision (one that the official SQLite WASM project decided to copy despite an alternative solution) so the service worker approach would be needed there as well.

URL.createObjectURL() can also take a MediaSource argument so I wondered whether creating a custom MediaSource would work for AccessHandlePoolVFS, but it sounds like this is now deprecated.