[HN Gopher] Using the SQLite-over-HTTP "hack" to make back end-l...
___________________________________________________________________
Using the SQLite-over-HTTP "hack" to make back end-less, offline-
friendly apps
Author : sekao
Score : 46 points
Date : 2022-01-01 14:42 UTC (8 hours ago)
(HTM) web link (ansiwave.net)
(TXT) w3m dump (ansiwave.net)
| damiankennedy wrote:
| 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?
| sekao wrote:
| 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).
| xg15 wrote:
| 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.
| sekao wrote:
| > 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.
| xg15 wrote:
| Ah, now I get it. I agree, if writes go through a
| backend, this should work.
| xg15 wrote:
| 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?
| sekao wrote:
| > 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.
| xg15 wrote:
| 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)
| sharps_xp wrote:
| 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
| sharps_xp wrote:
| 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.
| zubairq wrote:
| 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
| hitekker wrote:
| > 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.
| samwillis wrote:
| 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.
| feep wrote:
| 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...
| sekao wrote:
| My nim implementation of phiresky's technique is here:
| https://github.com/ansiwave/wavecore/blob/master/src/wavecor...
| ninkendo wrote:
| 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...
| sekao wrote:
| > 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.
| ninkendo wrote:
| 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?
| sekao wrote:
| 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.
___________________________________________________________________
(page generated 2022-01-01 23:02 UTC)