SQLite in the browser with WASM/JS
sqlite.org
sqlite.org
This is really good news, and exactly what the OPFS was designed for.
You may have seen “Absurd SQL” [1] which was a proof of concept for building a SQLite Virtual FS backend using IndexedDB. It provided full ACID compliment transactions. Incredible work but a hack at best.
The OPFS supersedes all that and makes it possible to have proper consistent and resilient transactions.
WASM SQLite with the OPFS is the future of offline first web app development. The concept of a single codebase web/mobile/desktop app with proper offline storage is here.
What I really want to see next is an eventually constant sync system between browser and server (or truly distributed with WebRTC). The SQLite Session Extension [2] potentially has the building blocks needed for such a system.
0: https://webkit.org/blog/12257/the-file-system-access-api-wit...
Question: is it really persisted indefinitely? And will this SQLite file be portable (import and export to somewhere else) ?
https://webkit.org/blog/12257/the-file-system-access-api-wit...
https://web.dev/storage-for-the-web/#:~:text=Starting%20in%2....
> ...This eviction policy does not apply to installed PWAs that have been added to the home screen.
sqlite's storage format is independent of the underlying device. OPFS is "just another backend" and the only tricky part of implementing it was that OPFS's API is largely asynchronous and sqlite3 requires synchronous I/O. The db that's stored in OPFS is exactly what would be stored on your hard drive if you were operating outside of the browser.
"Mozilla and Microsoft TRIED to block it, but COULDN'T stop it!"
Seriously though, I can't wait to use this for my website. This is going to be ridiculously awesome combined with htmx.
(All of this is as far as I understand things, I could be completely wrong.)
1. You can persist data with localStorage/IndexDB, can't you? 2. There is nothing in the article about syncing. There is no out-of-the-box solution, as I can see.
I guess so, but given the situation that I'd already be using WASM SQLite in the client's browser, then I'd have to implement that part myself, or use something like Absurd SQL. I'd rather use the implementation made by the creator of SQLite.
> There is nothing in the article about syncing.
Correct, that's something I'll have to do myself. I don't see any issues there though, I'd just need to verify the user is authorised to make those changes, which in my specific use case seems pretty trivial.
Sorry, but it's just you opinion. Do you have proofs of how t-shirt store will benefit of putting RDBMS on a client side (how much space does it take BTW) instead of using simple list of objects?
And you still haven't answered the questions.
Edit: Additionally, I encourage you to experiment with this. If you haven’t already, you may be surprised at how efficient/compact SQLite files are. They’re significantly more compact that JSON or XML documents and can include binary files like images. It’s much simpler to use SQLite as a file format than to create a custom binary file format.
Think of something like personal finance, accounting, payroll, project management, CRM, zettelkasten style note taking, health and fitness tracking, etc, where users may want flexible analysis and reporting tools.
Much of this is now done on the backend, which makes sense if the data is accessed by many users in a transactional manner. But if it's a single user app or something used by tiny teams then cutting latency down to zero could greatly improve usability while still benefitting from everything a real query engine has to offer.
We’ve made one: https://electric-sql.com
Active-active SQLite to Postgres with transactional causal+ consistency based on CRDTs.
Disclaimer: founder.
I previously experimented with combining Yjs (CRDT toolkit) with Pouch/CouchDB to create an eventually consistent db, but decided that CouchDB was the wrong backend.
It looks like you have built exactly what I wanted to do if I had had time.
https://electric-sql.com/docs/overview/technical-intro
I happen across AntidoteDB just the other day, and my take-away was that it was still under development but not really ready for production use. Curious your opinion / experience with it?
It’s not production ready and neither are we (we’re in developer preview mode, which is like a public alpha [0]).
There are also other aspects on the Antidote roadmap such as efficiently materialising consistent secondary indexes that are ongoing challenges but aren’t so relevant to how we’re using it as a replication layer.
We are working, alongside others, with the Antidote developers to help fix these issues and generally improve reliability / engineer correct behaviour under load. (Professor Annette Bieniusa, who leads the development, is our Chief Architect). We have a fork at electric-sql/vaxine [1].
We are also taking advantage of some simplifications which mitigate the known issues. We have an #antidote channel in the ElectricSQL discord [2] if you’d be interested in chatting more.
[0]: https://electric-sql.com/docs/overview/faqs
You can also follow the generic driver integration instructions [0]. Basically need to implement an adapter interface and compose the various utilities.
Happy to help with this — shout on Discord [1] if you’d be interested in collaborating on it.
How heavy is the download? What's the minified client size at the moment?
Download size depends on the driver (we support different SQLite drivers for different environments). The web is quite heavy as it loads the SQL.js WASM (as a separate file, it’s not bundled). I want to say ~350-400kb total but I need to check and it depends a bit on how you build/bundle and serve it.
One approach would be to look at the network tab of the browser console when running the web example: https://github.com/electric-sql/examples
is that basically a desktop app that doesn't need to be installed? You still download it using a browser but then you don't install it on your operating system you just run it in your browser?
Another CRDT + SQLite project as a native extension: https://github.com/aphrodite-sh/cr-sqlite
Should have a release out here shortly but feel free to browse the code, tests and readme till then to see how it works.
i recently had to work with WebSQL in the context of comparing it to sqlite's new WASM support (of which i'm the developer). WebSQL, quite frankly, is a toy. Its execution model is far too limited and excludes all sorts of functionality, not the least of which is that it's impossible to delete a WebSQL db.
> yet here we are, back to that same spot, but with less performance and more complexity
Wrong. We have benchmarked the two in apples-to-apples comparisons, taking into account WebSQL's limitations. The two approaches are roughly equivalent, with both winning out under certain loads, despite WebSQL being implemented in native code.
Also even at similar performance you still need to download a bunch of extra stuff to even run the WASM version. My whole webpage takes less than it...
We is the sqlite team. i'm the "JS/WASM Guy" for the project.
> and where I can see those benchmarks ?
You can't currently because we don't have them in a user-consumable form. We've done a tremendous amount of benchmarking during the development because All The Speed was one of our design goals. However, all such records were in transient spreadsheets intended for one-shot note-taking use, not publication.
Once our documentation effort settles down, and responding to user feedback from the initial announcement slows down, i hope to implement a benchmarking application similar to:
<https://rhashimoto.github.io/wa-sqlite/demo/benchmarks.html>
Until then, however, you'll simply have to (A) take my word for it, (B) try it out yourself, or (C) none of the above, as you wish. Edit: or (D): we have a WASM port of sqlite's standard benchmarking tool, known as "speedtest1", in the sqlite source tree, but getting it up and running requires reading a good deal of documentation:
<https://sqlite.org/src/dir/ext/wasm&ci=trunk>
That tool is how we've benchmarked it so far, with the exception of comparing it to WebSQL, which required a custom application which is also in that directory (batch-runner.*). batch-runner, however, is in no way user friendly.
Is there a required feature that only OPFS provides, other than file locking (which seems like a pretty non-essential feature tbh)?
https://developer.chrome.com/blog/deprecating-web-sql/
https://twitter.com/chromiumdev/status/1565105522092695553
Which would be pretty cool. Projects like SQL.js and absurd-sql are awesome but naturally a bit rough around the edges and not highly maintained.
Maybe if half the sites on the web start using it, browsers can finally be convinced that it would be OK to add SQLite to the base platform.
WASM SQLite is the correct solution. It’s extendable by the developer using it, they can uses SQLite extension modules, and build their own.
But almost more so it proves the idea of a WASM db engine backed by a low level block FS api. We will see other db engines uses this architecture. DuckDB have already done it. I’m sure MongoDBs Realm and CouchBase Mobile will do the same soon too.
We are in for an exciting time in the next few years.
Why not? Web SQL would not be different from WebGL/GLSL or even JavaScript itself in that respect. Developers use feature detection and and work to the lowest supported version. APIs and languages can evolve, but in (mostly) backwards compatible ways. Extension and versioning mechanisms can be made. That's how the web works and Web SQL could have worked that way too.
More broadly, you could apply arguments like this against everything in the entire web platform. Maybe everything should be a WASM module that developers could choose themselves! Image loading, video decoding, font rendering, DOM, JS engine, why not? It's actually a beautiful vision and I'd be all for it if not for cache partitioning. Every site would have to re-download and re-JIT an entire browser engine before it could do anything.
The base platform needs to include a diverse set of commonly used features so that apps don't have to download the world, and on a list of ubiquitous libraries SQLite is right up there with other libraries backing the web platform, like zlib.
WebGL/GLSL give the developer a low level api to the graphics hardware.
Video decoding apis talk to the hardware video decoding hardware.
OPFS gives developers a low level API to the persistent file system / HDD / SSD.
To clarify, Apple made a proposal[0] which got no real traction. It has migrated[1] to a Javascript API[2]
[0] https://github.com/WebKit/explainers/tree/main/model
The enemy of progress is perfect.
> I’m supposing that the problem is that the web browser is the universal platform for all applications
This is a feature not a problem.
> There’s a benefit for information consumption (web pages) being separate from functionality rich and infinitely fingerprintable “native capabilities”
What exactly is that benefit supposed to be? If you want a read-only publishing platform, put PDFs on a FTP site.
Isn't this like a revival of the dreadful era of browser plug-ins from Adobe Flash Player to Java applets?
The issue with flash and applets was not that you had to download stuff.
It was that the security was non-existent (leading to the embedded runtime routinely crashing your browser), the interactivity was divergent from its surroundings, and the accessibility model was MIA.
Also downloading 500K over 56k and over fiber or 5G are rather different propositions.
If we had block API and SQL (it could be just a concrete version running from WASM itself, to not bloat browser itself) it could save a lot on app size. App then could look at browser version and decide to use that or to download newest one to run.
Not only that, but WebSQL disables a lot of mundane functionality, like the instr() SQL function and it's impossible to VACUUM because WebSQL requires explicit transactions and VACUUM cannot run in a transaction. i had the "pleasure" of having to work with WebSQL over the past couple of months for purposes of comparing its performance to the new sqlite features, and IMO, WebSQL is, as the kids say today, _weak sauce_. Its API is far too limited.
All of a sudden you have multiple slightly different instances running just because there isn't a standard.
It can be done, and needs to be - and more securely than the half-assed efforts of the likes of NPM and Maven.
All dependencies signed - let's encrypt has made this a viable option.
I don't see how there's anything approximating an unresolvable privacy concern here.
Sharing a cache between websites has proven to be a privacy issue.
Read more here: https://developer.chrome.com/en/blog/http-cache-partitioning...
or here: https://www.peakhour.io/blog/cache-partitioning-firefox-chro...
> This means if you visited a website and it loaded the resource:
> https://www.somesite.com/foo.js
> and you then visited a second website, and it also included the same resource, then the resource would be loaded from the shared cache rather than being downloaded from the internet a second time. Cookies set by these resources would also be shared.
The privacy problem is not a result of the shared dependency, it's a result of the shared cookies.
Yes, if you share the execution space between multiple programs running the same lib, there's a privacy concern.
No shit - don't fucking do that.
No, it's not the result of the shared cookies. You just ignored all of the timing attacks and fingerprinting which a shared cache allows, as those articles discuss.
The cookie thing is honestly completely irrelevant to the topic of privacy, if you understand how the shared cache used to work. If you loaded the same library from separate CDNs on different websites, the shared cache didn't come into play at all. The library was loaded twice anyways. There was no chance for cookies from different CDNs to accidentally cross the streams. Browsers weren't attempting to heuristically determine if you were trying to load the same asset from different hosts.
The shared cache only came into play if you loaded the same asset from the same third-party CDN on multiple websites. The host serving an asset is the one responsible for setting the cookies, and sharing the cookies is helpful in that case, since the same CDN is the host serving the asset to the browser for both websites. These aren't cookies controlled by separate websites, they're cookies supplied by the CDN, and they should be the same for both requests anyways, outside of maybe some remote possibility of a theoretical attack involving a malicious CDN intentionally setting some weird request-dependent cookies, but I can't see how that would even do anything harmful anyways. So, the cookies get shared because the browser is serving a cached response for the same asset from the same host to both sites, which makes sense.
So, cookies aren't the problem here, and they're not the reason the shared cache was partitioned. If you want browsers to undo that in any form, you would have to solve the actual privacy problems here.
If I make a request for a given dependency, and am allowed to so much as time how long it takes to resolve, I can detect if it was already there and there's an information leak.
Sure. At some point though, a malicious site's gonna end up making some very weird requests - obviously polling the cache.
You could specify the dependency set in a static context and limit the ability of a site to measure how long dependency resolution takes.
Is there still an information leak? Yes.
Do I think it should stand in the way of a functional internet? Not really.
We have a functional internet. I'm using it to communicate right now.
I think it is completely fair to say that privacy-respecting shared caches are not simple.
Some solutions can be imagined, but they come with weird trade-offs or they do nothing for majority of the web. A new manifest format like you describe falls into the latter, since it would only apply to new websites using the new feature, and that’s without digging into the other problems it would pose.
In practice, people often visit the same websites repeatedly; they aren't constantly visiting new websites only once. A partitioned cache works just fine for the normal scenario. It's slightly less efficient for the first day someone uses their browser, but then things are honestly fine after that. It's unfortunate that we can't eek out the last tiny bit of performance for this, but I think the difference would be hard to measure in practice.
In my opinion, if websites would more commonly use brotli, that would make a far larger difference in efficiency than returning to a shared cache, and if browsers could have a standardized means of downloading only the bytes that changed in an asset like a javascript library instead of downloading the new version from scratch, that would make a much bigger difference too.
Couldn’t they make an exception for some domains and create a registry of really popular or fundamental links to packages like jquery et al? I have read on this topic before, but it sounded like all or nothing no shades of grey maximalism. Fine, partition those memes from imgur cdns, but let common libraries with known hashes to be shared at least. The potential attack is based on leaving a cdn-pixel and dl-time-testing it on other sites. But there is no big data in who has the 10 most popular releases of wasm-sqlite, dayjs or bootstap.min.css in their cache. These could be warmed up from literally anywhere, or even synced in background by an idle browser thread.
I don't really see the problem with that. Looking at the sql.js demo[1] the WASM binary is 610K (305K compressed/transferred), and it seems to run pretty fast even on my slow laptop.
> Maybe if half the sites on the web start using it
Most websites have no reason to use it; simple key/value localStorage is enough for many sites or apps that need some sort of storage. It's kind of a niche thing. Many regular desktop applications have no need SQLite, either.
Please do not put words in quotes as if I said them when, in fact, I did not. Thank you.
...and that is small by your standards ?
In case you hadn't noticed it, the new sqlite features include using localStorage and sessionStorage as db backend storage :).
The Origin Privet File System api is going to provide the opportunity for any db engine to be used in offline first PWAs.
There is no “one size fits all” database engine, that was proved by both WebSQL and IndexedDB. The OPFS in combination with WASM is the correct solution to in browser DBs.
Thanks to a few very small people, WebSQL was deprecated before it had a chance to prove anything.
There was (and still is) no independent implementation of SQLite that was battle-tested even a bit.
It was not proven at all. WebSQL was deprecated based on "we don't want to standarize on single project". IndexedDB happened because they wanted to standarize on single API (lmao), but it was just too inept.
There is no one size fits all but WebSQL fit A LOT of use cases.
Low level storage that works for DBs is interesting idea but that also means you need to ship additional megabytes of code with every app.
Also, couldn't the implementation of that storage spec. be shipped in the standard JS web APIs (i.e. by the browser)? Why would it be in every app?
> Also, couldn't the implementation of that storage spec. be shipped in the standard JS web APIs (i.e. by the browser)? Why would it be in every app?
Actually, that is exactly what happens to WebSQL and IndexedDB. WebSQL got deprecated, they were not able integrate SQLite, and created IndexedDB, which is hated by many developers. Just an example: https://news.ycombinator.com/item?id=27511941
Why do we need a separate WASM version of something that's already built right into every single browser? Why is there no all-browser-vendor-blessed `Sqlite3` global?
It is now :). We provide 4 separate APIs, from the lowest-level 1-to-1 C-via-WASM bindings to one quite similar to sql.js, plus all of the low-level pieces necessary to create your own.
Historical note: those of us within the sqlite project had never paid any attention to the unfortunately-named WebAssembly (which has been dubbed "neither web nor assembly") because it didn't seem to hold any relevance for us. It wasn't until April-ish 2022 that we took a look at it, and have been working on "officially" bringing it to the browser world ever since. Even so, folks have been producing WASM bindings of it for a number of years now, and the relative ease of doing so (sqlite3.c requires no changes whatsoever to compile to WASM) is possibly (i opine) why we didn't get requests from users to do this sooner.
Sidebar: someone is going to ask "if it's so easy, why did you need 6 months to get it out the door?" Fair question: we had some very specific technical goals which i'm not at liberty to elaborate on, and only a single developer to put on it. Plus, WASM was a completely new tech for us, so there was much learning and experimentation involved.
It's great that you went "what if we compile to WASM?" but this is something that's not on you: this is something that should have been on browser vendors to just _expose_ because every browser ships with sqlite baked in already. Every user already has it, they just can't use it.
Richard (the sqlite lead) has often described his definition of "freedom" as "being able to take care of yourself," a philosophy he lives and breathes with his software. The sqlite project providing wasm builds for folks, and the materials they need to make their own custom builds, is directly in line with that. Depending on every browser vendor to play along in sync is, quite frankly, a lost cause.
> ... they are a huge payload ...
If you truly believe that 500kb of content is "huge" nowadays, i challenge you to go watch (via the browser dev tools) how much stuff your favorite websites are serving. (HN, of course, is a spartan exception to the rule.) Hit any given news or social media site and you'll get at least a meg of content, most of which is constantly replaced (so caching it is of little use). Hit IMDB and you'll get 2+mb (compressed). Hit GDrive and you'll get 4.75mb compressed (nearly 15mb uncompressed!). i just hit www.google.com, which has a long history of minimalism, and it transferred 881kb (2.21mb uncompressed).
By comparison, 500kb-1mb (uncompressed) isn't even worthy of honorable mention.
Very true, but that is not mutually exclusive with not understanding why it's a lost cause. There's literally three browser vendors, and of those, really only one of them needs to go "fuck it, sqlite now has an API" and the other two kind of don't have a lot of choice but to follow suit. It's a Chrome world right now.
We could have easily done this, we chose not to. Why? (and you probably don't have the answer to that. I don't know if anyone does)
In the general case, admittedly true. Note, however, that the Chrome folks have no say-so in the shape of this particular sqlite API, with the tiny exception that they are not willing to create a 100% synchronous API for OPFS, which complicates the sqlite-side development of that one small part of the API considerably[^1]. The shape/flavor/whatever we'd like to call it of the sqlite JS/WASM APIs is left 100% to the project members' discretion. If Chrome dies tomorrow, this API is still a thing, it will just have severely reduced client-side persistence options until the next hypothetical vendor creates one, at which point we'd latch onto that one.
[1] = When we consider that async APIs exist largely to account for network latency, and OPFS _has no latency_ beyond the local storage device, OPFS's interface being async is, IMHO, a design flaw stemming from the misled assumption of "it's on the web ergo it must be async" (and i've told them that in emails and meetings). Based on our testing metrics, the performance of the OPFS sqlite layer would increased by at least 30% if it had a 100% synchronous API to work with, as it tends to waste anywhere from the low-30s to mid-40s percentage points of its time waiting at cross-thread communication boundaries.
By someone who makes stable, compatible, 100% battle-tested embedded SQL engines every saturday morning? There are plenty of teams to choose from:
End of list.
> This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path.
https://www.sqlite.org/copyright.html
"SQLite is open-source, meaning that you can make as many copies of it as you want and do whatever you want with those copies, without limitation. But SQLite is not open-contribution."
> the project does not accept patches from people who have not submitted an affidavit dedicating their contribution into the public domain.
So your statement that SQLite "does NOT accept contributions" is plainly wrong. SQLite is not "open contribution" because it requires an affidavit in order to ensure the project remains public domain.
> So your statement that SQLite "does NOT accept contributions" is plainly wrong. SQLite is not "open contribution" because it requires an affidavit in order to ensure the project remains public domain.
You're both right. It does require an affidavit, but the project does not simply accept affidavits from drive-by folks. It's effectively "by invitation." (Citation/disclaimer: i'm a member of the sqlite project team.)
665K sqlite3.wasm
306K sqlite3.wasm.gz (-54% compared to uncompressed)
266K sqlite3.wasm.br (-13% compared to gz, -60% compared to uncompressed)So, "hefty" seems like a bit of an exaggeration to me. All other things equal, lighter is always better, but 266KB for all the functionality SQLite offers isn't that bad, IMO.
On the topic of caching, since SQLite is (intentionally or not) going to be setting an example with their docs and demos[0], I would suggest that SQLite should demonstrate the industry best practices with regards to caching as well. Static assets like WASM and JavaScript should be served with a very high cache duration, and the name of the asset should include a hash of the asset. This way, the site maintainer can update the HTML to reference the new SQLite bundles by the new hash whenever they upload new versions, and browsers will immediately request the new versions, but they will otherwise instantly load the cached version after the first visit whenever there isn't a new version.
doesn't seem possible to do without leaking data
> so it is effectively a one time penalty for a given website
which shows that I already acknowledged the demise of cross-origin caching.
Each website the user visits that uses this library has to pay a 266KB penalty once, which isn't that bad. (Well, once, unless the site developer decides to upgrade the library, then the penalty applies once more, but that's expected behavior.)
That can't work on the documentation site because the wasm file is being served from the Fossil SCM and fossil cannot cache resources which are fetched by name because a new version may be checked in at any given moment (they're currently updated very often: https://sqlite.org/wasm/finfo/jswasm/sqlite3.wasm). Fossil can hypothetically cache resources which are fetched by hash, but that's not a feasible way for us to maintain the documentation on that site.
They're not relevant to the wasm/js deliverables. They're an implementation detail of the server which happens to be serving the related documentation. If you have specific verbiage which you feel would improve the docs in this regard, please feel free to send it. i'm likely to miss most responses in HN, so please use email (stephan at sqlite dot org) or the sqlite forum: https://sqlite.org/forum.
i think you'll find that many high-end websites often download more than a megabyte of CSS and JS code. imdb.com home page: 2.13mb transferred for 6.odd mb data. drive.google.com: 4.75mb transferred for nearly 15mb of data(!!!).
266kb doesn't even register nowadays for app-centric pages.
Sqlite updates are solid and as far as I know, do not break your code.
Almost every major SQLite release has a few minor releases after it which fix bug and regressions, including things like queries returning the wrong result.
s/millions/billions/g. It is widely believed to be either the single most widely-deployed piece of software in the world, or maybe second behind zlib (we have no way of being sure).
If every site embeds their own version, the application developers can choose their own version that runs the same across every browser. Or even compile it themselves if they want, so they can use extensions. The download size is unfortunate but remember - unlike javascript, wasm bytecode loads almost instantly. Having a 250k wasm module is more like having a 250k image on your site than 250k of javascript. (Though it might still affect time-to-interactive depending on how the site is built)
Nope. The downside is that some applications that want to use new one would have to download it, and the remaining 90% could use builtin one
Once you start giving special privileges to specific wasm binaries, the floodgates are open for a hundred different vendors to demand the same special treatment because 10% of websites use their .wasm blob. And then you've created a barrier to entry for competition. Nobody wins in the long run except a couple of your friends.
Browser behaviour changes (and even breaks) way often than SQLite does.
That applies to the C code. The wasm code is still in beta, and won't see a public beta release until 3.40 is released in November. See the notes about API stability here: <https://sqlite.org/wasm/doc/trunk/api-index.md>
That is interesting, but the repository you're looking at is a Fossil SCM repo and the wasm file is being served directly from it. Fossil does the compression transparently and doesn't support brotli. (Edit: i'll investigate whether brotli compression would be interesting for us to add to fossil.)
However, the sqlite project will not be hosting shared copies of the wasm/js files for use by arbitrary 3rd-party sites. It's up to each site to host their own (and even build their own if they need to customize the build), so they're free to use whatever compression they like.
As it's a fossil repo, here's the steps to clone it and check it out locally:
sudo apt install fossil # Install fossil (on Debian)
fossil clone https://sqlite.org/wasm # Clone the SQLite WASM repo
cd wasm # Navigate to the cloned repo
fossil ui # Launch fossil in UI-modeAwesome news. SQLite is already a defacto cross-platform file format. An official WASM release will be widely welcomed and extremely useful.
https://github.com/kripken/sql.js/commit/cebd80648dbd369b348...
Thank you very much for that info. i'll update the sqlite wasm docs with that.
Edit: updated, thank you! <https://sqlite.org/wasm/info/cacc33abc5d9013f>
for *the web*, not for WASM. SQLite was initially compiled to simple javascript, then to asm.js when it appeared, then to WASM when it replaced asm.js :)
The sql.js project is older than the idea of WASM itself. And I like to think it contributed to showing the potential of compiling native code to the browser, and in the creation of the WASM standard.
Docs have been updated. Thank you for the feedback! Edit: for future feedback (from anyone reading this) i can be reached via stephan at sqlite org. i'm unlikely to catch most doc feedback posted to HN.
Here's a better overview of the sqlite3 WASM project: https://sqlite.org/wasm/doc/trunk/index.md. Very excited to try this once support is added to Firefox and Safari!
Within the sqlite project we're fairly convinced that FF and Safari will catch up as soon as their larger customers start targeting Chromium-based browsers simply for the OPFS support. My estimate is mid- to late- 2023 at the latest. My (mis?)understanding is that Safari has most of this support but not the latest changes from Google (namely "sync handles"), and sqlite needs those latest features in order to use OPFS.
> Very excited to try this once support is added to Firefox and Safari!
That's up to the browser vendors, but it seems very likely that they'll jump on board once large apps start making use of it OPFS (independently of whether or not those apps use sqlite). If their impls are API-compatible with Chrome's (which Google is certainly pushing for), sqlite will "just work". It is likely that the OPFS APIs will be tweaked somewhat in the mean time (e.g. changes in the locking-related support are under discussion), and sqlite's support will/would need to be adjusted accordingly, but "one of these days" it will "just work" across the 3 major browsers.
i'm not sure where you read in that that Google is owning this whole thing. They're just the first out the gate with working OPFS, so that's the implementation we worked against to get this up and running with sqlite. Google has worked with the other browser vendors from the start on the API.
No telling when it will be ready, but it's encouraging that they seem to be working on it.
You can play with the wasm-compiled extension at the version of the sandbox I compiled here: https://llimllib.github.io/wasm_sqlite_with_stats/
I tried to be thorough with the writeup, so hopefully it helps somebody if that's something they need.
There are some amazing things for SQLite in the browser especially if you're looking for ways to host queryable data for cheap. The example I have below costs $0.42 cents a month to host. For 28GB. Insane.
I have a hacked up POC experimental version of the datasette-lite Python UI which runs in Emscripten to be able to look at multi-GB databases at https://github.com/simonw/datasette-lite/pull/49. It uses a hacked up chunk'd lazyFile implementation from emscripten and others to grab pages from Cloudflare R2.
Here's a test/demo with california's unclaimed property records (https://www.sco.ca.gov/upd_download_property_records.html) of a 28GB searching up that guy who owns Twitter:
https://datasette-lite-lab.mindflakes.com/index.html?url=htt...
I really need to dig in and figure out how Datasette (and Datasette Lite) can work better for this. I think this issue might help - the ability to turn off row counts entirely: https://github.com/simonw/datasette/issues/1818
(I noticed that trying to access the table directly seems to suck in a LOT of data, presumably because it's trying to calculate a count across the whole table?)
To the best of my knowledge, OPFS's current quota is about 256mb (per origin).
The browsers currently have no way of viewing/managing the content of OPFS, so it's sort of a storage black hole. i can't even tell you how many sqlite3 database files have been orphaned in my local OPFS since development of the new sqlite wasm support started, with no reasonable way of me being able to find them without using the OPFS-specific JS API to fish through the storage (which i haven't yet been willing to do).
OPFS storage cannot sensibly be exposed at the system filesystem level (i.e. browseable with a file manager) because that would open not only security holes (the ability to "side load" data into any origin) but also huge file locking headaches, especially on platforms which use virus scanners.
IMO, all that was missing was a truly compelling use case. The combination of the ubiquitous sqlite with non-trivially-sized persistent storage gives us that use case.
At the start of this development effort (April, IIRC (2022)) we (in the sqlite project) had only ever heard of wasm but hadn't paid any attention to it. Within just a few days of starting this project, we were fully convinced that the combination of sqlite and OPFS will be one of the Next Big Things for web app development.
As the project's "JS/WASM Guy" i'm exceedingly excited to see what people do with this and what improvements we'll make based on user feedback. (It's long been my experience that the most interesting feature suggestions come from users.)
Very nice :). Be aware that the copy of sqlite3.wasm on the sqlite.org/wasm site gets updated fairly frequently, so may differ from that at any moment. That particular site is only for documentation purposes, not for hosting the canonical wasm file release (which is pending along with the release of sqlite 3.40), and its sqlite3.wasm/js copies get updated hand in hand with development of the canonical copies.
Here is the code example for react: https://kikko-doc.netlify.app/react-integration/installation. Some technical details: with special sql`INSERT INTO ${sql.table`some-table`}` syntax, it tracks in which tables changes are happened, and notify other tabs to make refetch to the subscribed tables.
Project is in alpha, and already has support of absurd-sql, wa-sqlite for web; expo, tauri, electron, ionic, React Native.
I am super excited to add support of official SQLite wasm implementation
wa-sqlite can work without COOP, but it is under GPL, unfortunately. It don't allow using it in private codebase without open-sourcing the project
Only for the OPFS support, because any solution involving hiding an asynchronous API (OPFS) behind a synchronous one (sqlite) requires it because the "await" keyword in JS is "viral": it can only be used from global-scope code or from functions which are themselves flagged as "async", and flagging a function as "async" changes its return semantics in ways which are fundamentally incompatible with C code. Any solution to that problem in JS requires SharedArrayBuffer and the Atomics APIs. WASMFS's OPFS implementation has the same limitation, despite being implemented in native code.
(That said: there is some talk among those who know better than i of modifying WASM to be able to accept Promises as return values, and returning the resolved promise value to C. i don't think it's possible because promise _rejection_ cannot pass through C code, but folks who know better than i seem to think it can be done.)
If you don't need OPFS support you don't need COOP/COEP. (Citation: i'm the sqlite js/wasm developer and have had this discussion with Google's OPFS folks.)
You're not the only one :/. We suspect that the COOP/COEP requirement for OPFS will be outright untenable for many folks, in particular those using hosting which does not offer them the option of modifying outbound headers (in fact, we had to extend sqlite.org's http server, althttpd, to add that capability for this purpose!). We've brought this pain point to the OPFS folks' attention but there is currently simply no way around it.
You can now have both: a relational database in your key-value localStorage:
<https://sqlite.org/wasm/doc/trunk/persistence.md#kvvfs>
:-D
Absolutely, but _it works_ and nothing trumps working code ;). A slow and space-limited database is better than none at all!
We implemented that functionality primarily to make peoples' eyes bug out ;), but also so that folks who don't yet have OPFS can have some form of persistent sqlite databases.
not trying to be snarky but do you mean a desktop app?
Or maybe you could use a sqlite replication tool in the browser for near realtime changes in the master node db.
And for single user apps developped with tools like Electron having your db engine in the browser makes the app faster and life easier for the dev.
Node as app server and db backend for a single user app is inefficient, slow and resource hogging.
Using Sqlite you could get rid of node and therefore, reduce memory usage, speed up app loading and execution, have a single code base in the browser that makes debugging far easier, reduce code complexity and app size, reduce cpu cycles, energy usage and CO2 emissions, save precious life time, etc... :-)
And in all seriousness, WASM is exactly the way this should be done. It doesn’t tie a db engine to a specific standardised version. It’s far more important to develop low level block storage apis like the OPFS that WASM SQLite is using.
I guess they are trying to do it slowly so they don't break stuff, but they're already doing things like removing FTS support.
FWIW, i can say with some authority that they have been waiting on this new sqlite/wasm stuff so that they can offer a replacement to WebSQL for their folks who still use it. Despite Google's long history of pulling the plug on products (G+, how you are missed!), they're not willing to outright drop WebSQL without a viable replacement.
It would not be difficult to write a drop-in workalike WebSQL wrapper on top of the new wasm/js APIs, and we (in the sqlite project) may even get around to doing so as time allows for. The difficulty would be making it "quirk for quirk compatible," as such quirks are not documented anywhere. OTOH, as far as we're aware, the only extant WebSQL implementation is the one in Chrome, so that's the only one which counts for purposes of quirks.
Are there any numbers on performance?
Tucked away in spreadsheets there are but we don't currently have any benchmarks in a publishable form. In broad strokes, i can assure you (as the one who performed the benchmarks) that the new sqlite wasm is competitive with WebSQL in terms of performance, with either one winning out in certain benchmarks. Given that WebSQL is implemented in native code and sqlite in wasmified C, we're quite happy with those results.
Note, however, that benchmarks are very browser-dependent. Firefox's wasm engine, for example, is significantly slower than Chrome's, but it's also more consistent. If you run a given test 10 times in FF, the difference in runtimes across them will be small (maybe 10%), whereas there will be a +/-50% difference in runtimes for the same tests in Chrome. In Chrome, if the dev tools are open when wasm is running, wasm's performance can (for unknown reasons) drop by as much as half or more even if the wasm code produces no output.
While not one of said authors... You mind giving me an actual rundown on issues you've got with it instead of just slinging mud?
I've used IDB in a few projects, and apart from:
- The god awful callback crap born of being pre-promises. - The lack of partial indexes (e.g. indexing a subset of documents based on some parameters) - The iOS Webkit teams continued habit adding weird, app-breaking bugs every other release to an api/system that should be stable?
It's been fairly useful. Hell, it supports storing JS types like CryptoKey or Blob/File with no real issues[1]
[1] Sans iOS WebKit, which a version or 2 back would just eat Blobs at random.
Here's a log of one developer's anguish with IndexedDB...
https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2...
As for transactions.... Yeah I'll admit they suck hard, and can be a pain to remember the quirks of them, as well as dealing with the callbacks.
Quotas and Private mode... Those same issues would apply to webSQL, and webSQL didn't have a way to delete the database to clear it up.
See this argument falls apart because:
It's a key-value store... I'm fairly certain people are aware of that concept, or can grok it fairly quickly once introduced to it.
Also as to "without any reason"? It handles js data natively. You're not having to immediately throw an ORM or a bunch of custom code to marshal data into and out of the db. People build more than just todo lists. There was also some intent of it being "low level" that others could build niceties like rich query syntax etc atop.
Now, that didn't really happen much, so the debate about it being "terrible" should maybe focus there on what got missed for that goal.
No, it isn't. While kv is easy on its own, the IDB API was never the right answer to the demand. This is exactly the reason why we (devs) are so hot about the persistent client-side storage. We want just use something like SQLite (or WebSQL, or Postgres) and forget about the IndexedDB nightmare. I'm pretty sure, we will see a huge boost around libraries and tooling when the things get more stable eventually.