Notion about their usage of WASM SQLite

Meanwhile in AWS you would pay $27k a month to have the same IOPS as a Lenovo Thinkpad X1.

I just got done with a side project using WASM SQLite as well, it's incredibly powerful, even supports full text search.

My project "cluttr" is a local first site that focuses on cleaning up your screen shots folder and making it searchable via OCR. All in browser.

It also supports using Ollama to run a vision model against your images to provide more context.

Really helpful if you take a lot of screenshots for clients or PMs and need to find them later. The best part is 99% of the hosting cost is offloaded to the client, so I am not really worried about a large server bill.

https://cluttr.pages.dev/

(working on deployment to https://cluttr.ai later today)

There's a multiple readers and writers proposal [0]. It's been "position: positive" by Firefox [1], implemented in Chrome [2], and ignored by Webkit [3] (of course).

    0: https://github.com/whatwg/fs/blob/main/proposals/MultipleReadersWriters.md
    1: https://github.com/mozilla/standards-positions/issues/861
    2: https://chromestatus.com/feature/5172892632875008
    3: https://github.com/WebKit/standards-positions/issues/238

Also, looks like Roy takes advantage of the aforementioned proposal when he wrote `OPFSPermutedVFS`, which takes 2nd pace behind `IDBMirrorVFS` w/r/t perf. https://github.com/rhashimoto/wa-sqlite/blob/master/src/exam...

I'm glad they are making their app faster, in the meantime I (browser user) have cancelled my team's subscription and will be using something else.

[1] https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

I don't think this is correct:

https://developer.mozilla.org/en-US/docs/Web/API/File_System...

- tooling, support and debugging for opfs

- only really works with a single worker using sync api, but this is not available from service workers

- the official js releases are really not idiomatic js and require in depth understanding to pick out the right parts and then build a usable layer on top, afaik there is no existing npm package that just provides what is required

I'm not talking about 'using the DOM to store data' in the traditional sense. The idea is rather (roughly) to store each database 'table' as a child of a hidden DocumentFragment, with 'rows' as its children, etc. Then we'd query this data using CSS selectors (or XPath).

For example, instead of

  SELECT * FROM Employees WHERE gender = 'male' AND age > 30

  customersTbl.querySelectorAll('row:has(cell-gender[value="male"]):has(cell-age[value > "30"])')

  SELECT * FROM Employees WHERE gender = 'male' OR age > 30

  employeesTbl.querySelectorAll('row:has(cell-gender[value="male"], cell-age[value > "30"])')

  SELECT * FROM Employees WHERE name->>'first_name' = 'John'

  employeesTbl.querySelectorAll('row:has(cell-name:has(cell-first_name[value="John"]))')

  SELECT * FROM employees WHERE skills::jsonb ? 'JavaScript';

  employeesTbl.querySelectorAll('row:has(cell-skills > cell-skill[value="JavaScript"])')

Edit: silly me indeed to think CSS attribute selectors already can do numeric comparisons! See here: https://github.com/w3c/csswg-drafts/issues/354

One interesting thing is that opfs maximum disk usage is kind of browser specific but tends to be a percentage of the available diskspace measured in GB rather than some lowish number like 5MB as is typical for e.g. browser local storage. This makes it suitable for locally caching and syncing large amounts of remote database content.

We've had a few challenges with opfs and particularly limited support for this on Safari it kind of works but with some caveats. Chrome/Firefox are fine. Our web app is packaged up as a PWA for use on mobile.

Another issue is that opfs creates issues with loading resources from external websites. E.g. we have HTML previews that may include images on external domains that the browser will render fine without opfs enabled but will refuse to render when using opfs, unless you set crossOrigin=anonymous on the img tag.

Relative to indexed-db, which is supported by most browsers, you gain a more sane API to access data and more flexible querying and support for things like joins. I've had some exposure to indexed-db and IMHO it's a case study in bad API design gone horribly wrong. Querying is very limited and the APIs are poorly documented and have weird failure modes. Sqlite is an absolute pleasure to use in comparison and probably faster and way more capable.

SQLite has become the defacto local single file database standard.

It has a very permissive license.

It has support contracts out to the 2050s (service lifetime of Airbus A350 airframe).

SQLite will probably have a longer lifetime than whatever browser standard we have now.

Couldn't you cache the data in LocalStorage and get similar speed improvements?

I mean, how to run SQLite in the browser is a great topic and surely has a lot of very good use cases. But attributing the performance improvements to it sounds a bit misleading, when you had no client caching strategy before and you do now. Obviously loading data from a local cache is faster than loading data from a remote server.

Comparing WASM SQLite vs. other client caching solutions (LS, IndexDB, etc) could be interesting. In terms of perf, complexity, compatibility, storage capacity, query language, etc.

Discussion (19 points, yesterday) https://news.ycombinator.com/item?id=40931957