6 ms·

SQLite WASM: Something subtle in the browser

90 points by zainab-ali 4y ago

zainab-ali 4y ago
For those of you interested in browser-based search with SQLite WASM, I’ve written a small post on the Craft of Emacs search page.You can check out the actual search here: https://craft-of-emacs.kebab-ca.se/search.html https://craft-of-emacs.kebab-ca.se/search.html Kudos to the SQLite team. It was a joy to implement.
- tambourine_man 4y ago
  It was a joy to read this article. I'd love to know more on the nitty-gritty details of the implementation.
- Jwarder 4y ago
  Are there any gotchas around the sql parameterization? Looks like you're passing in an array into db.exec. I would have thought that would be enough, but if I search for org-mode then I see an error on the console complaining about no such column: mode.
  - philsnow 4y ago
    adding some quotes seems to help; you can search for e.g. "coe-xx-mode" and it will turn up results.
- hummus_bae 4y ago
  Is this an alternative to helm-swish?
aritmo 4y ago
>If you were really competent with dev tools, and dedicated enough to pour over the uncompressed JavaScript, you’d notice.. That hurt.
Macha 4y ago
I'd love to switch direct to this, but it needs Firefox to support OPFS or for some form of IndexedDB workaround akin to absurd-sql to be upstreamed for my use case where I'd like storage to be persistent. At least Firefox OPFS is under development and looking at the tracking ticket seems to have accelerated in January
- benjaminjackman 4y ago
  That's exciting about Firefox, though I really wish that firefox would support OPFS in such a way as to allow selecting the directory where the files are located, beyond just persistence, there are some use cases where that is required (vscode.dev for example).
  - streptomycin 4y ago
    Unfortunately, the whole point of OPFS is to not do that. Mozilla and Apple thought it'd be bad for security to let you read/write arbitrary files/folders on the real file system, even with a permissions dialog, so a fake filesystem only viewable by an individual website was the compromise solution that everyone agreed to implement.
- paulirish 4y ago
  For the others that weren't familiar, OPFS === Origin Private FS. https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System...
halapeenio 4y ago
780KB for sqlite.wasm is not exactly small. Might be acceptable for a heavy weight app, but not a general website.
- mcdonje 4y ago
  My first reaction was that you need to send all potential search results across the network in the DB, which would be ok for a small app. So, I guess there's a goldilocks app size where this is a good solution.
  - ywei3410 4y ago
    I have seen this technique (send all the indexed search terms) a few times in the wild. Racket (a scheme derivative specifically made for teaching) has a search page where everything public is indexed [1] (see plt-index.js) and it works really well. [1] https://docs.racket-lang.org/search/index.html https://docs.racket-lang.org/search/index.html
- cldellow 4y ago
  It's a good concern. It does seem like it's sent uncompressed, gzipping gets it down to 360KB. Still hefty, but not _crazy_.
- kgeist 4y ago
  As of 2022, the average webpage size is around 2.2 MB for desktop sites and 2 MB for mobile sites, according to HTTPArchive. Provided the WASM binary is cached by the browser (is it?), that would be equal to an additional web page load on the first visit.
- btown 4y ago
  A single high-quality image on, say, a vacation rental website could easily be this large, to say nothing of a gallery of images for just a single property. Unless you're explicitly designing for 4G mobile connectivity, a properly-cached and CDN-distributed 1MB payload is absolutely viable.
