16 ms·

SQLite Wasm in the browser backed by the Origin Private File System

372 points by bubblehack3r 4y ago

adelia89 4y ago
[dead]
Kelteseth 4y ago
Fun fact: The headline image is the Stadtbibliothek Stuttgart, the nicest library I ever visited: https://www.archdaily.com/193568/stuttgart-city-library-yi-architects https://www.archdaily.com/193568/stuttgart-city-library-yi-a...
- tomayac 4y ago
  0711 love! Viele Grüße!
systems 4y ago
what is exactly the use case for this?
- jchw 4y ago
  Based on this note: > In our blog post Deprecating and removing Web SQL, we promised a replacement for Web SQL based on SQLite. The SQLite Wasm library with the Origin Private File System persistence backend is our fulfillment of this promise. I'd venture to guess the best answer is "whatever the hell people used Web SQL for." Doesn't really answer the question, but alas.
  - deleted 4y ago
    [deleted]
- samwillis 4y ago
  It's an alternative to using IndexedDB which is a browser provided API. On use cases, you can give your web app offline support by locally caching data in an SQL database and have it be fully queryable. Say you are building an app like Notion, they already have "offline mode" for the mobile and desktop apps, this would enable you to build that for the web app. This is very much one of the final jigsaw pieces needed to make PWAs (progressive web apps) competitive for the majority of use cases. We just need Apple to catch up and fill in a few other blanks too. A design pattern that is beginning to emerge is "offline/local first". You design your app to fundamentally work offline, using things such as this, and the server component only works to synchronise clients. It's a bit like the design move to "mobile first" that happed 10 years ago, but going to another level.
  - wargames 4y ago
    I haven't used IndexedDB in a while.. but my recollection is that it is basically SQLite. Not sure I see the advantage of this above and beyond what IndexedDB is already providing. What are the pros and cons of each approach?
    - actionfromafar 4y ago
      https://nolanlawson.com/2021/08/22/speeding-up-indexeddb-reads-and-writes/ https://nolanlawson.com/2021/08/22/speeding-up-indexeddb-rea... IndexedDB seems like a whole bag of gotchas. SQLite + simpler browser provided file backing seems interesting to me.
    - azangru 4y ago
      > but my recollection is that it is basically SQLite. SQLite, which has a SQL database engine, is, by definition, a relational database. IndexedDB is a non-relational, or noSQL, database. One can't be basically the same as the other :-)
    - AndrewSChapman 4y ago
      IndexDb is not a relational data store. It's much closer to Mongo than it it to Sqlite. If you want relational tables, joins, aggregations etc, you want something like this (or the original Web SQL that was deprecated). There is definitely a use case for it.
      - mr_toad 4y ago
        > It's much closer to Mongo than it it to Sqlite. If only. It’s much closer to DBM than it is to Mongo.
    - simonw 4y ago
      One is a key value store, the other is a full relational database. Think Redis vs PostgreSQL.
  - phaedrus 4y ago
    So thick client applications get the last laugh in the end? It reminds me of an old concept, that has a catchy name I can't recall, positing an endless cycle in systems evolution where local peripherals grow processing capability until someone notices and (re)centralizes it, only for the local processing capability to quietly begin growing again, and so on.
- deleted 4y ago
  [deleted]
- azangru 4y ago
  SQL but in the web browser. Mobile apps have long been using sqlite for their needs. Now web apps can too.
- simonw 4y ago
  It lets you build static web applications that can manage relational data for your users that is stored on their own machines. This is a really powerful capability. You could build the equivalent of full desktop applications - things like Word, Excel, Access, Evernote, task trackers, Blender... all without any server side storage mechanism at all.
  - cudgy 4y ago
    So basically a desktop app that runs in a desktop app on a desktop?
    - simonw 4y ago
      Yup - but crucially one that people can start using by clicking a link, without having to download and install anything first. And it's an app that runs in a very robust sandbox, so much less risky than installing software.
      - cudgy 4y ago
        True, but you have it backwards. Web app needs user to download and install a web browser first; desktop app does not need anything downloaded first. Both can be started by clicking a link.
        simonw 4y ago
        I guess you mean downloading a browser version that supports the new origin filesystem feature? Every user has a web browser installed.
        cudgy 4y ago
        Quite simple. I do not need to download a web browser at all to run the desktop app. Deployment of the desktop app is simpler and provides the same or even more functionality without adding a complex desktop browser and sandbox container to run the app. Why is this so hard to get across?
- dboreham 4y ago
  You want to run some code in the browser that wants to use a persistent SQL database.
- cloudking 4y ago
  Perhaps this could be used to create more privacy focused apps where your data stays local and works offline. I'm not sure about the persistence though, like if you clear the cache does it wipe out the local database?
  - ehutch79 4y ago
    Or if you switch devices? Or need to collaborate on the data (i.e. a company with empoyees) Or have regulations/controls on where data lives 'at rest'
- 0x_rs 4y ago
  Plenty. Suppose you're working on a cross-platform application, building for web will get even easier, as up until a few months ago there weren't even official WASM builds from SQLite (but some amazing community run ones such as sql.js!). As for why somebody would want that, an example: your browser and everyone's is probably always open when you're at a PC, attrition is next to none compared to having to download an application, it's great for even just trying something out. SQLite really makes user data storage trivial in most circumstances, key:value doesn't.
samwillis 4y ago
This is a nice overview! Discussion from back when the SQLite project announced this last year: https://news.ycombinator.com/item?id=33374402 https://news.ycombinator.com/item?id=33374402 There isn't an "official" NPM package or ES6 model yet, but I believe they are working on it. It's also very much designed for the browser currently, but again I believe they are intending to support server WASM environments too eventually.
- lovasoa 4y ago
  Hey, I am the maintainer of sql.js. I was in contact with them, and I do not think they are working on a npm module, but I offered them to work on the existing "official" sql.js module.
  - samwillis 4y ago
    Looking back at the thread, you are right. It's the ES Module that they do intend to eventually have.
    - coliveira 4y ago
      He knows he is right. It is you who need to thank him for pointing this out.
