Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Sqlite3 WebAssembly

sqlite.org

Post reply on HN
Print view View original

151–160 of 200 posts

Re: Sqlite3 WebAssembly

#151

Post by chx »

digdugdirk wrote: post #98
wyager wrote: post #86

Earlier quoted context omitted.

WAL: Write ahead log, common strategy for DBs (sqlite, postgres, etc.) to improve commit performance. Instead of fsync()ing every change, you just fsync() a log file that contains all the changes and then you can fsync() the actual changes at your leisure Shared memory API: If you want to share (mutable) data between multiple processes, you need some kind of procedure in place to manage that. How do you get a referen…

Thank you! And side note on your last point - I've been burned too many times by confident hallucinations to trust my foundational learning to GPT. I hope someday that will improve, but for now ChatGPT is as trustworthy as an evening chat with someone at the bar. ... Someone who has been drinking since happy hour.

Of course. The only thing LLMs are good for is...

https://hachyderm.io/@inthehands/112006855076082650

> You might be surprised to learn that I actually think LLMs have the potential to be not only fun but genuinely useful. “Show me some bullshit that would be typical in this context” can be a genuinely helpful question to have answered, in code and in natural language — for brainstorming, for seeing common conventions in an unfamiliar context, for having something crappy to react to.

> Alas, that does not remotely resemble how people are pitching this technology.

Re: Sqlite3 WebAssembly

#152

Post by jauntywundrkind »

ncruces wrote: post #92
jauntywundrkind wrote: post #83

Earlier quoted context omitted.

Then don't use in-memory sqlite? Use file backed sqlite but have your wasm implementation of those "system calls" just be to memory? I dunno, feels like you're coming down too hard.

File backed SQLite in a browser? Do you mean like OPFS? https://sqlite.org/wasm/doc/trunk/persistence.md#opfs-wal Again, just because the all the Lego pieces sound like they should all just fit together, doesn't mean that they will. The VFS mechanism was primarily designed to make SQLite easy to port to multiple OSes. WAL mode is hard to port everytime you step away from a more traditional OS. “We have SQLite in the…

Emscripten's default file system provider is memfs, in memory. Maybe there would be some challenges, some spec limitations using that, but I strongly expect it closer to a weekend or two of hacking to get some special weird mystic quirkiness that WAL relies on than some long ordeal that keeps going on endlessly (to get sqlite running with WAL). https://emscripten.org/docs/api_reference/Filesystem-API.htm...

OPFS is interesting tech but again a red herring misdirecting from what had been raised, using an in-memory filesystem like the default thing that emscripten (the default toolchain) does.

Re: Sqlite3 WebAssembly

#153

Post by billywhizz »

adhamsalama wrote: post #148
billywhizz wrote: post #108

Earlier quoted context omitted.

hi simon. i direct messaged you on twitter about a PoC i did of this in aug 2022, but never heard back - i thought you might have been interested. my twitter handle is justjs14. i have some code i would have to dig out that did this very thing - it allows you to open a SQLite db in browser using sqlite (with a VFS) compiled to wasm (not the official WASM build), make changes and both push and pull WALs to and from a…

> (or indeed browser to browser would be possible both manually or over WebRTC) I have just done something similar in the past week, but without the WAL. It's pretty much an alternative to online spreadsheets for me. http://github.com/adhamsalama/sqlite-wasm-webrtc

cool. will check this out. i think it's an interesting approach and allows all sorts of very low rent interactivity as long as you don't need super high throughput or expect lots of contention.

Re: Sqlite3 WebAssembly

#154

Post by rockwotj »

ncruces wrote: post #68
simonw wrote: post #32

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-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 da…

There are many layers of that's not how it works at play here. In-memory SQLite databases don't use WAL. Wasm (and browser Wasm, in particular) doesn't support anything like the shared memory APIs SQLite wants for its WAL mode. Litestream requires a very precise WAL setup to work (which just so happens to work with the default native SQLite setup, but is hard to replicate with Wasm). Cloudflare Durable Objects may ha…

