That&#x27;s part of SQLite. It has been optimised to reduce disk reads, because those can be slow on spinning hard drives. Coincidentally, this translates well into an optimised algorithm that minimises the amount of HTTP range requests to make.

SQLite has runtime-pluggable VFS support, i.e. you give it a struct with functions for opening a file, reading some bytes, writing some bytes, synchronizing file contents, closing a file. This project provides such a VFS module, that, because it actually runs in the browser, performs HTTP requests to read data. Emscripten provides a way to run a mix of C&#x2F;C++ code in the same environment as some JavaScript code inside the browser. The reason SQLite has this pluggable VFS support is to properly support embedded systems, different locking APIs, and things like database encryption.<a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;vfs.html" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;vfs.html</a>

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.

Both the index and table data are btrees. These are trees - the root node sits in some known location (offset) in the file, referenced by the file header and metadata. As SQLite traverses the tree, it encounters new descendents it would like to visit, presumably identified by their byte offset in the file, which is all needed for this VFS magic to issue a suitable range request.- SQlite opens the file and reads 4kb worth of header -&gt; range request for byte 0-4096- headers&#x2F;metadata refers to index table with root node at 8192kb- user issues SELECT * from index WHERE name = &#x27;foo&#x27;- SQLite reads root node from the file (range request for 8192kb..)- Root node indicates left branch covers &#x27;foo&#x27;. Left branch node at address 12345kb- Fetch left branch (range request for 12345kb)- New node contains an index entry for &#x27;foo&#x27;, row 55 of data page at 919191kb- SQLite reads data page (range request for 91919191kb..)etc etc etc

I can&#x27;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?

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.

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&#x2F;CD solution just deploy it instead?There has to be a git.js implementation out there and you could move the DB to it&#x27;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.

&gt; 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&#x27;m sure there are use cases in peer to peer applications. Anyway, amazing innovation thank you for writing this :)

&gt; 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.If ever the intent were to involve eventually persisting those changes, then it would be worthwhile looking at remoteStorage, which works like this.

Seems like it might be possible to add WebRTC(or some other peer-based live system, I don&#x27;t know the performance characteristics of WebRTC) to get realtime writes. Those edits would then be added to the WAL, and every say, 10 minutes anyone who has been active for 10 minutes could checkpoint and push the changes to github.It&#x27;s slightly more centralized than perfect, but man do you get a lot for a little.

I was thinking you could implement a write API using the GitHub API, every write can be its own commit.

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&#x27;s already exactly what the WAL mode does: It&#x27;s a second file next to the database that&#x27;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&#x27;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&#x27;m not sure what the actual use case would be ;)

This 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.

Sort of. Access had a &quot;Forms&quot; feature that let you create basic GUIs on top of your database. Also, the OP&#x27;s project is (currently) only providing a read-only view of the SQLite database. Adding write support is possible but will be far less impressive to the HN crowd because SQLITE_BUSY will rear its ugly head ;-)

Microsoft Access Cloud Edition, basically?

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): <a href="https:&#x2F;&#x2F;phiresky.github.io&#x2F;tv-show-ratings&#x2F;" rel="nofollow">https:&#x2F;&#x2F;phiresky.github.io&#x2F;tv-show-ratings&#x2F;</a> That uses a protobufs split into a hashmap instead of SQLite though.

See also <a href="https:&#x2F;&#x2F;github.com&#x2F;bittorrent&#x2F;sqltorrent" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;bittorrent&#x2F;sqltorrent</a>, same trick but using BitTorrent

I&#x27;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&#x27;s an acceptable build time. But I do know of other people around the net who have mentioned having sites with 20k-ish pages taking an hour+ to build. For them I could see the desire to try this sqlite trick.

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

yea, sqlite FTS5 has been pretty amazing for quick search solutions (but I use english only)

Definitely. Just need to add a layer to the static site generator for it to populate the SQLite DB, right?

Would also be great to add (efficient) search to a static blog.

The 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?

This seems cool, but doesn&#x27;t Fossil (from the SQLite author) already do it &quot;out of the box&quot; ?<a href="https:&#x2F;&#x2F;www.fossil-scm.org&#x2F;home&#x2F;doc&#x2F;trunk&#x2F;www&#x2F;index.wiki" rel="nofollow">https:&#x2F;&#x2F;www.fossil-scm.org&#x2F;home&#x2F;doc&#x2F;trunk&#x2F;www&#x2F;index.wiki</a>

Incredibly clever use of paging and http range queries! Well done.

