Building sql.js-httpvfs for the Browser

2023-01-20

These are my steps to getting sqlite working in the browser. The project I'm currently working on is adding searching to my static site. I don't want to set up a proper backend for it so I need to be able to run database queries somehow in the browser.

Luckily sqlite has been compiled to wasm and so I can now download the database into memory and then run queries on it. This would be entirely local but downloading the entire database when you are probably only going to use a subset of it is quite wasteful.

This is where phiresky's brilliant project comes in, he has built a virtual filesystem that uses the range headers in http so that you can run selects on a database that is hosted on a server. I'm not sure how it all works technically but the idea seems to be that you download an index and then run the queries that can be answered by that index. The results are the fetched specifically from the sqlite file hosted on the server.

By using the range headers, you can get just the parts of the database that hold that data you need.

https://www.npmjs.com/package/sql.js-httpvfs

The project instructions are simple and easy to follow and I quickly got something working with them. This did require using webpack and nodejs to set things up and so I wanted to find a way to do this once and then have a built library ready to be used anywhere I need it. This is where Daniel Holth's post on dev.to came in handy. He outlines a couple of things that lets you build a module version of phiresky's library that can be built and used in the browser. This way the node build step is entirely removed!

https://dev.to/dholth/using-sqljs-httpvfs-with-browser-1il8

The following is the steps to get sqlite working in the browser without having to download the entire database first and without using node as a build step.

The first step is to create a node project:

mkdir sqlite-browser
cd sqlite-browser
npm init

The next step is to install the dependencies:

npm install --save-dev webpack webpack-cli typescript ts-loader
npm install --save sql.js-httpvfs

We need to create a tsconfig.js file:

{
    "compilerOptions": {
        "target": "es2020",
        "module": "es2020",
        "moduleResolution": "node",
    }
}

Now we need to create the webpack.config.js file and place the following in it:

module.exports = {
  entry: "./src/index.ts",
  module: {
    rules: [
      {
        test: /\.tsx?$/,
        use: "ts-loader",
        exclude: /node_modules/,
      },
    ],
  },
  resolve: {
    extensions: [".tsx", ".ts", ".js"],
  },
  output: {
    filename: "sql-httpvfs.js",
    library: {
      type: "module" // output a JavaScript module
    },
    module: true, // truly
  },
  experiments: {
    outputModule: true  // yes, we really want one
  },
  optimization: {
    minimize: true
  },
};

Now we need to create the src directory:

mkdir src

Then we create src/index.ts with the following:

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

const workerUrl = new URL(
  "sql.js-httpvfs/dist/sqlite.worker.js",
  import.meta.url
);
const wasmUrl = new URL("sql.js-httpvfs/dist/sql-wasm.wasm", import.meta.url);

async function load(url: string) {
  const worker = await createDbWorker(
    [
      {
        from: "inline",
        config: {
          serverMode: "full",
          url: url,
          requestChunkSize: 4096,
        },
      },
    ],
    workerUrl.toString(),
    wasmUrl.toString()
  );
  return worker;
}

export { load };

Now we can build the project:

./node_modules/.bin/webpack --mode=production

This will create 3 files in the dist directory, a wasm file which is the sqlite wasm binary, the js file with a hash is the worker and the last file is what we will be using in the browser.

$ ls dist
8a2a3c8efae774018112.wasm  
55d3459a09daf1539fb9.js  
sql-httpvfs.js

I renamed these files so it was a bit more obvious what they are.

We can now use these files in a regular html project. I created a folder called dist and copied the files over. I also created a dummy sqlite file called example.sqlite3.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <title>title</title>
  </head>
  <body>
    <script type="module">
      import { load } from "./dist/sql-httpvfs.js";

      const worker = await load("../example.sqlite3");

      const result = await worker.db.query(`select * from mytable`);

      console.log(result);
    </script>
  </body>
</html>

If everything went smoothly, we should be able to open the inspector and see what result looks like.

It should be array with a single object:

0: Object { foo: "hello", bar: "world" }

Voila! We can now query a static database file without downloading the entire thing.