Hosting SQLite databases on GitHub Pages or any static file hoster
phiresky.github.io
Hosting SQLite databases on GitHub Pages or any static file hoster
1–10 of 252 posts
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#2TL;DR:
Compile SQLite to JS with emscripten, implement a virtual fs in JS to stream chunks of a statically hosted (readonly) SQL database.
If queries make use of indices, only a fraction of the database needs to be downloaded.
Also, you can use SQLite to query the DOM.
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#3This is really cool! I wonder what the restrictions are and if we would ever be able to write to a SQLite db like this in the future. This could push more to the front end without needing to write apis.
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#4The innovation here is getting sql.js to use http and range requests for file access rather than all being in memory.
I wonder when people using next.js will start using this for faster builds for larger static sites?
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#5The innovation here is getting sql.js to use http and range requests for file access rather than all being in memory. I wonder when people using next.js will start using this for faster builds for larger static sites?
Microsoft Access Cloud Edition, basically?
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#6This is really cool! I wonder what the restrictions are and if we would ever be able to write to a SQLite db like this in the future. This could push more to the front end without needing to write apis.
The main restriction is that the DB really needs well fitting indexes, otherwise querying is really slow and fetches a lot of data.
Regarding writing:
You could of course implement a writing API with POST requests for changing pages of the database - but then you would lose most of the benefits of this (not requiring any special kind of server).
I also thought about implementing a kind of overlay filesystem, where chunks that are written to the file are stored in a local storage so the modified data is available locally while still reading everything else from the remote database.
Interestingly in SQLite that's already exactly what the WAL mode does: It's a second file next to the database that's just a set of pages that are overlaid over the main file when read queries happen - which allows concurrent readers and writers since the database itself isn't in an undefined state even when write transactions are happening.
So you could enable WAL mode and disable WAL auto checkpointing, then you get a downloadable WAL file that can be read by normal SQLite and written back to the main file. It would be neat, but I'm not sure what the actual use case would be ;)
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#7This is really cool! I wonder what the restrictions are and if we would ever be able to write to a SQLite db like this in the future. This could push more to the front end without needing to write apis.
The main restriction is that the DB really needs well fitting indexes, otherwise querying is really slow and fetches a lot of data. Regarding writing: You could of course implement a writing API with POST requests for changing pages of the database - but then you would lose most of the benefits of this (not requiring any special kind of server). I also thought about implementing a kind of overlay filesystem, where ch…
Since you can do static hosting from a git repo, I wonder if you could directly push your changes to your git repo and have your CI/CD solution just deploy it instead?
There has to be a git.js implementation out there and you could move the DB to it's own repo and create an https access token (for Github)... the issue there is that someone could use that token to commit whatever to your database repo.
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#8The innovation here is getting sql.js to use http and range requests for file access rather than all being in memory. I wonder when people using next.js will start using this for faster builds for larger static sites?
See also https://github.com/bittorrent/sqltorrent, same trick but using BitTorrent
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#9TL;DR: Compile SQLite to JS with emscripten, implement a virtual fs in JS to stream chunks of a statically hosted (readonly) SQL database. If queries make use of indices, only a fraction of the database needs to be downloaded. Also, you can use SQLite to query the DOM.
[deleted]
Re: Hosting SQLite databases on GitHub Pages or any static file hoster
#10This is really cool! I wonder what the restrictions are and if we would ever be able to write to a SQLite db like this in the future. This could push more to the front end without needing to write apis.
The main restriction is that the DB really needs well fitting indexes, otherwise querying is really slow and fetches a lot of data. Regarding writing: You could of course implement a writing API with POST requests for changing pages of the database - but then you would lose most of the benefits of this (not requiring any special kind of server). I also thought about implementing a kind of overlay filesystem, where ch…
> I also thought about implementing a kind of overlay filesystem, where chunks that are written to the file are stored in a local storage so the modified data is available locally while still reading everything else from the remote database.
Perhaps adding IPFS to the mix for persisting data would be interesting, I'm sure there are use cases in peer to peer applications. Anyway, amazing innovation thank you for writing this :)