Using the SQLite-over-HTTP “hack” to make back end-less, offline-friendly apps | Hacker News Reader

Using the SQLite-over-HTTP “hack” to make back end-less, offline-friendly apps

ansiwave.net

94 pointssekao24 comments

> Despite the title, we do still need a small backend for writes. Every time a user modifies the data, they will need the POST to your server, which will modify the SQLite database. This leads us to the big question: how do we update the database? Cloud object stores like S3 do not allow partial file updates, so what happens when your database becomes 1 GB or larger?

> 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.

Totally this!

Pretty slick.

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...

My nim implementation of phiresky's technique is here: https://github.com/ansiwave/wavecore/blob/master/src/wavecor...

So there’s a server which accepts arbitrary writes to the database? And maps HTTP ranges to arbitrary database chunks?

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…

> So there’s a server which accepts arbitrary writes to the database?

No, all writes have to go through a normal POST endpoint, it does not accept arbitrary writes from clients.

Is the POST endpoint accepting database chunks (symmetrical to the GET endpoint?) or is it serving up REST-style writes and performs SQLite update statements behind the scenes?

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?

No, it's not accepting database chunks; it's doing all the writes the typical way. And you are right that all data is completely public, unless you restrict access at the file host itself.

Interesting idea - but like some other posts here I'm wondering how exactly writes and concurrency would work - after all, a good amount of the complexity in traditional RDBMS stem from handling concurrent reads and writes.

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?

> Interesting idea - but like some other posts here I'm wondering how exactly writes and concurrency would work

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.

Makes sense. I think especially for DBs that are mostly read-only, this seems like a really useful solution.

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)

And with litestrean/rqlite/dqlite you could have multiple read replicas as well.

Oh now that’s interesting! It potentially solves SQLite on serverless platforms like CloudFlare Workers, like what I was talking about the the other day:

https://news.ycombinator.com/item?id=29728702

I truly believe 2022 is the year or SQLite on the front end and serverless.

This may be a stupid question but what happens when two users update data in the same block? Also, does every update send an entire block over the wire?

That would be fine, sqlite takes care of correctly updating the db file, though i do need to ensure i'm writing from a single thread. And yes every update sends the whole block over, which is 1/4 of a MB (could've made it smaller but i felt that was small enough).

I'm not so sure this would work. Yes, SQLite will do its best to synchronize the requests, but that's all built on the assumption the file exists on a normal file system with locks, consistency, etc. However, this is not the case here.

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.

> but that's all built on the assumption the file exists on a normal file system with locks, consistency, etc. However, this is not the case here.

It is the case here. Writes are happening on a normal linux VPS, and subsequently copied to S3. The read-only queries are using the HTTP VFS. It would be incredibly bad to enable direct writes to a db from arbitrary clients.

Ah, now I get it. I agree, if writes go through a backend, this should work.

when I saw that post a year ago, i thought that you can postpone scale issues with a cdn for a good long while if you could find a mechanism to update the sqlite chunks in a predictable user-friendly way. it’s quite an innovation.

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

oh and my idea for updating writes to a sqlite file was to send all writes to SQS, batch items with a lambda function that would update the sqlite database on EFS. it’s all theoretically possible, the issue is the user experience. how to show the user they really “liked” a post or something.

Actually visual JavaScript which you can find at yazz.com has done this for over two years where you can save a full html backed SQLite database app as a single page html file

Is the offline version read only? If not, do you just queue the update POSTs for when you’re back online?

I’m also not seeing how you synchronize reads against writes with this technique.

The offline version still needs internet to send new posts but it saves drafts so you can send them later. Not sure what you mean by synchronizing reads against writes; reads only ever occur on the copy in S3, and writes only directly occur on the copy in the linux server. In theory it is possible for a read to occur while S3 is in the middle of updating the db files (not sure if they update multiple files atomically) but it would be a rare problem, and probably could be automatically dealt with by rerunning the request after a short wait.

Yes, I was wondering how you avoid querying against a partially updated database given that (if I understood correctly) the database is split across multiple files that (I think) get updated independently.

Reply on news.ycombinator.com