I believe that streaming changes from SQLite is what https://sqlsync.dev/ is

Re: Sqlite3 WebAssembly

#155

Post by rahoulb »

globular-toast wrote: post #137
digdugdirk wrote: post #98

Earlier quoted context omitted.

Thank you! And side note on your last point - I've been burned too many times by confident hallucinations to trust my foundational learning to GPT. I hope someday that will improve, but for now ChatGPT is as trustworthy as an evening chat with someone at the bar. ... Someone who has been drinking since happy hour.

If you'd like a trustworthy overview, the book Designing Data-Intensive Applications by Martin Kleppmann is a classic. I really hope we get an updated version, but the fundamentals all still hold anyway.

Upvote for that book.

I read it a few months ago and was really impressed with how easy it was to read.

It starts out with simple stuff, like serialising data as JSON vs XML. But it moves into complex areas - like how replication and WALs work, including different ways of handling consensus when using leader-leader replication and how Spanner needs atomic clocks to handle it.

But even the complex stuff was explained in a way that I understood, which is an immense achievement.

Re: Sqlite3 WebAssembly

#156

Post by ncruces »

jauntywundrkind wrote: post #152
ncruces wrote: post #92

Earlier quoted context omitted.

File backed SQLite in a browser? Do you mean like OPFS? https://sqlite.org/wasm/doc/trunk/persistence.md#opfs-wal Again, just because the all the Lego pieces sound like they should all just fit together, doesn't mean that they will. The VFS mechanism was primarily designed to make SQLite easy to port to multiple OSes. WAL mode is hard to port everytime you step away from a more traditional OS. “We have SQLite in the…

Emscripten's default file system provider is memfs, in memory. Maybe there would be some challenges, some spec limitations using that, but I strongly expect it closer to a weekend or two of hacking to get some special weird mystic quirkiness that WAL relies on than some long ordeal that keeps going on endlessly (to get sqlite running with WAL). https://emscripten.org/docs/api_reference/Filesystem-API.htm... OPFS is i…

I… really don't get this.

The people on SQLite, employed to work on this full time for over a year, have this to say (on the link I posted above):

“Because the WASM build does not have shared memory APIs, activating WAL requires that a client specifically activate exclusive-locking mode for a db handle immediately after opening it, before doing anything else with it…

“WAL mode does not provide any concurrency benefits in this environment. On the contrary, the requirement for exclusive locking eliminates all concurrency support…”

I personally worked to implement shared memory WAL for a server side Wasm port of SQLite. But random internet poster decides to “strongly expect it closer to a weekend or two of hacking.”

Please, do me a favor and do spend that weekend or two for the benefit of the rest of us all. It'll sincerely be much appreciated.

