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

  • 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

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}) => {
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

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?

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 ♥

Web, Mobile, IoT, and all JS things since 00's. Formerly JS engineer at @nokia, @facebook, @twitter.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store