[HN Gopher] SQLite in the browser with WASM/JS
___________________________________________________________________
SQLite in the browser with WASM/JS
Author : hochmartinez
Score : 250 points
Date : 2022-10-28 17:21 UTC (5 hours ago)
(HTM) web link (sqlite.org)
(TXT) w3m dump (sqlite.org)
| hinkley wrote:
| I entirely missed the era where SQLite was a candidate for W3C
| inclusion. It was always a mental bookmark in case I found a need
| to have an app with any substantial offline mode, but by the time
| I found a perfect candidate (cataloging plants in situ), it was
| already deprecated, and a key-value store is no replacement for
| SQLite.
| exelib wrote:
| Key-value store would be OK, we (developers) can handle that.
| And in fact, we do it already. But IndexedDB is broken by
| design. But I agree, handle relational data is what we want.
| And you can easily emulate non-relation data with it, if you
| need.
| ramesh31 wrote:
| Feels like WASM is hitting an inflection point this year. After
| nearly a decade of hearing "someday", that day is finally here.
| MuffinFlavored wrote:
| I wonder what the catalyst finally is/was to cause such an
| acceleration
| adamrezich wrote:
| awesome! it's also great to see a project as important as sqlite
| provide first-class wasm support like this.
| lovasoa wrote:
| Hi! I'm the main maintainer of sql.js. Is there somewhere I can
| get in touch with you? Would you be interested in publishing this
| as a new major version of sql.js itself ?
| hochmartinez wrote:
| Demos you can inspect...
|
| https://sqlite.org/wasm/doc/tip/demo-123.html
|
| https://sqlite.org/wasm/doc/tip/demo-123-worker.html
| thruflo wrote:
| The Google Chrome team is working with the SQLite team to
| standardise a WASM based SQLite in the browser to replace WebSQL.
|
| 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.
| samwillis wrote:
| They aren't working to standardise SQLite, the are working to
| standardise a low level block based file system api that SQLite
| and other database engine can use. Thats much more exciting
| than "standardising SQLite" for the web.
|
| 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.
| justin66 wrote:
| > There is no "one size fits all" database engine, that was
| proved by both WebSQL and IndexedDB.
|
| Thanks to a few very small people, WebSQL was deprecated
| before it had a chance to prove anything.
| kevingadd wrote:
| Most likely prove its utility as an attack surface and
| source of compatibility issues
| modeless wrote:
| This is good but it can never be as good as Web SQL could have
| been, because it can't be a truly shared library. Like all WASM
| modules, It has to be downloaded and JIT compiled separately
| for every site that uses it. Not for technical reasons, but
| privacy reasons, so it's basically unfixable:
| https://developer.chrome.com/en/blog/http-cache-partitioning...
|
| 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.
| samwillis wrote:
| I disagree, this is much better than WebSQL. WebSQL would
| have been tied to one specific version of SQLite, and
| developers would have to work to the lowest supported
| version. There would have been no extension mechanism.
|
| 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.
| modeless wrote:
| > WebSQL would have been tied to one specific version of
| SQLite, and developers would have to work to the lowest
| supported version. There would have been no extension
| mechanism.
|
| 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.
| FerociousTimes wrote:
| > Every site would have to re-download and re-JIT an
| entire browser engine before it could do anything.
|
| Isn't this like a revival of the dreadful era of browser
| plug-ins from Adobe Flash Player to Java applets?
| samwillis wrote:
| WebGL/GLSL is a low level API the equivalent of the OPFS
| api. Standardising WebSQL would be like browsers
| standardising on Three.JS rather than WebGL/GLSL.
|
| 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.
| modeless wrote:
| The web platform has tons of very high level stuff in it,
| much higher level than SQLite, and more is being added.
| Even specifically on the topic of 3D, they're trying to
| add a <model> tag and standard 3D model file format to
| HTML right now. It doesn't make sense to reject Web SQL,
| which is much more foundational, on the grounds of being
| too high level. Not now, but even less so back when the
| decision was made in 2010, when the web itself was all
| higher level and the lower level APIs you mentioned
| didn't even exist.
| haimez wrote:
| All of these APIs are going to be very reliable
| fingerprinting opportunities, especially in combination.
| Keep that in mind when you think it's going to be great
| for your web browser to also be a full featured
| application runtime.
| Beltalowda wrote:
| > It has to be downloaded and JIT compiled separately for
| every site that uses it
|
| 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.
| yarg wrote:
| Dependency resolution for the web would fix this.
|
| 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.
| coder543 wrote:
| Signing dependencies is related to integrity, which is
| orthogonal to privacy.
|
| 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...
| yarg wrote:
| As per your second link:
|
| > 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.
| coder543 wrote:
| > The privacy problem is not a result of the shared
| dependency, it's a result of the shared cookies.
|
| 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.
| yarg wrote:
| Fingerprinting is possible if you know what's in the
| cache before you use it.
|
| 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.
| coder543 wrote:
| > 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.
| yarg wrote:
| OK. But it's far from efficient, and I think that you're
| exaggerating the difficulty associated with improving the
| situation.
| coder543 wrote:
| Google, Apple, and Mozilla apparently all failed to find
| a solution to this problem, even though lots of people
| _wanted_ there to be a solution. Certainly, any browser
| that shipped this feature in a privacy-respecting manner
| would have bragging rights for awhile, so there is an
| incentive. Given that, I don 't think I'm exaggerating
| the difficulty of the problem. If it's so easy, lots of
| people like you would have gone and solved it.
|
| 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.
|
| 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.
| thruflo wrote:
| Interesting -- so basically an overhead until it's fully
| native? I was reading the Chrome initiative as a pathway to
| native. Would that mean it can't be WASM?
| modeless wrote:
| That tweet is not implying that it will ever be native.
| Quite the opposite; Web SQL is native today and they are
| going to remove it since it was rejected by other browsers.
| rcarmo wrote:
| Very nice. I built sqlite3 for WASI/a-Shell to use on my iPad
| (https://github.com/rcarmo/wasi-sqlite) and it still has a few
| issues, I hope this will help (although right now the biggest
| issue seems to be that the REPL has some sort of memory leak when
| run inside WASI).
| TheRealPomax wrote:
| The thing I don't get here is that literally every browser
| already uses Sqlite3 for a million things, why are we not just
| finally acknowledging that, yes, this very specific library is a
| universal good, just like gzip compression, or the jpeg image
| format, and here's the JS API for directly working with it?
|
| 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?
| easton wrote:
| They tried that at one point, I think it was called WebSQL? But
| it was decided that they didn't want to make the quirks of
| sqlite3 a web standard since it'd be hard to do a clean room
| implementation.
| sedatk wrote:
| W3C tried this but it failed due to lack of independent
| implementations[1]:
|
| > 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.
|
| [1] https://www.w3.org/TR/webdatabase/
| coder543 wrote:
| As a little pro-tip, the demos appear to be serving a gzip
| compressed WASM file, but offering a brotli compressed version
| for clients that say they accept it would be even better:
| 665K sqlite3.wasm 306K sqlite3.wasm.gz (-54% compared to
| uncompressed) 266K sqlite3.wasm.br (-13% compared to gz,
| -60% compared to uncompressed)
| dstaley wrote:
| Oooff, 266 KB is a hefty price to pay. I really wish WebSQL
| would have won out over IndexedDB, because I would have loved
| to have been able to build libraries on top of that instead of
| the headache IndexedDB is.
| coder543 wrote:
| "Hefty"... for an asset that can easily be cached long term,
| so it is effectively a one time penalty for a given website,
| and many web apps will load images larger than this.
| Certainly, just about any marketing/product page will load
| more bytes than this in the form of images, let alone videos.
|
| 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.
|
| [0]: https://sqlite.org/wasm/doc/trunk/demo-123.md
| fooey wrote:
| cross-origin caching on the web has been dead for years
|
| doesn't seem possible to do without leaking data
| coder543 wrote:
| I agree. I said:
|
| > 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.)
| hochmartinez wrote:
| sqlite3.wasm should be included in Chrome, Firefox, Safary,
| Brave, etc. And updated automatically.
|
| Sqlite updates are solid and as far as I know, do not break
| your code.
| Beltalowda wrote:
| > 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.
| modeless wrote:
| Browser engines have regressions too. SQLite has a better
| test suite than any browser. It's not going to be a major
| source of regressions compared to the rest of the platform.
| hochmartinez wrote:
| That's correct. SQLite has an impressive test suite that
| makes it very stable. That's why it's used in millions of
| Android devices or will be used in Cloudflare's edge with
| D1, for example.
| josephg wrote:
| The downside of that is that application developers would
| have to use the minimum supported version of SQLite.
|
| 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)
| hochmartinez wrote:
| We have the same problem with the library in the OS. And
| with the default compilation, SQLite usually works fine.
| kevingadd wrote:
| This would provide more ways to fingerprint users. It's also
| not possible to guarantee no sqlite update will ever change
| observable behavior, even if it happens to mostly be true
| now.
| thesuperbigfrog wrote:
| "Folks have been building sqlite3 for WASM since at least as far
| back as 2019, but this subproject is the first effort
| "officially" associated with the SQLite project, created with the
| goal of making WASM builds of the library first-class members of
| the family of supported SQLite deliverables."
|
| Awesome news. SQLite is already a defacto cross-platform file
| format. An official WASM release will be widely welcomed and
| extremely useful.
| lovasoa wrote:
| SQLite was one of the first large C code bases to be ported to
| the web. Alon Zakai (@kripken, the creator of emscripten), made
| the first commit to sql.js at the start of 2012 [0]. I got
| involved two years later, and have been maintaining sql.js
| since. A lot was added since 2012, but the initial core api
| with 3 functions 'open', 'exec', and 'close' still works today.
|
| https://github.com/kripken/sql.js/commit/cebd80648dbd369b348...
| exelib wrote:
| Amazing! 313kb gzipped. I hope the author(s) of IndexedDB would
| finally understand how stupid the idea of IDB was.
|
| Are there any numbers on performance?
| nilslice wrote:
| very cool to look at the unpacked module, in all its wasm glory
| https://whatsinyourwasm.com/module/6 (warning: not mobile
| friendly...)
| alberth wrote:
| Will this help Cloudflare, Fly.io and others who started offering
| SQLite-like of offerings in the cloud?
| hn2017 wrote:
| Excited about this news, this the kind of change the modern web
| needs.
| boppo1 wrote:
| Can you help me understand how/why this is a big change the web
| needs? I'm not seeing the value proposition on a database-in-a-
| webpage
| Scarbutt wrote:
| For the same reason many complex desktop applications use
| sqlite or some other database.
| exelib wrote:
| My use case is an offline-capable app. There was already
| WebSQL, but it got deprectaed.
| chasd00 wrote:
| > My use case is an offline-capable app.
|
| not trying to be snarky but do you mean a desktop app?
| EvanAnderson wrote:
| Offline-capable apps on phones are "a thing". There are
| plenty of times when I have poor (or nonexistent)
| cellular coverage and would love web-based apps to work.
| hot_gril wrote:
| A desktop app that's cross-platform.
| hochmartinez wrote:
| It depends on you use case but, for example, if your db is
| not super large you could dowload it and use it locally
| istead of having to connect to an app server for every query
| or transaction. You could synchronize in the background with
| the app server. The app speed and responsiveness would be way
| better.
|
| 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... :-)
| ibash wrote:
| If you want to use sqlite in the browser today (chrome only) take
| a look at websql.
| samwillis wrote:
| WebSQL is dead, long live WASM SQLite.
|
| 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.
| resoluteteeth wrote:
| chrome is presumably eventually going to remove websql.
|
| 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.
| samwillis wrote:
| > ... support persistent client-side storage using available JS
| APIs. As of this writing, that includes the Origin-Private
| FileSystem (OPFS) [0]
|
| 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...
|
| 1: https://github.com/jlongster/absurd-sql
|
| 2: https://www.sqlite.org/sessionintro.html
| tantaman wrote:
| > What I really want to see next is an eventually consistent
| sync system between browser and server
|
| 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.
| thruflo wrote:
| > What I really want to see next is an eventually consistent
| sync system between browser and server
|
| We've made one: https://electric-sql.com
|
| Active-active SQLite to Postgres with transactional causal+
| consistency based on CRDTs.
|
| Disclaimer: founder.
| samwillis wrote:
| That is awesome! I'm going to have to play with that.
|
| 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.
| thruflo wrote:
| Very cool! Would love to swap ideas if you're up for a chat
| :)
| chasd00 wrote:
| > WASM SQLite with the OPFS is the future of offline first web
| app development.
|
| 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?
| Existenceblinks wrote:
| > persistent
|
| Question: is it really persisted indefinitely? And will _this_
| SQLite file be portable (import and export to somewhere else) ?
| harryvederci wrote:
| I can already imagine the youtube video titles:
|
| "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.
| dyml wrote:
| What features will this unlock for you?
| harryvederci wrote:
| Serving my htmx app as a WASM app, so interaction by users
| will be done on their computer. Then only the DB changes
| will be have to be synced with the backend, saving a lot of
| traffic / server capacity. This was already possible, but
| then the user's changes would be lost if they don't sync in
| time. If I understand things correctly this is the missing
| piece: persistence. So if the user goes offline and makes
| changes, those will persist so they can be synced when the
| user goes online again.
|
| (All of this is as far as I understand things, I could be
| completely wrong.)
| harryvederci wrote:
| I couldn't find a link to the source code on that page, I think
| this is it:
|
| https://sqlite.org/wasm/tree
|
| 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-mode
___________________________________________________________________
(page generated 2022-10-28 23:00 UTC)