- sgbeal 4y ago
  > There isn't an "official" NPM package or ES6 model yet (The sqlite team's JS/WASM Guy here...) An ES6 module build was added shortly after the 3.40 release. NPM/node.js are nowhere on our radar. Our build is structured such that people who want to plug it in the resulting JS file to whatever their favorite toolchain is are welcomed to do so, but we have neither the ambition nor the bandwidth to support every build/bundling platform out there, especially ones none of us otherwise use. > It's also very much designed for the browser currently, but again I believe they are intending to support server WASM environments too eventually. The JS code is targeted solely at browsers and there are no plans on changing that in the foreseeable future. The WASM build itself, we are working to provide server-side support for. We have a branch which builds under wasi-sdk, but we cannot create a JS binding for that build until/unless we find some substitute for Emscripten's transparent translation of POSIX I/O APIs.
gfodor 4y ago
Ironically I was just about to drop in absurd-sql [1] to a project, which uses indexeddb to back SQLite. This seems better. [1] https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql
- DylanSp 4y ago
  This is definitely better, yeah. The blog post[1] on absurd-sql notes that it's a hacky solution that would be improved with file system access; it references the old Storage Foundation proposal [2], which has since evolved into the current File System Access API proposal(s). [1] https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web [2] https://developer.chrome.com/docs/web-platform/storage-foundation/ https://developer.chrome.com/docs/web-platform/storage-found...
- Dork1234 4y ago
  Will either SQLite WASM or absurd-sql work on Firefox and Safari?
- tantaman 4y ago
  If it helps, I detail some of the pros/cons of using the official SQLite build vs unofficial builds (wa-sqlite, which is similar to absured-sql) here: https://vlcn.io/docs/guide-persistence#persistence-options https://vlcn.io/docs/guide-persistence#persistence-options
est 4y ago
TIL OPFS. https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API#origin_private_file_system https://developer.mozilla.org/en-US/docs/Web/API/File_System... What's next, mmap() for javascript?
- fabiospampinato 4y ago
  That's available already, in Bun.
- simula67 4y ago
  Sockets, hopefully. For example, it should be possible to build websites for downloading YouTube videos that does not use server side code execution or bandwidth. These were popular at some point but disappeared after Java applets stopped being supported by most browsers.
  - lxgr 4y ago
    Or just an XMPP web client.
  - heywhatupboys 4y ago
    i dont understand how this is not already possible?
  - jeroenhd 4y ago
    And make every website part of a botnet? I sure hope not. WebSockets are already a problem because many web devs don't know that other origins can connect to them and potentially fetch data they're not supposed to (even ignoring the fact you can construct these without a browser), unleashing raw sockets to the web platform would be hell. WebRTC and WebSockets provide more than enough already. If you need even more, your browser just isn't the place for this type of code IMO.
    - madacol 4y ago
      Instead of fighting against that feature, why not instead ask permission from the user?, just like the APIs of location and notifications do
DylanSp 4y ago
Does anyone have hands-on experience with how well different browsers support this? This blog post and caniuse both say Chrome support is currently somewhat limited (though being improved); caniuse also mentions that Safari (both desktop and iOS) only support the Origin Private File System part of the File System API, and that Firefox doesn't support it all. I'm curious how all that actually shakes out in practice.
- Existenceblinks 4y ago
  Yeah, so tired of excitement without thinking the full production process.
- dboreham 4y ago
  What happens is that one browser (usually Chrome) implements a useful new feature. Then applications are released that use that feature. Then the other browser vendors start hearing from their users that they suck because they don't support the nice new feature and hence can't run the nice new applications. And eventually they add support.
  - dmitriid 4y ago
    > What happens is that one browser (usually Chrome) implements a useful new feature. What happens is Chrome releases a Chrome-only non-standard (at a rate of 400 APIs per year). It cobbles together a barely legible spec [1], and then uses its multiple propaganda channels like web.dev to present them as actual completed standards that other browsers are just slow to implement. Even if other browsers only learn about this a few days before Chrome ships, or have multiple objections. [1] Example, File System API https://wicg.github.io/file-system-access/ https://wicg.github.io/file-system-access/ "not a W3C Standard nor is it on the W3C Standards Track."
    - tomayac 4y ago
      Please see https://developer.chrome.com/blog/capabilities/#how-will-we-design-implement-these-new-capabilities https://developer.chrome.com/blog/capabilities/#how-will-we-... for the actual process. Nothing happens behind closed doors; other vendors can chime in at any time, and are actually very much encouraged to do so.
rektide 4y ago
I have yet to see whether implementations allow users direct access via the filesystem to these contents. The idea of each origin having some files sounds fine. But if I genuinely want to help & empower my users, give them the most user agency, I'd want them to be able to access the sqlite sb themselves directly, with whatever hazards they thereby assume.
- samwillis 4y ago
  From my reading previously, no the currently OPFS implementations do not expose the files as files in directories the user would access. The File System Access API does give the webpage the ability access files on the users file system, with permission. However not at the block level, that is only enabled for the OPFS. In order to provide ACID compliance SQLite needs to be able to hold a lock to a file and write at the block level, which is only possible within the OPFS sandbox.
  - rektide 4y ago
    My use case is wanting a git implementation. I don't care about ACID, but I do care a lot about speed, and it absolutely is there 100% for the user's sake. So I feel shit-out-of-luck. Also, I'd rather use JS than wasm, and whatwg-fs/OPFS focused entirely on sync apis, to appease wasm folk who have cumbersome shims they didn't want to deal with. It's frustrating that the web community first build File System Access, but made a number of design choices compromising performance. Then AccessHandles/whatwg-fs, which unlocks performance, but severely limits it's usability to things users can't really interact with. And only release a sync implementation. I love the web platform, but this feels so unfortunate.
