Web hosting SQLite Databases on GitHub Pages

(or any static file hoster)

Apr 17, 2021 • Closing Replace Might possibly well additionally 03, 2021

I became once writing a small internet space to illustrate statistics of how great backed issue material a Youtube creator has over time when I noticed that I veritably write a small tool as a internet space that queries some recordsdata from a database after which shows it in a graph, a desk, or identical. However whenever you will want to make employ of a database, you either want to write a backend (which then you definately want to host and sustain forever) or download the general dataset into the browser (which is no longer so gigantic when the dataset is extra than 10MB).

Within the previous when I’ve frail a backend server for these small facet tasks in the end some exterior API goes down or a key expires or I ignore the backend and quit paying for whatever VPS it became once on. Then when I revisit it years later, I’m annoyed that it’s long gone and curse myself for counting on an exterior carrier – or on myself caring over an extended interval of time.

Web hosting a static internet space is arrangement more uncomplicated than a “trusty” server – there’s many free and loyal alternate solutions (fancy GitHub, GitLab Pages, Netlify, etc), and it scales to on the general infinity with none effort.

So I wrote a tool so that you can make employ of a trusty SQL database in a statically hosted internet space!

Here’s a demo the usage of the World Building Indicators dataset – a dataset with 6 tables and over 8 million rows (670 MiByte total).

earn out country_code, long_name from wdi_country restrict 3;

As you might possibly well see, we are able to place aside a query to the wdi_country desk while fetching only 1kB of recordsdata!

Here’s a paunchy SQLite put aside a query to engine. As such, we are able to employ e.g. the SQLite JSON functions:

earn out json_extract(arr.cost, '$.foo.bar') as bar
  from json_each('[{"foo": {"bar": 123}}, {"foo": {"bar": "baz"}}]') as arr

We are able to additionally register JS functions so that they would well also be known as from within a put aside a query to. Here’s an example with a getFlag feature that gets the flag emoji for a country:

feature getFlag(country_code) {
  // loyal some unicode magic
  return String.fromCodePoint(...Array.from(country_code||"")
    .plan(c => 127397 + c.codePointAt()));
}

now stay wide awake for db.create_function("get_flag", getFlag)
return now stay wide awake for db.put aside a query to(`
  earn out long_name, get_flag("2-alpha_code") as flag from wdi_country
    where role is no longer null and currency_unit = 'Euro';
`)

Press the Bustle button to lunge the next demos. You’d switch the code in any components you fancy, though whenever you develop a put aside a query to too abundant it will receive ravishing amounts of recordsdata 😉

Showcase that this internet space is 100% hosted on a static file hoster (GitHub Pages).

So how indulge in you utilize a database on a static file hoster? First and distinguished, SQLite (written in C) is compiled to WebAssembly. SQLite might possibly well also be compiled with emscripten with none adjustments, and the sql.js library is a thin JS wrapper around the wasm code.

sql.js only enables you to receive and skim from databases that are completely in memory though – so I implemented a digital file system that fetches chunks of the database with HTTP Differ requests when SQLite tries to read from the filesystem: sql.js-httpvfs. From SQLite’s perspective, it loyal appears fancy it’s living on a typical computer with an empty filesystem besides a file known as /wdi.sqlite3 that it will read from. Clearly it’ll’t write to this file, nonetheless a read-only database is restful very priceless.

Since fetching recordsdata through HTTP has a gorgeous ravishing overhead, we would like to receive recordsdata in chunks and salvage some steadiness between the want of requests and the frail bandwidth. Fortunately, SQLite already organizes its database in “pages” with a user-outlined internet page size (4 KiB by default). I’ve build the win page size to 1 KiB for this database.

Here’s an example of a straightforward index look up put aside a query to:

earn out indicator_code, long_definition from wdi_series where indicator_name
    = 'Literacy price, formative years total (% of folk ages 15-24)'

Bustle the above put aside a query to and check at the win page read log. SQLite does 7 internet page reads for that put aside a query to.

