12 ms·

Sqlite3 WebAssembly

647 points by whatever3 2y ago

dang-lover 2y ago
[flagged]
- szundi 2y ago
  We like that you like it
- bsimpson 2y ago
  And we loved you in Lethal Weapon.
- akutlay 2y ago
  [flagged]
TiredGuy 2y ago
So after downloading from the official downloads page and stripping away all the mjs files and "bundler-friendly" files, a minimal sqlite wasm dependency will be about 1.3MB. For an in-browser app, that seems a bit much but of course wasm runs in other places these days where it might make more sense.
- jt2190 2y ago
  The thing to keep in mind is that the WebAssembly sandbox model means that in theory the program (SqlLite in this case) can run wherever it makes sense to run it. That might mean running it locally or it might mean running on a central server or it might mean running nearby on the “edge”.
- flockonus 2y ago
  It's a good consideration, together with the fact browsers already have IndexedDB embedded. One use case still for in-browser apps like Figma / Photoshop-like / ML apps, where the application code and data is very big anyway, 1.3Mb may not add that much Also worth considering parsing of wasm is significantly faster than JS (unfortunately couldn't find the source for this claim, there is at lease one great article on the topic) https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_API https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_A...
  - aidos 2y ago
    When we built our frontend sync system we tried a few different options. We had a fairly simple case of just trying to store entities so we could pull incremental updates since you were last online. The one we ran in production for a while was IndexedDB but found the overhead wasn’t worth it. I played around with warm sqlite too. That was really nice but I decided against it due to the fact that it was totally unsupported.
- jsheard 2y ago
  It's pretty compressible at least, sqlite3.js+wasm are 1.3MB raw but minifying the JS and then compressing both files with Brotli gets them down to 410KB.
  - rmbyrro 2y ago
    A lot of HTML's nowadays have 100 - 300 kb. That's only the HTML (!!). Adding 400 for such a high quality piece of DB actually borders reasonability. And makes me think: what the hell are frontend devs thinking!? Multiple MB's in JS for a news website. Hundreds of KB's for HTML. It's totally unreasonable.
    - wahern 2y ago
      > what the hell are frontend devs thinking!? Multiple MB's in JS for a news website. Hundreds of KB's for HTML. It's totally unreasonable They're thinking, "adding [some fraction of existing total payload] for such a high quality [feature] actually borders reasonability". Wash. Rinse. Repeat.
      - Dylan16807 2y ago
        > They're thinking, "adding [some fraction of existing total payload] for such a high quality [feature] actually borders reasonability". Wash. Rinse. Repeat. Context makes all the difference here. If you're considering a big chunk of size for a relational database engine, you need to ask: are you making a complex application, or a normal web page? If it's the latter, then it's not reasonable at all. And anything that makes the HTML itself that big is almost certainly bloat, not "high quality", and shouldn't be used in any context.
      - rmbyrro 2y ago
        You're comparing 2 Mb of useless and broken animated scrolling with 400 Kb of SQLite, which tells me you have no idea what's behind SQLite's 400 kb. High quality software is something I rarely see nowadays when browsing "modern" websites.
    - jsheard 2y ago
- coder543 2y ago
  1.3MB seems perfectly reasonable in a modern web app, especially since it will be cached after the first visit to the site. If you’re just storing user preferences, obviously don’t download SQLite for your web app just to do that… but if you’re doing something that benefits from a full database, don’t fret so much about 1MB that you go try to reinvent the wheel for no reason. If the other comment is correct, then it won’t even be 1.3MB on the network anyways.
  - telotortium 2y ago
    A megabyte here, a megabyte there, pretty soon you’re talking about a really heavyweight app.
    - zdragnar 2y ago
      Given how hefty images are, a full database doesn't seem too bad for the purpose of an "app" that would benefit from it, especially when compression can being the size down even lower.
    - littlecranky67 2y ago
      We are past the stage where every piece of JS has to be loaded upfront and delay the first meaningful paint. Modern JS frameworks and module are chunked and can be eager/lazy loaded. Unless you make the sqlite DB integral part for your first meaningful page load, preloading those 1.3MB in the background/upon user request is easy.
    - Dylan16807 2y ago
      By the time you have a good reason to add this library, I think you're already in heavyweight app territory.
- ncruces 2y ago
  For server side, you'll likely need a different build of Wasm SQLite, that handles concurrency (and file locking) differently. Also, WASI is very far from answer (so far). The SQLite amalgamation builds fine for WASI but concurrency is an unsolved issue. I had to build a VFS from scratch to get my Wasm based SQLite driver into a usable shape. https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.md https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.m...
- hawski 2y ago
  Is there a way to statically compile an application with SQLite and the result WASM was smaller. So for example I have an app that would use only a specific subset of SQLite. Could the SQLite's WASM be built with this in mind cutting down on code that is not used? Or is there a way to prune it having the used API surface? In a regular compiler/linker scenario it would just be a static compilation. Here we have a JS app and WASM library.
  - hoten 2y ago
    Since SQL takes arbitrary strings as input, this would require explicit compiler flags to disable the knobs you don't want. Can't rely on excluding unused symbols really.
  - sgbeal 2y ago
    > Could the SQLite's WASM be built with this in mind cutting down on code that is not used? The pending 3.47 release has some build-side tweaks which enable a user to strip it down to "just the basics," but we've not yet been able to get it smaller than about 25-30% less than it otherwise is: cd ext/wasm make barebones=1 ; # requires GNU Make and the Emscripten SDK Doing that requires building it yourself - there are no plans to publish deliverables built that way. The build process also supports including one's own C code, which could hypothetically be used to embed an application and the wasm part of the library (as distinct from the JS part) into a single wasm file. Its primary intended usage is to add SQLite extensions which are not part of the standard amalgamation build. > Or is there a way to prune it having the used API surface? Not with the provided JS pieces. Those have to expose essentially the whole C library, so they will not be pruned from the wasm file. However, you could provide your own JS bindings which only use a small subset of the API, and Emscripten is supposedly pretty good about stripping out C-side code which neither explicitly exported nor referenced anywhere. You'd be on your own - that's not something we'll integrate into the canonical build process - but we could provide high-level support, via the project's forum, for folks taking that route.
    - sgbeal 2y ago
      Correction: make barebones=1 ; # requires GNU Make and the Emscripten SDK should be: make oz barebones=1 ; # requires GNU Make and the Emscripten SDK otherwise it will build with -O0, resulting in huge wasm files.
- deskr 2y ago
  Sadly, 1.3 MB is nothing on the modern web, especially for a static file. BBC's frontpage loads 3.78 MB. https://www.bbc.co.uk/ https://www.bbc.co.uk/
  - sgbeal 2y ago
    > BBC's frontpage loads 3.78 MB. FWIW: Google Drive just downloaded 15.4mb to boot up for me and imdb dot com hit some 7+mb before it started auto-loading videos on top of that.
- pdyc 2y ago
  That's correct, people in this thread are comparing single compressed dependency of sqlite+wasm of 400KB to the total size of web pages which run in MB. I did some actual tests while trying to use sqlite and it does adds noticeable delay on first page load on mobile due to big size+decompression+ additional scaffolding of wasm. Pages that run into MB have small files that are downloaded concurrently so the delay is not noticeable. I wrote about this and my other expriments with in browser db in my last article but it did not get any traction here.