smallerfish 4y ago
Very nice. I hope Firefox end up supporting OPFS. My use case is an entirely offline webapp that I'm building as a side project, partly just to see what is possible. I've tried indexeddb, pouchdb, absurd-sql, and various others, and have ended up back at localstorage, which is plain but low on edge cases. The other big pain point I am hacking around is cross device sync; I'm leaning towards simple chrome and firefox extensions that'll slurp data into your profile in order to sync, which'll work for cross computer sync for my needs. However, chrome on android doesn't support extensions, and there's no other good way that I've found to sync between a user's chrome profile and android, so that is still a hole in the story. Some kind of webrtc based thing is imaginable, but it's clunky.
- byhemechi 4y ago
  i believe OPFS is supported on nightly? don’t quote me on this yeah
  - galleywest200 4y ago
    From what I can see they are very much writing code for this. This request for move() in OPFS was closed four months ago. https://bugzilla.mozilla.org/show_bug.cgi?id=1789116 https://bugzilla.mozilla.org/show_bug.cgi?id=1789116
Existenceblinks 4y ago
The sqlite.wasm is 683kB and the sqlite.js is 300kB. So is it supposed to be used in PWA context where these files are cached? Why no one is talking about CAP here. Is it real-production-ready?
- simonw 4y ago
  What do you mean by CAP? 683KB plus 300KB is less than many web pages these days.
  - Existenceblinks 4y ago
    The theorem. Where is the source of truth. Does every offline app now require CRDT or OT? Hm.. 1 MB is good (just zero app code) ..okay.
    - dboreham 4y ago
      It's just a database (not distributed, not replicated), that you can use from your browser-hosted application. What you do with it is up to you.
  - chmod775 4y ago
    Many web pages these days also have embarrassingly subpar engineering. Just because everything is already shit doesn't mean it's a good idea to pile more on top.
typingmonkey 4y ago
Using SQLite in the browser is great until you have users that open your app in more then one browser tab.
- samwillis 4y ago
  See here: https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking It's mostly works with OPFS but with a few edge cases. They are working with the browser vendors on improving this in OPFS. Alternatively run it in a SharedWorker (https://developer.mozilla.org/en-US/docs/Web/API/SharedWorker https://developer.mozilla.org/en-US/docs/Web/API/SharedWorke...), which seems to have made a comeback after being dropped for security concerns. Or do some sort of leader election with browser tabs and use a BroadcastChannel.
  - tantaman 4y ago
    SQLite WASM uses SharedArrayBuffers and Atomics to turn async OPFS apis into blocking APIs (really just _one_ api -- getSyncAccessHandle) Given the use of SharedArrayBuffers and that SharedArrayBuffers can't be used in a shared worker (funny), SQLite WASM doesn't work in shared workers. If getSyncAccessHandle was made synchronous then maybe everything would just work. It'd also improve SQLite WASM perf by 30% according to their measurements [1] [1] https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e3713df2eb7963c7f08d954738a5f456a5d8a4f1 https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e...
- tantaman 4y ago
  What's wrong with many tabs? The DB handles getting hit by many tabs correctly -- it's just as if many processes were hitting the db in a non-browser environment. Example of syncing your app across tabs with SQLite here: https://vlcn.io/docs/guide-solving-tabs https://vlcn.io/docs/guide-solving-tabs
- omniglottal 4y ago
  If your app cannot handle concurrency while your back-end i/o is via a machine-local singleton, that sounds like a a problem which will come up for more than just multiple tabs. If architecting around this known-fact is a challenge, an engineer may have bigger problems.
yyyk 4y ago
The Web SQL standard was deprecated (by Mozilla) on the grounds of a single implementation. The result today: A single SQL implementation with no standard (beyond SQLite) and therefore no easy way for anyone to create a compatible alternate implementation.
- roblabla 4y ago
  Or an alternative read of the result today: A simple implementation of filesystem access, on top of which any number of database (SQL or not) can be implemented, thus allowing for more innovation in the database space.
- ngrilly 4y ago
  Nothing is preventing you to "create a compatible alternate implementation".
- CJefferson 4y ago
  However, now every webbrowser doesn't have to keep it correct, secure and up to date for the next 40 years.
  - lxgr 4y ago
    Yes, now every single site using SQLite has to do that.
- simonw 4y ago
  Thanks to Mozilla rejecting Web SQL we now get to run the exact version of SQLite we need, compiled to WASM and downloaded to the browser rather than being baked in and unable to upgrade. SQLite not right for a project? DuckDB has a WASM build too. Heck, so does PostgreSQL now! https://www.crunchydata.com/blog/crazy-idea-to-postgres-in-the-web-browser https://www.crunchydata.com/blog/crazy-idea-to-postgres-in-t...
  - yyyk 4y ago
    (Replying to this and other comments in unison) A SQL standard never precluded a separate filesystem access standard and implementing databases on top of it. But rejecting the Web SQL standard means that every SQL-using webapp will be tied to its own database - replicating the SQL incompatibility mess when we had a chance to finally enforce a little standardization based on current SQL standards - and require depending on WASM when WASM might have not been necessary.
helf 4y ago
Yay. More ways for people to force webapps upon us. I miss when the web was essentially a document viewer.
- tregoning 4y ago
  <meme>Why not both?</meme>
  - helf 4y ago
    Because they end up migrating more and more functionality to their web side? Deprecating desktop or mobile app functionality or entirely. So you end up with piss poor “web apps” that half the time require a specific browser to work remotely well and have kludged “offline” modes that break every other day. Do not want. I know I’m probably in the minority but it’s damned irritating. The UX on most is atrocious and you have to deal with a complete hodgepodge of interface styles and ideas and designs and also hope your browser keeps running smoothly after trying to use multiple more advanced webapps simultaneously. Oh, but all these advancements on wasm and all will continue improving performance and fixing problems!!! … you mean stuff that was already solved with native programs? “But you can work on your stuff from anywhere with just an account!” Oh, like a native app’s data synchronization? But but… It would be nice if we had both and both were equals. But that isn’t the case. It won’t be the case. And most companies see this as a wonderful way to vendor lock in even more than before and implement more and more IAP and subscription crap. No thanks.
    - endisneigh 4y ago
      How is that any different than desktop apps that require you to be online and only work with particular versions of particular operating systems?
      - sirjaz 4y ago
        When it is a desktop or even a mobile app I as the end user have a level of control over my environment that I don't with a webapp. In addition, my interface doesn't change at the whim of the company or developer.
