Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Hosting SQLite databases on GitHub Pages or any static file hoster

phiresky.github.io

Post reply on HN
Print view View original

151–160 of 252 posts

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#151

Post by altfredd »

crazygringo wrote: post #87
tlb wrote: post #74

The question I had is answered by this line of code: xhr.setRequestHeader("Range", "bytes=" + from + "-" + to); I am a little surprised you can just do that. In https://github.com/phiresky/sql.js-httpvfs/blob/master/src/l...

Not all webservers support/enable it, so YMMV. But as long as you're dealing with a known server that does, then gravy!

> Not all webservers support/enable it

Could you provide an example of server that does not?

AFAIK, Range is supported by all major CDNs, so not supporting it in web server would be a death knell for it's real-world adoption.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#153

Post by kindall »

iudqnolq wrote: post #134
fanf2 wrote: post #114

Earlier quoted context omitted.

One of the heaviest users of range requests is (or was) the Adobe Acrobat PDF plugin.

I'm surprised that works, iirc pdf isn't defined in order and can't be parsed streaming

I think that's what the "optimize for Web" checkbox does

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#154

Post by killingtime74 »

banana_giraffe wrote: post #99

In a similar vein, I've mentioned this before, but if you're doing Python stuff, you can use the apsw package (not the one in PyPi, though) to write a VFS layer that SQLite will use to read the database. I've used this for the same basic idea as this article, only letting me store SQLite databases in AWS's S3 that I can access with AWS APIs so they don't need to be public. It works well, though it's absolutely not fo…

This one? https://rogerbinns.github.io/apsw/

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#155

Post by scottlamb »

dreix wrote: post #67

Over the last few months I tried to think of a clever way to set up a legacy site for a dictionary that I serve on a VM just because I also need to run sqlite. Since I want to make sure it'll run for longer than me paying for the VM this is the best possible solution. At some point no more updates will happen and it's going to be a static website. So bundling it like this is incredible. I can run multiple backups on…

If you just do an occasional key/value lookup, you don't need 1.2 MiB of WebAssembly. [1] That might already exceed your total database size.

I'd solve it via sharding: divide the database into N pieces via range- or hash-sharding. [1] Choose an N that's large enough for each piece to be reasonably small. When you look up a key, fetch the shard of interest.

You can put each piece into separate files (a little simpler to code, and most static servers will use pre-gzipped files for "Content-Encoding: gzip requests" easily, but you waste more disk space due to internal fragmentation) or one file (with range serving and an index of the byte range offset for each piece).

The format for each piece can be anything, eg json (simple) or an sstable-like format (more efficient). [3]

[1] Content-Length of https://phiresky.github.io/youtube-sponsorship-stats/sql-was...

[2] hash-sharding means: piece[i] has all the keys where hash(key) % N = i.

[3] https://github.com/google/leveldb/blob/master/doc/table_form... although they just say "formatted according to the code in block_builder.cc" instead of describing the most relevant part.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#156

Post by blacha »

Valgrim wrote: post #122
ianlevesque wrote: post #16

This is easily the most clever web programming hack I’ve seen this year. Bravo. I had seen this used for video or audio of course but it never occurred to me you could use it for databases. There are probably a ton of other formats this is good for too.

I wonder if this could be used to serve dynamic maps.

This is pretty much exactly what we do to serve aerial/satellite imagery maps.

We convert the imagery into Cloud optimised geo tiffs and store them in S3 https://www.cogeo.org/ then the browser can request the tiles directly from S3.

Even the big imagery providers are now storing their imagery as COGs, eg https://registry.opendata.aws/sentinel-2-l2a-cogs/

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#157

Post by bdon »

durkie wrote: post #140
Valgrim wrote: post #122

Earlier quoted context omitted.

I wonder if this could be used to serve dynamic maps.

I believe this is protomaps approach: re-encode the mbtiles (sqlite-based ) format in to something that can be requested with a http range request and thus served from a single dumb webserver that doesn't need to understand sqlite or mbtiles parsing

This is the approach I took with http://github.com/protomaps/pmtiles , though it's optimized for the very specific use case of going from Z/X/Y integer coordinates to binary blobs, and takes shortcuts to accomplish that (fixed-width keys and root index page)

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#158

Post by mythrwy »

CyberRabbi wrote: post #142
mythrwy wrote: post #139

Earlier quoted context omitted.

I have been using SQLite databases for a few user application that has been running for close to a decade now. They are usually about 1.5GB. BTW, SQLite has a (theoretical?) max size of 140TB! (or so I've read)

What do you think is the size of the average SQLite database?

Given their widespread uses on phones, the overall average is likely very small. KBs even.

A more relevant question might be: what would the average size of SQLite databases for web type (or even this specific use case) applications. I don't know, but 10s or 100s of MBs might not be a bad guess.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#159

Post by NoInkling »

throwawayboise wrote: post #102
watermelon0 wrote: post #50

Earlier quoted context omitted.

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.

Do most static site hosters support range requests?

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 file hoster

#160

Post by altano »

city41 wrote: post #44
mvanaltvorst wrote: post #22

Earlier quoted context omitted.

I'm curious, in what manner could this method speed up Next.js builds? That's all done locally, which negates the effect of HTTP range requests, right?

I'm guessing they mean rather than build a static Next site that generates 10k+ pages (or whatever large means in the given context), it instead creates one page that just queries the data from the client. I have one Next static site that has about 20k pages and takes about 20 minutes to build and deploy. I think that's an acceptable build time. But I do know of other people around the net who have mentioned having s…

For really large sites Next.js already has Incremental Static Regeneration which is usually the right solution to fast [re]builds: https://www.smashingmagazine.com/2021/04/incremental-static-...
Post reply on HN