News for hackers. Stuff that matters.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by simonw on Sunday May 02, 2021 @03:17PM (#27018051)
Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is:

    select country_code, long_name from wdi_country
    order by rowid desc limit 100
It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB!

I have an animated GIF demo of this here: https://twitter.com/simonw/status/1388933800445452290

Re:Hosting SQLite databases on GitHub Pages or any static fi... by phiresky on Sunday May 02, 2021 @03:27PM (#27018163)
Huh, that's actually kind of a worst case I didn't think about: Since you're doing a reverse table scan my "sequential access" detection doesn't kick in. If you do the same query but with a forward scan it should fetch roughly the same amount of data but only do like 5 HTTP requests since the request size doubles for every sequential access.

e.g.:

select country_code, long_name from wdi_country where rowid >= 164 order by rowid asc limit 100;

Re:Hosting SQLite databases on GitHub Pages or any static fi... by vladf on Sunday May 02, 2021 @06:23PM (#27019613)
I solved a similar problem recently: given a stream of data, how should you choose packet size in an online way to minimize regret (a linear combination of spare capacity of last packet and total packets used).

Turns out doubling isn’t the best strategy. The optimal solution is actually to add a constant increment to packet size. How much depends on relative cost of the terms in the regret function.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by brandmeyer on Sunday May 02, 2021 @04:23PM (#27018688)
> I’ve set the page size to 1 KiB for this database.

> That’s because I implemented a pre-fetching system that tries to detect access patterns through three separate virtual read heads and exponentially increases the request size for sequential reads.

> Since you're doing a reverse table scan my "sequential access" detection doesn't kick in.

You know, starting off with the default 4kB page size naturally adds some resistance to these kinds of failure cases. If the VFS isn't issuing many requests in parallel, I would think that setting up a page size near target_bandwidth * round_trip_time would be a better initial guess. 1kB would be appropriate for a pretty low latency-bandwidth product.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by phiresky on Sunday May 02, 2021 @05:22PM (#27019155)
That's true, but it also means that random access will always use at least that amount of data even if it only has to fetch a tiny amount. I did a few (non-scientific) benchmarks on a few queries and 1kB seemed like an OK compromise.

And note that the request chunk size is bound to the SQLite page size, and to change that page size you have to rewrite the whole DB. So it can't be set on the fly unless you have multiple copies of the database.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by pjc50 on Sunday May 02, 2021 @05:45PM (#27019341)
1kb fits in most IP MTU sizes, so that seems reasonable.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by kelnos on Monday May 03, 2021 @03:05PM (#27029341)
Do most HTTP responses have less than ~500 bytes of headers? I guess specifically here, GH pages' responses.

It looks like one of the requests made to the DB included a little over 700 bytes of response status line and headers, so that would probably end up spilling into more than one response packet, unfortunately.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by londons_explore on Wednesday May 05, 2021 @03:39AM (#27047198)
With http/3 header compression, I assume the answer is yes.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by simonw on Sunday May 02, 2021 @03:31PM (#27018205)
Hah, I thought "in reverse order by ID" might be a stress test but I was still very impressed by how it performed!
Re:Hosting SQLite databases on GitHub Pages or any static fi... by bbkane on Sunday May 02, 2021 @03:24PM (#27018124)
Would it be possible to use a datasette frontend with this as a "backend" and statically host the whole thing?
Re:Hosting SQLite databases on GitHub Pages or any static fi... by simonw on Sunday May 02, 2021 @03:28PM (#27018174)
Not easily - Datasette is written in Python, so you could try running it in WebAssembly (like Mozilla did for Jupyter with https://hacks.mozilla.org/2019/03/iodide-an-experimental-too... ) but it would be enough work that it might be easier to reimplement a subset of Datasette directly in JavaScript.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by rewq4321 on Friday May 07, 2021 @10:00AM (#27075941)
I've got no experience with Datasette, but if it's all in pure python, then you can use Pyodide[0] to run it within a wasm module. I was surprised how easy it was - took about 15 minutes and I had a Python package working perfectly in the browser. The Python package is about 3x faster than the JS/wasm version, but I'm guessing that performance gap will narrow when we get wasm-gc.

[0] https://github.com/pyodide/pyodide

Re:Hosting SQLite databases on GitHub Pages or any static fi... by bbkane on Sunday May 02, 2021 @03:39PM (#27018280)
Thank you!
Re:Hosting SQLite databases on GitHub Pages or any static fi... by mohanmca on Wednesday May 05, 2021 @09:37PM (#27058173)
Amazing use of SQLite! Thanks!
Re:Hosting SQLite databases on GitHub Pages or any static fi... by f430 on Sunday May 02, 2021 @07:08PM (#27019919)
mind blown. how is this possible???
Re:Hosting SQLite databases on GitHub Pages or any static fi... by Salgat on Monday May 03, 2021 @02:31AM (#27022254)
Use Http Range (normally used for pausing and continuing large file downloads) to request specific byte ranges from the file. From there you can pull only what you need. With sql indexes it'll be very tiny since the lookup is optimized. Of course if you select *, you're still going to pull the entire database locally.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by havernator on Sunday May 02, 2021 @09:24PM (#27020703)
TL;DR http, properly implemented, supports a ton more stuff than even many “web developers” are aware of, like… range requests, which are exactly what you’d think they’d be.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by teruakohatu on Sunday May 02, 2021 @10:45PM (#27021108)
Also SQLite store data in pages and the page size can be tweaked. Combined with range requests any part of the database can be requested.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by Cullinet on Sunday May 02, 2021 @10:52PM (#27021139)
the most recent update to the W3C's own research webserver, written in Java, called Jigsaw, seems to be dated in 2007. I used it for a lot of purposes until 2002 but I don't know why I stopped working with Jigsaw only that by the time F# emerged in 2004 I was absorbed into a new direction :

https://jigsaw.w3.org/

iirc Jigsaw was used to develop and validate the WebDAV protocols and XQUERY which at the time I remember thinking XQUERY was sure to be the future as implementations of advanced data management and manipulation and query and distribution and declaration looked to be what the whole point of webservers were for. The incredible distractions caused by "rich media" as opposed to multimedia as it was understood then, are really worth thinking about. Saying that, however, the BBC is doing excellent work on restoring the balance of necessary powers to the network standards engineers https://www.bbc.co.uk/sounds/help/questions/about-bbc-sounds...

https://www.bbc.co.uk/rd/blog/2014-03-media-source-extension...

https://www.bbc.co.uk/rd/projects/nearly-live-production

Re:Hosting SQLite databases on GitHub Pages or any static fi... by tuukkah on Monday May 03, 2021 @04:58PM (#27030553)
The last link was a very interesting read - I wonder if BBC or anyone else has open-sourced a video streaming and editing system like that.

Connecting this to HTTP range requests, the edited video feed can consist of a list of the exact byte ranges that the clients need to download and play. Found this description of how Akamai uses range requests to serve low-latency streams: https://blogs.akamai.com/2020/11/using-ll-hls-with-byte-rang...

Re:Hosting SQLite databases on GitHub Pages or any static fi... by f430 on Monday May 03, 2021 @01:03PM (#27027630)
gpt-2 ?
Re:Hosting SQLite databases on GitHub Pages or any static fi... by Cullinet on Sunday May 02, 2021 @11:01PM (#27021189)
BTW thank you havernator, because I have just realised what I can do with the setup I'm almost ready to pull the trigger on that'll give me a surfeit of online capacity (at least a baseload can be maintained while the rest is used for work instead of cloud time) : I am definitely going to investigate the possibility of providing a high level of standards specifications for simple web serving. If the W3C Jigsaw project had been maintained, I'd simply put it up and invite interested users to persuade me to send them a shell login. OK obviously that's far too naive today, but I would love to run a especially standards compliant host for negligible nominal or even no charge so people could maybe get a view of better ways to present the WWW.

frankly I think that unless we do things like this, the Internet is simply going to become a closed shop to anyone not wielding enterprise budgets and legal department capabilities.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by batch12 on Monday May 03, 2021 @08:39AM (#27024273)
Not trying to offend, but this comment was hard to follow in a weird way. Along with your profile this makes me wonder-- are you gpt-2?
Re:Hosting SQLite databases on GitHub Pages or any static fi... by wildrhythms on Monday May 03, 2021 @06:45PM (#27031846)
Their profile contains a full essay of near incomprehensible mumbo jumbo:

https://news.ycombinator.com/user?id=Cullinet

Re:Hosting SQLite databases on GitHub Pages or any static fi... by amjd on Monday May 03, 2021 @09:22AM (#27024718)
So I'm not alone. I felt that too!
Re:Hosting SQLite databases on GitHub Pages or any static fi... by WJW on Sunday May 02, 2021 @03:22PM (#27018098)
So assuming no country has a name longer than 98 characters and that all country codes are 2 characters, that is over 500% overhead? Are you missing a /s in your post?
Re:Hosting SQLite databases on GitHub Pages or any static fi... by phiresky on Sunday May 02, 2021 @03:41PM (#27018305)
Since random accesses across the internet are really slow, for this kind of fairly small table (where SQLite stores the row data inline within the B-Tree of the table) it basically fetches the whole content for each row - so even if you query only the long_name and country_code column, it will in fact fetch the data of all 29 columns in that table.

If you want it to fetch less data for querying a subset of columns, you could create create an index on those columns - then SQLite will do an COVERING INDEX scan and thus read only the necessary data (with the B-Tree itself and the start / end page alignment being the only overhead).

Re:Hosting SQLite databases on GitHub Pages or any static fi... by cookguyruffles on Sunday May 02, 2021 @06:55PM (#27019831)
Nothing to add to the conversation, just wanted to say I absolutely adore this. Years ago I used to think it'd be nice if search engines simply exposed all their shards to clients and let clients do all the results merging and suchlike. Of course that's probably a terrible idea in practice for performance, but this library is definitely implementing the spirit in a very practical way!

It also reminded me of a vague inverse of this hack. In old versions of Qemu (possibly it is still implemented, but I have vague memories it got ripped out), you could point Qemu at a directory on disk and it'd produce an emulated floopy disk drive with a virtual FAT12 image containing the directory contents. AFAIK it didn't keep the actual data in memory, I guess all it needed was file sizes to know how to build a virtual memory mapping that contained the filesystem metadata + proxied reads from the underlying files for data sectors. I look forward to seeing your implementation of this concept in a virtualized SQLite file <-> GraphQL proxy ;)

edit: insane, it still exists and apparently supports write mode?! https://en.wikibooks.org/wiki/QEMU/Devices/Storage#Virtual_F...

Re:Hosting SQLite databases on GitHub Pages or any static fi... by watermelon0 on Sunday May 02, 2021 @03:30PM (#27018194)
Have you actually read the article? SQLite is unmodified, and thinks it runs on a virtual file system, which fetches file chunks via HTTP range headers.

It's REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by no-s on Monday May 03, 2021 @09:08PM (#27032958)
> It's REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.

the harsh reality is that doing sensible queries that only reference and return the data actually needed always makes things faster. Even with server DBMS. Oh, how many times have I lamented the naive "select *" for forcing all the row contents even when there was index coverage for the actually needed data.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by throwawayboise on Sunday May 02, 2021 @06:54PM (#27019819)
Do most static site hosters support range requests?
Re:Hosting SQLite databases on GitHub Pages or any static fi... by dspillett on Sunday May 02, 2021 @07:01PM (#27019874)
Most web servers do out of the box, so I would assume most do. Basically all unless they have some reason to turn range processing off or are running a custom/experimental/both server that have implemented the feature (yet).

Not supporting range requests would be a disadvantage for any service hosting large files. Resuming failed long downloads wouldn't work so users might not be happy and there would be more load on your bandwidth and other resources as the AU falls back to performing a full download.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by nbevans on Monday May 03, 2021 @04:07AM (#27022687)
Generally yes. Because not having range support means you can't resume file downloads. Which is a pretty essential feature for a static file host.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by derefr on Monday May 03, 2021 @12:08AM (#27021523)
More interestingly, do reverse-proxies like Varnish / CDNs like Cloudflare support range requests? If so, do they fetch the whole content on the back, and then allow arbitrary range requests within the cached content on the front?
Re:Hosting SQLite databases on GitHub Pages or any static fi... by matsur on Monday May 03, 2021 @01:59AM (#27022065)
Yes, Cloudflare behaves as you describe.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by NoInkling on Sunday May 02, 2021 @11:58PM (#27021477)
I was wondering that too. Support was spotty in general ~20 years ago but I assume things have improved since then.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by OOPMan on Sunday May 02, 2021 @06:52PM (#27019791)
It's impressive on one hand.

On the other it's still a lot of overhead.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by pfundstein on Sunday May 02, 2021 @07:46PM (#27020126)
I would say it's less overhead than downloading the entire db to query it locally...? What is your suggestion for accessing a static database with less overhead?
Re:Hosting SQLite databases on GitHub Pages or any static fi... by OskarS on Monday May 03, 2021 @03:17AM (#27022467)
I would bet that if you compare it to a traditional server-client database (which functionally does essentially the same thing: you send it a query over the network, and get a result back), the overhead is probably massive. This is a very clever way to cram that kind of functionality into a static hosting site, and you can imagine some uses for it, but it's clearly not a particularly efficient compared to doing it the "right" way.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by Salgat on Monday May 03, 2021 @02:35AM (#27022272)
The idea is that you're weighing the pros cons vs an actual live database. This is basically only a good idea if you're having someone else paying the hosting fees.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by OOPMan on Tuesday May 04, 2021 @08:12AM (#27036727)
Since it's a static database and the queries against it are most likely going to be static, just pre-run the queries and store the results statically in a more space-efficient format. When you're on a dogshit internet connection in a 3rd world country 50kb can actually be pretty unpleasant. Try rate limiting your internet to EDGE rated to see what I mean.

I'm not saying the whole thing isn't impressive, just that the concept itself is one of those "because I can" rather "because I should" things, which kinda devalues it a whole lot.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by ineedasername on Sunday May 02, 2021 @08:11PM (#27020296)
For a casual or personal use case though, the alternative of running a client-server database on something like a VPS is probably more overhead than this. It's unlikely to be a very scalable option, but for use cases as described by the author it seems like a good fit.
Re:Hosting SQLite databases on GitHub Pages or any static fi... by littlecranky67 on Monday May 03, 2021 @03:35AM (#27022556)
I know of the drawbacks of the approach and wouldn't chose it for a lot of my projects, but I would say it is very scalable in those cases where I would. Put the DB on GitHub Pages for free along with your HTML/JS code and you can scale to whatever GitHub is willing and capable of delivering. Yes, your users might transfer way more data than needed but you pay nothing for it and do not have to maintain servers.

In the standard scenario for personal projects (not enterprise) I would have a small VPS/Dedicated server with a REST service running - that would be hugged to death immediately if a link would make it to some site like HN. And also, I completely share the experience of the Author that after a couple of years you have moved on, the VPS is dead etc and you don't want to invest time.

Again, before considering using solution, be sure to understand how it works and the resulting limitations or you will likely chose wrong.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by ajfjrbfbf on Sunday May 02, 2021 @03:30PM (#27018190)
> sql.js only allows you to create and read from databases that are fully in memory though - so I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem: sql.js-httpvfs. From SQLite’s perspective, it just looks like it’s living on a normal computer with an empty filesystem except for a file called /wdi.sqlite3 that it can read from.

From this paragraph it should be pretty clear that it's actually a great result. The database will obviously need to read more data than it presents, so more is fetched.

Re:Hosting SQLite databases on GitHub Pages or any static fi... by nl on Sunday May 02, 2021 @07:55PM (#27020190)
This might be true.

But this approach lets you actually work out what the optimal size is:

  select sum(length(country_code) + length(long_name)) from wdi_country;
gives: 6307

Or on average:

  select sum(length(country_code) + length(long_name))/count(*) from wdi_country;
gives: 23

(Note that it doesn't seem possible to use aggregation functions with a limit clause)

Re:Hosting SQLite databases on GitHub Pages or any static fi... by simonw on Sunday May 02, 2021 @03:29PM (#27018183)
The clever part here is only fetching 50KB out of 680MB.