, then we would be able to quickly get all indicators for a single country, but not all country values of a single indicator. If the index was ordered country_code, indicator_code. If that index did not include the value column, the SQLite engine would have to do another random access (unpredictable) read and thus HTTP request to retrieve the actual value for every data point. For example, the index used in the above query is a INDEX ON wdi_data (indicator_code, country_code, year, value). You can see the effect of this by looking at the "Access pattern" column in the page read log above.Īll of this only works well when we have indices in the database that match the queries well. This means that index scans or table scans reading more than a few KiB of data will only cause a number of requests that is logarithmic in the total byte length of the scan. That’s because I implemented a pre-fetching system that tries to detect access patterns through three separate virtual read heads and exponentially increases the request size for sequential reads. Note that it only has to do 20 requests and not 270 (as would be expected when fetching 270 KiB with 1 KiB at a time). The above query should do 10-20 GET requests, fetching a total of 130 - 270KiB, depending on if you ran the above demos as well. Here’s an example of a simple index lookup query: I’ve set the page size to 1 KiB for this database. Thankfully, SQLite already organizes its database in "pages" with a user-defined page size (4 KiB by default). Since fetching data via HTTP has a pretty large overhead, we need to fetch data in chunks and find some balance between the number of requests and the used bandwidth. Of course it can’t write to this file, but a read-only database is still very useful. 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. 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. SQLite can be compiled with emscripten without any modifications, and the sql.js library is a thin JS wrapper around the wasm code. So how do you use a database on a static file hoster? Firstly, SQLite (written in C) is compiled to WebAssembly. Note that this website is 100% hosted on a static file hoster (GitHub Pages). You can change the code in any way you like, though if you make a query too broad it will fetch large amounts of data ) Press the Run button to run the following demos. Here’s a demo using the World Development Indicators dataset - a dataset with 6 tables and over 8 million rows (670 MiByte total). So I wrote a tool to be able to use a real SQL database in a statically hosted website! Hosting a static website is much easier than a "real" server - there’s many free and reliable options (like GitHub, GitLab Pages, Netlify, etc), and it scales to basically infinity without any effort. Then when I revisit it years later, I’m annoyed that it’s gone and curse myself for relying on an external service - or on myself caring over a longer period of time. In the past when I’ve used a backend server for these small side projects at some point some external API goes down or a key expires or I forget about the backend and stop paying for whatever VPS it was on. But if you want to use a database, you either need to write a backend (which you then need to host and maintain forever) or download the whole dataset into the browser (which is not so great when the dataset is more than 10MB). I was writing a tiny website to display statistics of how much sponsored content a Youtube creator has over time when I noticed that I often write a small tool as a website that queries some data from a database and then displays it in a graph, a table, or similar.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |