4 ms·

The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md ht

The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md https://sqlite.org/wasm/doc/trunk/index.md

NeutralForest 4y ago
I don't understand the use case? From the docs [https://sqlite.org/wasm/doc/trunk/demo-123.md https://sqlite.org/wasm/doc/trunk/demo-123.md], the database disappears on page reload on top of requiring using workers for longer running processes.
- cldellow 4y ago
  You can still persist them; you just have to wire it up yourself. Using workers is a best practice for any CPU-bound task, so that's not a drawback by itself in my mind. It's good for single-page applications. Many datasets are relatively small -- pushing them to the client is reasonable. In exchange, you get zero-latency querying and can build very responsive UIs that can use SQL, versus writing REST APIs or GraphQL APIs. Taken to an extreme, it permits publishing datasets that can be queried with no ongoing server-side expenses. A wild example: Datasette is a Python service that makes SQLite databases queryable via the web. It turns out that since you can compile Python and SQLite to WASM, you can run Datasette entirely in the user's browser [1]. The startup time is brutal, because it's literally simulating the `pip install`, but a purpose-built SPA wouldn't have this problem. [1]: https://lite.datasette.io/?url=https%3A%2F%2Fcongress-legislators.datasettes.com%2Flegislators.db https://lite.datasette.io/?url=https%3A%2F%2Fcongress-legisl...
  - NeutralForest 4y ago
    Thanks for the explanation, the author of Datasette himself answered, which is a pretty cool thing I like about HN =)
- simonw 4y ago
  Even without persistence it's absurdly useful. In 2023 loading even a 50MB+ database file into a browser is feasible (that's only 10 heavy React webpage loads) and now you can run SQL queries directly against it in the browser. Plenty of interesting databases fit into less than a MB even. I've been using SQLite in WebAssembly for my Datasette Lite project - a Python server-side web app running entirely in the browser: https://simonwillison.net/2022/May/4/datasette-lite/ https://simonwillison.net/2022/May/4/datasette-lite/ - here's an article showing how that can be useful: https://simonwillison.net/2022/Aug/21/scotrail/ https://simonwillison.net/2022/Aug/21/scotrail/ It's also available in Observable notebooks, which is really handy. Here's a project I built on top of that: https://simonwillison.net/2022/Nov/20/tracking-mastodon/ https://simonwillison.net/2022/Nov/20/tracking-mastodon/ - notebook here: https://observablehq.com/@simonw/mastodon-users-and-statuses-over-time https://observablehq.com/@simonw/mastodon-users-and-statuses...
  - NeutralForest 4y ago
    Oh wow, thanks for all the links!
  - mbo 4y ago
    Additionally, SQLite in WebAssembly with a HTTP-Range-request based virtual file system greatly simplifies interactive visualisation of large datasets https://observablehq.com/@mjbo/sqljs-httpvfs https://observablehq.com/@mjbo/sqljs-httpvfs (my own demo)
    - simonw 4y ago
      That's a really cool demo notebook.
- maccaw 4y ago
  Under persistant storage options: https://sqlite.org/wasm/doc/trunk/persistence.md https://sqlite.org/wasm/doc/trunk/persistence.md
- samwillis 4y ago
  The intention is to make use of the new "Origin privet file system" api, this provides the website with a sandboxed block level file system on the users device that can be used for efficient access and writes. It will be possible for WASM SQLite, or any other DB engine ported to WASM, to have full ACID compliance. The SQLite team have been working with browser developers to ensure the new API is sufficient to enable all this. Honestly, and I keep going on about it, SQLite in the browser via WASM is the missing piece to make "offline first" PWAs a serious contender when deciding an architecture for an app. 2023 is going to be the year of SQLite in the browser. https://webkit.org/blog/12257/the-file-system-access-api-with-origin-private-file-system/ https://webkit.org/blog/12257/the-file-system-access-api-wit... https://sqlite.org/wasm/doc/trunk/persistence.md#opfs https://sqlite.org/wasm/doc/trunk/persistence.md#opfs https://chromestatus.com/feature/5702777582911488 https://chromestatus.com/feature/5702777582911488
  - NeutralForest 4y ago
    Very interesting, seems like I have a lot of reading to do, thanks!
  - ec109685 4y ago
    Unfortunately unlike apps, the system doesn’t support backing up local storage managed by the browser, so if you get a new phone, you lose your data.
    - yencabulator 4y ago
      I think the idea is to still sync to a server, but support offline (and low latency!) use too. It's "offline first", not "offline only".
justsomeuser 4y ago
It would be nice if OPFS (Origin Private File System) allowed reading/writing to an actual SQLite file on the users disk. At the moment, as I understand it, the OPFS virtual disk is completely isolated from the users disk. This means you cannot just lightly query a 1GB file without first copying the 1GB from the users filesystem to the OPFS. Any writes mean you must then copy the 1GB SQLite db file from OPFS to the users local filesystem too. Is this correct?
- johnny22 4y ago
  well it makes sense that the browser can't work outside of the opfs boundary, but shouldn't there be a way to talk to the opfs from outside the browser?
- streptomycin 4y ago
  The original plan from Google for the file system access API was to allow read/write of real files, but both Mozilla and Apple said that was too dangerous, and the OPFS was created as a compromise. Yeah, it's going to be confusing for users when they want to actually want to use one of these files outside of the application that created it. But it's better than nothing!
  - justsomeuser 4y ago
    This does seem strange, as you can read and write whole files to/from the user's file system once they give your app permission. Adding those API's to read/write parts of the file is the next logical step. But it looks like it is limited to the isolated OPFS only.