&gt; SQLite databases of pure data usually aren’t over 10MB in sizeWhy do you think this?

It’s not hilariously wrong or wrong at all that over high latency links this would be virtually unusable.It’s certainly possible that people are using SQLite databases with sizes on the order of gigabytes but in my experience those are the exception not the rule.

That is hilariously wrong for a lot of use cases. I will find this very handy for some SQLite databases I have that are several GBs in size. I am looking right now at using this contribution.

The demo here uses a 670MB database file.

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 applications.

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&#x2F;s link with 300 ms RTT, one example would take about 2 seconds for the data transfer itself while spending another 3 seconds or so on waiting. Downloading the entire file would take around an hour and a half.For your 10 MB database, transferring it as a whole would take 80 seconds. Assuming this solution instead needs to read e.g. 250 kB (taking 2 seconds to transfer), it could still bounce around 250 times to the database before those 10 MB are fully downloaded. (This would be a really odd query, since it would only read on average two pages per read request)

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

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

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&#x27;ve read)

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.

This paper is basically what we do, except we have a big container type, aptly named &quot;Domain.cs&quot;. Inside, you will find a public List&lt;T&gt; of every type. We decided to emulate SQL throughout the vertical (i.e. each List is a table) in order to make mapping to SQL a trivial affair. None of our Domain types contains any complex type as a property. Everything can go 1:1 to SQL. We use LINQ (or SQL) to produce projections as appropriate.There isn&#x27;t actually just 1 big domain instance either. It&#x27;s more like one per user session, and then a global instance.The impact this had on reducing complexity and bugs is incredible. I haven&#x27;t seen a null ref exception in a long time. Also, being able to dump your entire universe to disk by serializing a single object is really nice.

There was a group that rewrote the Chromium DOM itself in a data oriented design (which learns from database design and sort of applies to cache utilization and locality) and got a 6X speedup in some places:<a href="https:&#x2F;&#x2F;meetingcpp.com&#x2F;mcpp&#x2F;slides&#x2F;2018&#x2F;Data-oriented%20design%20in%20practice_Nikolov_MeetingCpp18.pdf" rel="nofollow">https:&#x2F;&#x2F;meetingcpp.com&#x2F;mcpp&#x2F;slides&#x2F;2018&#x2F;Data-oriented%20desi...</a>

Modeling the DOM in SQL... Further evidence that anything we can imagine has some stable representation in third normal form. Is it fast? Maybe not. But is it correct? Provably.

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&#x27;t need to understand sqlite or mbtiles parsing

This is pretty much exactly what we do to serve aerial&#x2F;satellite imagery maps.We convert the imagery into Cloud optimised geo tiffs and store them in S3 <a href="https:&#x2F;&#x2F;www.cogeo.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.cogeo.org&#x2F;</a> then the browser can request the tiles directly from S3.Even the big imagery providers are now storing their imagery as COGs, eg <a href="https:&#x2F;&#x2F;registry.opendata.aws&#x2F;sentinel-2-l2a-cogs&#x2F;" rel="nofollow">https:&#x2F;&#x2F;registry.opendata.aws&#x2F;sentinel-2-l2a-cogs&#x2F;</a>

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

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.

Lunr needs the full index on client though, right?Being able to use fts-5 without the penalty of having to pull down the whole index make it work much better at larger scales, even with the penalty of additional network requests.

Time to create the index for lunr limits the size of the data-set that can be used. If you have a lot of tiny documents, then it is more practical to scan the documents directly, rather than using the lunr index.

If you are interested in full-text search on the client, Lunr is also an option: <a href="https:&#x2F;&#x2F;lunrjs.com&#x2F;docs&#x2F;index.html" rel="nofollow">https:&#x2F;&#x2F;lunrjs.com&#x2F;docs&#x2F;index.html</a>

Amazing, especially - for me - that the FTS5 full-text search just works. Longer term, I am if it were possible to split the DB code into read and write parts and cross-compile only read part for delivery to the browser.

This is awesome. Got my wheels turning :)

Honestly I think Tabix&#x27;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.

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

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 -&gt; chunk. This allows a browser based genome browser zoomed into a section of the genome to fetch information from a multi gigabyte file.<a href="http:&#x2F;&#x2F;www.htslib.org&#x2F;doc&#x2F;tabix.html" rel="nofollow">http:&#x2F;&#x2F;www.htslib.org&#x2F;doc&#x2F;tabix.html</a>

