Hosting SQLite databases on GitHub Pages or any static file hoster • Hacker News Board

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

#41

Post by simonw » Sun, May 02, 2021, 7:17 PM UTC

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is:

    select country_code, long_name from wdi_country
    order by rowid desc limit 100

It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB!

I have an animated GIF demo of this here: https://twitter.com/simonw/status/1388933800445452290

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

#42

Post by edoceo » Sun, May 02, 2021, 7:17 PM UTC

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.

yea, sqlite FTS5 has been pretty amazing for quick search solutions (but I use english only)

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

#43

Post by WJW » Sun, May 02, 2021, 7:22 PM UTC

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

So assuming no country has a name longer than 98 characters and that all country codes are 2 characters, that is over 500% overhead? Are you missing a /s in your post?

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

#44

Post by city41 » Sun, May 02, 2021, 7:22 PM UTC

mvanaltvorst wrote: post #22

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?

I'm curious, in what manner could this method speed up Next.js builds? That's all done locally, which negates the effect of HTTP range requests, right?

I'm guessing they mean rather than build a static Next site that generates 10k+ pages (or whatever large means in the given context), it instead creates one page that just queries the data from the client.

I have one Next static site that has about 20k pages and takes about 20 minutes to build and deploy. I think that's an acceptable build time. But I do know of other people around the net who have mentioned having sites with 20k-ish pages taking an hour+ to build. For them I could see the desire to try this sqlite trick.

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

#45

Post by bbkane » Sun, May 02, 2021, 7:24 PM UTC

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

Would it be possible to use a datasette frontend with this as a "backend" and statically host the whole thing?

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

#46

Post by phiresky » Sun, May 02, 2021, 7:27 PM UTC

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

Huh, that's actually kind of a worst case I didn't think about: Since you're doing a reverse table scan my "sequential access" detection doesn't kick in. If you do the same query but with a forward scan it should fetch roughly the same amount of data but only do like 5 HTTP requests since the request size doubles for every sequential access.

e.g.:

select country_code, long_name from wdi_country where rowid >= 164 order by rowid asc limit 100;

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

#47

Post by simonw » Sun, May 02, 2021, 7:28 PM UTC

bbkane wrote: post #45

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

Would it be possible to use a datasette frontend with this as a "backend" and statically host the whole thing?

Not easily - Datasette is written in Python, so you could try running it in WebAssembly (like Mozilla did for Jupyter with https://hacks.mozilla.org/2019/03/iodide-an-experimental-too... ) but it would be enough work that it might be easier to reimplement a subset of Datasette directly in JavaScript.

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

#48

Post by simonw » Sun, May 02, 2021, 7:29 PM UTC

WJW wrote: post #43

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

So assuming no country has a name longer than 98 characters and that all country codes are 2 characters, that is over 500% overhead? Are you missing a /s in your post?

The clever part here is only fetching 50KB out of 680MB.

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

#49

Post by ajfjrbfbf » Sun, May 02, 2021, 7:30 PM UTC

WJW wrote: post #43

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

So assuming no country has a name longer than 98 characters and that all country codes are 2 characters, that is over 500% overhead? Are you missing a /s in your post?

> sql.js only allows you to create and read from databases that are fully in memory though - so I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem: sql.js-httpvfs. From SQLite’s perspective, it just looks like it’s living on a normal computer with an empty filesystem except for a file called /wdi.sqlite3 that it can read from.

From this paragraph it should be pretty clear that it's actually a great result. The database will obviously need to read more data than it presents, so more is fetched.

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

#50

Post by watermelon0 » Sun, May 02, 2021, 7:30 PM UTC

WJW wrote: post #43

simonw wrote: post #41

Pop open the network pane in the browser tools and try running this SQL query for a demo of how clever this is: select country_code, long_name from wdi_country order by rowid desc limit 100 It fetches just 54.2KB of new data (across 49 small HTTP requests) to return 100 results - from a statically hosted database file that's 668.8MB! I have an animated GIF demo of this here: https://twitter.com/simonw/status/13889338…

So assuming no country has a name longer than 98 characters and that all country codes are 2 characters, that is over 500% overhead? Are you missing a /s in your post?

Have you actually read the article? SQLite is unmodified, and thinks it runs on a virtual file system, which fetches file chunks via HTTP range headers.

It's REALLY impressive that you only need to read 54 KB out of 700 MB, to fetch the records.