Synchronous JS build

[schickling]

First of all, thanks for the continuous great work that has gone into this project. I'm about to switch to wa-sqlite for LiveStore given a few limitations I've been facing with the "official" sqlite-wasm project.

In LiveStore I'm running two SQLite instances. (1) in-memory on the main thread and (2) a OPFS-persisted SQLite instance.
For (1) (and possibly for (2)) I'll need a synchronous build of SQLite. It seems like there is already a synchronous WASM built but the JS functions are using async / await:

wa-sqlite/src/sqlite-api.js

Lines 728 to 736 in f5092b5

	return async function(stmt) {
	verifyStatement(stmt);
	// Allow retry operations.
	const rc = await retry(() => f(stmt));
	return check(fname, rc, mapStmtToDB.get(stmt), [SQLite.SQLITE_ROW, SQLite.SQLITE_DONE]);
	};
	})();

Would you recommend me working with a local patch to remove the async code from the sqlite-api.js (would this even work?) or would you be open to also providing a synchronous API version?

Thanks a lot in advance! 🙏

[rhashimoto]

Would you recommend me working with a local patch to remove the async code from the sqlite-api.js (would this even work?) or would you be open to also providing a synchronous API version?

I think a patch or fork is the way to go. It's not something I'm interested in doing myself (unless under paid contract or sponsorship), but I'm happy to provide advice both to help you get started and along the way in case you run into problems.

Are you planning to write your own synchronous VFS or use one of the examples? If you're going to use an example VFS, I think that only MemoryVFS and AccessHandlePoolVFS will work. OPFSCoopSyncVFS is a synchronous VFS so it works with a synchronous WebAssembly build, but it also depends on the asynchronous JavaScript API wrappers that you want to change. That's what this line does:

wa-sqlite/src/sqlite-api.js

Lines 731 to 732 in f5092b5

	// Allow retry operations.
	const rc = await retry(() => f(stmt));

This lets wa-sqlite wait for asynchronous things to happen outside SQLite WebAssembly so the build can be synchronous. Right now OPFSCoopSyncVFS is the only example VFS that uses this mechanism. If you're not using it then I think your synchronous version could look like this:

   return function(stmt) { 
     verifyStatement(stmt); 
  
     console.assert(!Module.retryOps.length, 'retry operations ignored');
     const rc = f(stmt); 
  
     return check(fname, rc, mapStmtToDB.get(stmt), [SQLite.SQLITE_ROW, SQLite.SQLITE_DONE]); 
   }; 

[schickling]

I have now a synchronous build working locally by patching the sqlite-api.js. Thanks again for your guidance!

@rhashimoto I'd be interested in helping to contribute a synchronous build. One possible approach could be to add a fnSync() API variant to each fn() API that could be sync instead e.g. statements + statementsSync. Would you be open to accepting a PR for that?

An alternative approach could be to duplicate the sqlite-api.js file into a sqlite-api-sync.js or similar.

[rhashimoto]

Would you be open to accepting a PR for that?

I'm not necessarily opposed, but (1) I'm trying to figure out whether the benefits outweigh the increased API, documentation, and testing footprint, and (2) I'm wondering whether there is a more out of the box solution for you.

First I'd like to understand why the official SQLite WASM (OSW) library doesn't work for you since it has a synchronous API and supports in-memory databases as well as their own implementation of AccessHandlePoolVFS, and why you would prefer fixing wa-sqlite instead of OSW.

Second, I'd like to know why you want a synchronous API. I'm guessing performance - it should be faster not to enqueue a micro-task to retrieve every row. Is that correct (and if so, have you measured the performance difference), and are there other benefits you're after?

[schickling]

For the time being I'm using my own fork which contains the changes I need. Would be happy to see whether some of this changes can be contributed back (in a cleaned up way).

First I'd like to understand why the official SQLite WASM (OSW) library doesn't work for you

A few reasons:

• wa-sqlite has a smaller bundle footprint (both WASM and JS)
• wa-sqlite provides a more convenient setup to implement VFSs which fit my requirements. I've been running into a bunch of limitations/quirks using the persistence mechanisms of the OSW build (e.g. even when using the SAH pool persistence, it stills spawns and uses an asynx proxy worker 🤦)
• wa-sqlite seems like a much more "modern JS"-friendly code base as a foundation to potentially maintain my own SQLite WASM build as my requirements evolve. (Thanks again for your fantastic and relentless work on this!)

Second, I'd like to know why you want a synchronous API.

I'm using SQLite as part of LiveStore which is a continuation of the Riffle research project. The idea is to use SQLite in a "hybrid setup" which means running a sync, in-memory instance of SQLite in the main thread (which LiveStore adds a reactive query layer on top) and additionally have a persisted SQLite instance running on a worker. The persisted SQLite instance could also be async.

I'll experiment more with different VFS implementations to see what's the best setup for LiveStore.

Happy to answer more questions if you're interested.

[rhashimoto]

Okay, that's very helpful.

Your reasons for preferring wa-sqlite are somewhat atypical, but they make sense. However, I am having trouble imagining a significant number of other developers similarly wanting a synchronous API considering all the accompanying constraints. I'm not convinced that upstreaming your work here is the best way to go. I'm wondering if there is an alternative approach that might be more generally useful.

The idea is to use SQLite in a "hybrid setup" which means running a sync, in-memory instance of SQLite in the main thread

Why does the in-memory database need a synchronous wrapper API? Is it only for performance, or is it because of something else? For example, how does a synchronous version of open_v2() help you? If performance is a concern, what kinds of queries are currently too slow?

I'm thinking that an API call (or calls) could be added to iterate sqlite3_step() in C. The added call would be asynchronous, but steps within the call would be synchronous. Assuming micro-task overhead is a bottleneck, this should accelerate statements that produce multiple rows and/or statements repeated with multiple parameter bindings. If that works then it could improve performance for this usage on any build with any VFS.

[schickling]

I'm not convinced that upstreaming your work here is the best way to go. I'm wondering if there is an alternative approach that might be more generally useful.

That makes sense. Happy to explore other ideas/approaches to pool our efforts.

Why does the in-memory database need a synchronous wrapper API?

The main reason is to provide synchronous query results (similar to how you want setters in React.useState() to be synchronous).