simonw 2y ago
Slight point of confusion: that page says: > These components were initially released for public beta with version 3.40 and will tentatively be made API-stable with the 3.41 release, pending community feedback. But the most recent release of SQLite is 3.46.1 (from 2024-08-13) Presumably they are now "API-stable" but the page hasn't been updated yet. It would be great if the SQLite team published an official npm package bundling the WASM version, could be a neat distribution mechanism for them. (UPDATE: They do, see replies to this post.) My favourite version of SQLite-in-WASM remains the Pyodide variant, which has been around since long before the official SQLite implementation. If you use Pyodide you get a WASM SQLite for free as part of the Python standard library - I use that for https://lite.datasette.io/ https://lite.datasette.io/ and you can also try it out on https://pyodide.org/en/stable/console.html https://pyodide.org/en/stable/console.html import sqlite3 print(sqlite3.connect(':memory:').execute( 'select sqlite_version()' ).fetchall()) That returns 3.39.0 from 2022-06-25 so Pyodide could do with a version bump. Looks like it inherits that version from emscripten: https://github.com/emscripten-core/emscripten/blob/main/tools/ports/sqlite3.py https://github.com/emscripten-core/emscripten/blob/main/tool...
- rblank 2y ago
  https://github.com/sqlite/sqlite-wasm https://github.com/sqlite/sqlite-wasm sqlite-wasm loads much faster than Pyodide, so if you don't need Python, then the former is a better choice.
  - simonw 2y ago
    Amazing! npm install @sqlite.org/sqlite-wasm
    - wg0 2y ago
      This would run on client side I presume? Where the data would go? Okay that's listed here: https://sqlite.org/wasm/doc/trunk/persistence.md https://sqlite.org/wasm/doc/trunk/persistence.md EDIT: Self answered.
    - simonw 2y ago
outlore 2y ago
i’ve been looking for a Tanstack Query style library that is backed by Sqlite (backed by OPFS or some other browser storage) and syncs with an API in the background. Does anything like that exist? i’ve seen ElectricSQL and other sync engines but they are a bit opinionated. I’m pretty new to local-first but i feel like the developer ergonomics are not quite there yet Meanwhile for “local-only” it would be great to use sqlite in the browser + native file system API so that the db could be stored on the user’s file system and we wouldn’t have to worry about browser storage eviction. i think that could really open up a whole world of privacy preserving offline software delivered through the browser
- netghost 2y ago
  ElectricSQL and friends seem to be the best option so far, but they all come with a lot of caveats. It feels like local-first is near, and it's so tantalizing, but I haven't seen anything that feels like it's done enough to build on just yet.
- ochiba 2y ago
  Not sure if you've looked at PowerSync yet: https://www.powersync.com/ https://www.powersync.com/ (I'm on the team) For the read path it hooks into Postgres logical replication or MongoDB change streams (and MySQL binlog soon). It supports partial syncing using declarative rules. For the write path, it allows writing to the local SQLite database and also places writes into an upload queue, and then uses a developer-defined function to upload writes to the backend API. We did a deep dive on current options for SQLite on the web, and are currently using an IndexedDB-based VFS, and looking to move to OPFS: https://www.powersync.com/blog/sqlite-persistence-on-the-web https://www.powersync.com/blog/sqlite-persistence-on-the-web We recently released an integration with TanStack Query to allow leveraging some of its features in conjunction with PowerSync: https://docs.powersync.com/client-sdk-references/js-web/javascript-spa-frameworks https://docs.powersync.com/client-sdk-references/js-web/java... > Meanwhile for “local-only” it would be great to use sqlite in the browser + native file system API so that the db could be stored on the user’s file system and we wouldn’t have to worry about browser storage eviction. i think that could really open up a whole world of privacy preserving offline software delivered through the browser Agreed. This is a limitation of IndexedDB and OPFS as persistent browser storage currently
gnarbarian 2y ago
How long until we see WebAssembly/WebGPU become a platform independent choice for deploying server side code as well?
- ruined 2y ago
  as soon as wasi is settled
  - gnarbarian 2y ago
    https://wasi.dev/ https://wasi.dev/ wow I didn't know this was a thing. thanks for filling me in!
- paulddraper 2y ago
  Yesterday? There's a number of WASM platforms/tools: Wasmer, wasmCloud, a few others that escape my memory.
  - jt2190 2y ago
    https://wasmer.io/ https://wasmer.io/ https://wasmcloud.com/ https://wasmcloud.com/ https://wasmtime.dev/ https://wasmtime.dev/
- stackskipton 2y ago
  SRE here, it's currently happening in a few parts but overall, it's not as attractive on server side. Server Side code running is mostly a solved problem and for very few organizations, the benefits of WASM don't outweigh any difficulties in getting it running.
  - 6gvONxR4sf7o 2y ago
    > Server Side code running is mostly a solved problem I know what you mean here, but I think we're very limited in what we tend to run. Polyglot programming still isn't really a thing, and with things like WASI standardized (someday soon I hope), I could imagine it becoming a lot nicer.
me551ah 2y ago
After years of being able to run SQLite on my mobile phone, my tv, my router and gaming consoles, I can finally run it on my browser. Which also happens to be running on the most powerful machine I own
- ibash 2y ago
  surprise! it's been there for decades: https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database
  - adregan 2y ago
    It was there for a decade: https://caniuse.com/sql-storage https://caniuse.com/sql-storage
    - joemi 2y ago
      I wonder why it was unmaintained/dropped. Was there something wrong with it, and if so, would that also apply to this kind of wasm implementation?
      - debugnik 2y ago
        Mozilla refused to support it because then every implementation would have simply used SQLite, which would have promoted any implementation details to a de facto standard. (Even caniuse erroneously describes the feature as "allows SQLite database queries".) From the latest spec [1]: > 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/ https://www.w3.org/TR/webdatabase/ This won't be a problem for wasm SQLite because it isn't a standard being shipped by browsers, just another dependency.
        xyc 2y ago
        i have a feeling that it set back the web by a decade https://x.com/chxy/status/1822858746307170640 https://x.com/chxy/status/1822858746307170640
