phiresky

Yeah, I guess you could equivalently put a SQLite database with a view or virtual table in S3 which would give you the same level of indirection and API abstraction provided by this mechanism. Where DuckDB will have an advantage is in the processing speed for OLAP type queries. I don't know what the current state of SQLite Virtual Tables for parquet files on S3 is, but DuckDB has a number of optimisations for it like only reading the required columns and row groups through range queries. SQLite has a row oriented processing model so I suspect that it wouldn't do that in the same way unless there is a specific vtable extension for it. You can get a comparable benefit for data in a sqlite db itself with the project from the following blogpost but that wouldn't apply to collections of parquet files: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

This blog post explains how it works: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… Disclaimer: I wrote that article and was somewhat involved in the other sqlite over ipfs project this is forked from. Yes, for MB size files just downloading the whole thing will be faster and much easier - even if running in the browser. I'd say the boundary is somewhere around 10MB compressed (~20-200MB uncompressed). Looks like the sqlite dump used here is ~400MB in size, ~180MB compressed. Loading that in the browser would probably work but it wouldn't be too great.

It's the same trick that was described here - it's absolutely brilliant: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

In general, this is also my feeling about ORMs. I'm not sure of the best options in Python, but for TypeScript this article has a very helpful section on query builders: https://phiresky.github.io/blog/2020/sql-libs-for-typescript/ (disclaimer: one of them is mine).

I'll start off by quoting the winning submission. Libraries have been trying to collect humanity’s knowledge almost since the invention of writing. In the digital age, it might actually be possible to create a comprehensive collection of all human writing that meets certain criteria. That’s what shadow libraries do - collect and share as many books as possible. One shadow library, Anna’s Archive (which I will not link here directly due to copyright concerns), recently posed a question: How could we effectively visualize 100,000,000 books or more at once? There’s lots of data to view: Titles, authors, which countries the books come from, which publishers, how old they are, how many libraries hold them, whether they are available digitally, etc. - https://phiresky.github.io/blog/2025/visualizing-all-books-in-isbn-spa… Basically, legally gray online book repositories such as Anna's Archive, who was the creator of this bounty, are trying to collect a lot of books. The question quickly arises - how many books are there? The best way to track books is by using ISBN, international standard book number, basically the personal id of any given books, given to books by an international agency. Now that you know which books exist, you can check which books your repository already has and which ones are missing. But ISBN covers the space of over 2 billion possible existing books. That's a lot. So, Anna's Archive has created a contest to display this space in the cleanest way…

oh! I must've been confused with your TIL where you linked to an explainer of this technique https://simonwillison.net/2021/May/2/hosting-sqlite-databases-on-githu… https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… https://news.ycombinator.com/item?id=27016630

GitHub is intoxicatingly free hosting, but Git itself is a terrible database. Why not maintain an _actual_ database on GitHub, with tagged releases? Sqlite data is paged and so you can get away with only fetching the pages you need to resolve your query. https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

The winning submission [0] was discussed on HN recently [1]. It's highly impressive from both technical decisions and graphic design viewpoints, it somehow elegantly visualizes 2 billion books (in a way that resembles a bookcase no less). [0]: https://phiresky.github.io/blog/2025/visualizing-all-books-in-isbn-spa… [1]: https://news.ycombinator.com/item?id=42897120

https://phiresky.github.io/blog/2020/sqlite-performance-tuning/ does a better job of explaining imo

i've been playing around with this idea on and off for a while. this was the original demo/implementation afaik: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… this has all sorts of nice properties if it suits your needs, especially if you have lots of small read-only databases that just receive updates occasionally. there's a little demo i did here: https://billywhizz.io/squeel-demo/

Not this, but the sqlite.js-http-vfs wasm implementation[0] (not the current official one) has the browser DOM available as a virtual table. Sample query from the post: select count(*) as number_of_demos from dom where selector match '.content div.sqlite-httpvfs-demo'; [0]: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

They don't even need to output to JSON - https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… The entire thing could be a static site using the above method -- even if it's many hundreds of megabytes in size!

I would have appreciated some more details on the benchmark. SQLite has notoriously slow writes in the default journal mode, but proper configuration and WAL/WAL2 mode[0] should be a starting point for any comparison. It's the first time I've heard of Isar[1] though. I'm always surprised at how many solid-but-underused Apache projects are out there, chugging along. [0] https://phiresky.github.io/blog/2020/sqlite-performance-tuning/ [1] https://isar.dev/

One note that the article makes is that reading from S3 requires a full read of the file. As an alternate approach is to use HTTP range headers to read only part of a file and minimise how much data goes over the wire. I'm not sure if the 'kiwi' file format would support that (and this is only for reads I believe). A nice demo of this for sqlite is here - https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

Not sure if it's a usable concept but it sounds similar to what someone else did[1]: > ...I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem... [1]: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

SQLite is so modular that someone could write a replacement for the filesystem layer that ended up sending requests across HTTP to query a database on another server [1]. Without touching any other layer of the code. How much more modular do you want a database to be, without making other compromises? [1]: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

This guy built and hosts a SQLite database via github. Is this helpful? https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

SQLite isn't compressed by default, but there are extensions like https://phiresky.github.io/blog/2022/sqlite-zstd/ that offer compression. It would be interesting to see how well that extension works with this project.

Oh that’s an interesting idea. I saw someone built SQLite over HTTP with the Range header: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… So presumably in a similar manner as https://github.com/phiresky/sql.js-httpvfs you could map SQLite pages to leaf torrents too and get the chunking you are looking for.

It's a data format which can be expressed as a contiguous series of bytes and sent/stored; it's a serialization format. This is not just nit-picking, it's been used precisely as such: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

But it uses a shared zstd dictionary among rows! The details are described here: https://phiresky.github.io/blog/2022/sqlite-zstd/

Don't say server-"less" if you mean in-process (which may well run on a server). Here's Sqlite without a server, running in the client and backed by static file hosting: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

Yes, the author would probably get another huge speed up there. Some more potential optimizations here [1]. [1] https://phiresky.github.io/blog/2020/sqlite-performance-tuning/

Seems to still require a server which the static site client-side JS continues to call out to? Not really a 'static site' then. Occurs to me that you could probably go much further towards a truly static site by borrowing from https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… - store the user credentials publicly in a readable way, and use a sufficiently slow hash like scrypt to make the hashes non-bruteforceable. Writes can be done async or maybe through a system like IPFS or Zeronet, or just stored locally and visible to the user only... I'm not entirely sure where you'd want such a scheme, but if you can make a BBS using the SQlite approach (https://ansiwave.net/blog/semantic-web.html) I'm sure there's a niche or variant somewhere which might be interesting.

Does it support byte-range? so it can be used as a “server free” SQLite database: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

Pretty slick. It extends the post[0] from a year-ish ago with read-only sqlite on a static server with a tiny backend that allows writes. I am going to have to take a closer look at some point: https://github.com/ansiwave/wavecore/search?q=sqlite [0] https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

This is very cool! Reminds me of https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… which is similarly cool project.

https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… might be of interest too.

It's probably just a few steps away to finally build an IPFS version of sci-hub. Sadly, I'm not a fan of the site's method of searching the libgen index by using sqlite's partial load feature [1] mainly because of the possible limited available storage issue. [1]:https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu…

You are right. I hold my hands up: I did not give credit to what made me think of doing this. I think I saw this (or at least, their blog post https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-githu… on HN a few weeks back. I have now given them credit.