Using absurd-sql approach to support blazor wasm

[gentledepp]

Hello @ericsink!

I recently came across absurd-sql
Essentially, this guy is using the emscripten transpiled version of sqlite in the browser.
Normally, this would be in-memory only, but he managed to tweak the sqlite code to use the IndexDb as a file store.

While this sounds absurd this is 10 times faster than indexdb and actually works!
You can read his in-depth explanations here: https://jlongster.com/future-sql-web

Now, as SQLitePCL.raw is the foundation of all sqlite ORMs I know of (Microsoft.Data.SQLite => so even EF Core, sqlite-net-pcl, etc.) it would be awesome if there was a way to add support for absurt-sql somehow.

What I am thinking of is using Blazor WASM with full offline and persisted SQLite support.
This would be a game changer, as suddely, we could write completely offline capable apps for the browser - or even port them there.
Moreover we could write PWAs and, thus, deliver mobile applications without ever touching any app store or play store.
This means: Faster deployment for custom apps to customers.

Do I sound like I have lost my mind, or does this kind of make sense to you?

Thank you for your time - any insights of you are very well appreciated!

[bricelam]

/cc @SteveSanderson

[SteveSandersonMS]

@gentledepp See https://github.com/SteveSandersonMS/BlazeOrbital for a sample app that already does this.

[gentledepp]

Cool! So my issue is I do like the approach of absurdsql but I am not a native developer (mostly C#, JS and typescript) so I have no clue how to do this unfortunately.

We would like to use this approach in an application for field agents that do inspections of buildings, power plants, etc. off grid. Currently we are using Xamarin and we do sync quite a lot of data using SQLite. Sometimes our customers have a local database with over 100MB.
There they perform their inspeciton and all defects or new equipment is added to the local db and synchronized to the central server whenever the client goes online again.

So loading 100mb every second or storing it would be way to slow. This is why I am so interested in the paging solition of absurdsql.

So: yeah it i still "just" a local cache, but we have to be able to mutate it efficiently and robustly nevertheless.

Does this make more sense now?
Do you have an idea/a colleague that could point out how "alternate strategies" could be implemented? (Did you or any of your colleagues ever have a look at absurdsql?)

[SteveSandersonMS]

added to the local db and synchronized to the central server whenever the client goes online again. ... So loading 100mb every second or storing it would be way to slow

It's possible there's some misunderstanding of the data flow here. None of this has got anything to do with synchronizing to a central server over the network. I'd never recommend sending back the whole SQLite DB to a central server in any normal case; instead you'd just call server-side API endpoints that accept specific changes like "add new equipment item", and then the user would be able to resolve any conflicts interactively in the UI.

What happens in the BlazeOrbital case is only about copying from transient in-memory JS data to in-browser IndexedDB storage so that it's not lost if they reload the page. It's all local to the client, so completes almost instantly. And it doesn't happen every second - it only happens once within one second after the user actually writes a change to the DB, e.g., when they click a "Save" button on some form.

Do you have an idea/a colleague that could point out how "alternate strategies" could be implemented?

Yes, you could change the code here to persist the state in any other way that you want. This doesn't invalidate your suggestion that an AbsurdSQL-like approach could reduce the copying by supplying further page-level data about what specifically has changed within this blob. There may be cases where people benefit from that, but it would be relatively rare that you'd be able to tell the difference, plus it creates new failure modes where perhaps only a subset of the pages have been persisted (such an issue can't happen in the BlazeOrbital strategy, where the whole DB file is written atomically).

[gentledepp]

thank you so much for the clarification.
Firstly - there is no misunderstanding. I did not expect that we send a full database to the server. But thanks for pointing this out.

However, you clearly do store the full database every 1 second given it has changed. So this might not be that much of a problem on a pc, but it definitely is on a smartphone.
Our goal would have been to move from Xamarin to a PWA in certain scenarios as it provides more flexibility. The key piece we were missing was a robust approach to handle a vast amount of relational data in an offline scenario. So I guess we will have to search for another approach then.
Thanks for pointing out the inconsistency issue with absurdsql, though!

[SteveSandersonMS]

store the full database every 1 second given it has changed

Given it has changed since the last time, yes. This may mean once in total per user action. But if your DB changes continually (eg constantly streaming in frequent upstream changes over a websocket) then I definitely agree this would be a poor strategy!

[gismofx]

@SteveSandersonMS @gentledepp I have a similar use case. Users are in the field and a solid internet connection isn't always guaranteed. So, the preferred mode is persisting a complete local copy of the database and when the internet connection is stable, perform a sync. If done daily, syncs are only 20-50 records typically. Still, the whole database could get quite large(appx 600 mb) which would be nice to persist as a SQLite file somewhere locally.

[bricelam]

See also #472