mg 4y ago
Let's hope Firefox starts to support the File System Access API soon. For me, it is the last missing piece to make web apps as comfortable as native apps. I use a lot of web apps I fine-tuned exactly to my liking. I do all my writing in this web app for example: https://www.gibney.org/writer https://www.gibney.org/writer - I hit F11 and boom! I am in distraction free writing heaven :) If Firefox would support the File System Access API, it would be much more comfortable to load and save my writings.
- dmitriid 4y ago
  > Let's hope FireFox starts to support the File System Access API soon. File System Access considered harmful, will not be implemented: https://mozilla.github.io/standards-positions/ https://mozilla.github.io/standards-positions/ Note: in true Google fashion Chrome team implemented and released at least three different APIs all having something to do with files. Status of File System Access? "not a W3C Standard nor is it on the W3C Standards Track." https://wicg.github.io/file-system-access/ https://wicg.github.io/file-system-access/ Shipped in Chrome, of course.
  - tomayac 4y ago
    Mozilla opposes the picker methods of the File System Access API. They are fully behind the Origin Private File System part, and actually have the SQLite demo from the article running in Firefox Nightly.
  - geysersam 4y ago
    > but Mozilla could be supportive of parts, provided this were segmented better. They seem quite positive to parts of the spec. Don't know if that's the parts relevant here or not.
  - redwall_hp 4y ago
    Yep, Chrome is the new IE. Only instead of ActiveX and small proprietary HTML additions and EEE, it's a full coup de tat on web standards and Embrace, Extend, Control.
throwaway284534 4y ago
This is honestly very cool. VS Code uses a similar approach to their local file system provider, albeit with a wrapper around IndexDB instead of SQLite. There’s some interesting trade offs too, since IndexDB can store the browser’s native file handlers in a flat map — so there’s no need for a schema. IMO, the Chrome team is being a bit deceptive with their phrasing on synchronous file handles. The problem is that the entire API is wrapped up in an asynchronous ceremony. `createSyncAccessHandle` is only available in a worker context. So you can only communicate with the worker using an asynchronous postMesssage event dispatcher. And even when you’re in the worker, file handles can only be accessed through methods that return a promise. I understand the need for such boundaries when working with a single threaded language, but limiting the synchronous APIs to just workers seems like one too many layers of indirection. I recently attempted to write a POSIX-style BusyBox library and this sort of thing was a total show stopper.
- bobkazamakis 4y ago
  >limiting the synchronous APIs to just workers seems like one too many layers of indirection An unresponsive script is slowing this window down - kill process ot wait?
  - throwaway284534 4y ago
    The file handler is already tucked inside an asynchronous Promise based API. I think it’s reasonable that a mere attempt to get a handler synchronously is made possible. Whether to support synchronous read/write access is another matter. I would be delighted if handlers were more akin to byte arrays that could be written and read synchronously, albeit with an asynchronous function to persist changes to the disk.
mckravchyk 4y ago
I think this will be great for extensions. Currently the only solid choice is to use the dead simple storage.local which only allows to retrieve things by ID. There's one problem though, this new API is for the web, so the nature of this storage is temporary - obviously the user must be able to clear it when clearing site data and this what makes it currently an unviable solution for a persistent extensions data storage. https://bugs.chromium.org/p/chromium/issues/detail?id=1383216 https://bugs.chromium.org/p/chromium/issues/detail?id=138321...
- dfghjkjhg 4y ago
  yeah because full access to the filesystem is only solution. asking for permission to a longer storage.local exemption for the user? noooo let's not even think about giving user control of things. let's the European union pass post-fact legislation after file system access is abused by adnetworks. then they get the blame of being annoying, not the browser vendor who sell ads. let's stop being naive. there are a million ways to fix that bug with better UX and respect for user control, but this is a scape goat to have that feature.
- encryptluks2 4y ago
  Agreed, people want permission based file system access and so I don't see why a site can't prompt a user to store a file in a user-specified location. If they clear their site settings then giving permission to the same file should retain their data. I don't want to have to worry about losing something important or having my browser act as a file system.
  - mr_toad 4y ago
    > I don't see why a site can't prompt a user to store a file in a user-specified location. This is already possible with the file system access API. Clearing site data has no impact on these files. But you do have to re-grant permission every time you reload the page. https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System...
pfoof 4y ago
Cybersec is like "here we go again"
- deleted 4y ago
  [deleted]
tristanho 4y ago
Does anyone have a good idea of when OPFS will be broadly available in all major browsers? And what's best to use as a pseudo-polyfill in the mean time? Something relevant is absurd-sql, but unfortunately that's not even close to production ready :/ https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql
tarasglek 4y ago
I wanted to use it in a chrome extension worker, but sync version of OPFS isn't available in that context. Hope this gets fixed.
justinclift 4y ago
Interesting. Looks like the data persisted to disk should be available for opening via traditional SQLite GUI tools too (eg sqlitebrowser.org), though Chrome/Chromium would probably need to be not running at the same time for safety.
rasz 4y ago
we could call it WebSQL
msie 4y ago
I was so disappointed when sqlite/websql was ditched in favour of IndexedDB. What were they thinking? Too smart for their own good?
- fomine3 4y ago
  They avoid depending on specific DB implementation for standard.
Zuiii 4y ago
The custom header requirement will make this version of sqlite unusable for many usecases. Can SQLite fallback to using slower persistant APIs if neither Cross-Origin-Opener-Policy or Origin-Embedder-Policy are set? If not, does the SQLite project intend to provide a version that would? Devs who don't need the high performance but want wider compatibility will probably continue to use the "unofficial" versions. Also, last I checked, you could definitely load web assembly in a page that has a File:/// origin.

