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

Hosting SQLite databases on GitHub Pages or any static file hoster

phiresky.github.io

Post reply on HN
Print view View original

211–220 of 252 posts

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#211

Post by phiresky »

duskwuff wrote: post #189
yoz wrote: post #174

As everyone else has been saying, this is amazing work. It sounds like the biggest issue with loading the page is the initial sql.js download - it's about 1.2MB, is that right? Might it be feasible to easily strip down SQLite so that it only compiles the parts for read-only use? The browser version is obviously somewhat read-only but that's because of the sandbox. I'm talking about excluding the code for CREATE, UPDA…

I'd be curious whether there's any changes which could be made in the file format to optimize for read-only usage. The SQLite format probably has some features which aren't needed in this context -- information about free pages and autoincrement counters isn't relevant in a read-only file, for instance.

I think it wouldn't change much - SQLite is already pretty optimized towards reads, for example a write always replaces a whole page and locks the whole DB. The free pages can easily be removed by doing VACUUM beforehand which should be done anyways to balance the b-trees.

The storage of SQLite is already really efficient, for example integers are always stored as varints so small ones only take a byte. The only thing I think could maybe be improved for this use case is changing the structure of the b-tree to be more columnar - since right now all the data from different columns is intermingled with the btree structure itself, querying a subset of columns has a high overhead.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#212

Post by killingtime74 »

banana_giraffe wrote: post #161
killingtime74 wrote: post #154

Earlier quoted context omitted.

This one? https://rogerbinns.github.io/apsw/

Yep, exactly that one. There's a simple example of a VFS implementation on the examples page that's a reasonable starting point: https://rogerbinns.github.io/apsw/example.html Once you wrap your head around how you need to pass parameters to the helper, it's really straightforward, you just need to implement the xOpen and xRead calls.

Thank you!

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#214

Post by batch12 »

Cullinet wrote: post #148
havernator wrote: post #129

Earlier quoted context omitted.

TL;DR http, properly implemented, supports a ton more stuff than even many “web developers” are aware of, like… range requests, which are exactly what you’d think they’d be.

BTW thank you havernator, because I have just realised what I can do with the setup I'm almost ready to pull the trigger on that'll give me a surfeit of online capacity (at least a baseload can be maintained while the rest is used for work instead of cloud time) : I am definitely going to investigate the possibility of providing a high level of standards specifications for simple web serving. If the W3C Jigsaw projec…

Not trying to offend, but this comment was hard to follow in a weird way. Along with your profile this makes me wonder-- are you gpt-2?

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#215

Post by phiresky »

yoz wrote: post #174

As everyone else has been saying, this is amazing work. It sounds like the biggest issue with loading the page is the initial sql.js download - it's about 1.2MB, is that right? Might it be feasible to easily strip down SQLite so that it only compiles the parts for read-only use? The browser version is obviously somewhat read-only but that's because of the sandbox. I'm talking about excluding the code for CREATE, UPDA…

The actual transferred data for the sqlite code should only be 550kB (gzip compression).

Stripping out the write parts is a good idea. SQLite actually has a set of compile time flags to omit features [1]. I just tried enabling as many of those as possible, but it didn't seem to reduce wasm size much, though I might be doing something wrong. There's also no easy flags to disable CREATE / UPDATE / INSERT .

[1] https://www.sqlite.org/compile.html#omitfeatures

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#216

Post by phiresky »

graderjs wrote: post #175

Man you are a frickin genius, seriously. like how you put all this together all the depth of knowledge of different topics this would require the low level and the high level and the way you explain it simply confidently and with impact. Your work is really an inspiration. You computer scienced the sheet out of this thing. this achievement, and this blog post, to me is on par with blog posts that you would see from a…

Thank you, I really appreciate it. It's pretty fun to do this kind of thing for yourself, but it's really rewarding to be able to share it with other people.

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#218

Post by nanna »

tyingq wrote: post #28
sroussey wrote: post #4

The innovation here is getting sql.js to use http and range requests for file access rather than all being in memory. I wonder when people using next.js will start using this for faster builds for larger static sites?

Would also be great to add (efficient) search to a static blog.

Definitely. Just need to add a layer to the static site generator for it to populate the SQLite DB, right?

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#219

Post by amjd »

batch12 wrote: post #214
Cullinet wrote: post #148

Earlier quoted context omitted.

BTW thank you havernator, because I have just realised what I can do with the setup I'm almost ready to pull the trigger on that'll give me a surfeit of online capacity (at least a baseload can be maintained while the rest is used for work instead of cloud time) : I am definitely going to investigate the possibility of providing a high level of standards specifications for simple web serving. If the W3C Jigsaw projec…

Not trying to offend, but this comment was hard to follow in a weird way. Along with your profile this makes me wonder-- are you gpt-2?

So I'm not alone. I felt that too!

Re: Hosting SQLite databases on GitHub Pages or any static file hoster

#220

Post by aasasd »

aflag wrote: post #213

Very clever. I wonder if there are databases optimised for this use case. I can imagine something that always requires indexes to do the queries and stores data in disk in ways to make it easy to fetch only the bits you need.

That would be equivalent to always putting your data in b-trees or other structures, according to the request patterns, without keeping the tables themselves. Sort of how you need to do that in Redis for any kind of sane request strategy other than key-value lookups.
Post reply on HN