A persistent SQLite for the Web

Photo by Alexander Sinn on Unsplash

WebSQL left the Web a while ago, but WebAssembly brought it back, as nothing on the Web is as simple, yet powerful, as this wonderful Open Source project.

This post summarizes my idea behind sqlite-worker, a module that brings sql.js in Web pages and/or workers, and persists through the only option we have these days: IndexedDB.

A quick recap

“IndexedDB is the best non-blocking database a developer could desire”, said nobody ever, and the reason is simple:

• the API is everything but straight forward (low-level is the excuse)
• querying it makes little sense, as it cannot do much, compared to SQL
• but hey, it can store blobs (not everywhere) and buffers (bingo!)

The community, myself included, has never been too happy about the choice W3C made, and while reasons for dropping WebSQL made sense, nothing ever filled that gap … until recent days, where C or C++ programs could target WASM, and WASM could run everywhere, most importantly, on the Web.

Connecting the dots

So, there is this sql.js project, which brings SQLite to the Web, and there is IndexedDB, which can store the buffer exported by sql.js.

What are we missing then? Why are not all on board?

• legacy browsers won’t work (but personally I wouldn’t care for PWAs, as legacy browsers don’t run PWAs neither)
• sql.js is not persistent: it runs SQLite on RAM, it stores the DB on RAM too, but then it vanishes on each refresh. Amazing for a forever-running browser/runtime that never crashes, but pointless as persistent DB
• (IMHO) SQL is fun, but dealing with “all the things” is less fun, so that projects such as sqlit-tag becomes the strawberry on top to keep it simple

Web or Workers

import {init, SQLiteWorker} from 'sqlite-worker';
// direct bootstrap
// init({name: 'my-db'});
// Web Worker bootstrap
SQLiteWorker({name: 'my-db'}).then(async ({all, get, query}) => {
  await query`CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, value TEXT)`;
  const {total} = await get`SELECT COUNT(id) as total FROM todos`;
  if (total < 1) {
    console.log('Inserting some value');
    await query`INSERT INTO todos (value) VALUES (${'a'})`;
    await query`INSERT INTO todos (value) VALUES (${'b'})`;
    await query`INSERT INTO todos (value) VALUES (${'c'})`;
  }
  console.log(await all`SELECT * FROM todos`);
});

With a few optional bootstrap properties, this project offers an extremely simple interface to deal with the database and perform these operations:

• get a single row, instead of all of them
• retrieve all rows, if any
• query the db to INSERT, UPDATE, or DELETE entries

All these helpers are template literal tags that result into real bindings, so that all values passed to the query are always safely, and natively, escaped.

// the following operation
for (let i = 0; i < 3; i++)
  query`INSERT INTO table VALUES (${i})`;
// will be the equivalent of this one
db.run('INSERT INTO table VALUES (?)', [0]);
db.run('INSERT INTO table VALUES (?)', [1]);
db.run('INSERT INTO table VALUES (?)', [2]);

Keep It Simple

The sql.js project runs on RAM, is very fast by default, and if it fails during a valid operation it’s likely due the device being out of RAM, so that while transactions are a must know and use, when it comes to databases, a fully virtual, memory based, database that works on a single client and uses such client’s resources, won’t likely blow, unless it’s too late (browser crashed, and so on).

Since these cases should be more than rare/edge, and a crashed browser tab won’t ever restore a transaction, I think we can start “small” with this module, and see how many things we could do … here a few:

• MATCH AGAINST queries, for FTS5 tables, to be able to search text content without needing exact input match
• indexed, ordered, filtered, searches, without having JS on our way to manually do what SQL was born to do
• scale up to the allowed storage space, which is something WebSQL never offered, or better, it was limited by default to 5MB, scaling up to maximum 50MB on user permission and no more …

Here there is simplicity, performance, and freedom to use more space, respecting our users first, of course, but doing nothing different than what an IndexedDB could do already, in terms of storage, except, it’s much better.

How does it persist?

Well, as mentioned, INSERT, UPDATE, and DELETE queries, performed via the query tag, schedule an update timer that tries to not bee too greedy on the IndexedDB side, but it eventually update the unique entry the storage uses, with the exported uint8 buffer.

Through the update callback, it is also possible to store remotely the latest version of the DB, so that users might erase its phone and have it back from where they’ve left, and everything else should be explained in the README.

P.S. this module should work on Web Extensions too, but I might provide a database hook in the future to make it portable for literally any kind of storage, not just IndexedDB, but hey … I need you help me testing, and confirming, everything is fine ♥