koeng 2y ago
For use in Golang, I really like ncruces wasm SQLite package - https://github.com/ncruces/go-sqlite3 https://github.com/ncruces/go-sqlite3 . Unlike cznic's go package (which is great, btw), the wasm version works well on OpenBSD and the like.
- ncruces 2y ago
  Author here. If you're interested, do ask questions.
  - TN1ck 2y ago
    Very cool project! Do you know if this would be possible for duckdb? Is there something about sqlites APIs and wasm build that made it feasible? Context: Currently using go-duckdb and while it's working for us, getting rid of cgo would be a huge help. Would be quite interested myself to attempt this.
    - ncruces 2y ago
      I don't know much about DuckDB's architecture. Wasm is fine for compute (though concurrency is still a somewhat open question). To have Wasm talk to the outside world, you need “host calls” where the guest calls the host. On a browser that's Wasm calling JavaScript. On my Go driver, it's Wasm calling Go. For server side, there's also a standard set of “host calls” modeled around POSIX/Linux syscalls called WASI. I could've build my project around WASI, but WASI is rather limited (and SQLite support for WASI was more limited even, it's improved a bit since). DuckDB might work out-of-the-box this way. I, instead, took advantage of SQLite's architecture and replaced its VFS layer with one in Go: https://sqlite.org/vfs.html https://sqlite.org/vfs.html So SQLite in Wasm is just doing compute, and I do all the OS level stuff in Go. No need for Wasm concurrency, cause I can load multiple instances of my Wasm which act like independent OS processes that communicate through the filesystem (SQLite excels at this). As I said, I dunno how well all those decisions would map to DuckDB.
      - koeng 2y ago
brandonpollack2 2y ago
I was trying to get this working in a rust ecosystem some time ago but none of the blessed.rs sql (rusqlite, sqlx) wrappers seem to take advantage of it yet and wrapping it yourself is a bit tricky since when I was trying I couldn't figure out a way to to get emscripten wasm code to play nice with wasm32-unknown-unknown without some kind of JS wrapper which then requires implementing the interface those crates expect and exposing it from JS. Once that is done in rust itll be great there too!
- tonygiorgio 2y ago
  Yeah I’ve been waiting awhile for this myself. A few PRs with work pending for a year or so. I’ve seen some proof of concepts but nothing anywhere close to usable.
  - insipx 2y ago
    you should check out https://github.com/xmtp/diesel-wasm-sqlite https://github.com/xmtp/diesel-wasm-sqlite reliable so far, being dogfooded in production as we speak
    - tonygiorgio 2y ago
      Sorry just saw this. Looks promising, I love diesel! Haven’t looked at the landscape in a few months and don’t work on the project that needed it anymore but very cool to see. Even uses OPFS!
- aabhay 2y ago
  I have been working on one. If you're interested in working on it or contributing, feel free to chime in here: https://github.com/rhashimoto/wa-sqlite/discussions/154 https://github.com/rhashimoto/wa-sqlite/discussions/154 This essentially requires that we import the sqlite emscripten build via an extern C header in wasm bindgen, and then we need to re-implement the VFS in rust while compiling it in multi-threaded mode to allow for shared array buffer access. After that is all done, we will be able to access SQLite rows as raw wasm bytes. That gives us the ability to implement a rust-sqlite style wrapper or integration. There would still not be some of the niceties such as connection pooling, but in wasm you likely want to use the db in exclusive mode.
jjcm 2y ago
As a general question, in what scenarios is it more beneficial to send the full DB and let the browser handle the queries? Maybe phrased a better way - when would I use this to improve a user experience over the traditional server-hosted db model?
- bryanrasmussen 2y ago
  >when would I use this to improve a user experience over the traditional server-hosted db model? just my intuition when I read the headline of this post - something like the interplay between PouchDB and CouchDB for offline first apps https://medium.com/offline-camp/couchdb-pouchdb-and-hoodie-as-a-stack-for-progressive-web-apps-a6078a985f18 https://medium.com/offline-camp/couchdb-pouchdb-and-hoodie-a...
- harrisi 2y ago
  For offline use it can be good when dealing with large amounts of data. Anything from like an audio library to 3D modeling software. Changes can be made locally and persisted and then you can sync things server side regularly or when online again.
- ThatPlayer 2y ago
  Personally I'm using it for a statically hosted website, so a server-hosted database was never an option. Also with the right driver, it's possible to stream the chunks of the database as needed rather than sending the full database: https://github.com/mmomtchev/sqlite-wasm-http https://github.com/mmomtchev/sqlite-wasm-http I can even do Sqlite's full text search without downloading the entire FTS database. Just most of it, if the search term is short enough.
- pdyc 2y ago
  i am creating host of dashboards which directly talk to different services with very little data on my own server that is used for access control and token management only so actual data never comes to my servers. This kind of app is a good candidate for client side embedded db.
simonw 2y ago
Something that would be really fun would be to run SQLite in-memory in a browser but use the same tricks as Litestream and Cloudflare Durable Objects (https://simonwillison.net/2024/Oct/13/zero-latency-sqlite-storage-in-every-durable-object/ https://simonwillison.net/2024/Oct/13/zero-latency-sqlite-st...) to stream a copy of the WAL log to a server (maybe over a WebSocket, though intermittent fetch() POST would work too). Then on subsequent visits use that server-side data to rehydrate the client-side database. From https://sqlite.org/forum/info/50a4bfdb294333eec1ba4749661934521af19e6fc0790a6189696607f67c2b54?t=h https://sqlite.org/forum/info/50a4bfdb294333eec1ba4749661934... is looks like WAL mode is excluded from the default SQLite WASM build so you would have to go custom with that.
- dustinchilson 2y ago
  Are you thinking something like https://electric-sql.com/ https://electric-sql.com/
  - PUSH_AX 2y ago
    What’s the catch with this thing?
    - T-Winsnes 2y ago
      The security model is challenging, as it relies on Postgres users for iam. Your users essentially log directly into your db
      - dumbo-octopus 2y ago
        Isn’t Postgres a fairly capable IAM provider, all things considered? I’d their access control mechanisms at least as much as a run of the mill external backend’s.
        T-Winsnes 2y ago
        For basic auth it works well, but the challenge comes when you need to integrate with oidc, need to enforce mfa, enable sso etc. session invalidation is also quite complicated. You need an identity middle man in front of the Postgres identity to tackle these and validate that the session is still active. Last time I looked at electric it was a big challenge to integrate such a service. This might have improved since then however
baq 2y ago
See also https://github.com/electric-sql/pglite https://github.com/electric-sql/pglite (REPL at https://pglite.dev/repl/ https://pglite.dev/repl/) (Previously discussed 7 months ago: https://news.ycombinator.com/item?id=39477457 https://news.ycombinator.com/item?id=39477457)
bhelx 2y ago
I used the wasm build of sqlite and the Chicory runtime to create a pure JVM executed sqlite library: https://github.com/dylibso/sqlite-zero https://github.com/dylibso/sqlite-zero It's more of an experiment than an attempt to make something production ready, though I could see it being useful to bring dependency-less sqlite tooling to the JVM ecosystem.
- ncruces 2y ago
  What's the file system access like, WASI?
  - bhelx 2y ago
    Chicory has some partial wasip1 support. https://github.com/dylibso/chicory/tree/main/wasi https://github.com/dylibso/chicory/tree/main/wasi. We use jimfs to keep things simple and secure (and not worry about exposing the real filesystem): https://github.com/google/jimfs https://github.com/google/jimfs When I did this experiment a few months ago, what we could accomplish was pretty limited. I could load and query databases, but not write to them. However the Chicory wasip1 implementation is advancing. BTW, we've borrowed a few ideas from wazero so thanks for your work there :)
    - ncruces 2y ago
      If the goal is to improve Chicory WASI support, this is the way. If the goal was pure Java SQLite¹, a VFS from scratch would be better. I think since I started my Go/wazero effort, WASI+SQLite improved a bunch. I had to start with the demo VFS; the Unix VFS now builds. But custom VFS is still the way to go, IMO. And thanks! My contributions to wazero were tiny. Best of luck with Chicory! 1: strong NestedVM vibes here; 11 years ago… gosh, I feel old now. https://stackoverflow.com/questions/18186507/pure-java-vs-native-sqlitejdbc-driver-and-nested-vm https://stackoverflow.com/questions/18186507/pure-java-vs-na...
      - bhelx 2y ago
        > If the goal was pure Java SQLite¹, a VFS from scratch would be better. agreed, though this was more an experiment to test Chicory once we built initial wasi support. I'd love to see it picked up and improved. I think that's the direction I'd go if i want some kind of production ready library.
benthecarman 2y ago
Whats needed is a rust-wasm compatible library that can use this.
- aabhay 2y ago
  If you're interested in contributing to that, here is a good place to start (in early discussion stages): https://github.com/rhashimoto/wa-sqlite/discussions/154 https://github.com/rhashimoto/wa-sqlite/discussions/154
catapart 2y ago
I wasn't able to tell from a quick look through the page: could someone help me understand the use cases here? More specifically, would this be able to be a "replacement" for indexedDB? Does the data persist, or do I need to keep the sqlite file in the filesytemAPI (or indexedDB/localstorage) myself?
- azangru 2y ago
  From the about page: > Specific Goals of this Project > Insofar as possible, support persistent client-side storage using available JS APIs. As of this writing, that includes the Origin-Private FileSystem (OPFS) and (very limited) storage via the window.localStorage and window.sessionStorage backend.
  - catapart 2y ago
    Right but, to my eyes, that's vague? What I'm asking is if I need to manage the sqlite file, as I would on an OS's file system, or if accessing the sqlite library will automatically persist that data to those web-native storages, like the way indexedDB doesn't require me to load an "idb" file and then "save" or "commit" that save. I just access it and write. To be clear: I'm not asking academically. I wrote a whole library for managing data in indexedDB for local-first apps, and while it works well enough for what I need, it's iDB so it's subject to data deletion (not common, but allowed in the spec if necessary), and it's a pain to work with just because of its nature and API. So I've been waiting to move to sqlite for a while with the only holdbacks being "is it too heavy?", and "how much has to change?". With WASM, I think we're about as lightweight as its going to get. So I'm just curious if this aims to be a drop-in replacement, or if it still expects you to use it like sqlite on a native platform.
    - sgbeal 2y ago
      > Right but, to my eyes, that's vague? We (the sqlite project, where the "vague" description comes from) do not define the use cases. Similarly, in the docs for the C library you won't find any more than passing references to specific use cases, and those are typically contrived for the sake of example. (One notable exception: <https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html>) > What I'm asking is if I need to manage the sqlite file, as I would on an OS's file system, or if accessing the sqlite library will automatically persist that data to those web-native storages, like the way indexedDB doesn't require me to load an "idb" file and then "save" or "commit" that save. I just access it and write. That's all covered in the docs (of which there are well more than 100 lovingly-hand-written pages), but the short answer is "it just works." You have the _option_ of importing and exporting databases from the browser-native storage, but you don't have to. For starters, see: <https://sqlite.org/wasm/doc/tip/persistence.md https://sqlite.org/wasm/doc/tip/persistence.md>
parhamn 2y ago
WebSQL should've just been Sqlite and the whole offline-first (and general app storage) ecosystem would've been so much nicer. Is there any hope of that happening? Instead of abstracting and over specifying sqlite, can the spec just specify a version of the SQLite API browsers should support and roll the version periodically?
- emn13 2y ago
  "Rolling the version periodically" is probably quite problematic for browsers. Kind of a key point of the web is that stuff if at all possible keeps working. Breaking changes like that are hard. Even if the spec just listed occasional version and the webpage could choose which one; that means a potentially tricky maintenance burden on browser to support old versions of a potentially no longer supported sqlite, and each version is another megabyte. Why not then just choose this solution, and let each website pick its own poison? If the concern is the repeated downloads of common resources, well, we've accepted that for other CDN's too, and a solution for shared caching of common dependencies would in any case be more valuable than merely for sqlite. The current approach seems better than a browser-provided version.
  - justin66 2y ago
    Versioning was never really an issue. It's worth pointing out that Richard Hipp committed to creating and maintaining a flag in SQLite that would force SQLite to use whatever subset of SQL the WebSQL people settled on for their API. That would have of course worked independently of the SQLite version. (He also offered to write the SQL part of the spec.) https://news.ycombinator.com/item?id=15670808 https://news.ycombinator.com/item?id=15670808 I thought the reasons given for not moving forward with standardizing WebSQL and using a SQLite implementation were (and undoubtedly still are) very, very stupid so I'm not the right person to represent them here.
- simonw 2y ago
  I for one am glad WebSQL didn't establish itself. Now we get the most recent version of SQLite when we need it as a 410KB compressd WASM blob, as opposed to being stuck on browser-mandated versions of SQLite that might even be a decade old at this point.
chrysoprace 2y ago
I've been really interested in the local-first landscape lately but embedding SQLite seems really heavy-weight compared to using the browser's built-in storage APIs (in particular, IndexedDB) and it seems to be what most of the main open source libraries do. I'm interested to see a open-source solution (with sync) which provides an SQLite-like API but for the browser's native storage rather than trying to embed another executable in Web Assembly.
- jamesgpearce 2y ago
  Disclaimer: I'm the author. But you might be interested in TinyBase.
  - chrysoprace 2y ago
    Thanks I'll give it a look! I guess I really was fishing for someone to make a recommendation and I see you have a lot of backend persistence options which I'm very excited about. Another issue I have with a lot of the new local first products is that they tend to lock you into a particular database type on the backend, so this is refreshing.
  - arunaugustine 2y ago
    TinyBase looks very promising! Is there a doc or reference you can guide me to, for using TinyBase with Preact.js instead of React?
  - smallerfish 2y ago
    Nice work. Does it take time to hydrate tinybase from indexeddb, or do you mount it directly? What are your thoughts about search over the store? Hydrate a separate lunr index? And finally has anybody tried using your syncing over webrtc?
- gavmor 2y ago
  SQL is, arguably, more ergonomic than IndexedDB APIs, and may take up less RAM/CPU when, eg, using a `WHERE` clause rather than an `if() ` filter.
runarberg 2y ago
I’m working on a hobby-project that uses IndexedDB for persistent client-side storage, and it really feels like W3C made some very bad design decision and than instead of fixing they they have just given up on the standard. Issues like not being able to index values in objects in arrays [1] (not even in fixed position e.g. "key.path.[0].value") despite almost a decade of developers asking for it, a very limited query syntax, and even the documentation on MDN seems of very lower quality than the rest of the web docs. I’m happy that we are actually be able to use SQL in the browser now (although I would rather skip the MBs of the bundle bloat). But I feel like the standards committee will now have even less of a reason to fix the very broken state of IndexedDB. 1: https://github.com/w3c/IndexedDB/issues/35 https://github.com/w3c/IndexedDB/issues/35
baudaux 2y ago
I definitely have to put sqlite in https://exaequOS.com https://exaequOS.com
koolala 2y ago
The CORS restrictions / needing SharedArrayBuffer support kinda stinks. There is no way to use Sqlite3 off-thread without memory sharing? Couldn't postMessage work to pass data to the sqlite thread by using the third Transfer argument? Would postMessage transfer allow memory to be stored in a sqlite wasm database running a worker off-thread? Refering to this implementation's docs: https://github.com/sqlite/sqlite-wasm https://github.com/sqlite/sqlite-wasm
- sgbeal 2y ago
  > The CORS restrictions / needing SharedArrayBuffer support kinda stinks. We have no CORS restrictions but one specific (and optional) VFS requires COOP/COEP for SharedArrayBuffer. If SharedArrayBuffer isn't available, that VFS won't load, but the rest of the library will plod along just fine: https://sqlite.org/wasm/doc/tip/persistence.md https://sqlite.org/wasm/doc/tip/persistence.md
  - koolala 2y ago
    Persistence is the whole point to me for a Database. OPFS finally adding a filesystem to the browser made seem like the web file database situation could finally standardize but COOP/COEP ruins it.
    - eirikhm 2y ago
      https://github.com/rhashimoto/wa-sqlite https://github.com/rhashimoto/wa-sqlite
    - sgbeal 2y ago
      > OPFS finally adding a filesystem to the browser made seem like the web file database situation could finally standardize but COOP/COEP ruins it. We have two OPFS-based VFSes. One requires COOP/COEP and one does not. Each makes feature trade-offs, though, so they're not equivalent. We also offer persistence via localStorage and sessionStorage, so OPFS is not (for small databases) required for persistence.
k__ 2y ago
Half-OT: What's your opinion on SQLite in-memory vs plain objects/arrays? When would you use which and why?
kohlerm 2y ago
WASM ATM is IMHO most useful for VSCode Extension, where it can help to avoid the dependency nightmare that nodejs modules with native code cause.
deleted 2y ago
[deleted]
deleted 2y ago
[deleted]
throwaway81523 2y ago
Why have this instead of making it a browser API?
mococa 2y ago
It would be great if Go had a WebAssembly runtime with simple interoperability, so I could stop using CGO (I need to use it because SQLite in Go depends on CGO. There’s a Go version, but I don’t trust using transpiled code).
emrah 2y ago
Fwiw, duckdb is replacing sqlite for me, particularly due to its rich plugin ecosystem
shautvast 2y ago
Shameless plug for the fastest way to get the serverdata to the client: just send data in the format that sqlite itself uses: https://gitlab.com/sander-hautvast/sqlighter https://gitlab.com/sander-hautvast/sqlighter (available in java and rust, no dependencies on sqlite itself). This works well with the wasm build. The java project contains a demo that also shows how to setup the UI code.

Sqlite3 WebAssembly

647 points by whatever3 2y ago

dang-lover 2y ago

[flagged]

szundi 2y ago
We like that you like it
bsimpson 2y ago
And we loved you in Lethal Weapon.
akutlay 2y ago
[flagged]

TiredGuy 2y ago

So after downloading from the official downloads page and stripping away all the mjs files and "bundler-friendly" files, a minimal sqlite wasm dependency will be about 1.3MB. For an in-browser app, that seems a bit much but of course wasm runs in other places these days where it might make more sense.

jt2190 2y ago
The thing to keep in mind is that the WebAssembly sandbox model means that in theory the program (SqlLite in this case) can run wherever it makes sense to run it. That might mean running it locally or it might mean running on a central server or it might mean running nearby on the “edge”.
flockonus 2y ago
It's a good consideration, together with the fact browsers already have IndexedDB embedded. One use case still for in-browser apps like Figma / Photoshop-like / ML apps, where the application code and data is very big anyway, 1.3Mb may not add that much Also worth considering parsing of wasm is significantly faster than JS (unfortunately couldn't find the source for this claim, there is at lease one great article on the topic) https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_API https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_A...
- aidos 2y ago
  When we built our frontend sync system we tried a few different options. We had a fairly simple case of just trying to store entities so we could pull incremental updates since you were last online. The one we ran in production for a while was IndexedDB but found the overhead wasn’t worth it. I played around with warm sqlite too. That was really nice but I decided against it due to the fact that it was totally unsupported.
jsheard 2y ago
It's pretty compressible at least, sqlite3.js+wasm are 1.3MB raw but minifying the JS and then compressing both files with Brotli gets them down to 410KB.
- rmbyrro 2y ago
  A lot of HTML's nowadays have 100 - 300 kb. That's only the HTML (!!). Adding 400 for such a high quality piece of DB actually borders reasonability. And makes me think: what the hell are frontend devs thinking!? Multiple MB's in JS for a news website. Hundreds of KB's for HTML. It's totally unreasonable.
  - wahern 2y ago
    > what the hell are frontend devs thinking!? Multiple MB's in JS for a news website. Hundreds of KB's for HTML. It's totally unreasonable They're thinking, "adding [some fraction of existing total payload] for such a high quality [feature] actually borders reasonability". Wash. Rinse. Repeat.
    - Dylan16807 2y ago
      > They're thinking, "adding [some fraction of existing total payload] for such a high quality [feature] actually borders reasonability". Wash. Rinse. Repeat. Context makes all the difference here. If you're considering a big chunk of size for a relational database engine, you need to ask: are you making a complex application, or a normal web page? If it's the latter, then it's not reasonable at all. And anything that makes the HTML itself that big is almost certainly bloat, not "high quality", and shouldn't be used in any context.
    - rmbyrro 2y ago
      You're comparing 2 Mb of useless and broken animated scrolling with 400 Kb of SQLite, which tells me you have no idea what's behind SQLite's 400 kb. High quality software is something I rarely see nowadays when browsing "modern" websites.
  - jsheard 2y ago
coder543 2y ago
1.3MB seems perfectly reasonable in a modern web app, especially since it will be cached after the first visit to the site. If you’re just storing user preferences, obviously don’t download SQLite for your web app just to do that… but if you’re doing something that benefits from a full database, don’t fret so much about 1MB that you go try to reinvent the wheel for no reason. If the other comment is correct, then it won’t even be 1.3MB on the network anyways.
- telotortium 2y ago
  A megabyte here, a megabyte there, pretty soon you’re talking about a really heavyweight app.
  - zdragnar 2y ago
    Given how hefty images are, a full database doesn't seem too bad for the purpose of an "app" that would benefit from it, especially when compression can being the size down even lower.
  - littlecranky67 2y ago
    We are past the stage where every piece of JS has to be loaded upfront and delay the first meaningful paint. Modern JS frameworks and module are chunked and can be eager/lazy loaded. Unless you make the sqlite DB integral part for your first meaningful page load, preloading those 1.3MB in the background/upon user request is easy.
  - Dylan16807 2y ago
    By the time you have a good reason to add this library, I think you're already in heavyweight app territory.
ncruces 2y ago
For server side, you'll likely need a different build of Wasm SQLite, that handles concurrency (and file locking) differently. Also, WASI is very far from answer (so far). The SQLite amalgamation builds fine for WASI but concurrency is an unsolved issue. I had to build a VFS from scratch to get my Wasm based SQLite driver into a usable shape. https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.md https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.m...
hawski 2y ago
Is there a way to statically compile an application with SQLite and the result WASM was smaller. So for example I have an app that would use only a specific subset of SQLite. Could the SQLite's WASM be built with this in mind cutting down on code that is not used? Or is there a way to prune it having the used API surface? In a regular compiler/linker scenario it would just be a static compilation. Here we have a JS app and WASM library.
- hoten 2y ago
  Since SQL takes arbitrary strings as input, this would require explicit compiler flags to disable the knobs you don't want. Can't rely on excluding unused symbols really.
- sgbeal 2y ago
  > Could the SQLite's WASM be built with this in mind cutting down on code that is not used? The pending 3.47 release has some build-side tweaks which enable a user to strip it down to "just the basics," but we've not yet been able to get it smaller than about 25-30% less than it otherwise is: cd ext/wasm make barebones=1 ; # requires GNU Make and the Emscripten SDK Doing that requires building it yourself - there are no plans to publish deliverables built that way. The build process also supports including one's own C code, which could hypothetically be used to embed an application and the wasm part of the library (as distinct from the JS part) into a single wasm file. Its primary intended usage is to add SQLite extensions which are not part of the standard amalgamation build. > Or is there a way to prune it having the used API surface? Not with the provided JS pieces. Those have to expose essentially the whole C library, so they will not be pruned from the wasm file. However, you could provide your own JS bindings which only use a small subset of the API, and Emscripten is supposedly pretty good about stripping out C-side code which neither explicitly exported nor referenced anywhere. You'd be on your own - that's not something we'll integrate into the canonical build process - but we could provide high-level support, via the project's forum, for folks taking that route.
  - sgbeal 2y ago
    Correction: make barebones=1 ; # requires GNU Make and the Emscripten SDK should be: make oz barebones=1 ; # requires GNU Make and the Emscripten SDK otherwise it will build with -O0, resulting in huge wasm files.
deskr 2y ago
Sadly, 1.3 MB is nothing on the modern web, especially for a static file. BBC's frontpage loads 3.78 MB. https://www.bbc.co.uk/ https://www.bbc.co.uk/
- sgbeal 2y ago
  > BBC's frontpage loads 3.78 MB. FWIW: Google Drive just downloaded 15.4mb to boot up for me and imdb dot com hit some 7+mb before it started auto-loading videos on top of that.
pdyc 2y ago
That's correct, people in this thread are comparing single compressed dependency of sqlite+wasm of 400KB to the total size of web pages which run in MB. I did some actual tests while trying to use sqlite and it does adds noticeable delay on first page load on mobile due to big size+decompression+ additional scaffolding of wasm. Pages that run into MB have small files that are downloaded concurrently so the delay is not noticeable. I wrote about this and my other expriments with in browser db in my last article but it did not get any traction here.

simonw 2y ago

Slight point of confusion: that page says: > These components were initially released for public beta with version 3.40 and will tentatively be made API-stable with the 3.41 release, pending community feedback. But the most recent release of SQLite is 3.46.1 (from 2024-08-13) Presumably they are now "API-stable" but the page hasn't been updated yet. It would be great if the SQLite team published an official npm package bundling the WASM version, could be a neat distribution mechanism for them. (UPDATE: They do, see replies to this post.) My favourite version of SQLite-in-WASM remains the Pyodide variant, which has been around since long before the official SQLite implementation. If you use Pyodide you get a WASM SQLite for free as part of the Python standard library - I use that for https://lite.datasette.io/ https://lite.datasette.io/ and you can also try it out on https://pyodide.org/en/stable/console.html https://pyodide.org/en/stable/console.html import sqlite3 print(sqlite3.connect(':memory:').execute( 'select sqlite_version()' ).fetchall()) That returns 3.39.0 from 2022-06-25 so Pyodide could do with a version bump. Looks like it inherits that version from emscripten: https://github.com/emscripten-core/emscripten/blob/main/tools/ports/sqlite3.py https://github.com/emscripten-core/emscripten/blob/main/tool...

rblank 2y ago
https://github.com/sqlite/sqlite-wasm https://github.com/sqlite/sqlite-wasm sqlite-wasm loads much faster than Pyodide, so if you don't need Python, then the former is a better choice.
- simonw 2y ago
  Amazing! npm install @sqlite.org/sqlite-wasm
  - wg0 2y ago
    This would run on client side I presume? Where the data would go? Okay that's listed here: https://sqlite.org/wasm/doc/trunk/persistence.md https://sqlite.org/wasm/doc/trunk/persistence.md EDIT: Self answered.
  - simonw 2y ago

outlore 2y ago

i’ve been looking for a Tanstack Query style library that is backed by Sqlite (backed by OPFS or some other browser storage) and syncs with an API in the background. Does anything like that exist? i’ve seen ElectricSQL and other sync engines but they are a bit opinionated. I’m pretty new to local-first but i feel like the developer ergonomics are not quite there yet Meanwhile for “local-only” it would be great to use sqlite in the browser + native file system API so that the db could be stored on the user’s file system and we wouldn’t have to worry about browser storage eviction. i think that could really open up a whole world of privacy preserving offline software delivered through the browser

netghost 2y ago
ElectricSQL and friends seem to be the best option so far, but they all come with a lot of caveats. It feels like local-first is near, and it's so tantalizing, but I haven't seen anything that feels like it's done enough to build on just yet.
ochiba 2y ago
Not sure if you've looked at PowerSync yet: https://www.powersync.com/ https://www.powersync.com/ (I'm on the team) For the read path it hooks into Postgres logical replication or MongoDB change streams (and MySQL binlog soon). It supports partial syncing using declarative rules. For the write path, it allows writing to the local SQLite database and also places writes into an upload queue, and then uses a developer-defined function to upload writes to the backend API. We did a deep dive on current options for SQLite on the web, and are currently using an IndexedDB-based VFS, and looking to move to OPFS: https://www.powersync.com/blog/sqlite-persistence-on-the-web https://www.powersync.com/blog/sqlite-persistence-on-the-web We recently released an integration with TanStack Query to allow leveraging some of its features in conjunction with PowerSync: https://docs.powersync.com/client-sdk-references/js-web/javascript-spa-frameworks https://docs.powersync.com/client-sdk-references/js-web/java... > Meanwhile for “local-only” it would be great to use sqlite in the browser + native file system API so that the db could be stored on the user’s file system and we wouldn’t have to worry about browser storage eviction. i think that could really open up a whole world of privacy preserving offline software delivered through the browser Agreed. This is a limitation of IndexedDB and OPFS as persistent browser storage currently