Wicket stuff, loved it. Not sure if this will ever be “production ready” or merged by sql.JS-like projects but cool proof of concept.

Nice. Reminds me of Bellard’s <a href="https:&#x2F;&#x2F;vfsync.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;vfsync.org&#x2F;</a> . It’s file system pages served over http, to back his jslinux browser VMs

Using HTTP range requests like this is just SO clever.

The clever part here is only fetching 50KB out of 680MB.

&gt; 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 &#x2F;wdi.sqlite3 that it can read from.From this paragraph it should be pretty clear that it&#x27;s actually a great result. The database will obviously need to read more data than it presents, so more is fetched.

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&#x27;s REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.

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 &#x2F; end page alignment being the only overhead).

This might be true.But this approach lets you actually work out what the optimal size is:<pre><code> select sum(length(country_code) + length(long_name)) from wdi_country;
</code></pre>
gives: 6307Or on average:<pre><code> select sum(length(country_code) + length(long_name))&#x2F;count(*) from wdi_country;
</code></pre>
gives: 23(Note that it doesn&#x27;t seem possible to use aggregation functions with a limit clause)

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 &#x2F;s in your post?

Not easily - Datasette is written in Python, so you could try running it in WebAssembly (like Mozilla did for Jupyter with <a href="https:&#x2F;&#x2F;hacks.mozilla.org&#x2F;2019&#x2F;03&#x2F;iodide-an-experimental-tool-for-scientific-communicatiodide-for-scientific-communication-exploration-on-the-web&#x2F;" rel="nofollow">https:&#x2F;&#x2F;hacks.mozilla.org&#x2F;2019&#x2F;03&#x2F;iodide-an-experimental-too...</a> ) but it would be enough work that it might be easier to reimplement a subset of Datasette directly in JavaScript.

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

Hah, I thought &quot;in reverse order by ID&quot; might be a stress test but I was still very impressed by how it performed!

&gt; I’ve set the page size to 1 KiB for this database.&gt; 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.&gt; Since you&#x27;re doing a reverse table scan my &quot;sequential access&quot; detection doesn&#x27;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&#x27;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.

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.

Huh, that&#x27;s actually kind of a worst case I didn&#x27;t think about: Since you&#x27;re doing a reverse table scan my &quot;sequential access&quot; detection doesn&#x27;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 &gt;= 164 order by rowid asc limit 100;

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.

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&#x27;ll be very tiny since the lookup is optimized. Of course if you select *, you&#x27;re still going to pull the entire database locally.

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is:<pre><code> select country_code, long_name from wdi_country
 order by rowid desc limit 100
</code></pre>
It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that&#x27;s 668.8MB!I have an animated GIF demo of this here: <a href="https:&#x2F;&#x2F;twitter.com&#x2F;simonw&#x2F;status&#x2F;1388933800445452290" rel="nofollow">https:&#x2F;&#x2F;twitter.com&#x2F;simonw&#x2F;status&#x2F;1388933800445452290</a>

Git does actually diff binaries and stores them very efficiently :) If you do a single small UPDATE in your db file git will only store the changed information. 
It&#x27;s just kinda slow, and for most binary files the effort git spends to try and compute deltas for binary files is useless - which is why it has a bad reputation for binary files.Note that the diffs that git shows you are completely unrelated to the deltas it uses to compress it&#x27;s database - which are always &quot;binary deltas&quot; and not line-based diffs.Also I&#x27;m not sure why you mean that db corruption possibility has something to do with whether or not it stores diffs?

This is mostly correct. Git&#x27;s core object model is snapshots, which can then optionally be compressed. That should be transparent though.

Minor nitpick: Git does not store diffs for any file format but always the full file for each version.
So it does not really matter that its binary (except for not being able to VIEW the diff, but I guess you could even implement a plugin for that) but just that it&#x27;s a big file. Even a huge text file would be fully stored per version.&#x2F;edit: The sibling comments mentions that git can infact delta compress older commits for storage efficency. But my point was that git commits are not deltas but full snapshots.

You are only gonna encounter corruption if u either a) messed up the gitconfig for line endings or b) named the database mydbfile.txt

You can just store the database in text format (e.g. csv) in the git and turn it to SQLite db when building the website.

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 repo2. 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 - <a href="https:&#x2F;&#x2F;robinwinslow.uk&#x2F;dont-ever-commit-binary-files-to-git" rel="nofollow">https:&#x2F;&#x2F;robinwinslow.uk&#x2F;dont-ever-commit-binary-files-to-git</a>