• Three internet page reads are loyal some to receive some schema recordsdata (these are already cached)
• Two internet page reads are the index look up within the index on wdi_series (indicator_name)
• Two internet page reads are on the wdi_series desk recordsdata (the predominant to hunt down the row cost by predominant key, the second to receive the text recordsdata from an overflow internet page)

Each the index as effectively as the desk reads are B-Tree lookups.

A extra complex put aside a query to: What are the countries with the bottom formative years literacy price, per the most up-to-date recordsdata from after 2010?

with newest_datapoints as (
  earn out country_code, indicator_code, max(year) as year from wdi_data
  join wdi_series the usage of (indicator_code)
  where
    indicator_name = 'Literacy price, formative years total (% of folk ages 15-24)'
    and year > 2010
  community by country_code
)
earn out c.short_name as country, printf('%.1f %%', cost) as "Formative years Literacy Price"
from wdi_data
  join wdi_country c the usage of (country_code)
  join newest_datapoints the usage of (indicator_code, country_code, year)
affirm by cost asc restrict 10

The above put aside a query to must restful indulge in 10-20 GET requests, fetching a total of 130 – 270KiB, reckoning on whenever you ran the above demos as effectively. Showcase that it only has to indulge in 20 requests and no longer 270 (as might possibly well be expected when fetching 270 KiB with 1 KiB at a time). That’s on legend of I implemented a pre-fetching system that tries to detect receive entry to patterns through three separate digital read heads and exponentially increases the ask size for sequential reads. This style that index scans or desk scans reading extra than a few KiB of recordsdata will only trigger a necessity of requests that is logarithmic within the general byte size of the scan. You’d see the discontinue of this by the “Web actual of entry to pattern” column within the win page read log above.

All of this only works effectively when now we indulge in indices within the database that match the queries effectively. Shall we embrace, the index frail within the above put aside a query to is a INDEX ON wdi_data (indicator_code, country_code, year, cost). If that index did now not comprise the payment column, the SQLite engine would indulge in to indulge in but every other random receive entry to (unpredictable) read and thus HTTP ask to retrieve the trusty cost for every recordsdata point. If the index became once ordered country_code, indicator_code, ..., then we might possibly well be in a position to rapidly receive all indicators for a single country, nonetheless no longer all country values of a single indicator.

We are able to additionally develop employ of the SQLite FTS module so we are able to indulge in a paunchy-text search on the extra text-heavy recordsdata within the database – in this case there are over 1000 human pattern indicators within the database with longer descriptions.

earn out * from indicator_search
where indicator_search match 'educatiofemal*'
affirm by crude restrict 10

The entire quantity of recordsdata within the indicator_search FTS desk is round 8 MByte. The above put aside a query to must restful only receive round 70 KiB. You’d see how it’s miles constructed right here.

And finally, right here’s a extra entire demonstration of the usefulness of this method – right here’s an interactive graph showing the enchancment of a few countries over time, for any countries you would just like the usage of any indicator from the dataset:

Showcase that many indicators are only accessible for some countries, as an instance the indicator “Ladies who assume a husband is justified in beating his partner when she burns the meals” is per surveys only conducted in decrease-developed countries.

Bonus: DOM as a database

Since we’re already working a database in our browser, why no longer employ our browser as a database the usage of a digital desk known as dom?

earn out depend(*) as number_of_demos from dom
  where selector match '.issue material div.sqlite-httpvfs-demo';
earn out depend(*) as sqlite_mentions from dom
  where selector match '.issue material p' and textContent fancy '%SQLite%';

We are able to even insert substances without extend into the DOM:

insert into dom (mum or dad, tagName, textContent)
    earn out 'ul#outtable1', 'li', short_name
    from wdi_country where currency_unit = 'Euro'

Output:

And update substances within the DOM:

update dom build textContent =
  get_flag("2-alpha_code") || ' ' || textContent
from wdi_country
where selector match 'ul#outtable1 > li'
  and textContent = wdi_country.short_name

Clearly, every thing right here is begin source. The major implementation of the sqlite wrapper is in sql.js-httpvfs. The source code of this weblog post is a pandoc markdown file, with the demos being a personalized “fenced code block” React part.