gnarbarian 2y ago

How long until we see WebAssembly/WebGPU become a platform independent choice for deploying server side code as well?

ruined 2y ago
as soon as wasi is settled
- gnarbarian 2y ago
  https://wasi.dev/ https://wasi.dev/ wow I didn't know this was a thing. thanks for filling me in!
paulddraper 2y ago
Yesterday? There's a number of WASM platforms/tools: Wasmer, wasmCloud, a few others that escape my memory.
- jt2190 2y ago
  https://wasmer.io/ https://wasmer.io/ https://wasmcloud.com/ https://wasmcloud.com/ https://wasmtime.dev/ https://wasmtime.dev/
stackskipton 2y ago
SRE here, it's currently happening in a few parts but overall, it's not as attractive on server side. Server Side code running is mostly a solved problem and for very few organizations, the benefits of WASM don't outweigh any difficulties in getting it running.
- 6gvONxR4sf7o 2y ago
  > Server Side code running is mostly a solved problem I know what you mean here, but I think we're very limited in what we tend to run. Polyglot programming still isn't really a thing, and with things like WASI standardized (someday soon I hope), I could imagine it becoming a lot nicer.

me551ah 2y ago

After years of being able to run SQLite on my mobile phone, my tv, my router and gaming consoles, I can finally run it on my browser. Which also happens to be running on the most powerful machine I own