Same with those!  They&#x27;ll just grab the chunks they need (also with range requests) to do what you want from binary ROOT or (presumably, I haven&#x27;t actually used jsfive) HDF5 files.

I think you missed the main part: the dataset is not in memory. This is not SQLite-in-the-browser but using a virtual file system over HTTP. Neither of those alternatives do anything similar.

If you just want static data, using jsroot or jsfive might be a better option.

Yeaah I felt like at that point the article was already long enough so I didn&#x27;t bother describing the DOM part too much - even though I spent more time implementing that than I did implementing the rest ;)Basically SQLite has a virtual table mechanism [1] where you have to define a few functions that figure out how to scan your &quot;fake&quot; table &#x2F; which indices to use and then how to read &#x2F; write the actual data. I hook into this mechanism and redirect the request to DOM functions like querySelector() etc. Then there&#x27;s the issue about SQLite being fully synchronous, but I have to run it in a WebWorker - and the WebWorker can&#x27;t actually access the DOM and it can only communicate asynchronously with the main thread... So I have to do some weird stuff with SharedArrayBuffer and Atomics.wait to make that work [2].[1] <a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;vtab.html" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;vtab.html</a>
[2] <a href="https:&#x2F;&#x2F;github.com&#x2F;phiresky&#x2F;sql.js-httpvfs&#x2F;blob&#x2F;master&#x2F;src&#x2F;vtab.ts" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;phiresky&#x2F;sql.js-httpvfs&#x2F;blob&#x2F;master&#x2F;src&#x2F;v...</a>

&quot;But by that point I was so drawn in that I didn’t care and was just enjoying the ride.&quot;:-) There a high amount of SQLite content&#x2F;articles&#x2F;blogs on the web that can provide this effect. SQLite is to programmers like the stars are to astronomers. A wonder.

This is fantastically creative. And the author does a great job of starting out by describing why this is useful.And then using SQLite to insert and update DOM elements? Holy cow, icing on the cake. Unlike the first part, there’s no explanation of why you’d want to do that. But by that point I was so drawn in that I didn’t care and was just enjoying the ride.

I am a huge fan of sqlite and will definitely be giving this a shot in the near future.Incredible work.I see myself using this in conjunction with a conventionally hosted pg db for dynamic content.

If you just do an occasional key&#x2F;value lookup, you don&#x27;t need 1.2 MiB of WebAssembly. [1] That might already exceed your total database size.I&#x27;d solve it via sharding: divide the database into N pieces via range- or hash-sharding. [1] Choose an N that&#x27;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 &quot;Content-Encoding: gzip requests&quot; 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 <a href="https:&#x2F;&#x2F;phiresky.github.io&#x2F;youtube-sponsorship-stats&#x2F;sql-wasm-dd22a976e2e149886e6e.wasm" rel="nofollow">https:&#x2F;&#x2F;phiresky.github.io&#x2F;youtube-sponsorship-stats&#x2F;sql-was...</a>[2] hash-sharding means: piece[i] has all the keys where hash(key) % N = i.[3] <a href="https:&#x2F;&#x2F;github.com&#x2F;google&#x2F;leveldb&#x2F;blob&#x2F;master&#x2F;doc&#x2F;table_format.md" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;google&#x2F;leveldb&#x2F;blob&#x2F;master&#x2F;doc&#x2F;table_form...</a> although they just say &quot;formatted according to the code in block_builder.cc&quot; instead of describing the most relevant part.

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&#x27;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&#x27;s going to be a static website. So bundling it like this is incredible. I can run multiple backups on different hosts with no additional costs.

This is an really awesome idea! I have a plan for a static-ish site (updated daily) that I was going to use sqlite for anyway, but server side. I will definitely look into this approach instead!

nginx enables it by default. Another ingenious use of range request is zsync, it allows you to diff compressed binaries on a remote with local ones, so that you only have to download what has changed on an update. AppImage uses this

&gt; Not all webservers support&#x2F;enable itCould 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&#x27;s real-world adoption.

Not all webservers support&#x2F;enable it, so YMMV.But as long as you&#x27;re dealing with a known server that does, then gravy!

Here is direct link to GitHub with the usage:
<a href="https:&#x2F;&#x2F;github.com&#x2F;dasmoth&#x2F;dalliance&#x2F;search?q=bytes%3D%27&amp;type=" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;dasmoth&#x2F;dalliance&#x2F;search?q=bytes%3D%27&amp;ty...</a>

