SQLite Viewer – Server

[qwtel]

I’ve long been puzzled by VSCode’s lacklustre workspace.fs implementation which is missing basic features like reading files at a byte offset. This makes any kind of non-memory-based SQLite extension impossible, unless one escapes the “sandbox” and uses Electron/Node’s fs module or a custom binary, which in turn makes the extension incompatible with VSCode for Web, a tradeoff that I wasn’t willing to make.

That was until recently, when I learned about VS Code Server. It provides a convenient way of installing VS Code extensions on remote machines (SSH, WSL, Containers), which allows extensions authors to use any node module and even ship any binaries they like, without limiting the reach of the extension. Additionally, it provides a transparent message passing API back to a local webview, effectively creating an ad-hoc client-server architecture.

Since the process of installing a host extension and setting up the connection is so seamless, it actually offers an opportunity to build a SQLite editor that’s unlike any other. SQLite is not a client-server database, which makes accessing it from a remote machine cumbersome. That means sticking to ssh + cli tools¹, or bringing some kind of custom server component, which few (if any?) UIs do². Even if they did, I wouldn't necessarily trust a GUI app to install a server component on a remote machine, nor would I want to write such a component myself. Luckily, MS has done the heavy lifting here, and since VS Code Server is trustworthy enough to be used by many of the most popular extensions, SQLite Viewer can piggyback on this work to create possibly the most remote-friendly SQLite UI.

More concretely, the way it would work is as follows: Shipping a SQLite binary along with the extension → Letting VS Code Server install it on the remote machine → Sticking to its message passing API to report results back to the local webview → The experience is almost the same as on a local machine (no repeated roundtrips, no downloading the entire file).

What's needed to make this happen?

• SQLite Viewer will have to ship multiple SQLite binaries, because I don't see a way to make the WASM build support writing in WAL mode. I've written more about this here: More thoughts on WAL and read/write mode #55
• Rather than communicate with a Web Worker running SQLite WASM, the application has to pass messages to the extension host. Because Web Worker's and VSCode's message passing APIs are very similar, this is less of an issue than it otherwise would be, but it requires some workarounds (no MessageChannel) and possibly a lot of glue code.
• The UI has to support editing data and there's some open questions regarding stale/updated data in the UI. Currently all these issues are side-stepped due to the extension's readonly copy of the file, but this will have to change.

As of 06/09/24, work on this is in the initial, exploratory stage.

Footnotes

1. It would be fun making an interactive, ncurses-style TUI, but that is not the goal here. ↩

2. Note that accessing a SQLite file through a remote file system is a fast tracked way to corrupt the database, so I do not consider this an option. ↩

[qwtel]

Rather than communicate with a Web Worker running SQLite WASM, the application has to pass messages to the extension host.

This is now solved. I had to rip out any vestiges of OOP in the sqlite wrapper, which were relying on MessageChannel, which isn't supported by vscode's webview message passing API. I also had to change some return types to be plain old js objects (no Set or Map), because structured clone isn't supported by vscode's postMessage implementation either¹.

SQLite Viewer will have to ship multiple SQLite binaries

Using binaries from within a vscode extension is an even bigger clusterf*** than I initially thought. I feel completely vindicated going with WASM for the first version of this extension. If I went with native, I probably would have given up and the extension would have never shipped.

After spending a day trying to figure out how a vscode extension can call into a binary using something like napi or FFI, I found out that it is basically impossible and everybody is doing json-rpc over stdin/stdout instead 🙄.

Unlike Bun and Deno, Node has no builtin FFI module. There's a 3rd party tool that's made by a sole developer, practically unmaintained at this point, and it probably (afaict) doesn't even solve the module version mismatch issues that's making napi such a bad fit for a vscode extension in the first place. I'm admittedly light on the details here, but that's how it looks to me right now.

As much as I would like to get into Zig and do something fun like implementing a custom wrapper with a custom binary protocol, I have to stay focused on the primary objective here, which is to ship something asap, so I'm going to do something stupid that works instead²: I'm going to ship Deno or Bun alongside the extension, use their builtin FFI/SQLite to implement the sqlite functionality I need, and combine it with an off-the-shelf json-rpc library for communication.

Narrator: He did not ship something asap and got into Zig instead. See below.

Footnotes

1. No clue why it's even called postMessage. It is literally a different API. ↩

2. I'm increasingly convinced that "something stupid that works" is the secret to getting stuff done. ↩

[qwtel]

