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

71–80 of 252 posts

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

#71

Post by phiresky »

pudmaidai wrote: post #59
fulafel wrote: post #55

Earlier quoted context omitted.

B-Tree indexes are designed to work like this, to require a low number of IO operations. The index contains pointers to other places in the index.

So the answer is “yes, it has to download the index first”? None of these comments answer my question.

The B-Tree is a tree that in this case is perfectly balanced. So if you do a query with an index in a database it will fetch an logarithmic amount of data from the index and then a constant amount of data from the table.

For the example the wdi_data table is 300MB and an index on it is 100MB in size. This index has a tree depth of 4 - which means SQLite has to read exactly 4 pages (4KiB) to get to the bottom of it and find the exact position of the actual row data.

you can check the depth of the b-trees with `sqlite3_analyzer`.

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

#72

Post by cookguyruffles »

pudmaidai wrote: post #21
Gehinnn wrote: post #2

TL;DR: Compile SQLite to JS with emscripten, implement a virtual fs in JS to stream chunks of a statically hosted (readonly) SQL database. If queries make use of indices, only a fraction of the database needs to be downloaded. Also, you can use SQLite to query the DOM.

I can't figure out exactly how it knows which chunk to download. Does it always download the whole index first? Or does it include it in the built JS file itself?

Both the index and table data are btrees. These are trees - the root node sits in some known location (offset) in the file, referenced by the file header and metadata. As SQLite traverses the tree, it encounters new descendents it would like to visit, presumably identified by their byte offset in the file, which is all needed for this VFS magic to issue a suitable range request.

- SQlite opens the file and reads 4kb worth of header -> range request for byte 0-4096

- headers/metadata refers to index table with root node at 8192kb

- user issues SELECT * from index WHERE name = 'foo'

- SQLite reads root node from the file (range request for 8192kb..)

- Root node indicates left branch covers 'foo'. Left branch node at address 12345kb

- Fetch left branch (range request for 12345kb)

- New node contains an index entry for 'foo', row 55 of data page at 919191kb

- SQLite reads data page (range request for 91919191kb..)

etc etc etc

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

#75

Post by tehbeard »

drtournier wrote: post #60

Solution works really well to databases which will not be updated frequently, like a standalone site. Although one should be aware of one very important git behavior - git does not diff binary files (like SQLite dbs). That means 2 things: 1. Each db update will generate a new file in git, maintaining the whole old file in history, instead of the diff in bytes. This will accumulate a lot of clutter in the repo 2. As g…

You are only gonna encounter corruption if u either a) messed up the gitconfig for line endings or b) named the database mydbfile.txt

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

#76

Post by xucheng »

drtournier wrote: post #60

Solution works really well to databases which will not be updated frequently, like a standalone site. Although one should be aware of one very important git behavior - git does not diff binary files (like SQLite dbs). That means 2 things: 1. Each db update will generate a new file in git, maintaining the whole old file in history, instead of the diff in bytes. This will accumulate a lot of clutter in the repo 2. As g…

You can just store the database in text format (e.g. csv) in the git and turn it to SQLite db when building the website.

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

#77

Post by formerly_proven »

tehbeard wrote: post #68
rzzzt wrote: post #31

Earlier quoted context omitted.

If you are interested in full-text search on the client, Lunr is also an option: https://lunrjs.com/docs/index.html

Lunr needs the full index on client though, right? Being able to use fts-5 without the penalty of having to pull down the whole index make it work much better at larger scales, even with the penalty of additional network requests.

This could be genuinely interesting for tools like e.g. sphinx-doc, which currently has a client-side search that does indeed ship the entire index to the client.

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

#78

Post by rakoo »

pudmaidai wrote: post #59
fulafel wrote: post #55

Earlier quoted context omitted.

B-Tree indexes are designed to work like this, to require a low number of IO operations. The index contains pointers to other places in the index.

So the answer is “yes, it has to download the index first”? None of these comments answer my question.

Everything in SQLite is stored in B-Trees. Data or indexes. So you don't need to download the whole index first; you only need to download the necessary pages of the trees to access data, whether it's part of an index or actual data

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

#79

Post by modeless »

Super cool. I wonder if an approach like this could ever work for a writable database. I guess the issue is that you couldn't have fine grained access control without a whole lot of server side validation, at which point you might as well just run a regular database server.

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

#80

Post by tehbeard »

pudmaidai wrote: post #59
fulafel wrote: post #55

Earlier quoted context omitted.

B-Tree indexes are designed to work like this, to require a low number of IO operations. The index contains pointers to other places in the index.

So the answer is “yes, it has to download the index first”? None of these comments answer my question.

It just needs to download the pages (x KB chunks) to traverse from the header to the particular index and table schema, a fraction of the whole data.
Post reply on HN