I&#x27;ve learnt about this by using 
<a href="https:&#x2F;&#x2F;www.biodalliance.org" rel="nofollow">https:&#x2F;&#x2F;www.biodalliance.org</a>It&#x27;s an embedded genome viewer, you can just point it at a multigigabyte reference files and .seg files and it loads super quick

Range headers are also how DownloadThemAll works.If you&#x27;re pulling a single TCP stream across a crowded network, you get maybe 1&#x2F;40th of the available bandwidth. If you do four range requests instead, you might see &gt;3&#x2F;40th of the bandwidth.This place we were contracting at, the managers were widely rumored to stream sports games at their desks, and our release cycle happened to fall on a game day. My poor coworker was downloading our installer every week, and the ETA was over 40 minutes. &quot;Are you using DTA?&quot; &quot;No, what&#x27;s that?&quot; &lt;fiddle fiddle&gt; ETA: 12 minutes.12 minute pauses in the middle of a manual process are a lot easier to stomach than 40 minutes. Especially if something goes wrong and you have to do it twice.

Range headers are a pretty standard tools to e.g. continue interrupted downloads and similar.Any well designed system, especially if it has static sources and is server cached should support it.Surprisingly many web-frameworks don&#x27;t support it out of the box, or don&#x27;t support it well.Either way gh-pages are static content and probably with some server side regional caches, so I&#x27;m not surprised it works.

I&#x27;m surprised that works, iirc pdf isn&#x27;t defined in order and can&#x27;t be parsed streaming

Also .mp4 files. The format is designed for seekability, and browsers take advantage of this.

I remember putting together a sloppy http range implementation that initially only supported single ranges, it had quite the explosive effect on adobe reader when it didn&#x27;t get the expected response to its multi-range requests :)

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

I guess &quot;all&quot; that&#x27;s needed for write would be a webdav server with support for
PATCH x-update-range? :)<a href="https:&#x2F;&#x2F;tools.ietf.org&#x2F;html&#x2F;rfc5789" rel="nofollow">https:&#x2F;&#x2F;tools.ietf.org&#x2F;html&#x2F;rfc5789</a><a href="https:&#x2F;&#x2F;sabre.io&#x2F;dav&#x2F;http-patch&#x2F;" rel="nofollow">https:&#x2F;&#x2F;sabre.io&#x2F;dav&#x2F;http-patch&#x2F;</a>Unfortunately, solid, stand-alone webdav servers are harder to come by than decent http2&#x2F;1.1 servers.

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

Super cool. I wonder if an approach like this could ever work for a writable database. I guess the issue is that you couldn&#x27;t have fine grained access control without a whole lot of server side validation, at which point you might as well just run a regular database server.

That&#x27;s basically re-inventing the Database but on the client side. We have gone a long way but we are closer to having the server side just as a data store.

TA was clear - there&#x27;s no way to write, since static file hosting doesn&#x27;t support dynamic write to begin with.However, I imagine a service to support your scenario could be written in a standard back-end server language like Go or JS. The challenges involved would be much greater, however -- how to handle concurrency in particular. I suspect one would do better to just run PostgreSQL behind a web API.

Wow that&#x27;s fascinating, thanks. That would actually turn HTTP into a kind of random-access filesystem, if adopted.It&#x27;s amazing but also slightly terrifying in the knowledge that then someone&#x27;s going to write an SMB-over-HTTP connector.

The write equivalent would be the PATCH method using a &quot;message&#x2F;byteranges&quot; body: <a href="https:&#x2F;&#x2F;tools.ietf.org&#x2F;id&#x2F;draft-wright-http-partial-upload-01.html" rel="nofollow">https:&#x2F;&#x2F;tools.ietf.org&#x2F;id&#x2F;draft-wright-http-partial-upload-0...</a>

Even if there was, I can&#x27;t imagine your everyday static host ever supporting it.

This is hilariously clever.Using the &quot;Range&quot; HTTP header to read chunks of the database file absolutely works!But to be clear, there&#x27;s no write equivalent, is there? You can&#x27;t use &quot;Range&quot; with a PUT request.

Yep, exactly that one. There&#x27;s a simple example of a VFS implementation on the examples page that&#x27;s a reasonable starting point:<a href="https:&#x2F;&#x2F;rogerbinns.github.io&#x2F;apsw&#x2F;example.html" rel="nofollow">https:&#x2F;&#x2F;rogerbinns.github.io&#x2F;apsw&#x2F;example.html</a>Once you wrap your head around how you need to pass parameters to the helper, it&#x27;s really straightforward, you just need to implement the xOpen and xRead calls.

