[HN Gopher] SQLite in a PWA (Anita) with FileSystemAccessAPI
___________________________________________________________________
SQLite in a PWA (Anita) with FileSystemAccessAPI
Author : ildon
Score : 75 points
Date : 2021-12-01 14:01 UTC (9 hours ago)
(HTM) web link (anita-app.com)
(TXT) w3m dump (anita-app.com)
| SimeVidas wrote:
| Bug report: If you navigate to the start page and click the Get
| Started button, the browser's Back button stops working (you
| can't return to the start page by going back).
| wngr wrote:
| I wonder how the FS API performs compared to direct syscalls.
| Anyone knows?
| samwillis wrote:
| This is a good writeup.
|
| It's worth noting that this loads the entire db into memory, and
| only saves to the filesystem when you tell it to (not on each
| transaction) so you could loose changes on a crash.
|
| There is a brilliant project to add true transactional flushing
| and a block like storage backend to WASM Sqlite.js called "Absurd
| SQL", worth checking out. It's currently built on top of
| IndexedDB but they are working with the WG designing the
| FileSystemAccess APIs to ensure it has suitable block level
| support and locking for this type of tool.
|
| https://github.com/jlongster/absurd-sql
|
| https://jlongster.com/future-sql-web
| wngr wrote:
| Combining these two approaches would let you get rid of the
| IndexedDB abomination.
| samwillis wrote:
| I believe FileSystemAccessAPI, as implemented in browsers,
| does not yet support block level access and locking. Both
| required to make this work, the developer behind Absurd SQL
| is working with the team designing the API to ensure it will
| have support. IndexedDB is the only way of doing this on
| browsers currently.
|
| https://github.com/WICG/file-system-access/issues/323
| dmitriid wrote:
| Unless I'm mistaken, FileSystemAccessAPI is only
| implemented in Chrome. Both Safari and Firefox are not
| implementing until four different file proposals (all from
| Chrome) can be reduced to one.
| tehbeard wrote:
| I thought Firefox flat out didn't want to implement
| FileSystem APIs on grounds of "security" and protecting
| their users?
| ildon wrote:
| Yes, many are refusing to implement it due to security
| concerns. A notable one is Brave, which is based on
| Chromium, and yet refuses to enable it
| (https://github.com/brave/brave-browser/issues/11407).
|
| I'm skeptical that reducing access to the FS actually
| protects users. Those that would be fooled by scams based
| on FileSystemAccess APIs would very likely be fooled also
| with other less intricate tactics. So I doubt that the
| overall security of users is in practice affected.
|
| At least, browsers that refuse to implement the
| FileSystemAccess APIs could implement them, but leave
| them disabled by default, and require some non-trivial
| action to enable them. So users with a very basic
| understanding of how things work in the browser would not
| be able to enable them.
| dmitriid wrote:
| > So I doubt that the overall security of users is in
| practice affected.
|
| The problem is the sheer number of APIs that Chrome ships
| and wants other browsers to ship, and what browsers
| already ship that require access via prompts: camera,
| location, notifications, file access, bluetooth, usb,
| motion sensors, serial ports, midi devices, clipboard...
|
| Just prompting user to allow stuff is no longer enough,
| and adding more prompts leads to worse security.
| slaymaker1907 wrote:
| It's sort of a double prompt scenario since the
| FileSystemAccessAPI requires users to actually select the
| file/directory for the web app to use. You can save the
| file handles into IndexedDB in which case the user will
| only be prompted if they refresh the page to authorize
| the web app to continue using that file/directory.
|
| From my use of it, the only real vulnerability I see is
| that Chrome still considers anything from file:// to be
| the same origin. This means as a developer you absolutely
| should not be saving file handles to IndexedDB if you are
| loading your app via file:// instead of https://. This is
| a pretty niche use case, but I do think static html
| "apps" are an underappreciated form for distributing
| software and this new API makes such apps a lot more
| powerful.
|
| I wrote a plugin for TiddlyWiki that lets it operate
| really smoothly using this API
| https://github.com/slaymaker1907/TW5-browser-nativesaver
| that demonstrates why this API is worth the trouble.
| tehbeard wrote:
| I'm not sure any API that requires "secure context" can
| be run from a file://, for those reasons.
| samwillis wrote:
| I wouldn't be surprised if some (or most) browsers don't
| implement the parts of the FileSystem API that are
| designed to access the users home directory (with
| permission). But the parts that are for a "sandboxed
| virtual drive" will be, that is what's needed for web
| apps and PWAs for saving their own data in a local block
| level storage (rather that IndexedDB or LocalStorage).
| That part of the api is very much designed for enabling
| things like WASM Sqlite.
|
| https://developer.mozilla.org/en-
| US/docs/Web/API/FileSystem "This
| interface will not grant you access to the users
| filesystem. Instead you will have a "virtual drive"
| within the browser sandbox."
| ildon wrote:
| If you need to use IndexedDB and you do not want to have to
| handle the absurdities of IndexedDB, give Dexie a try
| (https://dexie.org/). It really makes using IndexedDB a
| breeze.
| ildon wrote:
| Glad you liked it! And thanks for mentioning absurd-sql, I did
| not know the project.
|
| You are absolutely right on memory and crashes. It is a very
| inefficient way of storing data in a browser from a memory
| perspective (and data loss risks). I did mention it in my
| conclusions :-)
|
| If FileSystemAccessAPIs added block level support and locking
| for at least certain files that would be a game changer for
| things like this. In the meanwhile IndexedDB is surely a better
| option.
| typingmonkey wrote:
| I analyzed [1] that problem and tried to reproduce data loss
| with LokiJS, which also has everything in memory and only
| partially writes to disc. It is very hard to reproduce a
| loss. You can call the saveDatabase() when the window
| 'unload' event fires and also after each write when the
| database is idle again. This works quite reliable.
|
| The only way to lose data is when I shutdown power directly
| after a write.
|
| It is also to mention that having everything in memory will
| not support multi-tab usage.
|
| [1] https://rxdb.info/slow-indexeddb.html
| throw_m239339 wrote:
| If only there was a spec that implements a web SQL database... Oh
| wait, Mozilla didn't want to make the web more powerful so they
| refused to even consider it with dubious excuses.... And no,
| there was absolutely no need to specify SQL in the spec, only how
| to query the database.
|
| What do you think is easier to code? A sql builder capable of
| supporting different SQL dialects, or an entire SQL database? So
| we are stuck with this horrid indexedDB API...
|
| This, the refusal to even consider the File System API, HTML
| imports and many others things are reasons I stopped supporting
| and recommending Mozilla and Firefox as a developer.
| samwillis wrote:
| Personally I think it was the right choice not to continue with
| WebSQL. Although it would have been good, there is so much more
| that you can now do with the coming low level file storage apis
| and WASM. There is going to be an explosion of different WASM
| databases, SQLite.js (which is better than websql as you can
| use all the various SQLite extensions) is only the first of
| many. It's only a matter of time until MongoDB Realm is
| available as WASM[0]
|
| 0: https://www.mongodb.com/community/forums/t/webassembly-in-
| ro...
| throw_m239339 wrote:
| SQLite.js is in memory only, it doesn't persist anything in
| web storage. There is absurd.js which uses indexedDB as a
| storage layer, but it's called absurd for a reason, the
| underlying storage engine browsers use, especially Firefox,
| is often Sqlite itself.
|
| So no, between a standard that allows the developer not have
| to load an extra blob of code and roll their own persistance
| layer, and a standard everybody implements, I'll take the
| standard.
|
| There is absolutely nothing right with the current situation.
| samwillis wrote:
| See my comments in this thread above:
|
| https://news.ycombinator.com/item?id=29404255
|
| A low level sandboxed file system api (which is 100%
| coming) will let developers do exactly what we want in
| incredible future proof way and not be tied to out of date
| and none extendable apis.
|
| I want sql in the browser, in my opinion websql was the
| wrong way to to it, Wasm is.
| [deleted]
| cube00 wrote:
| Great to see more effort in the PWA space if we ever hope to
| break the stranglehold of the app stores.
| ildon wrote:
| Yes! Unfortunately the most important one is the App Store, no
| wonder why PWA have the least and worst support on Apple
| devices...
| jcun4128 wrote:
| Yeah it's great if you're mostly a web person for graphics.
| Like Electron for me with Desktop apps. Still trying to learn
| C++/something like QT but yeah.
| encryptluks2 wrote:
| While I agree PWAs are often better than app stores, reliance
| on JS frameworks adds a lot of other issues.
| cirwin wrote:
| This is great!
|
| A few more exciting things are happening with file-systems in
| Chrome that will make this a lot better soon.
|
| Firstly OPFS gives you a private sandboxed filesystem you can
| access with `await navigator.storage.getDirectory()` to avoid the
| permission prompt.
|
| Secondly "Augmented OPFS" is coming to web workers, which will
| give you the ability to read/write partial files with
| `file.createSyncAccessHandle()`.
|
| There's a demo of this working from the Chrome team here:
| https://github.com/rstz/emscripten-pthreadfs/tree/main/pthre...
|
| And a more thorough write-up here:
| https://docs.google.com/document/d/1SmfDdmLRDo6_FoJMl5w1DVum...
| ildon wrote:
| This is super exiting! I did not know about the improvements,
| thanks for sharing!
| lsb wrote:
| Uh, one of the major selling points of a database like SQLite is
| that it writes out its contents as they get updated, and it seems
| like this is bulk overwriting the file (which could be dangerous
| if there's a power loss during writing)
| matt123456789 wrote:
| Yeah, this is still solvable at a 2x storage penalty by
| following a pattern like "write new copy/rename overwrite of
| copy to original" pattern. Doesn't solve the efficiency
| problem, though.
| lsb wrote:
| That is literally one of the ways that SQLite does its
| journalled commit
___________________________________________________________________
(page generated 2021-12-01 23:02 UTC)