6 ms·
Hosting SQLite databases on any static file hoster (2021)
- gaudat 4y ago(Read only)
- mr_toad 4y agoIn theory it should be possible to write back to static storage, but you’d have to deal with authentication and file management, cacheing writes, consistency/atomicity etc. It’d be a lot of work.
- pfundstein 4y agoPerhaps you could submit updates to the database as diffs and commit them via normal git channels
- pfundstein 4y agoWhich is still usefull for querying a large database without having to download the entire thing.
- altilunium 4y agoShould be included in the title then.
- chrisaycock 4y agoPrior discussion (May 2, 2021): https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630
- AceJohnny2 4y agoIt's funny, I was wondering whether this replicated Simon Willison's work on Datasette [1], but I guess I got things confused because Simon himself was the first to comment on that older post about how clever this was. [1] https://simonwillison.net/2018/Oct/4/datasette-ideas/ https://simonwillison.net/2018/Oct/4/datasette-ideas/
- crazysim 4y agoThere's a web browser version of datasette called datasette-lite which runs on Python ported to WASM with Pyodide which can load SQLite databases. I grafted the enhanced lazyFile implementation from emscripten and then from this implementation to datasette-lite relatively recently as a curious test. Threw in a 18GB CSV from CA's unclaimed property records here https://www.sco.ca.gov/upd_download_property_records.html https://www.sco.ca.gov/upd_download_property_records.html into a FTS5 Sqlite Database which came out to about 28GB after processing: POC, non-merging Log/Draft PR for the hack: https://github.com/simonw/datasette-lite/pull/49 https://github.com/simonw/datasette-lite/pull/49 You can run queries through to datasette-lite if you URL hack into it and just get to the query dialog, browsing is kind of a dud at the moment since datasette runs a count(*) which downloads everything. Example: https://datasette-lite-lab.mindflakes.com/index.html?url=https://datasette-lite-lab.mindflakes.com/sdb/2022-10-02_93eff57de3573985_ca_unclaimed_property.sqlite#/2022-10-02_93eff57de3573985_ca_unclaimed_property?sql=SELECT+*+FROM+records+WHERE+records.owner_name+MATCH+%22Elon+Musk%22+ORDER+BY+CAST%28CURRENT_CASH_BALANCE+AS+FLOAT%29++DESC%3B https://datasette-lite-lab.mindflakes.com/index.html?url=htt... Still, not bad for a $0.42/mo hostable cached CDN'd read-only database. It's on Cloudflare R2, so there's no BW costs.
- marvel_boy 4y agoAmazing. Runs smoothly. Elon could not like the real state example :). By the way, R2 service is in beta, right?
- crazysim 4y agoGA'd https://blog.cloudflare.com/r2-ga/ https://blog.cloudflare.com/r2-ga/
- CharlesW 4y ago"GitHub Pages" is presumably in the title as a hook, but in TFA the author notes that this will work for any statically-hosted website.
- svnpenn 4y agoHowever, if you check the AFT, the author notes that GitHub is strictly required, so I don't know what to think.
- langsoul-com 4y agoDidn't the author mention it uses wasm and emscript? Which both can be run/compiled on browsers. I think it's possible everywhere, but you'd just need to do more than copy paste the authors work.
- svnpenn 4y agoMy entire comment was fabricated. I was making fun of the fact that other user was using obscure acronym.
- CharlesW 4y ago"TFA" is a popular initialism of "the fucking article"/"the fine article", with a long history alongside "RTFM", "RTFA", etc.
- svnpenn 4y agoI don't think its as popular as you think it is. Plus its used by a bunch of other crap: https://hn.algolia.com/?q=TFA https://hn.algolia.com/?q=TFA so its probably worth just spelling out the thing.
- CharlesW 4y ago> I don't think its as popular as you think it is. It is here, so you're one of today's lucky 10,000! https://xkcd.com/1053/ https://xkcd.com/1053/ Also, this is the search you wanted: https://hn.algolia.com/?dateRange=all&page=0&prefix=false&query=TFA&sort=byPopularity&type=all https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu... (12,797 results as I type this).
- LAC-Tech 4y agoReminder that Mozilla killed off SQLite in the browser, to push indexedDB, a much more limited database that they then went and implemented in Firefox with... SQLite.
- jrockway 4y agoThis is SQLite compiled to WebAssembly along with a storage layer ("virtual filesystem") that fetches database pages from a web server. So you have the full power of SQLite, and the page doesn't have to download unnecessary data from the server (like the entire dataset just to show you one row). It's a super nifty hack. SQLite built into the browser is somewhat orthogonal to this concern; it didn't allow you to store database pages on the server, and this one doesn't let you write to it.
- pfundstein 4y agoThat's a bit misleading. IndexedDB is a standard maintained by the World Wide Web Consortium (W3C) and is supported by all major browsers. Web SQL Database was a prior API developed by Apple. But Firefox refused to add support for it and argued against it becoming a standard because it would codify the quirks of SQLite. It was thus deprecated in favor of IndexedDB.
- kevingadd 4y agoI hate indexedDB as much as almost anyone, but this is not an accurate characterization of what happened. People wanted to ship 'just SQLite' and magically anoint an open source library as a standard. There are lots of problems with that.
- rrdharan 4y agoStill fair to ask, given the world we have now where there’s kinda just “nothing” / broken and crappy implementations, would the alternative world in which SQLite was anointed, have been better head-to-head? I think maybe yes it would have? The perfect defeated the good?
- 4y ago
- lopkeny12ko 4y agoThis doesn't seem to work for me. Every time I run the SQL, the output is [error: TypeError: /blog/_next/static/media/sqlite.worker.39534d39.js is not a valid URL.]
- svnpenn 4y agoJust write a server; don't bastardize GitHub Pages. Why do people bend over backwards to avoid using a server for anything? Its literally 11 lines of code: https://go.dev/play/p/vUo6wKni3_A https://go.dev/play/p/vUo6wKni3_A
- deleted 4y ago[deleted]
- teaearlgraycold 4y agoGitHub pages is free forever, fast, and you don’t need to think about software updates or CPUs or anything. And it’s just a fun project.
- aaaaaaaaaaab 4y ago>free forever Like Geocities?
- rjmunro 4y ago"Writing" a server is the easy bit. It's running it and letting people connect that is the problem. Also scaling to spikes in load etc. This isn't just about github pages, it will work on most static hosting systems like Amazon S3 etc.
- floodle 4y agoSeafowl (https://www.splitgraph.com/blog/seafowl https://www.splitgraph.com/blog/seafowl), which was only launched a couple of days ago, is a database that can be hosted statically
- mildbyte 4y agoHey, I co-built Seafowl, thanks for the plug! To clarify, Seafowl itself can't be hosted statically (it's a Rust server-side application), but it works well for statically hosted pages. It's basically designed to run analytical SQL queries over HTTP, with caching by a CDN/Varnish for SQL query results. The Web page user downloads just the query result rather than required fragments of the database (which, if you're running aggregation queries, might have to scan through a large part of it).
- langsoul-com 4y agoThe author mentioned how running a Web server risks it going down because they forgot to pay or something. Then uses "free" services instead. Unlike paying, which you can easily set to auto pay. Free services can go up and down whenever they wish. In fact, it's probs in their terms of service. The more generous the free service, the more likely it'd get cut down and be unusable later or just more expensive than alternatives. Like heroku free tier. Paying money means there's an actual incentive and legal contract for the company to provide you service you paid for.
- LukeLambert 4y agoStatic sites benefit from being low-maintenance and highly-portable. You can easily switch from generous free services (Cloudflare Pages, Netlify, Vercel, Firebase, etc.) to highly-available paid services (Amazon S3, Cloudflare R2, Backblaze B2, Google Cloud Storage, etc.) Keeping a VM running and updated is more work (and sometimes higher cost).
- maxpert 4y agoThis can potentially become very interesting where if all these pages are pushed to edge via CDN, then browsers can essentially have a GraphQL built on top of SQLite on the client side.
- DishyDev 4y agoThis is a cool project, and really well presented. I've looked doing similar, trying to query a large dataset through ranged lookups on a static file server in the past. I ran into issue of some hosters not providing support for ranged lookups. Or the CDNs I was using having behaviour like they only fetch the origin data source in 8MB blocks meaning there was quite a lot of latency when doing lots of small reads across a massive file. It would have been interesting to find out a bit more about these topics, and see a bit more on performance.
- apignotti 4y agoA point of great pain for me in the use of HTTP range headers is that they are absolutely incompatible with HTTP compression. This boils down to an ambiguity found in the early days of HTTP: when you ask for a byte range with compression enabled, are you referring to a range in the uncompressed or compressed stream? The only reasonable answer, in my opinion: a range of bytes in the uncompressed version, which is then compressed on-the-fly to save bandwidth. I suspect the controversial point was that originally HTTP gzip encoding was used via pre-compression of the static files. On-the-fly encoding was probably too computationally expensive at the time. Anyway, the result of this choice done decades ago is currently preventing effective use of the ranged HTTP requests. As an example, it is fairly likely that chunks from the SQLite database can be significantly compressed, but both HTTP servers and CDN refuse to compress "206 Partial Content" replies. Interestingly, another use case of HTTP byte ranges is video streaming, which is not affected by this problem since video is _already_ compressed and there is no redundancy to remove anymore.
- matharmin 4y agoI think another issue with compressing partial content is that those compressed responses cannot be cached efficiently - it would have to be compressed on-the-fly for every range requested. And while compression is not as computationally expensive as it used to be, it does still add overhead that could be more than the bandwidth overhead of uncompressed data. There should be a workaround for this using a custom pre-compression scheme, instead of relying on HTTP for the compression. Blocks within the file will have to be compressed separately, and you'll need some kind of index mapping uncompressed block offsets to compressed block offsets. Unfortunately there doesn't seem to be a common of-the-shelf compression format that does this, and it means that you can't just use a standard SQLite file anymore. But it's definitely possible.
- apignotti 4y agoI see the caching argument only partially. It applies to any ranged request, non just compressed ones, and there are plenty of ways for origin servers to describe what and how to cache content. The origin may explicitly only allow caching when the access patterns are expected to likely repeat.