SQLite in the browser

I'm getting more interested in SQLite as a database, keeping everything in memory to the process itself. When do we actually need to have a multi-process solution for a website? How much can we really do within a single process?

One type of architecture I'm exploring is to have a zillion little sqlite files that are shipped around as needed, rather than providing an API to parse them out have one way where we can generate a bunch of effectively static assets of the structured data and then having the visualization happen on the client side. Let's look at two JavaScript examples that let that all happen in the browser.

Both of these use wasm to ship the actually sqlite binary code and run in in browser!

SQL.js

We are going to use npm to download the packages only. We need to get sql-wasm.js and sql-wasm.wasm and simply serve them up in the browser.

1
2
3
4
  npm init -y
  npm install sql.js
  cp node_modules/sql.js/dist/sql-wasm.js .
  cp node_modules/sql.js/dist/sql-wasm.wasm .

Test CLI Script

Lets look at how to create and populate a simple database on the command line.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
  var fs = require("fs");
  var initSqlJs = require('./sql-wasm.js');

  initSqlJs().then( function(SQL) {
      var db = new SQL.Database();
      // Run a query without reading the results
      db.run("CREATE TABLE test (col1, col2);");
      // Insert two rows: (1,111) and (2,222)
      db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);

      var data = db.export();
      var buffer = new Buffer.from(data);
      fs.writeFileSync("filename.sqlite", buffer);
  } )

Which we can test with:

1
  echo select \* from test\; | sqlite3 sqljs/filename.sqlite

1	111
2	222

Not wildly exciting data but there there you go.

Test webpage

Load up the sql-wasm.js file, which provides initSqlJs.
locateFile is used to figure out where sql-wasm.wasm is.
Fetch our database that we genereated previously, called filename.sqlite.
Create a new database using the Uint8Array of the file loaded.
Do some awesome HTML DOM manipulation to create the table.
Run the query, and populate the table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
  <html>
    <head>
      <script src='./sql-wasm.js'></script>
      <script type="module">
        // Load sqj.js module and database
        const sqlPromise = initSqlJs({
          locateFile: file => `./${file}`
        });
        const dataPromise = fetch("filename.sqlite").then(res => res.arrayBuffer());
        const [SQL, buf] = await Promise.all([sqlPromise, dataPromise])
        const db = new SQL.Database(new Uint8Array(buf));

        // Grab the table element
        const table = document.querySelector( "table" );
        table.innerHTML = '<tr><th>Col1</th><th>Col2</th></tr>';
      
        // Prepare a statement
        var stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");
        stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111}

        // Bind new values
        stmt.bind({$start:1, $end:2});
        while(stmt.step()) { //
        var row = stmt.getAsObject();
          table.innerHTML += `<tr><td>${row['col1']}</td><td>${row['col2']}</td></tr>`;
        }
        console.log( "Done" );
      </script>
    </head>
    <body>
      <h1>Results</h1>

      <table></table>
    </body>
  </html>

In the directory with the html, wasm, and sqlite files, run:

1
npx live-server

And you should get:

deno-sqlite

Lets look at using a different method, using deno instead of node.

Installing deno

I used asdf, but you can follow the official instructions.

1
2
  asdf plugin add deno
  asdf install deno latest

And then check to see what you have installed:

1
deno --version

deno 1.8.3 (release, x86_64-unknown-linux-gnu)
v8 9.0.257.3
typescript 4.2.2

Configuring TypeScript

I need this to make my emacs integration work, but it could be optional for you.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
  { 
      "compilerOptions": {
          "lib": [
            "es6",
          ],
          "plugins": [
              {
                  "name": "typescript-deno-plugin",
                  "enable": true, // default is `true`
                  "importmap": "import_map.json"
              }
          ]
      }
  }

Dependencies

Create deps.ts to centralize your version dependancies:

1
  export { DB } from "https://deno.land/x/sqlite/mod.ts";

Test cli script

Lets create a test.ts script which creates a database and popualtes it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
import { DB } from "./deps.ts";

// Open a database
const db = new DB("test.db");
db.query(
  "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
);

const names = ["Peter Parker", "Clark Kent", "Bruce Wayne"];

// Run a simple query
for (const name of names) {
  db.query("INSERT INTO people (name) VALUES (?)", [name]);
}

// Print out data in table
for (const [name] of db.query("SELECT name FROM people")) {
  console.log(name);
}

// Close connection
db.close();

And then we can run this with:

1
deno run --allow-read --allow-write test.ts

Which returns:

Peter	Parker
Clark	Kent
Bruce	Wayne

Test web script webworkers

Here we are going to write a web.tsx file that will function as a WebWorker. Our database will run in a different thread and the main worker thread.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
  import { DB } from "./deps.ts";
  const ctx: Worker = self as any;

  ctx.onmessage = (e) => {
      console.log( "Got message" );
      ctx.postMessage( "Hi there" );
      for (const [name] of db.query("SELECT name FROM people")) {
          console.log(name);
      }

  }

  const db = new DB();

  db.query(
      "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
  );

  const names = ["Peter Parker", "Clark Kent", "Bruce Wayne"];

  // Run a simple query
  for (const name of names) {
      db.query("INSERT INTO people (name) VALUES (?)", [name]);
  }

  console.log( "Loaded the worker" );

Then we need to bundle it up:

1
deno bundle web.tsx web.js

This creates a web.js file that contains our webworker as well as the entire sqlite binary in wasm.

Webpage

This code is simpler that the previous code. We load up web.js as a modulepreload, and then inside of our script tag we create the new webwork, and send it a message. Once it gets the message it creates the in-memory database, then

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
  <html>
    <head>
    <link rel="modulepreload" href="web.js">
    </head>
    <body>
      <div id="status">Loading...</div>
      <table id="data"></table>

      <script type="module">
        const worker = new Worker( "./web.js", {type: "module"});

        worker.onmessage = (e) => { console.log( `received ${e}` ) };
      
        worker.postMessage( ['hello', 'This is my message'] );
        console.log( "Sending hello message" );

        const status = document.querySelector( "#status" );
        status.innerText = "Loaded";

      </script>
    </body>
  </html>

But that's not the same!

I don't know how to load in the database file with the deno bundle based solution, so at this moment while it works I don't know how to solve that issue. Still the approach is cleaner.

Conclusion

Right now it looks like sql.js will do what I want. I'm surprised at how fast web assembly is.