I have this working now. I can now bundle a native SQLite binary with the extension and communicate with it through stdio. I actually couldn't make any "off-the-shelf json-rpc" libraries to work, so I hacked together my own. It's small and does the job. I was eye-ing the vscode json rpc library, but I couldn't get any examples to work and it's OOP-astronaut-style API looked so demented that I lost all confidence pursuing it further¹.

With that, the biggest hurdled is cleared, now I have to solve all the issues related to cross-compilation and distribution --- which should be easy in theory but is probably gonna take 10x the time in practice.

Footnotes

1. I remember watching a talk by Jonathan Braid where he blasted the vscode language server for its needless complexity and I'm starting to see why... ↩

[qwtel]

I've been sidetracked pretty badly (see below), but I've now shipped this as part of the extension and it is unlockable with a paid upgrade, along with a couple other features. For more on the paid version see here: https://vscode.sqliteviewer.app. Here's how it works in action:

Thanks to the pre-existing WASM backend, the viewer can open remote db files in readonly mode even without being installed there (as seen in the gif above). It works the same way as the free version, by copying the contents from the remote host to the local VS instance. Obviously this only works for small files. Because the extension is only 2.5 MB, it's pretty quick to install on the remote host though (also in gif above), which unlocks readwrite mode.

How did I keep the size so low? I ended up not going the easy route and bundling a known heavy JS runtime like Deno, which would have 50x-ed the size of the extension. Instead, I've came across a small, but mostly complete JS runtime called txiki.js, which bundles a JS interpreter with libuv and sqlite3. It's only ~2.5MB uncompressed.

Getting Sidetracked

Since the extension targets everything that VS Code supports (win32-x64, win32-arm64, linux-x64, linux-arm64, linux-armhf, alpine-x64, alpine-arm64, darwin-x64, darwin-arm64) I needed to compile multiple versions of txiki.js. While there's obviously things like GH Actions that help with that, I was hearing a lot about Zig Build and its cross-compilation features the time, so I've used it as an excuse to learn Zig Build. I ended up porting mimalloc, sqlite, libuv and quickjs to the Zig build system, which was a decent crash course in C and C build systems. I almost gave up a couple of times along the way. Building C, all the many different compilers and ifdef macros are confusing as hell. Nevertheless I've eventually figured out all the confusing linking errors and the end result can be found here: qwtel/zig-build-txiki. In the end, nothing about it is too unexpected or difficult, but a lot was just new to me. The last time I dealt with C must have been 2 decades ago. In any case, the result is pretty sweet. It can spit out native binaries for all the above targets from my local machine within seconds.

The result is a small JS runtime that wraps a native SQLite instance and can run my pre-existing JS wrapper code, which was the key motivation for the whole ordeal in the first place. Would there have been simpler solutions? Certainly, but for this project I've allowed myself to pick the most interesting/fun path most of the time (to get it done at all I had to make some more reasonable decisions later on).

Speaking of interesting/fun parts, what I didn't mention yet is that I've effectively built my own RPC system as well, by implementing the web's postMessage/MessageChannel API on top of any byte stream pair. The whole thing is deceptively difficult (specifically the MessageChannel part) and I can't claim to have it fully figured out, but it works for the limited point-to-point use case of the extension. Technically it's not a RPC system -- that comes from putting Comlink on top, which I've also heavily modified to play nicely with my custom MessagePort class... yeah.

Zig, the Language

After I've figured out the Zig build system, I couldn't resist learning Zig the language. While not strictly speaking necessary, I wanted a faster serialization for the messages that were sent to and from the txiki.js process. The various MessagePack-in-JS libraries are a slog when run through interpreted JS, and JSON.stringify is not an option for data that can contain blobs, as SQLite rows often do.

QuickJS has its own serialisation format, but nobody else speaks it. Node/V8 also has its own serialization format. Nobody speaks that either, but I've ported it to JS before, so another port to QuickJS/Zig was in the cards: v8-serialize.zig. As an aside, writing a serialization/deserialization library is a pretty good way of learning a new language --- it feels like I've touched almost every aspect of the (admittedly small) Zig language surface.
There are some rough edges to be sure, but the subset that's in use by the RPC system works fine. Especially satisfying is how it's used within in the extension: Because I've implemented the Node API, all that's necessary was defining an alias in the build process:

  alias: {
    '@workers/v8-value-serializer/v8': 'tjs:v8',
  },

On the VS Code/Node.js side, there's a similar alias:

  alias: {
    '@workers/v8-value-serializer/v8': 'node:v8',
  },

That means everything except the webview is using the fastest possible serialization library that's available in the respective runtime, which is pretty sweet. The webview runs the JS version, which isn't as fast as something like cbor-x, but not off by that much either.