Hosting SQLite databases on GitHub Pages or any static file hoster

[ekanna]

Here is hacker news link which explains how to host SQLite databases on GitHub Pages or any CDN.
https://news.ycombinator.com/item?id=27016630

Write
LiteStream --> S3

Read
S3 --> Browser

In effect, we need AppServer only for writes.

For reads, we don't need anything just CDN

[benbjohnson]

It could be interesting to combine Litestream & the sql.js/VFS approach from the article. Litestream backs up WAL frames which just represent pages in the main database file. If Litestream wrote up a small metadata file mapping WAL frames to pages then you could run transactional queries against a live, streaming back up on a CDN. 🤔

[oakes]

@benbjohnson Am i understanding you correctly that this idea wouldn't work right now because it would require the CDN to host the db as a normal sqlite file, whereas litestream saves data to the CDN as separate WAL frames? Would this require changes to sqlite itself? I'm really interested in trying this out.

[benbjohnson]

Litestream works against S3 already which would work as the CDN. The idea is you would have a single database writer on a server somewhere that automatically replicates WAL frames to S3 via Litesteam. Then you'd need a virtual file system (VFS) on the sql.js side that can read those frames and maintain a mapping of DB pages to WAL pages. It wouldn't require changes to SQLite—you'd just need the VFS. I haven't tried writing a VFS in sql.js before though so I'm not sure how hard it is.

[ekanna]

FYI. @benbjohnson
https://ansiwave.net/blog/sqlite-over-http.html

Discussin:
https://news.ycombinator.com/item?id=29758613

[oakes]

That's me :D I ended up going with the multiplex VFS (with minor changes) since it was already written and seemed to work well. The WAL approach could work as well but there were more unknowns for me so i didn't go that route.