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.
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 ServiceWorkers 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.
In any generic page, it is possible to import this module via native ESM with, or without, the help of a CDN:
<scripttype="module">// no ?module needed, it's the main export in unpkgimport{SQLiteWorker}from'//unpkg.com/sqlite-worker';// `dist` option resolved automatically via import.meta.urlSQLiteWorker({name: 'my-db'}).then(async({all, get, query, raw})=>{consttable=raw`todos`;awaitquery`CREATE TABLE IF NOT EXISTS ${table} (id INTEGER PRIMARY KEY, value TEXT)`;const{total}=awaitget`SELECT COUNT(id) as total FROM ${table}`;if(total<1){console.log('Inserting some value');awaitquery`INSERT INTO ${table} (value) VALUES (${'a'})`;awaitquery`INSERT INTO ${table} (value) VALUES (${'b'})`;awaitquery`INSERT INTO ${table} (value) VALUES (${'c'})`;}console.log(awaitall`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.
<scripttype="module">// no ?module needed, it's the main export in unpkgimport{init}from'//unpkg.com/sqlite-worker';// `dist` option resolved automatically via import.meta.urlinit({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.
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" initiatorimportScripts('./dist/sw.js');/* ⚠ IMPORTANT ⚠ */constdist='./dist/';sqliteWorker({dist,name: 'my-db'}).then(async({all, get, query, raw, transaction})=>{consttable=raw`todos`;awaitquery`CREATE TABLE IF NOT EXISTS ${table} (id INTEGER PRIMARY KEY, value TEXT)`;const{total}=awaitget`SELECT COUNT(id) as total FROM ${table}`;if(total<1){console.log('Inserting some value');constpopulate=transaction();transaction`INSERT INTO ${table} (value) VALUES (${'a'})`;transaction`INSERT INTO ${table} (value) VALUES (${'b'})`;transaction`INSERT INTO ${table} (value) VALUES (${'c'})`;}awaittransaction.commit();console.table(awaitall`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.
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.urlin 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.
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
importScriptsis used instead. - database: an initial SQLite database, as
Uint8Arrayinstance. This is used only the very first time, and it fallbacks tonew 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.
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 anINSERT, aDELETE, or anUPDATE.
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, orinclude, defaulting toomit, or better, undefined credentials.
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.
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 schematables: {todos: {id: 'INTEGER PRIMARY KEY',value: 'TEXT'}}}).then(async({all, get, query, raw})=>{const{total}=awaitget`SELECT COUNT(id) as total FROM todos`;if(total<1){console.log('Inserting some value');awaitquery`INSERT INTO todos (value) VALUES (${'a'})`;awaitquery`INSERT INTO todos (value) VALUES (${'b'})`;awaitquery`INSERT INTO todos (value) VALUES (${'c'})`;}console.table(awaitall`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})=>{// ...});This module requires a browser compatible with WASM and native ESMimport.
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.