Query SQLite on GitHub Pages with sql.js-httpvfs

Source: Dev.to

The Problem

I once had a 670 MB SQLite database and a simple requirement: put it on a static site so users could search it by keyword.

• I didn’t want a backend – the whole project is static.
• Uploading the raw DB and letting the browser download it would make users wait for a 670 MB transfer.

The Solution – sql.js-httpvfs

sql.js-httpvfs builds on sql.js, adding an HTTP Range‑Request‑based virtual file system so the browser only fetches the SQLite pages a query actually needs.

That same 670 MB database? A simple key lookup transfers roughly 1 KB.

How SQLite pages work

• SQLite stores data in fixed‑size pages (default 4096 bytes).
• Every B‑Tree node, index entry, and row maps to a specific page number.
• An indexed query only reads the pages along the B‑Tree path – it never scans the whole table.

How sql.js-httpvfs exploits this

It replaces the Emscripten VFS layer. Instead of reading from an in‑memory ArrayBuffer, it issues HTTP Range Requests:

GET /data.sqlite HTTP/1.1
Range: bytes=4096-8191

The server returns just those 4096 bytes, which are handed to the SQLite engine.
All work runs inside a Web Worker, so the main thread stays responsive and every query is async.

Prefetching

The library implements three virtual read heads that track access patterns.
If a read head detects sequential page access, it automatically ramps up pre‑fetching—from one page at a time to several pages per request. This is crucial for full‑text search, which traverses many tree nodes in sequence.

Indexes are essential

• Good – uses an index (covers the query, no data‑row reads).
• Bad – full table scan (downloads the whole table).

-- Good: uses index
EXPLAIN QUERY PLAN
SELECT name, price FROM products WHERE sku = 'ABC123';
-- Output: SEARCH products USING INDEX idx_sku (sku=?)

-- Bad: full table scan
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE description LIKE '%keyword%';
-- Output: SCAN products

Installation

npm install sql.js-httpvfs

sql.js-httpvfs needs two additional static assets: sql-wasm.wasm and a Worker JS file. Both are included in the package – just copy them into your public directory.

# With Vite (or any static‑file server)
cp node_modules/sql.js-httpvfs/dist/sql-wasm.wasm public/
cp node_modules/sql.js-httpvfs/dist/sqlite.worker.js public/

Initialising the Worker

import { createDbWorker } from 'sql.js-httpvfs';

// URLs must point to the static files you just copied
const workerUrl = new URL('/sqlite.worker.js', import.meta.url);
const wasmUrl   = new URL('/sql-wasm.wasm', import.meta.url);

const worker = await createDbWorker(
  [
    {
      from: 'url',               // load DB from a URL (there's also an inline mode)
      config: {
        serverMode: 'full',      // single‑file mode
        url: '/data.sqlite',     // path to the database
        requestChunkSize: 4096, // Range Request size, aligned to SQLite page size
      },
    },
  ],
  workerUrl.toString(),
  wasmUrl.toString()
);

requestChunkSize defaults to 4096, matching SQLite’s default page size. If you use a different page size, adjust this value accordingly.

Optimising the SQLite file

The database’s page size directly affects transfer efficiency. Do these steps before uploading the file:

-- Smaller page size → finer‑grained Range Requests
PRAGMA page_size = 1024;          -- must be set before any tables are created

-- Remove the WAL file (otherwise you’d need to keep two files in sync)
PRAGMA journal_mode = delete;

-- Rebuild the DB to apply the new page size and remove fragmentation
VACUUM;

Designing Indexes

Think about your query patterns and use covering indexes whenever possible.

-- Example: common query is
--   WHERE category = ? ORDER BY created_at DESC LIMIT 20
-- A covering index includes all columns needed by SELECT,
-- so the query never touches the data rows.
CREATE INDEX idx_category_date_cover
  ON articles(category, created_at DESC, title, slug);

Full‑text search with FTS5

CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  content,
  content='articles',      -- reference the source table to avoid duplicate storage
  content_rowid='id'
);