PS: it was a random internet poster¹ (who's been posting in this thread) who helped me figure out how to implement shared memory WAL for my port. It still took way more than “a weekend or two.” So if you do figure out how to crack this, I'm sure that people who've been trying for the past year² will definitely appreciate it.

1: https://github.com/ncruces/go-sqlite3/discussions/69

2: https://github.com/rhashimoto/wa-sqlite

Re: Sqlite3 WebAssembly

#157

Post by ncruces »

rockwotj wrote: post #154
ncruces wrote: post #68

Earlier quoted context omitted.

There are many layers of that's not how it works at play here. In-memory SQLite databases don't use WAL. Wasm (and browser Wasm, in particular) doesn't support anything like the shared memory APIs SQLite wants for its WAL mode. Litestream requires a very precise WAL setup to work (which just so happens to work with the default native SQLite setup, but is hard to replicate with Wasm). Cloudflare Durable Objects may ha…

I believe that streaming changes from SQLite is what https://sqlsync.dev/ is

Yep, that doesn't use WAL mode at all, and it's a decent amount of work.

It uses a custom VFS, memory journal mode, and bypasses SQLite for optimistic concurrency handling. I dunno how it handles crash safety, or if it even uses the same on disk file format.

My point here is not that this is impossible, it's that SQlite's WAL implementation isn't meant to be pluggable, and Litestream is a very clever hack, that requires a very specific setup to work well.

It takes a fair amount of work to replicate that in other environments.

Re: Sqlite3 WebAssembly

#158

Post by globular-toast »

rahoulb wrote: post #155
globular-toast wrote: post #137

Earlier quoted context omitted.

If you'd like a trustworthy overview, the book Designing Data-Intensive Applications by Martin Kleppmann is a classic. I really hope we get an updated version, but the fundamentals all still hold anyway.

Upvote for that book. I read it a few months ago and was really impressed with how easy it was to read. It starts out with simple stuff, like serialising data as JSON vs XML. But it moves into complex areas - like how replication and WALs work, including different ways of handling consensus when using leader-leader replication and how Spanner needs atomic clocks to handle it. But even the complex stuff was explained…

Yep, this is my number 1 "I wish I'd read this X years ago" book.

I'm someone who has been doing this stuff for almost two decades without really knowing this is what I'm doing. I used to think what I was going to do was systems level programming like operating systems and maybe the database systems themselves (e.g. postgres, datomic etc.). But for whatever reason my entire career (so far, but I don't see it changing) has been building data systems for businesses and users.

I read the book from cover to cover and half of it was like "ohh... that's how that works, that's what I'm doing wrong" and the other half was "shit, this is something I kinda knew after trying and failing for years, and someone has just written it down in a way I never could".

Re: Sqlite3 WebAssembly

#159

Post by sgbeal »

asdfman123 wrote: post #136
sgbeal wrote: post #117

Earlier quoted context omitted.

> 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 >) > What I'm asking is if I need to manage the sqlit…

> do not define the use cases I genuinely don't mean to sound rude, and maybe I misunderstand, but how do you build software if you're not doing it with use cases in mind?

> but how do you build software if you're not doing it with use cases in mind?

That's a fair question in the general case, but that aspect doesn't much apply to SQLite's continued evolution. Perhaps it's an uncommon case in that regard.

SQLite initially grew out of a single, highly-specific use case which Richard wanted to solve. The solution, however, was highly generic, suitable for solving many, many specific problems. In the mean time, the set of use cases has evolved to, essentially, "just about anything for which you need to save data locally":

https://sqlite.org/whentouse.html>

There are literally millions of concrete uses of SQLite in the wild, the majority of which were never conceived when the library first took shape but (and people are still coming up with exotic uses for it).

Individual features are sometimes added to help support concrete use cases, but the library is general-purpose enough that concrete use cases don't play a considerable role in its day-to-day development.

Re: Sqlite3 WebAssembly

#160

Post by jauntywundrkind »

ncruces wrote: post #156
jauntywundrkind wrote: post #152

Earlier quoted context omitted.

Emscripten's default file system provider is memfs, in memory. Maybe there would be some challenges, some spec limitations using that, but I strongly expect it closer to a weekend or two of hacking to get some special weird mystic quirkiness that WAL relies on than some long ordeal that keeps going on endlessly (to get sqlite running with WAL). https://emscripten.org/docs/api_reference/Filesystem-API.htm... OPFS is i…

I… really don't get this. The people on SQLite, employed to work on this full time for over a year, have this to say (on the link I posted above): “Because the WASM build does not have shared memory APIs, activating WAL requires that a client specifically activate exclusive-locking mode for a db handle immediately after opening it, before doing anything else with it… “WAL mode does not provide any concurrency benefit…

> WAL mode does not provide any concurrency benefits in this environment.

Except we aren't interested in concurrency or performance benefits of WAL; we want it for something else entirely (replication).

Post reply on HN