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

Hosting SQLite Databases on GitHub Pages

phiresky.netlify.app

Post reply on HN
Print view View original

61–70 of 82 posts

Re: Hosting SQLite Databases on GitHub Pages

#61

Post by zinekeller »

pwdisswordfish8 wrote: post #58
phiresky wrote: post #13

Earlier quoted context omitted.

Looks like Netlify changed something since I wrote this article regarding what headers they send. Detecting support for Range-requests is kinda tricky and relies on heuristics [1]. Not sure why it still works in Chrome though. 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 thos…

Don't rely on browser implementation details and a hope that they won't break in the future. Add a small supplementary file to your published data which has a known pattern at a fixed offset, then make a request for that offset and check the response.

Browsers actually implement range-requests correctly, what Netlify has done is advertise support for it but send data incorrectly.

Re: Hosting SQLite Databases on GitHub Pages

#62

Post by schwartzworld »

Hackbraten wrote: post #55
schwartzworld wrote: post #54

Earlier quoted context omitted.

The real reason is that they want you to get your apps from the app store and that's it. They can't get paid when people install PWAs or sideload open source apps. Same reason for killing the headphone jack. Same reason for having a different shape magsafe for every generation of MacBook. To keep the suckers spending.

MagSafe has had only two variants while it existed (2006–2017). That’s more than a decade. 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.

If you're buying apple products still, you're telling on yourself.

Re: Hosting SQLite Databases on GitHub Pages

#63

Post by derekbaker783 »

manmal wrote: post #53
deanclatworthy wrote: post #38

Earlier quoted context omitted.

Nothing stopping you doing that right now with localStorage or IndexedDB. The issue is the browser cannot be trusted to keep that data, or at least these APIs aren't designed for long-term persistent storage. If we could solve this problem, we could go a long way towards some level of decentralisation. On the other hand, which is more secure? Your service or the user's machine. So there's a lot to consider.

Wouldn't such a model limit the user to just one device? Usually there's no sync of localStorage across devices.

If I'm understanding your scenario, I think PouchDB and CouchDB kind of address this concern, but for IndexexDB.

Re: Hosting SQLite Databases on GitHub Pages

#64

Post by makmanalp »

galaxyLogic wrote: post #60
makmanalp wrote: post #51

This is a great example of how as technology changes, it changes use cases, which can prompt a revisiting of what was once considered a good idea. You'll often see the pendulum of consensus swing in one direction, and then swing back to the exact opposite direction less than a decade later. 2010s saw REST-conforming APIs with json in the body largely as an (appropriate) reaction to what came before, and also in accor…

I would think that if we know the SQL-queries we need we could pre-perform them and store the results into simple indexed tables. The web-app would then need to just ask for the data at a given index-value. No SQL needed on the browser. Could this work? Pre-executing SQL.

Yeah, that's caching, and it's great when one can do it :-)

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.

Re: Hosting SQLite Databases on GitHub Pages

#65

Post by pwdisswordfish8 »

zinekeller wrote: post #61
pwdisswordfish8 wrote: post #58

Earlier quoted context omitted.

Don't rely on browser implementation details and a hope that they won't break in the future. Add a small supplementary file to your published data which has a known pattern at a fixed offset, then make a request for that offset and check the response.

Browsers actually implement range-requests correctly, what Netlify has done is advertise support for it but send data incorrectly.

That doesn't change the thrust of the comment. If you're trying to work around spotty support and detecting the feature is "tricky", then change the program so it performs a small power-on self-test against a known dataset.

(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.)

Re: Hosting SQLite Databases on GitHub Pages

#67

Post by randomdata »

manmal wrote: post #23

I can’t fully put my finger on why exactly, but I feel that this is a transformative idea. What’s to stop me from emulating a private SQLite DB for every user of a web app, and use that instead of GraphQL?

Nothing, I'm sure, but the idea behind GraphQL is that you can query all of the different backend services you have in a single request, reducing the network latency associated with firing off requests to all those services individually. It would seem that an emulated SQLite database would bring you right back to having to perform multiple network requests, assuming your data needs are more complex than a single relation. Under normal usage, SQLite avoids the N+1 problem by not having IPC overhead, but that wouldn't apply here.

Re: Hosting SQLite Databases on GitHub Pages

#68

Post by westurner »

ticklemyelmo wrote: post #44
cafxx wrote: post #41

That's one lovely trick. 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 fro…

I think that's the core innovation here, smart HTTP block storage. 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.

> Methods for remotely accessing/paging data in from a client when a complete download of the dataset is unnecessary:

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

Re: Hosting SQLite Databases on GitHub Pages

#69

Post by nwsm »

manmal wrote: post #23

I can’t fully put my finger on why exactly, but I feel that this is a transformative idea. What’s to stop me from emulating a private SQLite DB for every user of a web app, and use that instead of GraphQL?

You don't own the database. You can't be sure it's not being tampered with, and joining any other users' data together still requires your own backend. You also can't promise data won't be lost.

As another reply said, this could be useful for data-intensive readonly applications.

Re: Hosting SQLite Databases on GitHub Pages

#70

Post by fouc »

zinekeller wrote: post #18
phiresky wrote: post #13

Earlier quoted context omitted.

Looks like Netlify changed something since I wrote this article regarding what headers they send. Detecting support for Range-requests is kinda tricky and relies on heuristics [1]. Not sure why it still works in Chrome though. 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 thos…

Maybe because Firefox is stricter? Netlify (incorrectly) sends a 4,583 byte chunk, while GitHub (correctly) sent precisely 1024 bytes. Chrome might just trim it to 1024 bytes, but Firefox just fail-safe at the difference. 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 it doesn't seem like it'd be easy to get netlify to fix this type of behavior unfortunately: https://answers.netlify.com/t/add-unpacked-content-length-he...
Post reply on HN