-- Populate the FTS index on initial data load
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

Querying the Database

Once the worker is set up, querying looks similar to regular sql.js – but everything returns a Promise.

Standard query

const results = await worker.db.query(
  `SELECT title, slug, created_at
   FROM articles
   WHERE category = ?
   ORDER BY created_at DESC
   LIMIT 20`,
  ['frontend']
);

// results → { columns: string[], values: any[][] }
console.log(results.columns); // ['title', 'slug', 'created_at']
console.log(results.values);  // [['Article title', 'slug-here', '2024-01-01'], ...]

Full‑text search

const ftsResults = await worker.db.query(
  `SELECT a.title,
          a.slug,
          snippet(articles_fts, 1, '', '', '...', 20) AS excerpt
   FROM articles_fts
   JOIN articles a ON articles_fts.rowid = a.id
   WHERE articles_fts MATCH ?
   ORDER BY rank
   LIMIT 10`,
  [keyword]
);

Measuring Transfer Size

One of my favorite features: you can see exactly how many bytes each query fetches.

// Record stats before the query
const bytesBefore = worker.getStats().totalFetchedBytes;

await worker.db.query('SELECT * FROM articles WHERE id = ?', [42]);

// Compare after
const bytesAfter = worker.getStats().totalFetchedBytes;
console.log(`Query transferred: ${bytesAfter - bytesBefore} bytes`);

getStats() returns an object with:

• totalFetchedBytes – cumulative bytes transferred so far.
• totalRequests – cumulative number of HTTP Range Requests made.

TL;DR

• sql.js-httpvfs lets a static site query a large SQLite DB without downloading the whole file.
• It works by issuing HTTP Range Requests for the exact pages needed.
• Indexes (especially covering indexes) are essential for keeping transfers tiny.
• The library ships with a Web Worker and a WASM build; just copy the two static assets, initialise the worker, and query as you would with sql.js.

Happy static‑site searching!

Chunked SQLite Files for the Browser

During development I display request‑count numbers on screen to verify that indexes are actually working.

For large databases you can split the file into fixed‑size chunks, which makes CDN caching much more effective.

Split with the system split command (Linux/macOS)

split -b 10m data.sqlite data.sqlite.
# Produces: data.sqlite.aa, data.sqlite.ab, …

Or use the tool bundled with sql.js‑httpvfs

npx sql.js-httpvfs-tools split data.sqlite --chunk-size 10485760
# Produces split files and a JSON manifest describing the chunks

Configure sql.js-httpvfs for chunked mode

{
  from: 'url',
  config: {
    serverMode: 'chunked',
    serverChunkSize: 10 * 1024 * 1024,   // 10 MB per chunk
    urlPrefix: '/db/data.sqlite.',       // prefix shared by all chunk files
    urlSuffix: '',
    fromCache: false,
    requestChunkSize: 4096,
  },
}

1. Put the database and static assets in your repository (or use Git LFS), then push.
   GitHub’s static file server supports Range Requests out of the box — no special configuration needed.
2. S3, Cloudflare Pages, and Netlify also support Range Requests, so any of those work directly.

CORS considerations

If your frontend and database are on different origins, the server must return:

Access-Control-Allow-Origin: *
Access-Control-Allow-Headers: Range
Access-Control-Expose-Headers: Content-Range, Accept-Ranges

Things to know before committing to this approach

• Read‑only – HTTP Range Requests are read operations only; writes require a backend.
  For read/write in the browser, see the official SQLite Wasm with OPFS or wa‑sqlite.
• No cache eviction – Pages downloaded during a session are cached in Worker memory, and that cache never shrinks. Heavy query usage can cause memory growth.
• Experimental – The author describes this as demo‑level code in the README; it isn’t recommended for high‑stability production.

Further reading

• Getting Started with sql.js – SQLite in the Browser (fundamentals).
• Offline Web Apps with sql.js and IndexedDB – complete implementation for offline writes.
• Browser Storage Solutions Compared – broader comparison of browser storage options.