3 ms·

This awesome, I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs. Assuming this is normal WASM SQLite any persistence will be by

This awesome, I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs. Assuming this is normal WASM SQLite any persistence will be by flushing to LocalStorage/IndexedDB. There won't be any ACID compliance. There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well! https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql I think combining WASM SQLite with the session extension (https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html) would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes.

sgbeal 4y ago
> There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well! (The fiddle dev here...) That is, AFAIK, the current state of the art for persisting sqlite wasm-side dbs. The JS-side APIs for providing such support are currently evolving within the browsers. With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years. (Those things take time to develop and, just as importantly, propagate through the browser ecosystem.) > I think combining WASM SQLite with the session extension ... would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes. FWIW, that's what i personally envision will likely be the most compelling use case for the sqlite wasm builds. The fiddle app is _just_ the sqlite3 shell, not the sqlite3 library, but we've also created a plain-jane wasm binding of the C library and written an OO-style API on top of that (roughly akin to sql.js and friends, with different design decisions in some places). We're working on an API conceptually similar to sql.js's "worker" API, where the sqlite3 wasm can run in a WebWorker and the main thread can talk to it via WebWorker messages. That said, for the type of jobs sqlite will likely be doing in web clients, the workloads will be fast enough that running the wasm build in the main thread is unlikely to lead to any usability issues (blocked UI). Nobody's going to be using a 20gb db in such an app (because the browser won't let them) and sqlite is blazing fast for small data sets. Even so, the core APIs are agnostic of whether they're running in the UI thread or a WebWorker, so the client can decide for themselves how to plug it in.
- samwillis 4y ago
  > We're working on an API conceptually similar to sql.js's "worker" API Thats interesting, so it will be an "official" WASM JS binding for SQLite? Is it available anywhere? > With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence?
  - sgbeal 4y ago
    > Thats interesting, so it will be an "official" WASM JS binding for SQLite? That's not guaranteed/official yet but it has been discussed and seems to be where we're headed. In the end it's Richard's decision, though, not mine. (That said, i would like to see that happen but won't be soul-crushed if it doesn't because creating a wasm build of sqlite is _so easy_ to do that any project can create their own with little effort.) > Is it available anywhere? Source repo: https://sqlite.org/src https://sqlite.org/src It's currently in the sqlite3 trunk. After configuring the tree, run "make fiddle" or (cd ext/fiddle; make), though the latter approach requires GNU make, not some under-powered make-wannabe. (The top-level makefile is compatible with Lesser Makes.) Edit: it requires emscripten and ext/fiddle/index.md provides an overview of how to get it going. > If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence? Not until we have JS APIs which are built for that type of thing. Absurd's approach is an interesting proof of concept but is aptly named. Absurd is a stopgap measure which will, with any luck, "soon" be obsoleted by ... how to put this... "less absurd" alternatives ;).
mrtesthah 4y ago
Is this some sort of competition to see how many redundant layered of abstraction we can add before the user starts to wonder why the responsiveness of the app has dropped to 1987 levels?
- samwillis 4y ago
  The strange thing is that AbsurdSQL appears to be 10x more performant than IndexedDB: https://github.com/jlongster/absurd-sql#performance https://github.com/jlongster/absurd-sql#performance (from memory the was some discussion that it wasn't a completely fair comparison, but even so was still more performant)
- robocat 4y ago
  Ironically, latency was far better in 1987 for plenty of users, even though you are implying it was worse. https://danluu.com/input-lag/ https://danluu.com/input-lag/ I would like to see latency figures for the dominant spreadsheet on the average computer over the years though, starting with VisiCalc on Apple ][.
- loxias 4y ago
  > responsiveness of the app has dropped to 1987 levels? Donno about 1987, but everything I do other than locally develop code with emacs has a responsiveness far worse than I remember from 1997. Everything has to load some stupid javascript, everything has a delayed reaction to each click or scroll. Nothing just runs or just works anymore. The cost of a lower barrier to entry for "software" development. :/
  - edgyquant 4y ago
    You and I remember ‘97 quite differently. It took 5-10 minutes to download .txt guides to video games (in 99 I downloaded ocarina of times walkthrough and that thing was a monster!) Hell I remember waiting almost an hour to download the Spider-Man 2 trailer in 2002. Meanwhile now I’m watching 1080p YouTube videos while running my entire dev environment locally (database, front and backend + code editor) and still it takes a second at the most to load 99% of pages.