This one? <a href="https:&#x2F;&#x2F;rogerbinns.github.io&#x2F;apsw&#x2F;" rel="nofollow">https:&#x2F;&#x2F;rogerbinns.github.io&#x2F;apsw&#x2F;</a>

In a similar vein, I&#x27;ve mentioned this before, but if you&#x27;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&#x27;ve used this for the same basic idea as this article, only letting me store SQLite databases in AWS&#x27;s S3 that I can access with AWS APIs so they don&#x27;t need to be public. It works well, though it&#x27;s absolutely not for every use case, the overhead is considerable.I even used it once to read SQLite database files in a zip file stored in S3 without having any local storage to use. Not one of my prouder moments, but hey, I coded my way out of the corner someone else designed for me.

Very clever! Keep in mind this is a read-only dataset with the really clever bit being the ability to scan through the dataset and support sqlite query syntax.

Searching the Common Crawl does similar byte level access to S3 based on a smaller index of indexes. Really great, actually.

I&#x27;m also impressed with the implementation for the blog itself. I love this sort of clean, version-tracked sort of implementation.

I thought the &quot;static&quot; part of &quot;static file hoster&quot; was clear enough.

&gt;Of course it can’t write to this file, but a read-only database is still very useful.perhaps read-only should be added to the title

yeah I have come across Lunr and maybe a couple of other things in my research and I think for blogging it&#x27;ll work well. What I&#x27;m interested in finding out is what works for a larger static site, that won&#x27;t require you to load the index up-front. I&#x27;m also curious about how this sqlite thing picks the correct range to load (haven&#x27;t looked at the code) and what the worst case might be.

This certainly does look like an interesting solution, I&#x27;d be keen to try it myself. However, just in case you didn&#x27;t already know about Lunr (<a href="https:&#x2F;&#x2F;lunrjs.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;lunrjs.com&#x2F;</a>), it is fairly commonly used to implement search on static websites.e.g. <a href="https:&#x2F;&#x2F;squidfunk.github.io&#x2F;mkdocs-material&#x2F;" rel="nofollow">https:&#x2F;&#x2F;squidfunk.github.io&#x2F;mkdocs-material&#x2F;</a>There are of course other similar libraries too.EDIT: Whoops, just saw a few comments below Lunr is already mentioned.

This is perfect for my need, been looking for a way to add search to my static site completely free of server. Now I can use sqlite as index.

This is certainly a clever hack. But it makes things cheaper and easier for the website owner at the expense of the users. This kind of solution will add to the bloat that is making the web unusable enough that people are seriously proposing a remote browser as a solution for desktop users (remember Mighty the other day?). We shouldn&#x27;t let the cleverness distract us from this drawback.

This is pretty awesome.  Now if only GitHub Pages did IPv6 as well... :)

How close are we to hosting searchable torrent indices on IPFS?

This is incredible ! Don&#x27;t have a use case for this right now but am certainly saving this for later.

I think it wouldn&#x27;t change much - SQLite is already pretty optimized towards reads, for example a write always replaces a whole page and locks the whole DB. The free pages can easily be removed by doing VACUUM beforehand which should be done anyways to balance the b-trees.The storage of SQLite is already really efficient, for example integers are always stored as varints so small ones only take a byte. The only thing I think could maybe be improved for this use case is changing the structure of the b-tree to be more columnar - since right now all the data from different columns is intermingled with the btree structure itself, querying a subset of columns has a high overhead.

I&#x27;d be curious whether there&#x27;s any changes which could be made in the file format to optimize for read-only usage. The SQLite format probably has some features which aren&#x27;t needed in this context -- information about free pages and autoincrement counters isn&#x27;t relevant in a read-only file, for instance.

Sounds feasible to me. Either by replacing all those functions on the C side with empty shells or maybe even with wasm-opt ( but probably the OP has already used it removed all garbage collectible paths.)

The actual transferred data for the sqlite code should only be 550kB (gzip compression).Stripping out the write parts is a good idea. SQLite actually has a set of compile time flags to omit features [1]. I just tried enabling as many of those as possible, but it didn&#x27;t seem to reduce wasm size much, though I might be doing something wrong. There&#x27;s also no easy flags to disable CREATE &#x2F; UPDATE &#x2F; INSERT .[1] <a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;compile.html#omitfeatures" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;compile.html#omitfeatures</a>