372 points by bubblehack3r 4y ago

[dead]

Fun fact: The headline image is the Stadtbibliothek Stuttgart, the nicest library I ever visited: https://www.archdaily.com/193568/stuttgart-city-library-yi-architects https://www.archdaily.com/193568/stuttgart-city-library-yi-a...

tomayac 4y ago
0711 love! Viele Grüße!

systems 4y ago

what is exactly the use case for this?

jchw 4y ago
Based on this note: > In our blog post Deprecating and removing Web SQL, we promised a replacement for Web SQL based on SQLite. The SQLite Wasm library with the Origin Private File System persistence backend is our fulfillment of this promise. I'd venture to guess the best answer is "whatever the hell people used Web SQL for." Doesn't really answer the question, but alas.
- deleted 4y ago
  [deleted]
samwillis 4y ago
It's an alternative to using IndexedDB which is a browser provided API. On use cases, you can give your web app offline support by locally caching data in an SQL database and have it be fully queryable. Say you are building an app like Notion, they already have "offline mode" for the mobile and desktop apps, this would enable you to build that for the web app. This is very much one of the final jigsaw pieces needed to make PWAs (progressive web apps) competitive for the majority of use cases. We just need Apple to catch up and fill in a few other blanks too. A design pattern that is beginning to emerge is "offline/local first". You design your app to fundamentally work offline, using things such as this, and the server component only works to synchronise clients. It's a bit like the design move to "mobile first" that happed 10 years ago, but going to another level.
- wargames 4y ago
  I haven't used IndexedDB in a while.. but my recollection is that it is basically SQLite. Not sure I see the advantage of this above and beyond what IndexedDB is already providing. What are the pros and cons of each approach?
  - actionfromafar 4y ago
    https://nolanlawson.com/2021/08/22/speeding-up-indexeddb-reads-and-writes/ https://nolanlawson.com/2021/08/22/speeding-up-indexeddb-rea... IndexedDB seems like a whole bag of gotchas. SQLite + simpler browser provided file backing seems interesting to me.
  - azangru 4y ago
    > but my recollection is that it is basically SQLite. SQLite, which has a SQL database engine, is, by definition, a relational database. IndexedDB is a non-relational, or noSQL, database. One can't be basically the same as the other :-)
  - AndrewSChapman 4y ago
    IndexDb is not a relational data store. It's much closer to Mongo than it it to Sqlite. If you want relational tables, joins, aggregations etc, you want something like this (or the original Web SQL that was deprecated). There is definitely a use case for it.
    - mr_toad 4y ago
      > It's much closer to Mongo than it it to Sqlite. If only. It’s much closer to DBM than it is to Mongo.
  - simonw 4y ago
    One is a key value store, the other is a full relational database. Think Redis vs PostgreSQL.
- phaedrus 4y ago
  So thick client applications get the last laugh in the end? It reminds me of an old concept, that has a catchy name I can't recall, positing an endless cycle in systems evolution where local peripherals grow processing capability until someone notices and (re)centralizes it, only for the local processing capability to quietly begin growing again, and so on.
deleted 4y ago
[deleted]
azangru 4y ago
SQL but in the web browser. Mobile apps have long been using sqlite for their needs. Now web apps can too.
simonw 4y ago
It lets you build static web applications that can manage relational data for your users that is stored on their own machines. This is a really powerful capability. You could build the equivalent of full desktop applications - things like Word, Excel, Access, Evernote, task trackers, Blender... all without any server side storage mechanism at all.
- cudgy 4y ago
  So basically a desktop app that runs in a desktop app on a desktop?
  - simonw 4y ago
    Yup - but crucially one that people can start using by clicking a link, without having to download and install anything first. And it's an app that runs in a very robust sandbox, so much less risky than installing software.
    - cudgy 4y ago
      True, but you have it backwards. Web app needs user to download and install a web browser first; desktop app does not need anything downloaded first. Both can be started by clicking a link.
      - simonw 4y ago
        I guess you mean downloading a browser version that supports the new origin filesystem feature? Every user has a web browser installed.
        cudgy 4y ago
        Quite simple. I do not need to download a web browser at all to run the desktop app. Deployment of the desktop app is simpler and provides the same or even more functionality without adding a complex desktop browser and sandbox container to run the app. Why is this so hard to get across?
dboreham 4y ago
You want to run some code in the browser that wants to use a persistent SQL database.
cloudking 4y ago
Perhaps this could be used to create more privacy focused apps where your data stays local and works offline. I'm not sure about the persistence though, like if you clear the cache does it wipe out the local database?
- ehutch79 4y ago
  Or if you switch devices? Or need to collaborate on the data (i.e. a company with empoyees) Or have regulations/controls on where data lives 'at rest'
0x_rs 4y ago
Plenty. Suppose you're working on a cross-platform application, building for web will get even easier, as up until a few months ago there weren't even official WASM builds from SQLite (but some amazing community run ones such as sql.js!). As for why somebody would want that, an example: your browser and everyone's is probably always open when you're at a PC, attrition is next to none compared to having to download an application, it's great for even just trying something out. SQLite really makes user data storage trivial in most circumstances, key:value doesn't.

samwillis 4y ago

This is a nice overview! Discussion from back when the SQLite project announced this last year: https://news.ycombinator.com/item?id=33374402 https://news.ycombinator.com/item?id=33374402 There isn't an "official" NPM package or ES6 model yet, but I believe they are working on it. It's also very much designed for the browser currently, but again I believe they are intending to support server WASM environments too eventually.

lovasoa 4y ago
Hey, I am the maintainer of sql.js. I was in contact with them, and I do not think they are working on a npm module, but I offered them to work on the existing "official" sql.js module.
- samwillis 4y ago
  Looking back at the thread, you are right. It's the ES Module that they do intend to eventually have.
  - coliveira 4y ago
    He knows he is right. It is you who need to thank him for pointing this out.
sgbeal 4y ago
> There isn't an "official" NPM package or ES6 model yet (The sqlite team's JS/WASM Guy here...) An ES6 module build was added shortly after the 3.40 release. NPM/node.js are nowhere on our radar. Our build is structured such that people who want to plug it in the resulting JS file to whatever their favorite toolchain is are welcomed to do so, but we have neither the ambition nor the bandwidth to support every build/bundling platform out there, especially ones none of us otherwise use. > It's also very much designed for the browser currently, but again I believe they are intending to support server WASM environments too eventually. The JS code is targeted solely at browsers and there are no plans on changing that in the foreseeable future. The WASM build itself, we are working to provide server-side support for. We have a branch which builds under wasi-sdk, but we cannot create a JS binding for that build until/unless we find some substitute for Emscripten's transparent translation of POSIX I/O APIs.

gfodor 4y ago

Ironically I was just about to drop in absurd-sql [1] to a project, which uses indexeddb to back SQLite. This seems better. [1] https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql

DylanSp 4y ago
This is definitely better, yeah. The blog post[1] on absurd-sql notes that it's a hacky solution that would be improved with file system access; it references the old Storage Foundation proposal [2], which has since evolved into the current File System Access API proposal(s). [1] https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web [2] https://developer.chrome.com/docs/web-platform/storage-foundation/ https://developer.chrome.com/docs/web-platform/storage-found...
Dork1234 4y ago
Will either SQLite WASM or absurd-sql work on Firefox and Safari?
tantaman 4y ago
If it helps, I detail some of the pros/cons of using the official SQLite build vs unofficial builds (wa-sqlite, which is similar to absured-sql) here: https://vlcn.io/docs/guide-persistence#persistence-options https://vlcn.io/docs/guide-persistence#persistence-options

est 4y ago

TIL OPFS. https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API#origin_private_file_system https://developer.mozilla.org/en-US/docs/Web/API/File_System... What's next, mmap() for javascript?

fabiospampinato 4y ago
That's available already, in Bun.
simula67 4y ago
Sockets, hopefully. For example, it should be possible to build websites for downloading YouTube videos that does not use server side code execution or bandwidth. These were popular at some point but disappeared after Java applets stopped being supported by most browsers.
- lxgr 4y ago
  Or just an XMPP web client.
- heywhatupboys 4y ago
  i dont understand how this is not already possible?
- jeroenhd 4y ago
  And make every website part of a botnet? I sure hope not. WebSockets are already a problem because many web devs don't know that other origins can connect to them and potentially fetch data they're not supposed to (even ignoring the fact you can construct these without a browser), unleashing raw sockets to the web platform would be hell. WebRTC and WebSockets provide more than enough already. If you need even more, your browser just isn't the place for this type of code IMO.
  - madacol 4y ago
    Instead of fighting against that feature, why not instead ask permission from the user?, just like the APIs of location and notifications do

DylanSp 4y ago

Does anyone have hands-on experience with how well different browsers support this? This blog post and caniuse both say Chrome support is currently somewhat limited (though being improved); caniuse also mentions that Safari (both desktop and iOS) only support the Origin Private File System part of the File System API, and that Firefox doesn't support it all. I'm curious how all that actually shakes out in practice.

Existenceblinks 4y ago
Yeah, so tired of excitement without thinking the full production process.
dboreham 4y ago
What happens is that one browser (usually Chrome) implements a useful new feature. Then applications are released that use that feature. Then the other browser vendors start hearing from their users that they suck because they don't support the nice new feature and hence can't run the nice new applications. And eventually they add support.
- dmitriid 4y ago
  > What happens is that one browser (usually Chrome) implements a useful new feature. What happens is Chrome releases a Chrome-only non-standard (at a rate of 400 APIs per year). It cobbles together a barely legible spec [1], and then uses its multiple propaganda channels like web.dev to present them as actual completed standards that other browsers are just slow to implement. Even if other browsers only learn about this a few days before Chrome ships, or have multiple objections. [1] Example, File System API https://wicg.github.io/file-system-access/ https://wicg.github.io/file-system-access/ "not a W3C Standard nor is it on the W3C Standards Track."
  - tomayac 4y ago
    Please see https://developer.chrome.com/blog/capabilities/#how-will-we-design-implement-these-new-capabilities https://developer.chrome.com/blog/capabilities/#how-will-we-... for the actual process. Nothing happens behind closed doors; other vendors can chime in at any time, and are actually very much encouraged to do so.

rektide 4y ago

I have yet to see whether implementations allow users direct access via the filesystem to these contents. The idea of each origin having some files sounds fine. But if I genuinely want to help & empower my users, give them the most user agency, I'd want them to be able to access the sqlite sb themselves directly, with whatever hazards they thereby assume.

samwillis 4y ago
From my reading previously, no the currently OPFS implementations do not expose the files as files in directories the user would access. The File System Access API does give the webpage the ability access files on the users file system, with permission. However not at the block level, that is only enabled for the OPFS. In order to provide ACID compliance SQLite needs to be able to hold a lock to a file and write at the block level, which is only possible within the OPFS sandbox.
- rektide 4y ago
  My use case is wanting a git implementation. I don't care about ACID, but I do care a lot about speed, and it absolutely is there 100% for the user's sake. So I feel shit-out-of-luck. Also, I'd rather use JS than wasm, and whatwg-fs/OPFS focused entirely on sync apis, to appease wasm folk who have cumbersome shims they didn't want to deal with. It's frustrating that the web community first build File System Access, but made a number of design choices compromising performance. Then AccessHandles/whatwg-fs, which unlocks performance, but severely limits it's usability to things users can't really interact with. And only release a sync implementation. I love the web platform, but this feels so unfortunate.

smallerfish 4y ago

