Hosting SQLite Databases on GitHub Pages
phiresky.netlify.app
phiresky.netlify.app
[error: RuntimeError: abort(Error: Couldn't load https://phiresky.netlify.app/world-development-indicators-sq.... Status: 0). Build with -s ASSERTIONS=1 for more info.]
Other than that, is pretty awesome and exactly what I was hoping for.
You can go to this version of my blog, it should work there:
https://phiresky.github.io/blog/2021/hosting-sqlite-database...
Maybe the link could be updated?
Except for the DOM demo, since those need some Cross-Site-Isolation Headers you can't set on GitHub Pages (that's the reason I mirrored it to Netlify originally)
Edit: Netlify is indeed wrong: asserts in the headers that the content length is 1024 bytes but sends up 4,583 bytes of content. That will definitely fail in Firefox.
(And what's the point of crafting a comment in this tone? Is it supposed to be a retort? Whether or not Netlify is doing the wrong thing, if it works in Chrome, but not in Firefox, then that's a materially relevant fact. Don't rely on implementation details and a hope that they won't break in the future.)
If I may suggest one thing... instead of range requests on a single huge file how about splitting the file in 1-page fragments in separate files and fetching them individually? This buys you caching (e.g. on CDNs) and compression (that you could also perform ahead of time), both things that are somewhat tricky with a single giant file and range requests.
With the reduction in size you get from compression, you can also use larger pages almost for free, potentially further decreasing the number of roundtrips.
There's also a bunch of other things that could be tried later, like using a custom dictionary for compressing the individual pages.
I wonder if there has been any research into optimizing all http range requests at the client level in a similar way. i.e. considering the history of requests on a particular url and doing the same predictive exponential requests, or grabbing the full file asynchronously at a certain point.
> - Query e.g. parquet on e.g. GitHub with DuckDB: duckdb/test_parquet_remote.test https://github.com/duckdb/duckdb/blob/6c7c9805fdf1604039ebed...
> - Query sqlite on e.g. GitHub with SQLite: [Hosting SQLite databases on Github Pages - (or any static file hoster) - phiresky's blog](...)
>> The above query should do 10-20 GET requests, fetching a total of 130 - 270KiB, depending on if you ran the above demos as well. Note that it only has to do 20 requests and not 270 (as would be expected when fetching 270 KiB with 1 KiB at a time). 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. This means that index scans or table scans reading more than a few KiB of data will only cause a number of requests that is logarithmic in the total byte length of the scan. You can see the effect of this by looking at the “Access pattern” column in the page read log above.
> - bittorrent/sqltorrent https://github.com/bittorrent/sqltorrent
>> Sqltorrent is a custom VFS for sqlite which allows applications to query an sqlite database contained within a torrent. Queries can be processed immediately after the database has been opened, even though the database file is still being downloaded. Pieces of the file which are required to complete a query are prioritized so that queries complete reasonably quickly even if only a small fraction of the whole database has been downloaded.
>> […] Creating torrents: Sqltorrent currently only supports torrents containing a single sqlite database file. For efficiency the piece size of the torrent should be kept fairly small, around 32KB. It is also recommended to set the page size equal to the piece size when creating the sqlite database
Would BitTorrent be faster over HTTP/3 (UDP) or is that already a thing for web seeding?
> - https://web.dev/file-system-access/
> The File System Access API: simplifying access to local files: The File System Access API allows web apps to read or save changes directly to files and folders on the user’s device
Hadn't seen wilsonzlin/edgesearch, thx:
> Serverless full-text search with Cloudflare Workers, WebAssembly, and Roaring Bitmaps https://github.com/wilsonzlin/edgesearch
>> How it works: Edgesearch builds a reverse index by mapping terms to a compressed bit set (using Roaring Bitmaps) of IDs of documents containing the term, and creates a custom worker script and data to upload to Cloudflare Workers
The BT protocol itself runs on both TCP and UDP, but it has preferred the UDP variant for many years already.
> Both HTTP/1.1 and HTTP/2 use TCP as their transport. HTTP/3 uses QUIC, a transport layer network protocol which uses user space congestion control over the User Datagram Protocol (UDP). The switch to QUIC aims to fix a major problem of HTTP/2 called "head-of-line blocking": because the parallel nature of HTTP/2's multiplexing is not visible to TCP's loss recovery mechanisms, a lost or reordered packet causes all active transactions to experience a stall regardless of whether that transaction was impacted by the lost packet. Because QUIC provides native multiplexing, lost packets only impact the streams where data has been lost.
And HTTP Pipelining / Multiplexing isn't specified by just UDP or QUIC:
> HTTP/1.1 specification requires servers to respond to pipelined requests correctly, sending back non-pipelined but valid responses even if server does not support HTTP pipelining. Despite this requirement, many legacy HTTP/1.1 servers do not support pipelining correctly, forcing most HTTP clients to not use HTTP pipelining in practice.
> Time diagram of non-pipelined vs. pipelined connection The technique was superseded by multiplexing via HTTP/2,[2] which is supported by most modern browsers.[3]
> In HTTP/3, the multiplexing is accomplished through the new underlying QUIC transport protocol, which replaces TCP. This further reduces loading time, as there is no head-of-line blocking anymore https://en.wikipedia.org/wiki/HTTP_pipelining
Edgesearch does that though with Cloudflare Workers mediating searches: https://github.com/wilsonzlin/edgesearch
Uses roaring-bitmaps to index, but could also use Stavros' trick (bloom/cuckoo-filters) to further gate false-positives on-the-client: https://news.ycombinator.com/item?id=23473365
I designed a system 15 years ago that released dimensional star schemas for specific reports as sqllite databases into Adobe Air (or whatever the prerelease name was) for a large retailer in the UK. We would query the data warehouse, build the sqlite db file (I can't remember the exact db sizes but they weren't too big - 15mb or so) and the computational performance we got from using sqlite as our crunching layer when building the dimensional reports with drilldown was just astounding.
exactly
>And locally with sqlite you don't really have to worry about latency, so you should be able to just get atomicity and consistency on the thread
for high performance read you don't need much more.
> think as with other standards bickering, ten years from now something (Canvas API) will come out that more or less replicates the technology that was already standard ten years ago (Flash graphics), with lots of people cheering for it as if someone just invented sliced bread.
flash and actionscript revolutionized the web and a lot of what we have now web tooling wise stems to the desire to compete what flash was possible since 2003.
IndexedDB lacks SQL support natively, but there is nothing about key value systems in general that preclude the use of SQL. CockroachDB comes to mind as a key value store that depends on SQL. SQL support could be built atop IndexedDB by a willing developer. JsStore is in that vein, although the approach is a little bit different.
Very nice for read-only static data sets for small sites though. Infact this may be very useful for county mapping systems, converting over the GIS data to tables in SQLite.
If at all possible it would be better if this could be in ES5 (no async await) javascript, only very very modern browsers are going to be able to access it. People with older phones (which is many) wouldn't be able to use it at all.
Caniuse.com does not agree with this assertion.
The concept reminds me a little bit of CD-ROM multimedia, in that it's so self-contained. For something like that it's great.
I've heard variations of this batted around recently. Specifically, if we could allow web apps out of the sandbox so they could work like native apps, or have more access to the file system, we could maybe work our way out of the walled gardens and into totally distributed storage / processing / hosting. And it's true, it's just that...
>> On the other hand, which is more secure? Your service or the user's machine.
This.
As another reply said, this could be useful for data-intensive readonly applications.
All the code snippets, when run, give me the following error message:
[error: RuntimeError: abort(Error: server uses gzip or doesn't have length). Build with -s ASSERTIONS=1 for more info. (evaluating 'new WebAssembly.RuntimeError(e)')]
Could that be a Mobile Safari thing?
> They are all Safari skins
That's crazy.
For example, if Apple allowed third-party browser engines, any vendor could offer a browser with a vulnerability. That browser could be abused to install apps through that vulnerability, including malicious ones.
I’m not implying that it’s ok for Apple to act like that. My point is that it’s consistent with their security model for iOS.
Both MacBook Pros I’ve owned at that time each came with its power supply included, and lasted me seven years each.
I consider myself frugal, not a sucker.
More like embedded WebViews.
[1]: https://news.ycombinator.com/item?id=28016906 [2]: https://phiresky.github.io/blog/2021/hosting-sqlite-database...
http://matthewrocklin.com/blog/work/2018/02/06/hdf-in-the-cl...
2010s saw REST-conforming APIs with json in the body largely as an (appropriate) reaction to what came before, and also in accordance with changes around what browsers were able to do, and thus how much of web apps moved from the backend to the front.
But then, that brought even more momentum where web apps started doing /even more/. There was a time when downloading a few megabytes per page, generating an SVG chart or drawing an image, interacting to live user interaction was all unthinkable. But interactive charting is now de facto. So now we need ways to access ranges and pieces of bulk data. And it looks a lot more like block storage access than REST.
---
These are core database ideas: you maintain a fast and easy to access local cache of key bits of data (called a bufferpool, stored in memory, in e.g. mysql). In this local cache you keep information on how to access the remaining bulk of the data (called an index). You minimize dipping into "remote" storage that takes 10-100x time to access.
Database people refer to the "memory wall" as a big gap in the cache hierarchy(CPU registers, L1-L3, main memory, disk / network) where the second you dip beyond it, latency tanks (Cue the "latency numbers every programmer should know" chart). And so you have to treat this specially and build your query plan to work around it. As storage techniques changed (e.g. SSDs, then NVMEs and 3d x-point etc), databases research shifted to adapt techniques to leverage new tools.
In this new case, the "wall" is just before the WAN internet, instead of being before the disk subsystem.
---
This new environment might call for a new database (and application) architectural style where executing large and complex code quickly at the client side is no problem at all in an era of 8 core CPUs, emscripten, and javascript JITs. So the query engine can move to the client, the main indexes can be loaded and cached within the app, and the function of the backend is suddenly reduced to simply storing and fetching blocks of data, something "static" file hosts can do no problem.
The fundamental idea is: where do I keep my data stored, where do I keep my business logic, and where do I handle presentation. The answer is what varies. Variations on this thought:
We've already had products that completely remove the "query engine" from the "storage" and provides it as a separate service, e.g. Presto / Athena where you set it up to use anything from flat files to RDBMSs as "data stores" across which it can do fairly complicated query plans, joins, predicate pushdown, etc. Slightly differently, Snowflake is an example of a database that's architected around storing main data in large, cheap cloud storage like s3, no need to copy and keep entire files to the ec2 node, only the block ranges you know you need. Yet another example of leveraging the boundary between the execution and the data.
People have already questioned the wisdom of having a mostly dumb CRUD backend layer with minimal business logic between the web client and the database. The answer is because databases just suck at catering to this niche, but nothing vastly more complicated than that. They certainly could do granular auth, serde, validation, vastly better performance isolation, HTTP instead of a special protocol, javascript client, etc etc. Some tried.
Stored procedures are also generally considered bad (bad tooling, bad performance characteristics and isolation, large element of surprise), but they needn't be. They're vastly better in some products that are generally inaccessible to or unpopular with large chunks of the public. But they're a half baked attempt to keep business logic and data close together. And some companies had decided at a certain time that their faults were not greater than their benefits, and had large portions of critical applications written in this way not too long ago.
---
Part of advancing as an engineer is to be able to weigh the cost of when it's appropriate to sometimes free yourself from the yoke of "best practices" and "how it's done". You might recognize that something about what you're trying to do is different, or times and conditions have changed since a thing was decided.
And also to know when it's not appropriate: existing, excellent tooling probably works okay for many use cases, and the cost of invention is unnecessary.
We see this often when companies and products that are pushing boundaries or up against certain limitations might do something that seems silly or goes against the grain of what's obviously good. That's okay: they're not you, and you're not them, and we all have our own reasons, and that's the point.
edit: kidding aside, this is approximately what a database view is, at least in some implementations (though probably not as common as the more simple implementation): a view is created as a SQL query that pulls some data. It's stored as is. As data is inserted to the database, the views (a.k.a. queries) that would include that row are updated too.