Also of course data validation - the main reason why there is usually a layer between browser and DB is to restrict what a user can see and do. This seems difficult to implement here.
Also, if we're already exposing the DB directly to the world, why take the detour through SQLite and a custom file system layer?
Why not just use a traditional DB like MySQL or Postgres on the backend and a WASM SQL client on the frontend?
For writes, SQLite's single threaded restriction still applies. But for most use cases, scaling reads is by far the most important thing.
> Why not just use a traditional DB like MySQL or Postgres on the backend and a WASM SQL client on the frontend?
The point is that you don't need a backend at all for reads, only for writes. All reads can be served by a static file host or cloud object store.
In some way, this reminds me of the way some of the old image boards were written: Instead of querying the DB and rendering HTML on each GET, the HTML was regenerated on POSTs and written to disk as static files. All GETs were handled like static assets.
This gave you high read performance, caching and partial reads for free, even for pages that normally would be dynamic.
(Of course the cost was bad write performance and severe restrictions on dynamic or user-dependent content)
Are we just completely relying on the client for security here? (i.e. no security at all?) Is this only for use cases where you have a whole database per user, and effectively a single security domain for the whole db? Because otherwise it smells very insecure to me…
No, all writes have to go through a normal POST endpoint, it does not accept arbitrary writes from clients.
I guess if it’s the latter, it’s just a normal REST API for writes… but it still doesn’t secure read access at all, right? That is to say, you can’t put anything in the database you don’t want all users to be able to see?
At the very least, you'd have to emulate file locks on the HTTP layer - and deal with the problem of requests being delayed, getting losts, clients suddenly vanishing, etc.
> though i do need to ensure i'm writing from a single thread.
But how would you do that? This being a web app, it's hard to ensure you're even writing from a single machine.
I’m also not seeing how you synchronize reads against writes with this technique.
> For me, the solution was lying inconspicuously in the SQLite source tree: an optional extension that allows you to multiplex a SQLite database across multiple files. Choose a chunk size, and your database will be automatically broken into multiple files as you add data to it. Now, you can just use a tool like rclone to copy the parts that have changed, instead of the entire 1+ GB database.
> This is not just theoretical. The technique above is how I built ANSIWAVE BBS. The entire BBS is hosted on S3, and every time someone writes a post, the SQLite database is updated there.
I strongly recommend authoring a tutorial on your discovery and submitting it to HN. I don't think most folks, myself included, realized that AnsiBBS was using SQLite range requests, or that you figured out how to update a multi-gigabyte SQL file in production.
You’re on the cusp of something big.
It extends the post[0] from a year-ish ago with read-only sqlite on a static server with a tiny backend that allows writes.
I am going to have to take a closer look at some point:
https://github.com/ansiwave/wavecore/search?q=sqlite
[0] https://phiresky.github.io/blog/2021/hosting-sqlite-database...
after some meditation, i opted to do something simpler. if i was going to delay the the write consistency anyway, it’s just easier for the user to just download a lagging html file. this definitely depends on the use case, but i avoid a lot of this wasm js complexity by just having the browser download a static eventually updated html file
https://news.ycombinator.com/item?id=29728702
I truly believe 2022 is the year or SQLite on the front end and serverless.
