More thoughts on WAL and read/write mode

[qwtel]

Jotting down my research into this area for future reference as well as anyone with similar interests who might find it via google.

Also, full disclosure to users of the extension: If I ever ship read/write mode with WAL support, even in a limited way, it will be as some kind of payed upgrade. This is sucking up too much time for a hobby project, read on if you want to know why:

Supporting WAL mode is a difficult problem under the constraints of this extension. As a reminder, these constraints are platform independence (in practice: using a WASM build of SQLite) and working in all vscode modes (local fs, remote fs, web). That means sticking to the FileSystemProvider API. This interface has very limited capabilities: Read a file wholesale, write a file wholesale, and not much else. This is incompatible with WAL mode (actually, sqlite in general), which is why this extension for the longest time has been cached, in-memory, and readonly. It works as follows: read file wholesale -> hope it's not in an inconsistent state -> dump into system memory -> run sqlite in memory mode.

In practice there seem to be 3 major implementations of these file system providers in vscode:

1. As a wrapper around a local filesystem
2. As a wrapper around SSH when using in a remote workspace
3. As a wrapper around the github api when used on the web / github.dev

For (3) it might actually be fine to support read/write mode by just dumping the entire in-memory db wholesale. By definition, the db is checked into git, and there's no one else using it. It won't work well if the file is large, but the extension is already not working well in that case anyway.

In local mode (1), the use of vscode's fs is optional. It's possible to just load node's fs module and use that instead, which would be enough to support journal mode with just WASM and a custom VFS, or go even crazier and spawn child processes and execute random commands (lots of extensions actually do this lol) including sqlite3.

Scenario (2) is the most dangerous. It's the same as (1), but there's no option to use a real sqlite3 binary (I don't think vscode even allows sniffing around the remote host the same way it does on the local host, but even if it did, I wouldn't want to). Likely it can only ever be readonly in this case, but I'll be happy to be wrong here. I should do more research into this area. Actually, a VSCode extension can do incredibly well here: #61

In theory, the file system provider api should be enough to at least support viewing wal mode dbs, by reading the -wal file (praying that it is in a consistent state) and then "applying" it to the in-memory representation. In practice this would require a lot of effort and possibly a patched version of sqlite, since there doesn't appear to be any public API that implements "applying" the wal file to an in-memory db. Currently it's not clear to me if it's even safe to read the -wal file while it's in use. At a minimum, there's the risk of reading garbage, worst case it interferes with other SQLite processes in ways I don't fully understand. Shipped in #35

That's the story so far. Let's revisit what I want to achieve here: Platform independence and working in all vscode modes. Using a WASM build and sticking to workspace.fs aren't the only ways of achieve this, just the easiest/quickest to cover the most ground.

Now that I have that working, it can be relaxed in certain scenarios. Specifically, if the extension detects that it is running on a local machine and the file is on the same machine, it can do things differently.

One option is to bite the bullet and ship precompiled sqlite binaries for the most common platform/cpu combinations and interact with it from the vscode extension the way any native sqlite UI would do it. That lifts all restrictions and ensures ~0 risk of file corruption. This is probably how people assume it's already working.

I don't like this approach because it blows up the size and complexity of the extension, it will never work on a future architecture like risc-v without additional work, and it's not intellectually challenging (while I'm working on this for free that's not a minor consideration).
Tbf, a lot of this is cope, which is why I'll probably just bite the bullet and bundle some binaries in the near future. Creating these binaries isn't as tedious as it once was, and it's the most likely to give users what they actually care about.

A more interesting option would be to stick with the WASM build, but implement a custom VFS that supports the full v2 vfs, i.e. the shared memory functions. It has been done before using insane workarounds. It's even harder in node.js which has no mmap capabilities of its own. Would you have guessed it, there's precedence for adding this too, but it's outdated at this point and it's not clear if the same approach can work to mmap into WebAssembly.Memory. Since it relies on native extensions for node.js, it would again involve shipping multiple binaries, only smaller ones, and then there's the question if vscode even allows doing any of this to its node.js instance...

I think it's clear that I'll pass on this idea for the time being, but I wanted to write this down in case someone finds it in the future. I've read dozens for posts like this while researching this topic, so here's my contribution to the space.

There's also the option to implement a limited VFS using only what's provided by node's fs module. This should be enough to support read/write in journal mode, as well as WAL mode in exclusive mode. However, from what I've gleaned from the SQLite documentation, exclusive WAL mode only works if the first connection to the WAL db acquires the exclusive lock, which isn't something a GUI application can ensure. It could have some utility if the UI can clearly communicate the issue though.

[qwtel]

In practice this would require a lot of effort and possibly a patched version of sqlite, since there doesn't appear to be any public API that implements "applying" the wal file to an in-memory db.

As soon as a finished writing this, I had an idea for a dumb but effect solution that doesn't require messing with sqlite internals. Going to prototype something soon, hopefully something shippable in a couple of weeks

[qwtel]

I have a working prototype in the browser now. Making it work in vscode is going to be a bit more difficult, but I have line of sight on a shipping solution.

[qwtel]

It's working in vscode now 🎉

[qwtel]

The way reading WAL databases now works in vscode:

The extension just reads the -wal file along the main db using vscode's workspace.fs functions and then puts both files into a custom built in-memory VFS (this was the dumb but effective idea I mentioned earlier). It then instructs the unmodified wasm build of SQLite open to database in exclusive mode (this removes the need to provide shims for the shared memory VFS methods) and to perform a WAL checkpoint. This effectively merges the -wal file into the main database.

It would be nice if there was a API function that takes two buffers and returns the merged db like (db: Uint8Array, walFile: Uint8Array) => Uint8Array, but this custom in-memory VFS is the next best thing.

At this point the main db buffer is clean could be exported for use with :memory: mode like it did in previous versions, but I haven't found a noticeable performance difference continuing to use the custom in-memory VFS so I just leave it there. I set a couple pragmas that are a better fit for a in-memory VFS and that's it.