alephnan 4y ago
> I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs. In what use cases would it make sense to transfer the entire db file over the network, then pluck out the relevant rows into JSON, versus just doing that server side and transfer the JSON back? Is the trade off that the server does less computation, but then pay for network bandwidth fees?
- edgyquant 4y ago
  Not all applications need a centralized DB and even those that do likely have client only data.
- sgbeal 4y ago
  > In what use cases would it make sense to transfer the entire db file over the network, then pluck out the relevant rows into JSON, versus just doing that server side and transfer the JSON back? Lots of dbs are small - a few hundred kb, maybe a meg. Many sites, by comparison, serve _megabytes_ of CSS and/or JS (minified!). i just hit imdb dot com for example's sake: 7MB of Stuff to load the front page, with something like 1MB of it being JS code. In addition, this type of app permits the user to inject a copy of a client-local db with no network traffic. In developing fiddle we've seen it work with dbs of approximately 80mb (haven't tested anything larger), but those limits are going to be very browser-/environment-dependent. > Is the trade off that the server does less computation, but then pay for network bandwidth fees? That's one tradeoff. Another is less coding effort needed to implement the client/server halves of the interaction. In conventional apps, every client-side operation on data results in a call to a server-side counterpart of that client-side operation. That needn't be the case for this style of app. For some apps, all of the code can now be client-side. For some, data might be synced/pushed to the server after changes are made, but there need not be a continual back-and-forth between the client and server. Another point is data privacy: this sort of app need not send the db to anyone. The db can stay entirely on the client, so they can use private databases without any concern that the server might squirrel away a copy or leak a copy to other folks. That said: the wasm approach, with a client-embedded db, is most definitely not a one-size-fits-all solution. It will serve a certain niche of applications, but not the majority of web app data needs.
- 4y ago
rasz 4y ago
> I'm convinced WASM SQLite is the future of in-browser strorage Now imagine if we shipped native sqlite in all browsers, and provided native API for it, we could call it websql or something. Im sure the biggest proponents of open source world wide web would be all for it!

sgbeal 4y ago

> There is a project called "Absurd SQL" to back SQLite with a custom block based FS on top of IndexedDB. It is somewhat absurd but it works incredibly well! (The fiddle dev here...) That is, AFAIK, the current state of the art for persisting sqlite wasm-side dbs. The JS-side APIs for providing such support are currently evolving within the browsers. With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years. (Those things take time to develop and, just as importantly, propagate through the browser ecosystem.) > I think combining WASM SQLite with the session extension ... would be a super interesting way to build an eventually consistent datastore for web apps. You could do all transactions locally and sync back and fourth just the changes. FWIW, that's what i personally envision will likely be the most compelling use case for the sqlite wasm builds. The fiddle app is _just_ the sqlite3 shell, not the sqlite3 library, but we've also created a plain-jane wasm binding of the C library and written an OO-style API on top of that (roughly akin to sql.js and friends, with different design decisions in some places). We're working on an API conceptually similar to sql.js's "worker" API, where the sqlite3 wasm can run in a WebWorker and the main thread can talk to it via WebWorker messages. That said, for the type of jobs sqlite will likely be doing in web clients, the workloads will be fast enough that running the wasm build in the main thread is unlikely to lead to any usability issues (blocked UI). Nobody's going to be using a 20gb db in such an app (because the browser won't let them) and sqlite is blazing fast for small data sets. Even so, the core APIs are agnostic of whether they're running in the UI thread or a WebWorker, so the client can decide for themselves how to plug it in.

samwillis 4y ago
> We're working on an API conceptually similar to sql.js's "worker" API Thats interesting, so it will be an "official" WASM JS binding for SQLite? Is it available anywhere? > With any luck, we'll have widespread JS-native persistent storage for sqlite within the next couple of/few years If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence?
- sgbeal 4y ago
  > Thats interesting, so it will be an "official" WASM JS binding for SQLite? That's not guaranteed/official yet but it has been discussed and seems to be where we're headed. In the end it's Richard's decision, though, not mine. (That said, i would like to see that happen but won't be soul-crushed if it doesn't because creating a wasm build of sqlite is _so easy_ to do that any project can create their own with little effort.) > Is it available anywhere? Source repo: https://sqlite.org/src https://sqlite.org/src It's currently in the sqlite3 trunk. After configuring the tree, run "make fiddle" or (cd ext/fiddle; make), though the latter approach requires GNU make, not some under-powered make-wannabe. (The top-level makefile is compatible with Lesser Makes.) Edit: it requires emscripten and ext/fiddle/index.md provides an overview of how to get it going. > If you are building an "official" WASM JS binding have you considered doing something similar to absurd SQL in order to achieve atomic persistence? Not until we have JS APIs which are built for that type of thing. Absurd's approach is an interesting proof of concept but is aptly named. Absurd is a stopgap measure which will, with any luck, "soon" be obsoleted by ... how to put this... "less absurd" alternatives ;).

