5 ms·

SQLite in a PWA (Anita) with FileSystemAccessAPI

81 points by ildon 5y ago

SimeVidas 5y ago
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 5y ago
I wonder how the FS API performs compared to direct syscalls. Anyone knows?
samwillis 5y ago
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://github.com/jlongster/absurd-sql https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web
- wngr 5y ago
  Combining these two approaches would let you get rid of the IndexedDB abomination.
  - samwillis 5y ago
    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 https://github.com/WICG/file-system-access/issues/323
    - dmitriid 5y ago
      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 5y ago
        I thought Firefox flat out didn't want to implement FileSystem APIs on grounds of "security" and protecting their users?
        ildon 5y ago
        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 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 5y ago
        > 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 5y ago
  - ildon 5y ago
    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/ https://dexie.org/). It really makes using IndexedDB a breeze.
- ildon 5y ago
  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 5y ago
    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 https://rxdb.info/slow-indexeddb.html
lsb 5y ago
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 5y ago
  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 5y ago
    That is literally one of the ways that SQLite does its journalled commit
cube00 5y ago
Great to see more effort in the PWA space if we ever hope to break the stranglehold of the app stores.
- encryptluks2 5y ago
  While I agree PWAs are often better than app stores, reliance on JS frameworks adds a lot of other issues.
- ildon 5y ago
  Yes! Unfortunately the most important one is the App Store, no wonder why PWA have the least and worst support on Apple devices...
- jcun4128 5y ago
  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.
cirwin 5y ago
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/pthreadfs https://github.com/rstz/emscripten-pthreadfs/tree/main/pthre... And a more thorough write-up here: https://docs.google.com/document/d/1SmfDdmLRDo6_FoJMl5w1DVumC6Je0PQvHXSLVqtEWHk/ https://docs.google.com/document/d/1SmfDdmLRDo6_FoJMl5w1DVum...
- ildon 5y ago
  This is super exiting! I did not know about the improvements, thanks for sharing!
throw_m239339 5y ago
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 5y ago
  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-roadmap/132218 https://www.mongodb.com/community/forums/t/webassembly-in-ro...
  - throw_m239339 5y ago
    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 5y ago
      See my comments in this thread above: https://news.ycombinator.com/item?id=29404255 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.

81 points by ildon 5y ago

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 5y ago

I wonder how the FS API performs compared to direct syscalls. Anyone knows?

samwillis 5y ago

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://github.com/jlongster/absurd-sql https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web

wngr 5y ago
Combining these two approaches would let you get rid of the IndexedDB abomination.
- samwillis 5y ago
  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 https://github.com/WICG/file-system-access/issues/323
  - dmitriid 5y ago
    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 5y ago
      I thought Firefox flat out didn't want to implement FileSystem APIs on grounds of "security" and protecting their users?
      - ildon 5y ago
        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 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 5y ago
        > 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 5y ago
- ildon 5y ago
  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/ https://dexie.org/). It really makes using IndexedDB a breeze.
ildon 5y ago
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 5y ago
  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 https://rxdb.info/slow-indexeddb.html

lsb 5y ago

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 5y ago
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 5y ago
  That is literally one of the ways that SQLite does its journalled commit

cube00 5y ago

Great to see more effort in the PWA space if we ever hope to break the stranglehold of the app stores.

encryptluks2 5y ago
While I agree PWAs are often better than app stores, reliance on JS frameworks adds a lot of other issues.
ildon 5y ago
Yes! Unfortunately the most important one is the App Store, no wonder why PWA have the least and worst support on Apple devices...
jcun4128 5y ago
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.

cirwin 5y ago

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/pthreadfs https://github.com/rstz/emscripten-pthreadfs/tree/main/pthre... And a more thorough write-up here: https://docs.google.com/document/d/1SmfDdmLRDo6_FoJMl5w1DVumC6Je0PQvHXSLVqtEWHk/ https://docs.google.com/document/d/1SmfDdmLRDo6_FoJMl5w1DVum...

ildon 5y ago
This is super exiting! I did not know about the improvements, thanks for sharing!

throw_m239339 5y ago

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 5y ago
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-roadmap/132218 https://www.mongodb.com/community/forums/t/webassembly-in-ro...
- throw_m239339 5y ago
  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 5y ago
    See my comments in this thread above: https://news.ycombinator.com/item?id=29404255 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.