Watermelon adapter for Origin Private File System

[nmeibergen]

I developed an application for react native which I now want to extend to the web. As my app requires quite some complicated SQL queries using the more basic LokiJS adapter would not suffice. As such I started looking for an alternative which leaves me with no other option than using some SQLite Wasm implementation to leverage the 'full power' of SQL in the browser.

Based on everything I read about this Wasm project I figured out that, looking at performance and persistence, the best results should be achievable with the Origin Private File System, see here, here and here.

The out of the box API for OPFS with SQLite Wasm is fairly limited, see the available APIs here: OPFS must be accessed via a worker, and as such the Wasm is also run in a worker, giving limited options. Therefore I started

1. building a 'minimal' implementation of an interface for the Sqlite Wasm that provides practically the same functionalities as the original OO1 API, but all methods being async. I called it bare-sqlite-opfs, see here. I included a couple of methods (like run and all) to easier 'adapt' with Watermelon.
2. implementing an adapter for bare-sqlite-opfs in WatermelonDB, see here (which has a slightly messed op fork but is almost on par with the latest watermelondb version).

Earlier today I created a 'test' web app to see how it was working: watermelon launches as expected and creates and observes models as expected. So, that is a very good start.

This is all very 'fresh' code which I do not recommend for direct usage in your project. However, based also on your feedback and input I'd like to proceed the necessary development if it is all deemed worthwhile. The code still needs a rigorous testing suite, which is something I need to look into for I am not familiar yet with testing workers.

My main question to you

• What are your general thoughts on OPFS together with Sqlite Wasm for Watermelon?
• Do you see any important drawbacks in creating this Sqlite Wasm interface and adapter for Watermelon?
• What criteria do you deem important for making such adapter worthwhile? In particular, due to the discussion we had before

[radex]

@nmeibergen Excellent, happy to hear that you're working on it!

Good timing - I was just writing the other day a guide to contributing new adapters. So let me finish that and I'll give you feedback within a few days

[radex]

@nmeibergen I'm not sure if you need this, but maybe this new doc will be helpful for you: https://github.com/Nozbe/WatermelonDB/blob/master/docs-website/docs/docs/Implementation/DatabaseAdapters.md

[radex]

@nmeibergen I read up on what you posted here. I wasn't aware that sqlite has an official wasm port. Using OPFS for this seems a lot better than previous approaches I've seen. I would highly encourage you to contribute the sqlite/OPFS support to Watermelon core when you feel you're ready for this.

Can we maybe reuse the @nozbe/sqlite repo & NPM package to ship the wasm SQLite with Watermelon? That would be very convenient in terms of keeping the sqlite version & update workflow consistent across platforms

I would encourage you to try running sqlite-opfs synchronously on main thread instead of in a worker. You might be surprised that this works better and more convenient thanks to synchronous operation. (Of course this is use case-dependent, if you have very heavy queries then you might be forced to be asynchronous).

Regarding a test suite - developing this as part of Watermelon core gives you the ability to run the Watermelon test case suite that runs for all other adapters to ensure compatibility. This will be easier to build and maintain than a bespoke set of test cases.

[radex]

@nmeibergen Hey! Any progress here? Also, can you please email me ( [email protected] ), I have a matter to discuss but could not find any contact information for you :)