mrtesthah 4y ago

Is this some sort of competition to see how many redundant layered of abstraction we can add before the user starts to wonder why the responsiveness of the app has dropped to 1987 levels?

samwillis 4y ago
The strange thing is that AbsurdSQL appears to be 10x more performant than IndexedDB: https://github.com/jlongster/absurd-sql#performance https://github.com/jlongster/absurd-sql#performance (from memory the was some discussion that it wasn't a completely fair comparison, but even so was still more performant)
robocat 4y ago
Ironically, latency was far better in 1987 for plenty of users, even though you are implying it was worse. https://danluu.com/input-lag/ https://danluu.com/input-lag/ I would like to see latency figures for the dominant spreadsheet on the average computer over the years though, starting with VisiCalc on Apple ][.
loxias 4y ago
> responsiveness of the app has dropped to 1987 levels? Donno about 1987, but everything I do other than locally develop code with emacs has a responsiveness far worse than I remember from 1997. Everything has to load some stupid javascript, everything has a delayed reaction to each click or scroll. Nothing just runs or just works anymore. The cost of a lower barrier to entry for "software" development. :/
- edgyquant 4y ago
  You and I remember ‘97 quite differently. It took 5-10 minutes to download .txt guides to video games (in 99 I downloaded ocarina of times walkthrough and that thing was a monster!) Hell I remember waiting almost an hour to download the Spider-Man 2 trailer in 2002. Meanwhile now I’m watching 1080p YouTube videos while running my entire dev environment locally (database, front and backend + code editor) and still it takes a second at the most to load 99% of pages.

alephnan 4y ago

> I'm convinced WASM SQLite is the future of in-browser strorage for web apps/PWAs. In what use cases would it make sense to transfer the entire db file over the network, then pluck out the relevant rows into JSON, versus just doing that server side and transfer the JSON back? Is the trade off that the server does less computation, but then pay for network bandwidth fees?

edgyquant 4y ago
Not all applications need a centralized DB and even those that do likely have client only data.
sgbeal 4y ago
> In what use cases would it make sense to transfer the entire db file over the network, then pluck out the relevant rows into JSON, versus just doing that server side and transfer the JSON back? Lots of dbs are small - a few hundred kb, maybe a meg. Many sites, by comparison, serve _megabytes_ of CSS and/or JS (minified!). i just hit imdb dot com for example's sake: 7MB of Stuff to load the front page, with something like 1MB of it being JS code. In addition, this type of app permits the user to inject a copy of a client-local db with no network traffic. In developing fiddle we've seen it work with dbs of approximately 80mb (haven't tested anything larger), but those limits are going to be very browser-/environment-dependent. > Is the trade off that the server does less computation, but then pay for network bandwidth fees? That's one tradeoff. Another is less coding effort needed to implement the client/server halves of the interaction. In conventional apps, every client-side operation on data results in a call to a server-side counterpart of that client-side operation. That needn't be the case for this style of app. For some apps, all of the code can now be client-side. For some, data might be synced/pushed to the server after changes are made, but there need not be a continual back-and-forth between the client and server. Another point is data privacy: this sort of app need not send the db to anyone. The db can stay entirely on the client, so they can use private databases without any concern that the server might squirrel away a copy or leak a copy to other folks. That said: the wasm approach, with a client-embedded db, is most definitely not a one-size-fits-all solution. It will serve a certain niche of applications, but not the majority of web app data needs.
4y ago

rasz 4y ago

> I'm convinced WASM SQLite is the future of in-browser strorage Now imagine if we shipped native sqlite in all browsers, and provided native API for it, we could call it websql or something. Im sure the biggest proponents of open source world wide web would be all for it!