As everyone else has been saying, this is amazing work. It sounds like the biggest issue with loading the page is the initial sql.js download - it&#x27;s about 1.2MB, is that right?Might it be feasible to easily strip down SQLite so that it only compiles the parts for read-only use? The browser version is obviously somewhat read-only but that&#x27;s because of the sandbox. I&#x27;m talking about excluding the code for CREATE, UPDATE, INSERT and everything else which is just for writing. The aim here would be to produce a significantly smaller WASM binary.I&#x27;m guessing that the answer is no, there&#x27;s no easy way of doing this without significant rewrites of SQLite&#x27;s core, but... I can&#x27;t be the only one to think of this, surely?

have you thought of getting this to work with network replicated p2p sqlite js library? forgot what it was called.also does this mean that static sites now can scale without relying on db???what other implications and application do you see?wonderful work!!!! please let me know how i can donate

Thank you, I really appreciate it. It&#x27;s pretty fun to do this kind of thing for yourself, but it&#x27;s really rewarding to be able to share it with other people.

Man you are a frickin genius, seriously. like how you put all this together all the depth of knowledge of different topics this would require the low level and the high level and the way you explain it simply confidently and with impact. Your work is really an inspiration. You computer scienced the sheet out of this thing.this achievement, and this blog post, to me is on par with blog posts that you would see from a major company where they solve some significant business critical technical challenge in-house. for example: the GitHub blog post about how they created their spinning globe of commits on their homepage, or a Netflix blog post of how they optimized their Network infrastructure to serve so many customers.your work is truly incredible. You&#x27;re next level of next level.

If your statistic or whatever can be precomputed, you can precompute it and put it in db table rather than compute it each time by reading the 300MB.

First of all, this is a very cool web hack, I like it very much.I have a question. It&#x27;s a 668.8MB database file. What does actually happen if the query has to scan 300 mb before finding the right answer? Wouldn&#x27;t it be better to do the work up front and deliver the answers as static json files? Sure you loose the flexibility of dynamic queries, but do you really have that flexibility in non trivial cases (e.g. 300 mb search)?

That doesn&#x27;t answer the question.Yes the web server supports range requests. Yes it only returns 50kb.But what mechanism is letting it scan to just those ranges in the binary file. Doesn&#x27;t the file system make you seek to the starting block and then read from there?The point is, while it looks very efficient, there might be a crap ton of IO going on for your little 50kb response.EDIT: probably the webserver is doing an fseek(), and performance will vary based on file system impl. This is something I will need to benchmark.

No, see here, for example<a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=27018194" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=27018194</a>

Doesn&#x27;t the webserver have to seek from the beginning of the ~600mb file to the range you want, unless the file is in memory?

This is both a clever hack and a brilliant solution. But it also worries me a bit. Mostly because I&#x27;ve seen a lot of Visual Basic + MS Access solutions. They work fine on a single computer, but they you put a database on a network share to be able to share it between a few computers and the performance is often horrendous. If you&#x27;re doing a lot of data processing it&#x27;s often best to do it as close to the data as possible.But as always, it&#x27;s seldom the tools, but the right tool used for the wrong usecase that is the problem.

This is great but wish it had write ability, not just read only.

I have the feeling I&#x27;m watching some semi-major new web tech being born... out of the blue, in a blog post.Fucking amazing, mad props. Beautiful work!

I wonder sometimes, what if we focus on producing native JS VM hardware, would it perform same as or better than WASM? Let&#x27;s run everything on JS.

Hi, I&#x27;m working on SkySQL, maybe it&#x27;s useful for you: <a href="https:&#x2F;&#x2F;github.com&#x2F;upshot-tech&#x2F;SkySQL" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;upshot-tech&#x2F;SkySQL</a>

<pre><code> &gt;&gt;&gt; From SQLite’s perspective, it just looks like it’s living on a normal computer with an empty filesystem except for a file called &#x2F;wdi.sqlite3 that it can read from. 
</code></pre>
Beyond static hosting : Now imagine also implementing a virtual file system that SENDS chunks of the database with HTTP Range requests when SQLite tries to write from the filesystemOr more generally: I predict a WASI implementation which will treat ANY server resource as a virtual file, replacing REST.

This is incredibly clever and fun.That said, all you need to query an SQLite database on the server side is a simple PHP script (as opposed to a running db server), and most static hosting providers offer PHP support.

