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

51–60 of 252 posts

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

#51

Post by westurner »

phiresky wrote: post #20
misterdata wrote: post #8

Earlier quoted context omitted.

See also https://github.com/bittorrent/sqltorrent , same trick but using BitTorrent

Yeah, that was one of the inspirations for this. That one does not work in the browser though, would be a good project to do that same thing but with sqlite in wasm and integrated with WebTorrent instead of a native torrent program. I actually did also implement a similar thing fetching data on demand from WebTorrent (and in turn helping to host the data yourself by being on the website): https://phiresky.github.io/t…

This looks pretty efficient. Some chains can be interacted with without e.g. web3.js? LevelDB indexes aren't SQLite.

Datasette is one application for views of read-only SQLite dbs with out-of-band replication. https://github.com/simonw/datasette

There are a bunch of *-to-sqlite utilities in corresponding dogsheep project.

Arrow JS for 'paged' browser client access to DuckDB might be possible and faster but without full SQLite SQL compatibility and the SQLite test suite. https://arrow.apache.org/docs/js/

https://duckdb.org/ :

> Direct Parquet & CSV querying

In-browser notebooks like Pyodide and Jyve have local filesystem access with the new "Filesystem Access API", but downloading/copying all data to the browser for every run of a browser-hosted notebook may not be necessary. https://web.dev/file-system-access/

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

#52

Post by simonw »

phiresky wrote: post #46
simonw wrote: post #41

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/13889338…

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 ord…

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

#53

Post by detaro »

CyberRabbi wrote: post #14

Over high latency links this would be virtually unusable. Why not just download the entire database into memory over XHR on page load? SQLite databases of pure data usually aren’t over 10MB in size.

And the article starts by mentioning that you can download the entire file if it's not too large. And then goes on to present a solution for larger files. What more answer to "Why not just download the entire database" do you expect?

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

#54

Post by bbkane »

simonw wrote: post #47
bbkane wrote: post #45

Earlier quoted context omitted.

Would it be possible to use a datasette frontend with this as a "backend" and statically host the whole thing?

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.

Thank you!

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

#55

Post by fulafel »

pudmaidai wrote: post #21
Gehinnn wrote: post #2

TL;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.

I can't figure out exactly how it knows which chunk to download. Does it always download the whole index first? Or does it include it in the built JS file itself?

B-Tree indexes are designed to work like this, to require a low number of IO operations. The index contains pointers to other places in the index.

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

#56

Post by phiresky »

WJW wrote: post #43
simonw wrote: post #41

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/13889338…

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?

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

#57

Post by brandmeyer »

CyberRabbi wrote: post #38
formerly_proven wrote: post #35

Earlier quoted context omitted.

This particular demo page actually makes queries against an almost 700 MB large (fills one CD!) SQLite database. Because the amount of data read is almost negligible (few hundred kB), performance is limited by latency (as you say). However, high-latency links also tend to be slower, so downloading the entire database a-priori would almost always be much slower. For example, on a 1 megabit/s link with 300 ms RTT, one…

Right but that is an artificially created demo by the author to justify the solution being presented (no offense). The question is how common are ~GB large SQLite databases in the real world relative to databases that are ~MB large? In my experience SQLite databases of millions of rows of raw tabular data tend to compress very well into dozens of megabytes. Indeed SQLite is often touted as a file format for applicati…

My team has a few TB of data in SQLite files that are themselves dozens of GB each.

We're using them as a replacement for leveldb's sstables, but with the structure of full SQL. It is highly effective.

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

#58

Post by laurencerowe »

Ultimatt wrote: post #36
laurencerowe wrote: post #19

In the genomics world, Tabix indices enables similar use cases. An ordered TSV file is compressed in chunks (bgzip) and a Tabix index created to allow range based access by mapping from the index -> chunk. This allows a browser based genome browser zoomed into a section of the genome to fetch information from a multi gigabyte file. http://www.htslib.org/doc/tabix.html

now if only tabix and most hokey bioinformatics formats would die and just be replaced with a formal schema spec in SQLite...

Honestly I think Tabix's bgzipped TSV is one of the less hokey bioinformatics formats, at least compared to the various custom binary formats floating around.

For web browser based genome browsers I suspect this (very cool!) sqlite hack would require many more http requests.

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

#59

Post by pudmaidai »

fulafel wrote: post #55
pudmaidai wrote: post #21

Earlier quoted context omitted.

I can't figure out exactly how it knows which chunk to download. Does it always download the whole index first? Or does it include it in the built JS file itself?

B-Tree indexes are designed to work like this, to require a low number of IO operations. The index contains pointers to other places in the index.

So the answer is “yes, it has to download the index first”? None of these comments answer my question.

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

#60

Post by drtournier »

Solution works really well to databases which will not be updated frequently, like a standalone site.

Although one should be aware of one very important git behavior - git does not diff binary files (like SQLite dbs). That means 2 things:

1. Each db update will generate a new file in git, maintaining the whole old file in history, instead of the diff in bytes. This will accumulate a lot of clutter in the repo

2. As git does not diff binaries, there is a very small risk of corruption (especially if you work in multiple OSs, because of CRLF)

Ref - https://robinwinslow.uk/dont-ever-commit-binary-files-to-git

Post reply on HN