DotmimSync with a Blazor WebAssembly App - are there other SQLite libraries required/recommended

[Sean-URS]

Has anyone tested DotmimSync with a Blazor WebAssembly App. (Where the WebAssembly Client uses a local SQLite database)
Is something like SQLitePCLRaw.bundle_e_sqlite3 recommended?

Brief background: I am working on a product what needs to account for an environment with regular power supply interruptions.
I started with Blazor MAUI for POC, now I'm testing a Blazor WebAssembly App ASP.NET Core Hosted Progressive Web Application.
The client app needs to be able to pull down a large number of products (hundreds of thousands), say at the start of the day or when the server indicates that there are changes.
(I was using DotmimSync sync with Server priority to do this in the MAUI POC). Once synced, the products table is instantly accessible (like the BlazeOrbital demo by @SteveSanderson). Local transactions are stored and then pushed via an API once internet connection is reestablished (power interruption ends). I am thinking of a blend of something like CarChecker (SteveSandersonMS/CarChecker) which uses the browser's IndexedDB APIs with power of DotminSync for pulling down the large product dataset. It's still to be determined how the local transactions are stored, either similar to CarCheckes's local updates or as records in tables in the local SQLite database (potentially encrypted in the future).

Any pointers to working with a combination of local SQLite databases, DotminSync and WebAssembly would be apricated.
/cc @gentledepp

(I looked briefly at SqliteWasmHelper @JeremyLikness)

[ethhill]

We have a similar project that's released to production. SQL Server on the server with several hundred thousand rows to sync (many more than that on the server) synchronizing to a Blazor WASM PWA/SPA.

We do use SQLitePCLRaw.bundle_e_sqlite3 and use Entity Framework with SQLite, which works fine. We are also switching to SQLitePCLRaw.provider.e_sqlcipher (encrypted) shortly.

A few things to note...

• Asp.NET sessions: Blazor WASM does not natively support Asp.NET sessions, which Dotmim Sync requires on the server. We worked around this by adding a Dotmim interceptor on WebRemoteOrchestrator.OnHttpSendingRequest and setting Request.SetBrowserRequestCredentials to BrowserRequestCredentials.Include. This overrides the HttpClient's dfefault behavior; allowing it to store the session cookie that Asp.NET needs for the session.

• Full sync performance: The initial sync of that many rows to the client is SLOW. In our case, on a very fast PC it takes about 10 minutes to do the first sync. On mobile devices it takes much longer (sometimes hours). Regardless of what Microsoft claims about WASM performance, running the sync in the browser instead of directly on a PC is approximately 20 times slower on the same machine. Consequently, we create the initial synchronization on the server and download the SQLite db to the client when initializing, then do a sync to "catch up". It runs MUCH faster. We also have a process running overnight to create the initial databases, further increasing perceived performance.

• Storage: Blazor has no direct access to a persistent file system. The way the Sanderson persists his db between sessions is to copy the entire database from the virtual file system to indexedDB after any database writes. If you have a larger database this gets expensive (ours is ~100 MB). Try to keep your tables as narrow as possible, only including the columns necessary on the client (or that're not nullable). This also helps in sync performance. Emscripten is in the process of adding another file system called WasmFS which should help, but despite being discussed since 2021 it hasn't really started dev (https://emscripten.org/docs/api_reference/Filesystem-API.html#new-file-system-wasmfs).

• Threading: Sync takes 100% of the browser to do its work. Unfortunately Blazor has no threading (until later this year), so this pretty much locks up the browser during any sync operations. As noted above, for an initial sync this can take a while. We added a Dotmim Sync interceptor on syncAgent.LocalOrchestrator.OnRowsChangesApplying and do a Task.Delay(1) on every 1000'th iteration, at least allowing some responsiveness for progress reporting and to avoid browser busy prompts.

• SQLite Pooling: Minor, but if you need write access to the SQLite db file (e.g. deletes, etc) call SqliteConnection.ClearAllPools() first. SQLite connection pooling keeps the files open even after DotMim sync has released them.

FYI - my overall impression of Blazor WASM is that it needs a few more iterations before it's really production ready. As MS is on annual releases, it'll take at least another year for it to become more viable. We're using it in the meantime, but there's some "gotchas".

Good luck with your project.

[Mimetis]

Hey @ethhill, your solution seems to be really interesting !
Do you think it would be possible for you to create a small sample using DMS with Blazor WASM ?
Something really simple, using the AdventureWorks script, that we may want to include in the /samples repository ?

Would be really cool to have such example in the repo!

[ethhill]

I'd be happy to put together a sample but it'll most likely be a few weeks until I have a time to do so.

[Sean-URS]

Hi @ethhill, thank you very much for your feedback. There is lots of food for thought here :-)