ibash 2y ago
surprise! it's been there for decades: https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database
- adregan 2y ago
  It was there for a decade: https://caniuse.com/sql-storage https://caniuse.com/sql-storage
  - joemi 2y ago
    I wonder why it was unmaintained/dropped. Was there something wrong with it, and if so, would that also apply to this kind of wasm implementation?
    - debugnik 2y ago
      Mozilla refused to support it because then every implementation would have simply used SQLite, which would have promoted any implementation details to a de facto standard. (Even caniuse erroneously describes the feature as "allows SQLite database queries".) From the latest spec [1]: > 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/ https://www.w3.org/TR/webdatabase/ This won't be a problem for wasm SQLite because it isn't a standard being shipped by browsers, just another dependency.
      - xyc 2y ago
        i have a feeling that it set back the web by a decade https://x.com/chxy/status/1822858746307170640 https://x.com/chxy/status/1822858746307170640

koeng 2y ago

For use in Golang, I really like ncruces wasm SQLite package - https://github.com/ncruces/go-sqlite3 https://github.com/ncruces/go-sqlite3 . Unlike cznic's go package (which is great, btw), the wasm version works well on OpenBSD and the like.

ncruces 2y ago
Author here. If you're interested, do ask questions.
- TN1ck 2y ago
  Very cool project! Do you know if this would be possible for duckdb? Is there something about sqlites APIs and wasm build that made it feasible? Context: Currently using go-duckdb and while it's working for us, getting rid of cgo would be a huge help. Would be quite interested myself to attempt this.
  - ncruces 2y ago
    I don't know much about DuckDB's architecture. Wasm is fine for compute (though concurrency is still a somewhat open question). To have Wasm talk to the outside world, you need “host calls” where the guest calls the host. On a browser that's Wasm calling JavaScript. On my Go driver, it's Wasm calling Go. For server side, there's also a standard set of “host calls” modeled around POSIX/Linux syscalls called WASI. I could've build my project around WASI, but WASI is rather limited (and SQLite support for WASI was more limited even, it's improved a bit since). DuckDB might work out-of-the-box this way. I, instead, took advantage of SQLite's architecture and replaced its VFS layer with one in Go: https://sqlite.org/vfs.html https://sqlite.org/vfs.html So SQLite in Wasm is just doing compute, and I do all the OS level stuff in Go. No need for Wasm concurrency, cause I can load multiple instances of my Wasm which act like independent OS processes that communicate through the filesystem (SQLite excels at this). As I said, I dunno how well all those decisions would map to DuckDB.
    - koeng 2y ago

brandonpollack2 2y ago

I was trying to get this working in a rust ecosystem some time ago but none of the blessed.rs sql (rusqlite, sqlx) wrappers seem to take advantage of it yet and wrapping it yourself is a bit tricky since when I was trying I couldn't figure out a way to to get emscripten wasm code to play nice with wasm32-unknown-unknown without some kind of JS wrapper which then requires implementing the interface those crates expect and exposing it from JS. Once that is done in rust itll be great there too!

tonygiorgio 2y ago
Yeah I’ve been waiting awhile for this myself. A few PRs with work pending for a year or so. I’ve seen some proof of concepts but nothing anywhere close to usable.
- insipx 2y ago
  you should check out https://github.com/xmtp/diesel-wasm-sqlite https://github.com/xmtp/diesel-wasm-sqlite reliable so far, being dogfooded in production as we speak
  - tonygiorgio 2y ago
    Sorry just saw this. Looks promising, I love diesel! Haven’t looked at the landscape in a few months and don’t work on the project that needed it anymore but very cool to see. Even uses OPFS!
aabhay 2y ago
I have been working on one. If you're interested in working on it or contributing, feel free to chime in here: https://github.com/rhashimoto/wa-sqlite/discussions/154 https://github.com/rhashimoto/wa-sqlite/discussions/154 This essentially requires that we import the sqlite emscripten build via an extern C header in wasm bindgen, and then we need to re-implement the VFS in rust while compiling it in multi-threaded mode to allow for shared array buffer access. After that is all done, we will be able to access SQLite rows as raw wasm bytes. That gives us the ability to implement a rust-sqlite style wrapper or integration. There would still not be some of the niceties such as connection pooling, but in wasm you likely want to use the db in exclusive mode.

jjcm 2y ago

As a general question, in what scenarios is it more beneficial to send the full DB and let the browser handle the queries? Maybe phrased a better way - when would I use this to improve a user experience over the traditional server-hosted db model?

bryanrasmussen 2y ago
>when would I use this to improve a user experience over the traditional server-hosted db model? just my intuition when I read the headline of this post - something like the interplay between PouchDB and CouchDB for offline first apps https://medium.com/offline-camp/couchdb-pouchdb-and-hoodie-as-a-stack-for-progressive-web-apps-a6078a985f18 https://medium.com/offline-camp/couchdb-pouchdb-and-hoodie-a...
harrisi 2y ago
For offline use it can be good when dealing with large amounts of data. Anything from like an audio library to 3D modeling software. Changes can be made locally and persisted and then you can sync things server side regularly or when online again.
ThatPlayer 2y ago
Personally I'm using it for a statically hosted website, so a server-hosted database was never an option. Also with the right driver, it's possible to stream the chunks of the database as needed rather than sending the full database: https://github.com/mmomtchev/sqlite-wasm-http https://github.com/mmomtchev/sqlite-wasm-http I can even do Sqlite's full text search without downloading the entire FTS database. Just most of it, if the search term is short enough.
pdyc 2y ago
i am creating host of dashboards which directly talk to different services with very little data on my own server that is used for access control and token management only so actual data never comes to my servers. This kind of app is a good candidate for client side embedded db.

simonw 2y ago

Something that would be really fun would be to run SQLite in-memory in a browser but use the same tricks as Litestream and Cloudflare Durable Objects (https://simonwillison.net/2024/Oct/13/zero-latency-sqlite-storage-in-every-durable-object/ https://simonwillison.net/2024/Oct/13/zero-latency-sqlite-st...) to stream a copy of the WAL log to a server (maybe over a WebSocket, though intermittent fetch() POST would work too). Then on subsequent visits use that server-side data to rehydrate the client-side database. From https://sqlite.org/forum/info/50a4bfdb294333eec1ba4749661934521af19e6fc0790a6189696607f67c2b54?t=h https://sqlite.org/forum/info/50a4bfdb294333eec1ba4749661934... is looks like WAL mode is excluded from the default SQLite WASM build so you would have to go custom with that.

dustinchilson 2y ago
Are you thinking something like https://electric-sql.com/ https://electric-sql.com/
- PUSH_AX 2y ago
  What’s the catch with this thing?
  - T-Winsnes 2y ago
    The security model is challenging, as it relies on Postgres users for iam. Your users essentially log directly into your db
    - dumbo-octopus 2y ago
      Isn’t Postgres a fairly capable IAM provider, all things considered? I’d their access control mechanisms at least as much as a run of the mill external backend’s.
      - T-Winsnes 2y ago
        For basic auth it works well, but the challenge comes when you need to integrate with oidc, need to enforce mfa, enable sso etc. session invalidation is also quite complicated. You need an identity middle man in front of the Postgres identity to tackle these and validate that the session is still active. Last time I looked at electric it was a big challenge to integrate such a service. This might have improved since then however

baq 2y ago

See also https://github.com/electric-sql/pglite https://github.com/electric-sql/pglite (REPL at https://pglite.dev/repl/ https://pglite.dev/repl/) (Previously discussed 7 months ago: https://news.ycombinator.com/item?id=39477457 https://news.ycombinator.com/item?id=39477457)

bhelx 2y ago

I used the wasm build of sqlite and the Chicory runtime to create a pure JVM executed sqlite library: https://github.com/dylibso/sqlite-zero https://github.com/dylibso/sqlite-zero It's more of an experiment than an attempt to make something production ready, though I could see it being useful to bring dependency-less sqlite tooling to the JVM ecosystem.

ncruces 2y ago
What's the file system access like, WASI?
- bhelx 2y ago
  Chicory has some partial wasip1 support. https://github.com/dylibso/chicory/tree/main/wasi https://github.com/dylibso/chicory/tree/main/wasi. We use jimfs to keep things simple and secure (and not worry about exposing the real filesystem): https://github.com/google/jimfs https://github.com/google/jimfs When I did this experiment a few months ago, what we could accomplish was pretty limited. I could load and query databases, but not write to them. However the Chicory wasip1 implementation is advancing. BTW, we've borrowed a few ideas from wazero so thanks for your work there :)
  - ncruces 2y ago
    If the goal is to improve Chicory WASI support, this is the way. If the goal was pure Java SQLite¹, a VFS from scratch would be better. I think since I started my Go/wazero effort, WASI+SQLite improved a bunch. I had to start with the demo VFS; the Unix VFS now builds. But custom VFS is still the way to go, IMO. And thanks! My contributions to wazero were tiny. Best of luck with Chicory! 1: strong NestedVM vibes here; 11 years ago… gosh, I feel old now. https://stackoverflow.com/questions/18186507/pure-java-vs-native-sqlitejdbc-driver-and-nested-vm https://stackoverflow.com/questions/18186507/pure-java-vs-na...
    - bhelx 2y ago
      > If the goal was pure Java SQLite¹, a VFS from scratch would be better. agreed, though this was more an experiment to test Chicory once we built initial wasi support. I'd love to see it picked up and improved. I think that's the direction I'd go if i want some kind of production ready library.

benthecarman 2y ago

Whats needed is a rust-wasm compatible library that can use this.

aabhay 2y ago
If you're interested in contributing to that, here is a good place to start (in early discussion stages): https://github.com/rhashimoto/wa-sqlite/discussions/154 https://github.com/rhashimoto/wa-sqlite/discussions/154

catapart 2y ago

I wasn't able to tell from a quick look through the page: could someone help me understand the use cases here? More specifically, would this be able to be a "replacement" for indexedDB? Does the data persist, or do I need to keep the sqlite file in the filesytemAPI (or indexedDB/localstorage) myself?

azangru 2y ago
From the about page: > Specific Goals of this Project > Insofar as possible, support persistent client-side storage using available JS APIs. As of this writing, that includes the Origin-Private FileSystem (OPFS) and (very limited) storage via the window.localStorage and window.sessionStorage backend.
- catapart 2y ago
  Right but, to my eyes, that's vague? What I'm asking is if I need to manage the sqlite file, as I would on an OS's file system, or if accessing the sqlite library will automatically persist that data to those web-native storages, like the way indexedDB doesn't require me to load an "idb" file and then "save" or "commit" that save. I just access it and write. To be clear: I'm not asking academically. I wrote a whole library for managing data in indexedDB for local-first apps, and while it works well enough for what I need, it's iDB so it's subject to data deletion (not common, but allowed in the spec if necessary), and it's a pain to work with just because of its nature and API. So I've been waiting to move to sqlite for a while with the only holdbacks being "is it too heavy?", and "how much has to change?". With WASM, I think we're about as lightweight as its going to get. So I'm just curious if this aims to be a drop-in replacement, or if it still expects you to use it like sqlite on a native platform.
  - sgbeal 2y ago
    > Right but, to my eyes, that's vague? We (the sqlite project, where the "vague" description comes from) do not define the use cases. Similarly, in the docs for the C library you won't find any more than passing references to specific use cases, and those are typically contrived for the sake of example. (One notable exception: <https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html>) > What I'm asking is if I need to manage the sqlite file, as I would on an OS's file system, or if accessing the sqlite library will automatically persist that data to those web-native storages, like the way indexedDB doesn't require me to load an "idb" file and then "save" or "commit" that save. I just access it and write. That's all covered in the docs (of which there are well more than 100 lovingly-hand-written pages), but the short answer is "it just works." You have the _option_ of importing and exporting databases from the browser-native storage, but you don't have to. For starters, see: <https://sqlite.org/wasm/doc/tip/persistence.md https://sqlite.org/wasm/doc/tip/persistence.md>

parhamn 2y ago

WebSQL should've just been Sqlite and the whole offline-first (and general app storage) ecosystem would've been so much nicer. Is there any hope of that happening? Instead of abstracting and over specifying sqlite, can the spec just specify a version of the SQLite API browsers should support and roll the version periodically?

emn13 2y ago
"Rolling the version periodically" is probably quite problematic for browsers. Kind of a key point of the web is that stuff if at all possible keeps working. Breaking changes like that are hard. Even if the spec just listed occasional version and the webpage could choose which one; that means a potentially tricky maintenance burden on browser to support old versions of a potentially no longer supported sqlite, and each version is another megabyte. Why not then just choose this solution, and let each website pick its own poison? If the concern is the repeated downloads of common resources, well, we've accepted that for other CDN's too, and a solution for shared caching of common dependencies would in any case be more valuable than merely for sqlite. The current approach seems better than a browser-provided version.
- justin66 2y ago
  Versioning was never really an issue. It's worth pointing out that Richard Hipp committed to creating and maintaining a flag in SQLite that would force SQLite to use whatever subset of SQL the WebSQL people settled on for their API. That would have of course worked independently of the SQLite version. (He also offered to write the SQL part of the spec.) https://news.ycombinator.com/item?id=15670808 https://news.ycombinator.com/item?id=15670808 I thought the reasons given for not moving forward with standardizing WebSQL and using a SQLite implementation were (and undoubtedly still are) very, very stupid so I'm not the right person to represent them here.
simonw 2y ago
I for one am glad WebSQL didn't establish itself. Now we get the most recent version of SQLite when we need it as a 410KB compressd WASM blob, as opposed to being stuck on browser-mandated versions of SQLite that might even be a decade old at this point.

chrysoprace 2y ago

I've been really interested in the local-first landscape lately but embedding SQLite seems really heavy-weight compared to using the browser's built-in storage APIs (in particular, IndexedDB) and it seems to be what most of the main open source libraries do. I'm interested to see a open-source solution (with sync) which provides an SQLite-like API but for the browser's native storage rather than trying to embed another executable in Web Assembly.

jamesgpearce 2y ago
Disclaimer: I'm the author. But you might be interested in TinyBase.
- chrysoprace 2y ago
  Thanks I'll give it a look! I guess I really was fishing for someone to make a recommendation and I see you have a lot of backend persistence options which I'm very excited about. Another issue I have with a lot of the new local first products is that they tend to lock you into a particular database type on the backend, so this is refreshing.
- arunaugustine 2y ago
  TinyBase looks very promising! Is there a doc or reference you can guide me to, for using TinyBase with Preact.js instead of React?
- smallerfish 2y ago
  Nice work. Does it take time to hydrate tinybase from indexeddb, or do you mount it directly? What are your thoughts about search over the store? Hydrate a separate lunr index? And finally has anybody tried using your syncing over webrtc?
gavmor 2y ago
SQL is, arguably, more ergonomic than IndexedDB APIs, and may take up less RAM/CPU when, eg, using a `WHERE` clause rather than an `if() ` filter.

runarberg 2y ago

I’m working on a hobby-project that uses IndexedDB for persistent client-side storage, and it really feels like W3C made some very bad design decision and than instead of fixing they they have just given up on the standard. Issues like not being able to index values in objects in arrays [1] (not even in fixed position e.g. "key.path.[0].value") despite almost a decade of developers asking for it, a very limited query syntax, and even the documentation on MDN seems of very lower quality than the rest of the web docs. I’m happy that we are actually be able to use SQL in the browser now (although I would rather skip the MBs of the bundle bloat). But I feel like the standards committee will now have even less of a reason to fix the very broken state of IndexedDB. 1: https://github.com/w3c/IndexedDB/issues/35 https://github.com/w3c/IndexedDB/issues/35

baudaux 2y ago

I definitely have to put sqlite in https://exaequOS.com https://exaequOS.com

koolala 2y ago

The CORS restrictions / needing SharedArrayBuffer support kinda stinks. There is no way to use Sqlite3 off-thread without memory sharing? Couldn't postMessage work to pass data to the sqlite thread by using the third Transfer argument? Would postMessage transfer allow memory to be stored in a sqlite wasm database running a worker off-thread? Refering to this implementation's docs: https://github.com/sqlite/sqlite-wasm https://github.com/sqlite/sqlite-wasm

sgbeal 2y ago
> The CORS restrictions / needing SharedArrayBuffer support kinda stinks. We have no CORS restrictions but one specific (and optional) VFS requires COOP/COEP for SharedArrayBuffer. If SharedArrayBuffer isn't available, that VFS won't load, but the rest of the library will plod along just fine: https://sqlite.org/wasm/doc/tip/persistence.md https://sqlite.org/wasm/doc/tip/persistence.md
- koolala 2y ago
  Persistence is the whole point to me for a Database. OPFS finally adding a filesystem to the browser made seem like the web file database situation could finally standardize but COOP/COEP ruins it.
  - eirikhm 2y ago
    https://github.com/rhashimoto/wa-sqlite https://github.com/rhashimoto/wa-sqlite
  - sgbeal 2y ago
    > OPFS finally adding a filesystem to the browser made seem like the web file database situation could finally standardize but COOP/COEP ruins it. We have two OPFS-based VFSes. One requires COOP/COEP and one does not. Each makes feature trade-offs, though, so they're not equivalent. We also offer persistence via localStorage and sessionStorage, so OPFS is not (for small databases) required for persistence.

k__ 2y ago

Half-OT: What's your opinion on SQLite in-memory vs plain objects/arrays? When would you use which and why?

kohlerm 2y ago

WASM ATM is IMHO most useful for VSCode Extension, where it can help to avoid the dependency nightmare that nodejs modules with native code cause.

deleted 2y ago

[deleted]

deleted 2y ago

[deleted]

throwaway81523 2y ago

Why have this instead of making it a browser API?

mococa 2y ago

It would be great if Go had a WebAssembly runtime with simple interoperability, so I could stop using CGO (I need to use it because SQLite in Go depends on CGO. There’s a Go version, but I don’t trust using transpiled code).

emrah 2y ago

Fwiw, duckdb is replacing sqlite for me, particularly due to its rich plugin ecosystem

shautvast 2y ago

Shameless plug for the fastest way to get the serverdata to the client: just send data in the format that sqlite itself uses: https://gitlab.com/sander-hautvast/sqlighter https://gitlab.com/sander-hautvast/sqlighter (available in java and rust, no dependencies on sqlite itself). This works well with the wasm build. The java project contains a demo that also shows how to setup the UI code.