Hosting SQLite Databases on GitHub Pages • Hacker News Board

Re: Hosting SQLite Databases on GitHub Pages

#41

Post by cafxx » Sat, Jul 31, 2021, 2:12 PM UTC

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

Re: Hosting SQLite Databases on GitHub Pages

#42

Post by noduerme » Sat, Jul 31, 2021, 2:15 PM UTC

deanclatworthy wrote: post #38

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

>> If we could solve this problem, we could go a long way towards some level of decentralisation

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.

Re: Hosting SQLite Databases on GitHub Pages

#43

Post by Mooty » Sat, Jul 31, 2021, 2:24 PM UTC

I wonder if you could turn this with a Google Spreadsheet into a real DB system with writing access and a little obfuscated security wrapper.

Re: Hosting SQLite Databases on GitHub Pages

#44

Post by ticklemyelmo » Sat, Jul 31, 2021, 3:01 PM UTC

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.

Re: Hosting SQLite Databases on GitHub Pages

#45

Post by goeiedaggoeie » Sat, Jul 31, 2021, 3:34 PM UTC

noduerme wrote: post #36

justsomeuser wrote: post #33

Earlier quoted context omitted.

It was deprecated because it was difficult to write a standard spec for the existing SQLite code (but a key value system is much easier to specify as there is no SQL language).

Yeah but a key value system lacks all the really good things about SQL. 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. This shouldn't be a lot to ask from an embedded web DB. I think as with other standards bickering, ten years from now something (Canvas API) will come out that more or less replicates the technology that…

>Yeah but a key value system lacks all the really good things about SQL

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.

Re: Hosting SQLite Databases on GitHub Pages

#46

Post by littlecranky67 » Sat, Jul 31, 2021, 3:40 PM UTC

noduerme wrote: post #36

justsomeuser wrote: post #33

Earlier quoted context omitted.

It was deprecated because it was difficult to write a standard spec for the existing SQLite code (but a key value system is much easier to specify as there is no SQL language).

Yeah but a key value system lacks all the really good things about SQL. 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. This shouldn't be a lot to ask from an embedded web DB. I think as with other standards bickering, ten years from now something (Canvas API) will come out that more or less replicates the technology that…

Additional issue was that to become a standard, there would have to be at least two different proof-of-concept implementations. That would mean, someone would have to write a SQLite compatible database, that is not SQLite.

Re: Hosting SQLite Databases on GitHub Pages

#47

Post by ignoramous » Sat, Jul 31, 2021, 3:43 PM UTC

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…

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

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

Re: Hosting SQLite Databases on GitHub Pages

#48

Post by devwastaken » Sat, Jul 31, 2021, 3:59 PM UTC

goeiedaggoeie wrote: post #26

SQLite not being in browsers instead of indexdb saddens me today still. 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 compu…

SQLite was not designed for arbitrary execution with control over queries and internal state. Someone demonstrated that you could redirect the pointer address for various callbacks and potentially exploit it. The solution currently is compiling sqlite in webassembly. Though I am certainly saddened that browsers don't have some sort of web-made equivalent natively.

Re: Hosting SQLite Databases on GitHub Pages

#49

Post by devwastaken » Sat, Jul 31, 2021, 4:04 PM UTC

Huh, this is a funny one. I had this idea a long time ago when doing some napkin design of a "static wiki". Problem was the querying didn't fit how software optimizes content delivery, so millions of people requesting from a single database would most likely be difficult to accomplish in a performant manner. Secondarily writing to said database would of course be impossible because locking, and you'd need a server anyways to do any sort session based submittal of data.

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.

Re: Hosting SQLite Databases on GitHub Pages

#50

Post by awestroke » Sat, Jul 31, 2021, 4:18 PM UTC

devwastaken wrote: post #49

Huh, this is a funny one. I had this idea a long time ago when doing some napkin design of a "static wiki". Problem was the querying didn't fit how software optimizes content delivery, so millions of people requesting from a single database would most likely be difficult to accomplish in a performant manner. Secondarily writing to said database would of course be impossible because locking, and you'd need a server an…

Just transpile it