The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md https://sqlite.org/wasm/doc/trunk/index.md

NeutralForest 4y ago

I don't understand the use case? From the docs [https://sqlite.org/wasm/doc/trunk/demo-123.md https://sqlite.org/wasm/doc/trunk/demo-123.md], the database disappears on page reload on top of requiring using workers for longer running processes.

cldellow 4y ago
You can still persist them; you just have to wire it up yourself. Using workers is a best practice for any CPU-bound task, so that's not a drawback by itself in my mind. It's good for single-page applications. Many datasets are relatively small -- pushing them to the client is reasonable. In exchange, you get zero-latency querying and can build very responsive UIs that can use SQL, versus writing REST APIs or GraphQL APIs. Taken to an extreme, it permits publishing datasets that can be queried with no ongoing server-side expenses. A wild example: Datasette is a Python service that makes SQLite databases queryable via the web. It turns out that since you can compile Python and SQLite to WASM, you can run Datasette entirely in the user's browser [1]. The startup time is brutal, because it's literally simulating the `pip install`, but a purpose-built SPA wouldn't have this problem. [1]: https://lite.datasette.io/?url=https%3A%2F%2Fcongress-legislators.datasettes.com%2Flegislators.db https://lite.datasette.io/?url=https%3A%2F%2Fcongress-legisl...
- NeutralForest 4y ago
  Thanks for the explanation, the author of Datasette himself answered, which is a pretty cool thing I like about HN =)
simonw 4y ago
Even without persistence it's absurdly useful. In 2023 loading even a 50MB+ database file into a browser is feasible (that's only 10 heavy React webpage loads) and now you can run SQL queries directly against it in the browser. Plenty of interesting databases fit into less than a MB even. I've been using SQLite in WebAssembly for my Datasette Lite project - a Python server-side web app running entirely in the browser: https://simonwillison.net/2022/May/4/datasette-lite/ https://simonwillison.net/2022/May/4/datasette-lite/ - here's an article showing how that can be useful: https://simonwillison.net/2022/Aug/21/scotrail/ https://simonwillison.net/2022/Aug/21/scotrail/ It's also available in Observable notebooks, which is really handy. Here's a project I built on top of that: https://simonwillison.net/2022/Nov/20/tracking-mastodon/ https://simonwillison.net/2022/Nov/20/tracking-mastodon/ - notebook here: https://observablehq.com/@simonw/mastodon-users-and-statuses-over-time https://observablehq.com/@simonw/mastodon-users-and-statuses...
- NeutralForest 4y ago
  Oh wow, thanks for all the links!
- mbo 4y ago
  Additionally, SQLite in WebAssembly with a HTTP-Range-request based virtual file system greatly simplifies interactive visualisation of large datasets https://observablehq.com/@mjbo/sqljs-httpvfs https://observablehq.com/@mjbo/sqljs-httpvfs (my own demo)
  - simonw 4y ago
    That's a really cool demo notebook.
maccaw 4y ago
Under persistant storage options: https://sqlite.org/wasm/doc/trunk/persistence.md https://sqlite.org/wasm/doc/trunk/persistence.md
samwillis 4y ago
The intention is to make use of the new "Origin privet file system" api, this provides the website with a sandboxed block level file system on the users device that can be used for efficient access and writes. It will be possible for WASM SQLite, or any other DB engine ported to WASM, to have full ACID compliance. The SQLite team have been working with browser developers to ensure the new API is sufficient to enable all this. Honestly, and I keep going on about it, SQLite in the browser via WASM is the missing piece to make "offline first" PWAs a serious contender when deciding an architecture for an app. 2023 is going to be the year of SQLite in the browser. https://webkit.org/blog/12257/the-file-system-access-api-with-origin-private-file-system/ https://webkit.org/blog/12257/the-file-system-access-api-wit... https://sqlite.org/wasm/doc/trunk/persistence.md#opfs https://sqlite.org/wasm/doc/trunk/persistence.md#opfs https://chromestatus.com/feature/5702777582911488 https://chromestatus.com/feature/5702777582911488
- NeutralForest 4y ago
  Very interesting, seems like I have a lot of reading to do, thanks!
- ec109685 4y ago
  Unfortunately unlike apps, the system doesn’t support backing up local storage managed by the browser, so if you get a new phone, you lose your data.
  - yencabulator 4y ago
    I think the idea is to still sync to a server, but support offline (and low latency!) use too. It's "offline first", not "offline only".

justsomeuser 4y ago

It would be nice if OPFS (Origin Private File System) allowed reading/writing to an actual SQLite file on the users disk. At the moment, as I understand it, the OPFS virtual disk is completely isolated from the users disk. This means you cannot just lightly query a 1GB file without first copying the 1GB from the users filesystem to the OPFS. Any writes mean you must then copy the 1GB SQLite db file from OPFS to the users local filesystem too. Is this correct?

johnny22 4y ago
well it makes sense that the browser can't work outside of the opfs boundary, but shouldn't there be a way to talk to the opfs from outside the browser?
streptomycin 4y ago
The original plan from Google for the file system access API was to allow read/write of real files, but both Mozilla and Apple said that was too dangerous, and the OPFS was created as a compromise. Yeah, it's going to be confusing for users when they want to actually want to use one of these files outside of the application that created it. But it's better than nothing!
- justsomeuser 4y ago
  This does seem strange, as you can read and write whole files to/from the user's file system once they give your app permission. Adding those API's to read/write parts of the file is the next logical step. But it looks like it is limited to the isolated OPFS only.