sqlite-worker

sqlite-worker

^{Social Media Photo by Alexander Sinn on Unsplash}

A simple, and persistent, SQLite database for Web and Workers, based on sql.js and sqlite-tag.

How to use this module

The most important thing for this module to work, is being able to reach its pre-built, and pre-optimized files, via its own dist folder.

The resolution is done automatically, whenever this modules is imported via native ESM, but due to a long standing bug that involves both Web and Service Workers across browsers, such dist folder must be specified manually, whenever this module is used directly within either a Service Worker, or a generic Web Worker.

Importing on Web pages via ESM

In any generic page, it is possible to import this module via native ESM with, or without, the help of a CDN:

<script type="module">
// no ?module needed, it's the main export in unpkg
import {SQLiteWorker} from '//unpkg.com/sqlite-worker';

// `dist` option resolved automatically via import.meta.url
SQLiteWorker({name: 'my-db'})
  .then(async ({all, get, query, raw}) => {
    const table = raw`todos`;
    await query`CREATE TABLE IF NOT EXISTS ${table} (id INTEGER PRIMARY KEY, value TEXT)`;
    const {total} = await get`SELECT COUNT(id) as total FROM ${table}`;
    if (total < 1) {
      console.log('Inserting some value');
      await query`INSERT INTO ${table} (value) VALUES (${'a'})`;
      await query`INSERT INTO ${table} (value) VALUES (${'b'})`;
      await query`INSERT INTO ${table} (value) VALUES (${'c'})`;
    }
    console.log(await all`SELECT * FROM ${table}`);
  });
</script>

If the current dist folder is pre-installed though, import {SQLiteWorker} from './js/sqlite-worker/dist/index.js'; would work too.

While above example would run sqlite-worker through a Web Worker, which is recommended, it is also possible to bootstrap this module right away in the main thread.

<script type="module">
// no ?module needed, it's the main export in unpkg
import {init} from '//unpkg.com/sqlite-worker';

// `dist` option resolved automatically via import.meta.url
init({name: 'my-db'}).then(async ({all, get, query, raw}) => {
  // ... same code as before ...
});
</script>

Beside being slightly faster, avoiding the worker postMessage dance, the main difference between SQLiteWorker and init is that init accepts an extra update option, that could be used to synchronize remotely the local database, whenever it's needed.

import {init} from 'sqlite-worker';

init({name: 'my-db', update(uInt8Array) {
  // store the latest uInt8Array somewhere
}});

The very same stored buffer could be used in the future to start from last stored update, in case the client erased its data (changed phone, IndexedDB cleared data, etc.).

This functionality could also be used in a Service Worker, but the initialization in there would be slightly different.

Importing on Service Worker

Instead of import, we must use importScripts to have cross browser compatibility, but this is not an issue, as this module provides, through its dist folder, everything needed to do so, as long as such folder is reachable:

// will add a `sqliteWorker` "global" initiator
importScripts('./dist/sw.js');

/* ⚠ IMPORTANT ⚠ */
const dist = './dist/';

sqliteWorker({dist, name: 'my-db'})
  .then(async ({all, get, query, raw, transaction}) => {
    const table = raw`todos`;
    await query`CREATE TABLE IF NOT EXISTS ${table} (id INTEGER PRIMARY KEY, value TEXT)`;
    const {total} = await get`SELECT COUNT(id) as total FROM ${table}`;
    if (total < 1) {
      console.log('Inserting some value');
      const populate = transaction();
      transaction`INSERT INTO ${table} (value) VALUES (${'a'})`;
      transaction`INSERT INTO ${table} (value) VALUES (${'b'})`;
      transaction`INSERT INTO ${table} (value) VALUES (${'c'})`;
    }
    await transaction.commit();
    console.table(await all`SELECT * FROM ${table}`);
  });

The dist option could also be used from generic pages, but usually with import.meta.url such information can be easily, automatically, retrieved by the module itself.

ℹ About Bundlers

Because of its own folder dependencies, including the WASM file, and the module, needed to bootstripe SQLite 3, importing this module via bundlers might break its actual execution if:

• all files are not also included in the bundle folder
• the bundler transform import.meta.url in a "too smart" way, breaking its native functionality
• something else some bundler might do

However, as previously mentioned, if the dist option is provided, everything should be fine, even if bundled.

Initialization Options

Both init([options]) and SQLiteWorker([options]) optionally accept a configuration/options object with the following fields:

• name: the persistent database name. By default it's the string 'sqlite-worker'
• dist: the folder, as string, containing all distribution files of this module. This is resolved automatically on pages that are not workers, but it must be provided within workers when importScripts is used instead.
• database: an initial SQLite database, as Uint8Array instance. This is used only the very first time, and it fallbacks to new Uint8Array(0).
• timeout: minimum interval, in milliseconds, between saves, to make storing, and exporting, the database, less greedy. By default it's the number 250.

Direct init Extra Options

These options work only with direct initialization, so either in the main thread or via Service Worker (once fixed in Chrome) after importing its init export.

• update: a function that receives latest version of the database, as Uint8Array, whenever some query executed an INSERT, a DELETE, or an UPDATE.

SQLiteWorker Extra Options

These options work only with SQLiteWorker initialization.

• worker: the string path where the JS worker to use is located. By default, this is the dist/worker.js file, which is a pre-optimized version of this source.
• credentials: the optional credentials string between omit, same-origin, or include, defaulting to omit, or better, undefined credentials.

After Initialization Helpers

Both init(...) and SQLiteWorker(...) resolves with the sqlite-tag API.

The API in a nutshell is:

• all: a template literal tag to retrieve all rows that match the query
• get: a template literal tag to retrieve one row that matches the query
• query: a template literal tag to simply query the database (no result returned)
• raw: a template literal tag to represent static parts of the query (not values)
• transaction: a function that returns a template literal tag to perform any statement until tag.commit() is awaited and executed.
• close: a function to force save and close the db at any time.
• create_function: a function to register custom SQLite functions. Please note when used as Worker it requires a pure function (no outer scope access) and Function evaluation. No limitations when used directly through init.

All tags, except the raw helper, are asynchronous, so that it's possible to await their result.

Extra Initialization Helpers

The sqlite-worker/tables export helps defining, or modifying, tables at runtime, without needing to write complex logic, or queries.

All it's needed, is a tables property that describe the table name and its fields, handled via sqlite-tables-handler, before returning all module helpers.

import {init, SQLiteWorker} from 'sqlite-worker/tables';

init({
  name: 'test-db',
  // the tables schema
  tables: {
    todos: {
      id: 'INTEGER PRIMARY KEY',
      value: 'TEXT'
    }
  }
}).then(async ({all, get, query, raw}) => {
  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.table(await all`SELECT * FROM todos`);
});

For Service Worker one must use the dist/sw-tables.js file instead of dist/sw.js.

importScripts('./dist/sw-tables.js');

sqliteWorker({
  dist: './dist',
  name: 'my-db',
  tables: {
    todos: {
      id: 'INTEGER PRIMARY KEY',
      value: 'TEXT'
    }
  }
})
  .then(async ({all, get, query}) => {
    // ...
  });

Compatibility

This module requires a browser compatible with WASM and native ESM import.

This module won't work in old Edge or IE.

Live Demo - please note if you read two OK after the list of expected errors (due code coverage) it means everything is fine and your browser works as expected.

CodePen - will show the table result, as JSON, in the body.