Very nice. I hope Firefox end up supporting OPFS. My use case is an entirely offline webapp that I'm building as a side project, partly just to see what is possible. I've tried indexeddb, pouchdb, absurd-sql, and various others, and have ended up back at localstorage, which is plain but low on edge cases. The other big pain point I am hacking around is cross device sync; I'm leaning towards simple chrome and firefox extensions that'll slurp data into your profile in order to sync, which'll work for cross computer sync for my needs. However, chrome on android doesn't support extensions, and there's no other good way that I've found to sync between a user's chrome profile and android, so that is still a hole in the story. Some kind of webrtc based thing is imaginable, but it's clunky.

byhemechi 4y ago
i believe OPFS is supported on nightly? don’t quote me on this yeah
- galleywest200 4y ago
  From what I can see they are very much writing code for this. This request for move() in OPFS was closed four months ago. https://bugzilla.mozilla.org/show_bug.cgi?id=1789116 https://bugzilla.mozilla.org/show_bug.cgi?id=1789116

Existenceblinks 4y ago

The sqlite.wasm is 683kB and the sqlite.js is 300kB. So is it supposed to be used in PWA context where these files are cached? Why no one is talking about CAP here. Is it real-production-ready?

simonw 4y ago
What do you mean by CAP? 683KB plus 300KB is less than many web pages these days.
- Existenceblinks 4y ago
  The theorem. Where is the source of truth. Does every offline app now require CRDT or OT? Hm.. 1 MB is good (just zero app code) ..okay.
  - dboreham 4y ago
    It's just a database (not distributed, not replicated), that you can use from your browser-hosted application. What you do with it is up to you.
- chmod775 4y ago
  Many web pages these days also have embarrassingly subpar engineering. Just because everything is already shit doesn't mean it's a good idea to pile more on top.

typingmonkey 4y ago

Using SQLite in the browser is great until you have users that open your app in more then one browser tab.

samwillis 4y ago
See here: https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking It's mostly works with OPFS but with a few edge cases. They are working with the browser vendors on improving this in OPFS. Alternatively run it in a SharedWorker (https://developer.mozilla.org/en-US/docs/Web/API/SharedWorker https://developer.mozilla.org/en-US/docs/Web/API/SharedWorke...), which seems to have made a comeback after being dropped for security concerns. Or do some sort of leader election with browser tabs and use a BroadcastChannel.
- tantaman 4y ago
  SQLite WASM uses SharedArrayBuffers and Atomics to turn async OPFS apis into blocking APIs (really just _one_ api -- getSyncAccessHandle) Given the use of SharedArrayBuffers and that SharedArrayBuffers can't be used in a shared worker (funny), SQLite WASM doesn't work in shared workers. If getSyncAccessHandle was made synchronous then maybe everything would just work. It'd also improve SQLite WASM perf by 30% according to their measurements [1] [1] https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e3713df2eb7963c7f08d954738a5f456a5d8a4f1 https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e...
tantaman 4y ago
What's wrong with many tabs? The DB handles getting hit by many tabs correctly -- it's just as if many processes were hitting the db in a non-browser environment. Example of syncing your app across tabs with SQLite here: https://vlcn.io/docs/guide-solving-tabs https://vlcn.io/docs/guide-solving-tabs
omniglottal 4y ago
If your app cannot handle concurrency while your back-end i/o is via a machine-local singleton, that sounds like a a problem which will come up for more than just multiple tabs. If architecting around this known-fact is a challenge, an engineer may have bigger problems.

yyyk 4y ago

The Web SQL standard was deprecated (by Mozilla) on the grounds of a single implementation. The result today: A single SQL implementation with no standard (beyond SQLite) and therefore no easy way for anyone to create a compatible alternate implementation.

roblabla 4y ago
Or an alternative read of the result today: A simple implementation of filesystem access, on top of which any number of database (SQL or not) can be implemented, thus allowing for more innovation in the database space.
ngrilly 4y ago
Nothing is preventing you to "create a compatible alternate implementation".
CJefferson 4y ago
However, now every webbrowser doesn't have to keep it correct, secure and up to date for the next 40 years.
- lxgr 4y ago
  Yes, now every single site using SQLite has to do that.
simonw 4y ago
Thanks to Mozilla rejecting Web SQL we now get to run the exact version of SQLite we need, compiled to WASM and downloaded to the browser rather than being baked in and unable to upgrade. SQLite not right for a project? DuckDB has a WASM build too. Heck, so does PostgreSQL now! https://www.crunchydata.com/blog/crazy-idea-to-postgres-in-the-web-browser https://www.crunchydata.com/blog/crazy-idea-to-postgres-in-t...
- yyyk 4y ago
  (Replying to this and other comments in unison) A SQL standard never precluded a separate filesystem access standard and implementing databases on top of it. But rejecting the Web SQL standard means that every SQL-using webapp will be tied to its own database - replicating the SQL incompatibility mess when we had a chance to finally enforce a little standardization based on current SQL standards - and require depending on WASM when WASM might have not been necessary.

helf 4y ago

Yay. More ways for people to force webapps upon us. I miss when the web was essentially a document viewer.

tregoning 4y ago
<meme>Why not both?</meme>
- helf 4y ago
  Because they end up migrating more and more functionality to their web side? Deprecating desktop or mobile app functionality or entirely. So you end up with piss poor “web apps” that half the time require a specific browser to work remotely well and have kludged “offline” modes that break every other day. Do not want. I know I’m probably in the minority but it’s damned irritating. The UX on most is atrocious and you have to deal with a complete hodgepodge of interface styles and ideas and designs and also hope your browser keeps running smoothly after trying to use multiple more advanced webapps simultaneously. Oh, but all these advancements on wasm and all will continue improving performance and fixing problems!!! … you mean stuff that was already solved with native programs? “But you can work on your stuff from anywhere with just an account!” Oh, like a native app’s data synchronization? But but… It would be nice if we had both and both were equals. But that isn’t the case. It won’t be the case. And most companies see this as a wonderful way to vendor lock in even more than before and implement more and more IAP and subscription crap. No thanks.
  - endisneigh 4y ago
    How is that any different than desktop apps that require you to be online and only work with particular versions of particular operating systems?
    - sirjaz 4y ago
      When it is a desktop or even a mobile app I as the end user have a level of control over my environment that I don't with a webapp. In addition, my interface doesn't change at the whim of the company or developer.

