sqlite3 API considered harmful

[rhashimoto]

The title is clickbait. This is not a criticism of SQLite but instead of how people (like me, like the official SQLite WASM library, and like the original sql.js we were inspired by) are exporting it from WebAssembly to Javascript. We basically just expose the sqlite3 C API as-is with some thin Javascript wrappers for type conversions. That works surprisingly well, minimizes design effort, minimizes documentation, and is familiar to anyone who has previously written a sqlite3 program. I also think it is slow.

sqlite3 is a very granular API - it is mostly a set of very low-level calls, and in most cases it takes a lot of calls to satisfy a typical user request. For example, unless you use the sqlite3_exec() convenience function¹, it takes an API call to produce each row and an API call to retrieve each column. That can get expensive when all those calls have to cross the WebAssembly-Javascript language barrier.

I find the original OpenGL API (for 3D graphics) to be a good analogy. You had to make a call to send each type of data for each vertex - so a call for position, a call for color, a call for texture coordinates, for every geometric vertex. As graphics systems evolved, the thousands of calls required to draw a single object severely impacted performance. So today's OpenGL and successor APIs are designed to function without this granularity while remaining low-level, i.e. still flexible and powerful but in a fraction of the calls. I think this needs to happen with WebAssembly SQLite to make it significantly faster.

What should such a less granular exported API look like? I don't know exactly, but here's a wish list:

• Support both prepared and unprepared statements. Manage unprepared statement lifetimes completely within WebAssembly.
• Iterate a statement over multiple sets of binding parameters within WebAssembly. Useful for populating a database.
• Provide an entire row of data at once from SELECT queries, maybe multiple rows. Avoid the per-column calls.

I don't think this would be a huge effort but it is a challenging one. The hard part is designing a good API, which might take a few iterations. Then the implementation will take some expertise with a WebAssembly-compatible language as well as Javascript glue. It might be an opportunity for someone. I don't recommend waiting for "someone" to be me.

Footnotes

1. The wa-sqlite function exec() does not actually call sqlite3_exec() and so does not reduce the number of sqlite3 API calls. ↩

[sgbeal]

The title is clickbait.

Ha! Indeed, but the sentiment behind it is spot on.

This is a topic which has consumed many of my nights, both with JS and Java/JNI, and my reason for sticking with, and promoting, "thin wrappers" boils down to precisely the points that you summarize:

That works surprisingly well, minimizes design effort, minimizes documentation, and is familiar to anyone who has previously written a sqlite3 program. ... The hard part is designing a good API, which might take a few iterations.

We in the SQLite project collectively feel that there is no One Best Wrapper, and that an interested community is the best place to evolve "the best" wrapper for any given context.

The low-level API provides a solid basis for exploring an endless variety of high-level wrappers, and that is a compelling reason (for me, anyway) to provide as-thin-as-possible bindings, as opposed to trying to find an ideal shape for any given high-level wrapper. More often than not, adding a level to a high-level wrapper causes a lower-level feature to be lost in the translation. (And that's fine, provided the target for that wrapper doesn't need that capability. WebSQL, for example, shaved off lots of "sharp edges" in its wrapper, but that also eliminated access to any and all power-user features. In its designers' defense, though, they were attempting to provide a generic SQL wrapper, not an SQLite wrapper.)

In the cases of WASM and JNI, the criss-crossing across language/runtime boundaries certainly brings a performance hit with it, but my suspicion¹ is that as WASM evolves, more and more of client-side code will end up in the WASM side of the constellation, reducing this implicit performance hit as code migrates to the other side of the runtime boundary.

0.02€ and all that.

Footnotes

1. Noting that my track record for accurately predicting tech trends is astoundingly poor. ↩

[rkistner]

Designing a good API for this is indeed challenging. I worked on a project that attempts to define "cross-platform" JS SQLite APIs last year, but never got around to get a polished version out. The work-in-progress is here, also with some outdated wa-sqlite support (builds on top of the wa-sqlite APIs, instead of re-defining them).

The goal was a little different, since the idea was to get one small set of APIs that can be implemented by any SQLite driver. The tricky parts were:

1. Keeping the API surface small, but still flexible.
2. Have as little as possible overhead with async APIs, especially relevant when web workers or Node worker_threads are used (with the database in a different worker).

It's a little different from the use case for wa-sqlite:

1. You are free to add more APIs to cover different use cases. You could always keep the current APIs to keep the flexible nature, while having higher-level APIs providing a little better performance and usability for the common cases.
2. You care about wasm <-> js overhead, even if the calls are synchronous.

That said, I think there are a lot of similarities, so I can give you my findings so far:

1. I settled around the entire API working around prepared statements created synchronously in JavaScript, even though it is only evaluated asynchronously when the query is first run.
   • This avoids the overhead of an async prepare call.
   • The same APIs also work for "unprepared" statements. For that you may want a flag or separate API to automatically finalize the statement after the first call.
2. I opted for separate APIs to return each row as an array versus an object.
   • There are cases where you can have duplicate column names, in which case the array API is required. This is common with ORMs such as Drizzle.
   • Working with objects is by far the most common use case when querying directly, so optimizing that is important.
   • You can convert a row from an array to an object, but it is usually more efficient to create the row as an object from the start.
3. I had options for returning all rows in the query results as a single array, or alternative as an iterator yielding batches of rows at a time.
   • I haven't properly benchmarked the difference between yielding chunks versus single rows yet.
   • It is theoretically possible to only have the iterator API. However, getting all results is the most common use case, and having a call returning it all at once makes it easier to optimize that case (once again to only need a single asynchronous call).
4. I also had APIs for connection pooling, which becomes relevant when you want concurrency using multiple workers. It doesn't affect the primary per-connection APIs - all query APIs are on a single connection at a time.
5. If you start with a synchronous API (such as with better-sqlite3 or the new node:sqlite, or maybe even with a synchronous wa-sqlite VFS), wrapping it in async calls is always going to add significant overhead. Batching or "pipelining" calls can get you close in performance again for bulk reads and writes.
6. You'll never get the same flexibility with a high-level as with individual prepare / bind / step / column / reset calls, but with careful API design I think you can get close.

The low-level API provides a solid basis for exploring an endless variety of high-level wrappers, and that is a compelling reason (for me, anyway) to provide as-thin-as-possible bindings, as opposed to trying to find an ideal shape for any given high-level wrapper. More often than not, adding a level to a high-level wrapper causes a lower-level feature to be lost in the translation.

I've found the same thing. In the JS world, there are many JS bindings out there for various platforms that leave so much to be desired in terms of low-level capabilities. For JS you unfortunately need higher-level wrappers to properly work with its asynchronous nature, which makes the balance very difficult. Many bindings go the wrong direction, exposing (often broken) APIs for transactions for example, instead of exposing better low-level functionality.

---

There is another project attempting to standardize database APIs in JS here: halvardssm/stdext#6. The goal there is for cross-database support though, so I think it's a little less relevant here, although there are some interesting discussions.