- dspillett 4y ago
revision17 4y ago
Very cool! DuckDB also has a WASM version for flexible in-browser analytics/visualization: https://github.com/duckdb/duckdb-wasm https://github.com/duckdb/duckdb-wasm
wtatum 4y ago
Could you comment on which "flavor" of SQLite for WASM this is using and how you built or pulled the glue code? I think sql.js and absurd-sql are the best known solutions for this but it's not clear from the blog if you're using those or rolled your own. Information on how it was built or what prebuilt you're using would be fantastic. I also see that load-db.js is loading the known search index values into localStorage ... do you have some tooling for creating this file from a known SQLite base file or was it just handrolled from a localStorage already holding a valid DB? EDIT: Looking in the code I found this const db = new sqlite3.oo1.DB({ filename: 'local', // The 't' flag enables tracing. flags: 'r', vfs: 'kvvfs' }); Googling lead me to the "official" SQLite WASM pages which otherwise don't appear too prominently in search results for whatever reason. This (https://sqlite.org/wasm/doc/trunk/about.md https://sqlite.org/wasm/doc/trunk/about.md) seems like a good starting point and notes that both sql.js and absurd-sql are in fact inspirations
- zainab-ali 4y ago
  Of course! As you’ve found through digging into the js, it uses the official SQLite WASM build. The site currently uses a slightly old vendored version built from the SQLite codebase, but the SQLite team have recently released a binary at https://sqlite.org/download.html https://sqlite.org/download.html (under the WebAssembly section) that anyone can try out. You can find the docs at https://sqlite.org/wasm/doc/trunk/index.md https://sqlite.org/wasm/doc/trunk/index.md, which have been rapidly improving over the past few months. The process for building the database is a bit complex. I want to support all browsers, so unfortunately need to use local storage to back it up. Firefox has a while to go before it supports the Origin Private File System, but once it does so the build will be a lot smoother. I build the index as part of the site’s CI (using nix), by running SQLite-WASM in deno to pre-load local storage. I then extract the keys from local storage and populate them as part of the site load using the hand-rolled load-db.js file. SQLite WASM does have better support for importing / exporting databases on OPFS, so this process should be simpler as soon as I can move to it. I’ll write a follow up post at some point on the implementation details.
  - wtatum 4y ago
ingenieroariel 4y ago
Is the author stuffing the book contents in SQLite on load time or fetching them from a file?
- wtatum 4y ago
  Looks to be using the kvvfs which uses local storage key value pairs as the DB VFS. The file load-db.js (https://craft-of-emacs.kebab-ca.se/load-db.js https://craft-of-emacs.kebab-ca.se/load-db.js) pre-loads the values into localStorage. I assume the values were determined by loading local storage through another method and then extracting it during development.
dang 4y ago
Related: SQLite Wasm in the browser backed by the Origin Private File System - https://news.ycombinator.com/item?id=34352935 https://news.ycombinator.com/item?id=34352935 - Jan 2023 (208 comments) SQLite 3.40.0 with WASM Support - https://news.ycombinator.com/item?id=33696837 https://news.ycombinator.com/item?id=33696837 - Nov 2022 (15 comments) SQLite Release 3.40.0 - https://news.ycombinator.com/item?id=33628136 https://news.ycombinator.com/item?id=33628136 - Nov 2022 (16 comments) SQLite in the browser with WASM/JS - https://news.ycombinator.com/item?id=33374402 https://news.ycombinator.com/item?id=33374402 - Oct 2022 (198 comments)
phibz 4y ago
The article asserts that javascript is interpreted and was is bytecompiled so its faster. I think this ignores modern JIT based javascript engines like v8. They're very good at optimizing hot path javascript code as native code. WASM should be easier to optimize give the lack of variability and permissiveness in calls. BUt as far as I know, this hasn't been done yet for WASM so it runs more slowly than it could.
fmajid 4y ago
All this to rebuild the wonderful WebSQL that Microsoft and Mozilla conspired to kill and replace with the grossly inferior IndexedDB (just see what grotesque contortions are required to do something as simple as SELECT COUNT(*) FROM some_table; to see what I mean).
- jt2190 4y ago
  WebSQL was a proposed web standard that all standards-compliant browsers would have to implement. The standard was tightly coupled to the SQLite implementation, which was a problem for any browser that could not just bundle SQLite. Ultimately the standard was rejected because it was too coupled to a single implementation.
  - tracker1 4y ago
    Maybe as a standard it was lacking... but iirc Firefox, Safari and Chrome all had support, and IE/MS was the one holdout. I get that it was too "loosely" defined as a Standard. I also think the UX for WebSQL isn't great and a modern async/promises based interface would have been better (even if queries themselves non-blocking and serialized on another thread). Combined with the File System Access API, this could be really useful though. Currently toying with a Rust/Tauri project, and debating on using the SQLite plugin to do the data access in the UI, or in the rust side, then serialize the requests across more manually. Since I'm dealing with other services, will have to do a lot of that anyway.
    - wtatum 4y ago
      Also using Tauri in SQLite for a project. I only briefly looked at the provided SQLite plugin and quickly decided it didn't support everything I needed (custom scalar functions for example but there were others) but as far as I know all "official" Tauri plugins use the same event/command RPC mechanism available to you in userland for calling into Rust so I don't actually think the Tauri SQLite plugin "does the access in the UI" in the truest sense -- otherwise it wouldn't be a Tauri plugin it would just be a vanilla JS lib. If you've looked more closely and know that not to be the case would like to hear what you've seen but my understanding is that anything that leaves the WebView sandbox is using RPC to make calls to Rust.
topcat31 4y ago
I love this writeup! I'm not a developer but am very interested in "small databases" on the web - there was a good discussion around my post on this on HN last week. The magic of small databases: https://news.ycombinator.com/item?id=34558054 https://news.ycombinator.com/item?id=34558054
huksley 4y ago
Just tried to search "lists" and it does not search in titles.

90 points by zainab-ali 4y ago

For those of you interested in browser-based search with SQLite WASM, I’ve written a small post on the Craft of Emacs search page.You can check out the actual search here: https://craft-of-emacs.kebab-ca.se/search.html https://craft-of-emacs.kebab-ca.se/search.html Kudos to the SQLite team. It was a joy to implement.

tambourine_man 4y ago
It was a joy to read this article. I'd love to know more on the nitty-gritty details of the implementation.
Jwarder 4y ago
Are there any gotchas around the sql parameterization? Looks like you're passing in an array into db.exec. I would have thought that would be enough, but if I search for org-mode then I see an error on the console complaining about no such column: mode.
- philsnow 4y ago
  adding some quotes seems to help; you can search for e.g. "coe-xx-mode" and it will turn up results.
hummus_bae 4y ago
Is this an alternative to helm-swish?

aritmo 4y ago

>If you were really competent with dev tools, and dedicated enough to pour over the uncompressed JavaScript, you’d notice.. That hurt.

Macha 4y ago

I'd love to switch direct to this, but it needs Firefox to support OPFS or for some form of IndexedDB workaround akin to absurd-sql to be upstreamed for my use case where I'd like storage to be persistent. At least Firefox OPFS is under development and looking at the tracking ticket seems to have accelerated in January

benjaminjackman 4y ago
That's exciting about Firefox, though I really wish that firefox would support OPFS in such a way as to allow selecting the directory where the files are located, beyond just persistence, there are some use cases where that is required (vscode.dev for example).
- streptomycin 4y ago
  Unfortunately, the whole point of OPFS is to not do that. Mozilla and Apple thought it'd be bad for security to let you read/write arbitrary files/folders on the real file system, even with a permissions dialog, so a fake filesystem only viewable by an individual website was the compromise solution that everyone agreed to implement.
paulirish 4y ago
For the others that weren't familiar, OPFS === Origin Private FS. https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System...

halapeenio 4y ago

780KB for sqlite.wasm is not exactly small. Might be acceptable for a heavy weight app, but not a general website.

mcdonje 4y ago
My first reaction was that you need to send all potential search results across the network in the DB, which would be ok for a small app. So, I guess there's a goldilocks app size where this is a good solution.
- ywei3410 4y ago
  I have seen this technique (send all the indexed search terms) a few times in the wild. Racket (a scheme derivative specifically made for teaching) has a search page where everything public is indexed [1] (see plt-index.js) and it works really well. [1] https://docs.racket-lang.org/search/index.html https://docs.racket-lang.org/search/index.html
cldellow 4y ago
It's a good concern. It does seem like it's sent uncompressed, gzipping gets it down to 360KB. Still hefty, but not _crazy_.
kgeist 4y ago
As of 2022, the average webpage size is around 2.2 MB for desktop sites and 2 MB for mobile sites, according to HTTPArchive. Provided the WASM binary is cached by the browser (is it?), that would be equal to an additional web page load on the first visit.
btown 4y ago
A single high-quality image on, say, a vacation rental website could easily be this large, to say nothing of a gallery of images for just a single property. Unless you're explicitly designing for 4G mobile connectivity, a properly-cached and CDN-distributed 1MB payload is absolutely viable.
dspillett 4y ago

revision17 4y ago

Very cool! DuckDB also has a WASM version for flexible in-browser analytics/visualization: https://github.com/duckdb/duckdb-wasm https://github.com/duckdb/duckdb-wasm

wtatum 4y ago

Could you comment on which "flavor" of SQLite for WASM this is using and how you built or pulled the glue code? I think sql.js and absurd-sql are the best known solutions for this but it's not clear from the blog if you're using those or rolled your own. Information on how it was built or what prebuilt you're using would be fantastic. I also see that load-db.js is loading the known search index values into localStorage ... do you have some tooling for creating this file from a known SQLite base file or was it just handrolled from a localStorage already holding a valid DB? EDIT: Looking in the code I found this const db = new sqlite3.oo1.DB({ filename: 'local', // The 't' flag enables tracing. flags: 'r', vfs: 'kvvfs' }); Googling lead me to the "official" SQLite WASM pages which otherwise don't appear too prominently in search results for whatever reason. This (https://sqlite.org/wasm/doc/trunk/about.md https://sqlite.org/wasm/doc/trunk/about.md) seems like a good starting point and notes that both sql.js and absurd-sql are in fact inspirations

zainab-ali 4y ago
Of course! As you’ve found through digging into the js, it uses the official SQLite WASM build. The site currently uses a slightly old vendored version built from the SQLite codebase, but the SQLite team have recently released a binary at https://sqlite.org/download.html https://sqlite.org/download.html (under the WebAssembly section) that anyone can try out. You can find the docs at https://sqlite.org/wasm/doc/trunk/index.md https://sqlite.org/wasm/doc/trunk/index.md, which have been rapidly improving over the past few months. The process for building the database is a bit complex. I want to support all browsers, so unfortunately need to use local storage to back it up. Firefox has a while to go before it supports the Origin Private File System, but once it does so the build will be a lot smoother. I build the index as part of the site’s CI (using nix), by running SQLite-WASM in deno to pre-load local storage. I then extract the keys from local storage and populate them as part of the site load using the hand-rolled load-db.js file. SQLite WASM does have better support for importing / exporting databases on OPFS, so this process should be simpler as soon as I can move to it. I’ll write a follow up post at some point on the implementation details.
- wtatum 4y ago

ingenieroariel 4y ago

Is the author stuffing the book contents in SQLite on load time or fetching them from a file?

wtatum 4y ago
Looks to be using the kvvfs which uses local storage key value pairs as the DB VFS. The file load-db.js (https://craft-of-emacs.kebab-ca.se/load-db.js https://craft-of-emacs.kebab-ca.se/load-db.js) pre-loads the values into localStorage. I assume the values were determined by loading local storage through another method and then extracting it during development.

dang 4y ago

Related: SQLite Wasm in the browser backed by the Origin Private File System - https://news.ycombinator.com/item?id=34352935 https://news.ycombinator.com/item?id=34352935 - Jan 2023 (208 comments) SQLite 3.40.0 with WASM Support - https://news.ycombinator.com/item?id=33696837 https://news.ycombinator.com/item?id=33696837 - Nov 2022 (15 comments) SQLite Release 3.40.0 - https://news.ycombinator.com/item?id=33628136 https://news.ycombinator.com/item?id=33628136 - Nov 2022 (16 comments) SQLite in the browser with WASM/JS - https://news.ycombinator.com/item?id=33374402 https://news.ycombinator.com/item?id=33374402 - Oct 2022 (198 comments)

phibz 4y ago

The article asserts that javascript is interpreted and was is bytecompiled so its faster. I think this ignores modern JIT based javascript engines like v8. They're very good at optimizing hot path javascript code as native code. WASM should be easier to optimize give the lack of variability and permissiveness in calls. BUt as far as I know, this hasn't been done yet for WASM so it runs more slowly than it could.

fmajid 4y ago

All this to rebuild the wonderful WebSQL that Microsoft and Mozilla conspired to kill and replace with the grossly inferior IndexedDB (just see what grotesque contortions are required to do something as simple as SELECT COUNT(*) FROM some_table; to see what I mean).

jt2190 4y ago
WebSQL was a proposed web standard that all standards-compliant browsers would have to implement. The standard was tightly coupled to the SQLite implementation, which was a problem for any browser that could not just bundle SQLite. Ultimately the standard was rejected because it was too coupled to a single implementation.
- tracker1 4y ago
  Maybe as a standard it was lacking... but iirc Firefox, Safari and Chrome all had support, and IE/MS was the one holdout. I get that it was too "loosely" defined as a Standard. I also think the UX for WebSQL isn't great and a modern async/promises based interface would have been better (even if queries themselves non-blocking and serialized on another thread). Combined with the File System Access API, this could be really useful though. Currently toying with a Rust/Tauri project, and debating on using the SQLite plugin to do the data access in the UI, or in the rust side, then serialize the requests across more manually. Since I'm dealing with other services, will have to do a lot of that anyway.
  - wtatum 4y ago
    Also using Tauri in SQLite for a project. I only briefly looked at the provided SQLite plugin and quickly decided it didn't support everything I needed (custom scalar functions for example but there were others) but as far as I know all "official" Tauri plugins use the same event/command RPC mechanism available to you in userland for calling into Rust so I don't actually think the Tauri SQLite plugin "does the access in the UI" in the truest sense -- otherwise it wouldn't be a Tauri plugin it would just be a vanilla JS lib. If you've looked more closely and know that not to be the case would like to hear what you've seen but my understanding is that anything that leaves the WebView sandbox is using RPC to make calls to Rust.

topcat31 4y ago

I love this writeup! I'm not a developer but am very interested in "small databases" on the web - there was a good discussion around my post on this on HN last week. The magic of small databases: https://news.ycombinator.com/item?id=34558054 https://news.ycombinator.com/item?id=34558054

huksley 4y ago

Just tried to search "lists" and it does not search in titles.