mg 4y ago

Let's hope Firefox starts to support the File System Access API soon. For me, it is the last missing piece to make web apps as comfortable as native apps. I use a lot of web apps I fine-tuned exactly to my liking. I do all my writing in this web app for example: https://www.gibney.org/writer https://www.gibney.org/writer - I hit F11 and boom! I am in distraction free writing heaven :) If Firefox would support the File System Access API, it would be much more comfortable to load and save my writings.

dmitriid 4y ago
> Let's hope FireFox starts to support the File System Access API soon. File System Access considered harmful, will not be implemented: https://mozilla.github.io/standards-positions/ https://mozilla.github.io/standards-positions/ Note: in true Google fashion Chrome team implemented and released at least three different APIs all having something to do with files. Status of File System Access? "not a W3C Standard nor is it on the W3C Standards Track." https://wicg.github.io/file-system-access/ https://wicg.github.io/file-system-access/ Shipped in Chrome, of course.
- tomayac 4y ago
  Mozilla opposes the picker methods of the File System Access API. They are fully behind the Origin Private File System part, and actually have the SQLite demo from the article running in Firefox Nightly.
- geysersam 4y ago
  > but Mozilla could be supportive of parts, provided this were segmented better. They seem quite positive to parts of the spec. Don't know if that's the parts relevant here or not.
- redwall_hp 4y ago
  Yep, Chrome is the new IE. Only instead of ActiveX and small proprietary HTML additions and EEE, it's a full coup de tat on web standards and Embrace, Extend, Control.

throwaway284534 4y ago

This is honestly very cool. VS Code uses a similar approach to their local file system provider, albeit with a wrapper around IndexDB instead of SQLite. There’s some interesting trade offs too, since IndexDB can store the browser’s native file handlers in a flat map — so there’s no need for a schema. IMO, the Chrome team is being a bit deceptive with their phrasing on synchronous file handles. The problem is that the entire API is wrapped up in an asynchronous ceremony. `createSyncAccessHandle` is only available in a worker context. So you can only communicate with the worker using an asynchronous postMesssage event dispatcher. And even when you’re in the worker, file handles can only be accessed through methods that return a promise. I understand the need for such boundaries when working with a single threaded language, but limiting the synchronous APIs to just workers seems like one too many layers of indirection. I recently attempted to write a POSIX-style BusyBox library and this sort of thing was a total show stopper.

bobkazamakis 4y ago
>limiting the synchronous APIs to just workers seems like one too many layers of indirection An unresponsive script is slowing this window down - kill process ot wait?
- throwaway284534 4y ago
  The file handler is already tucked inside an asynchronous Promise based API. I think it’s reasonable that a mere attempt to get a handler synchronously is made possible. Whether to support synchronous read/write access is another matter. I would be delighted if handlers were more akin to byte arrays that could be written and read synchronously, albeit with an asynchronous function to persist changes to the disk.

mckravchyk 4y ago

I think this will be great for extensions. Currently the only solid choice is to use the dead simple storage.local which only allows to retrieve things by ID. There's one problem though, this new API is for the web, so the nature of this storage is temporary - obviously the user must be able to clear it when clearing site data and this what makes it currently an unviable solution for a persistent extensions data storage. https://bugs.chromium.org/p/chromium/issues/detail?id=1383216 https://bugs.chromium.org/p/chromium/issues/detail?id=138321...

dfghjkjhg 4y ago
yeah because full access to the filesystem is only solution. asking for permission to a longer storage.local exemption for the user? noooo let's not even think about giving user control of things. let's the European union pass post-fact legislation after file system access is abused by adnetworks. then they get the blame of being annoying, not the browser vendor who sell ads. let's stop being naive. there are a million ways to fix that bug with better UX and respect for user control, but this is a scape goat to have that feature.
encryptluks2 4y ago
Agreed, people want permission based file system access and so I don't see why a site can't prompt a user to store a file in a user-specified location. If they clear their site settings then giving permission to the same file should retain their data. I don't want to have to worry about losing something important or having my browser act as a file system.
- mr_toad 4y ago
  > I don't see why a site can't prompt a user to store a file in a user-specified location. This is already possible with the file system access API. Clearing site data has no impact on these files. But you do have to re-grant permission every time you reload the page. https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System...

pfoof 4y ago

Cybersec is like "here we go again"

deleted 4y ago
[deleted]

tristanho 4y ago

Does anyone have a good idea of when OPFS will be broadly available in all major browsers? And what's best to use as a pseudo-polyfill in the mean time? Something relevant is absurd-sql, but unfortunately that's not even close to production ready :/ https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql

tarasglek 4y ago

I wanted to use it in a chrome extension worker, but sync version of OPFS isn't available in that context. Hope this gets fixed.

justinclift 4y ago

Interesting. Looks like the data persisted to disk should be available for opening via traditional SQLite GUI tools too (eg sqlitebrowser.org), though Chrome/Chromium would probably need to be not running at the same time for safety.

rasz 4y ago

we could call it WebSQL

msie 4y ago

I was so disappointed when sqlite/websql was ditched in favour of IndexedDB. What were they thinking? Too smart for their own good?

fomine3 4y ago
They avoid depending on specific DB implementation for standard.

Zuiii 4y ago

The custom header requirement will make this version of sqlite unusable for many usecases. Can SQLite fallback to using slower persistant APIs if neither Cross-Origin-Opener-Policy or Origin-Embedder-Policy are set? If not, does the SQLite project intend to provide a version that would? Devs who don't need the high performance but want wider compatibility will probably continue to use the "unofficial" versions. Also, last I checked, you could definitely load web assembly in a page that has a File:/// origin.