What is dangerous here? Wasm is more crippled in access than normal js. And js is simply in every web page these days. You can&#x27;t make a static site hosting service and tell people not to use js, that would be very lame.

This is amazing!I&#x27;m also surprised that Github Pages lets you present arbitrary JS to the point where you can upload SQLite as WebAssembly. Isn&#x27;t this dangerous?

works on ObservableHQ<a href="https:&#x2F;&#x2F;observablehq.com&#x2F;@tomlarkworthy&#x2F;phiresky-sqlite-query" rel="nofollow">https:&#x2F;&#x2F;observablehq.com&#x2F;@tomlarkworthy&#x2F;phiresky-sqlite-quer...</a>

The Hacker News demographic, mostly US and EU based, might be under appreciating how impactful this is because of CDNs. For anyone not in the continent of your central database, this means absurdly faster interactions for data visualization.I moved from US to Brazil 3 years ago, and I still notice the latency when a site runs their backend only in one location. This cleaver solution makes interacting with the graph supper snappy even compared to enterprises that do have database servers in Brazil. Very impressive!

Apart from the genius of the author (conceiving a sequence of relatively understandable steps that unlock a potentially huge area), this highlights how efficient a SQLite can be in terms of slow resource usage.

Hm, yeah, I think a key-value store would be easier to implement. I haven&#x27;t looked at redis for some time now, but last time I did, persistence was done through snapshotting and everything would really be loaded into memory at start time. So that wouldn&#x27;t work for this use case, where all you can do is serve a static file.But my question revolves around databases assuming that the disk they access is local or at least a fast network storage. I wonder if there are any databases optimized to access slow storage over low bandwidth, where you&#x27;re really trying to optimize the amount of data read more than anything else.

That would be equivalent to always putting your data in b-trees or other structures, according to the request patterns, without keeping the tables themselves. Sort of how you need to do that in Redis for any kind of sane request strategy other than key-value lookups.

Very clever. I wonder if there are databases optimised for this use case. I can imagine something that always requires indexes to do the queries and stores data in disk in ways to make it easy to fetch only the bits you need.

Hmm... Is it possible to use this with two seperate databases, one a remote read only and one a local writable DB that could be re-exported to a static file host? Having just glanced at the code it looks like you would have to load two seperate libraries, one from this project and one from the original sql.js

I wrote a similar thing in Rust for a Factorio mod manager. Mods are hosted on the remote HTTP server as ZIP files, and the mod manager needs a single `info.json` file from the ZIP for the mod metadata. So the mod manager avoids downloading the whole mod and then unpacking it by building a file abstraction that uses HTTP range queries to download just the chunks it needs. For ZIP files the directory is stored at the end at an unknown offset, so the read pattern is to gradually seek backwards from the end until you find the start of the directory, then find the file entry, then seek and read the file.I didn&#x27;t fiddle with the window sizes like the submitted article (the chunk is fixed to 8KiB), but I did optimize it so that reading chunk N+1 of the file reused the response reader of chunk N rather than make a new request. Furthermore I keep an LRU cache of only the last three chunks in memory, because the ZIP files are each only read once.[1]: <a href="https:&#x2F;&#x2F;github.com&#x2F;Arnavion&#x2F;fac-rs&#x2F;blob&#x2F;2d2622a1c9934719ce65b1fc61503e32b36eea02&#x2F;src&#x2F;solve&#x2F;web_reader.rs" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;Arnavion&#x2F;fac-rs&#x2F;blob&#x2F;2d2622a1c9934719ce65...</a>[2]: <a href="https:&#x2F;&#x2F;github.com&#x2F;Arnavion&#x2F;fac-rs&#x2F;blob&#x2F;2d2622a1c9934719ce65b1fc61503e32b36eea02&#x2F;src&#x2F;solve&#x2F;zip.rs" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;Arnavion&#x2F;fac-rs&#x2F;blob&#x2F;2d2622a1c9934719ce65...</a>

This is super clever. If you add handling POST somehow as random access writes, you could have a read-write DB hosted pretty much anywhere

This hack reminds me of Godel&#x27;s hacking of an axiomatic mathematical system, to prove and plant his incompleteness theorem;This is really a great hack: using a system in a unexpected and creative way for which it wan not originally deigned, but which is also very useful and nice.

For similar results, you can use SQL with IndexedDB running fully client-side: <a href="https:&#x2F;&#x2F;github.com&#x2F;agershun&#x2F;alasql" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;agershun&#x2F;alasql</a>

Hosting SQLite databases on GitHub Pages or any static file hoster