Blazor PWA with SQLite

Peter Y 1

Hello,

We are trying to create a Blazor WebAssembly with PWA enabled and EntityFramework ORM with SQLite backing application. I followed Steve Sanderson's instructions in youtube: https://www.youtube.com/watch?v=kesUNeBZ1Os in dotNet channel. I tried pasting my entire code here but it seems like there is an issue with the code and I can't upload a zip file so screenshot it is. I apologize if it is too much screenshot guys. Anyways, I was able to get it to work in debug and release mode but it fails in Docker with this error:

No errors with "docker build" as far as I can tell.

No error with "docker run" as well.

This is the docker image and container screenshots:

Solution screenshot:

I tried to publish it to a folder using "dotnet publish" and I also get an error:

Since I cannot attach or copy the code here, I'll just screenshot them.

Project file:

wwwroot folder:

dbstorage.js file:

Data folder:

ClientSideDbContext file:

DataSynchronizer.cs file:

Wave.cs file:

WaveDataService.cs file:

Pages folder:

Index.razor file:

Project root directory:

Dockerfile file:

nginx.conf file:

Program file:

I really hope somebody can help us on this one. Thank you.

Zhi Lv - MSFT 32,016 Reputation points Microsoft Vendor

2022-02-08T09:24:04.767+00:00

Hi @Peter Y ,

This seems like an open issue, you can check this thread: Publish BLAZOR WASM with native File Reference SQLite failled.

Best regards,
Dillion
Peter Y 1 Reputation point

2022-02-08T13:55:48.08+00:00

Hello Dillion,

Thank you for your quick response. The EF issue was resolved by adding PublishTrimmed = false in my project file.

But you are right, SQLite is still an issue.

I hope Microsoft's team of developers will be able to figure it out soon.

1 answer

Bruce (SqlWork.com) 56,846 Reputation points

2022-02-09T17:53:54.94+00:00

while this is a cool proof of concept, and support of existing WASM compatable libraries is a great feature, hosting EF and sqlite seems a little overkill.

the WASM sqlite is a memory only database, as WASM does not have file access. The blazor sample uses javascript to copy the table data to local storage for persistence. its a lot of code to load just to get EF working and the ability to use EF's linq

Instead you could just use linq to objects and a small refactor of the sync code. you could also just use a service worker to cache the ajax calls.
Please sign in to rate this answer.
Peter Y 1 Reputation point

2022-02-11T20:47:38.417+00:00

the thing is, we are not allowed to store confidential information in the local storage since when you go to the Developer Tool (e.g. Chrome) the data can be read in plain text. If we go that route we will fail on our security audit conducted by our corporate security engineers. with this approach, since it is the sqlite db file that is being saved in the indexeddb then there is no plain text to read.

so these are the things we need:

Linq capability

Encryption of data

Limited timeline to implement

with EF + SQLite we have the Linq and SQLite for an encryption effect on data since it cannot be read easily from the developer tool. Linq is mostly for convenience of our developers. SQLite is for the 120 tables and a range of 5-40 columns per table.

this application will also be used heavily offline since the users for this are people going to remote locations that has no internet connectivity.

Bruce (SqlWork.com) 56,846 Reputation points

2022-02-11T21:24:32.5+00:00

SqlLite does not have builtin encryption for data files, and certainly not for the in-memory database. at best you are obfuscating the data, but don't call it encryption. there are several local storage encryption packages on npm if you stick with sqllite and need encryption.

The common solution for offline storage is indexedDb:

https://developers.google.com/web/ilt/pwa/working-with-indexeddb

this solution allows access from both the application, and the service worker. The service worker can upload the db changes when there is a connection, even when the app is not running, so this is the most common solution for offline access. also unlike local storage which is limited to about 5 mb, indexDb can use up to 20% of the webkit quota of 50% of free disk space. you might be interested in this component

https://github.com/Jinjinov/IndexedDB.Blazor

note: my interest in blazor is purely technical, I use react for my actual applications. sql.js supplies the same solution for javascript developers (sqlite via WebAssembly)

Peter Y 1 Reputation point

2022-02-14T16:15:27.487+00:00

hey Bruce,

thank you for the reply. anyways, when I said "encryption effect" i meant obfuscating the data. the word just escaped my mind during the time of writing.

for the initial PoC i created, i used SQLCipher to encrypt the data paired with a desktop application but the business guys is asking if there is another approach, hence, we thought PWA and SQLite combo. we are hoping corporate security engineers will not require us to encrypt the data since there is no physical file and hoping that obfuscating the data is good enough. if not, i already talked to Richard Hipp on possibility of buying a license of SEE for our solution.

hopefully we can have this figured out. if not, we will stick with desktop application + encrypted SQLite combo for the next few years until we will be ask to modernize the app again to whatever is relevant in the next 5 years.

Bruce (SqlWork.com) 56,846 Reputation points

2022-02-14T16:55:23.48+00:00

The other options is question the need to encrypt at rest. with the chrome tools or a sniffer, looking at the network traces will display all the traffic in clear text. the api is open and can be called with other utilities. you should not send data the user is not supposed to "see". if you need to send context data, it should be encrypted at the server.

if you use sqllite with a desktop app, the user can open the database with many tools to see the data.

maybe you just need a data obfuscation library.

note: depending on your delivery time frame you might be interest in blazor desktop (basically electron for blazor)
Sign in to comment