Creating an Offline-Ready SvelteKit Application Using SQLite (Part 1): Setting up the Database

The web has been around for some time. It has evolved from a simple document sharing platform to a dynamic environment for building complex applications. Over the years, many use cases that were once reserved for native applications have migrated to the web. A considerable number of “native” apps on my laptop nowadays are powered by web technologies, such as VS Code, the code editor I’m using to write this post.

Although this transition has come at the cost of reduced efficiency, it offers numerous benefits. For me, the most crucial aspect is accessibility. It’s a significant technological achievement that almost anyone in the world can create some HTML (with or without CSS and JS) and share it with the world. The web is no exclusive club, almost any internet-connected device can visit websites. The web can be accessed through watches, fridges, and even cars, which is truly remarkable.

Data-driven web applications are increasingly popular today. However, when there’s no internet connection, accessing your data becomes impossible, causing problems in various situations. For instance, in Germany, where mobile data is expensive and rural reception is poor, this can be particularly troublesome.

With the introduction of Progressive Web Apps (PWA) we now have features that enable us to develop web apps with a native-like feel. One such advancement includes building apps that operate seamlessly even without an active internet connection.

We will explore storing the HTML, JS, and CSS files in one of the upcoming parts of this blog series. The more challenging aspect is storing the user-data, which is the central part of a data-driven app.

The browser offers two built-in solutions for this scenario:

LocalStorage is not ideal for this use case, as it’s not designed for huge amounts of data. It has size limitations and is not that fast.

IndexedDB is the browser’s intended way to store large amounts of data. It is transactional, object-oriented, key-indexed, and allows schema definition. The API is a bit complex (at least for me as an SQL enthusiast) but there are libraries (like Dexie), that make it easier to use.

IndexedDB has some pain points, such as past Safari bugs and its lack of ACID compliance. Paulus Esterhazy summarized the downsides of IndexedDB in a great Gist.

If I hadn’t found this tweet, I would have happily used IndexedDB for client-side storage:

SQLite is my favorite database due to its simplicity, power, and speed. I don’t want to go into details, but here are some reasons why this software is so remarkable:

Read more about SQLite on their site.

As the Tweet shows, SQLite is now available in the browser thanks to WebAssembly (short WASM). This is a low-level, portable, binary code format that can be a compilation target for a wide variety of languages.

Another factor is the Origin Private File System (short OPFS) which adds a private file system for web apps. This feature can be used to store the SQLite database file.

The first step is to download the SQLite WASM library. You can find it on the SQLite site. Under the section “WebAssembly & JavaScript” you can get a zip with all the files you need.

Before integrating SQLite into your project, you need to install the following dependencies:

1yarn add -D vite-plugin-cross-origin-isolation vite-plugin-mkcert
2

The first dependency, vite-plugin-cross-origin-isolation, modifies the default Vite configuration to enable the Cross-Origin Isolation (COOP/COEP) headers. This is required to use the OPFS because it relies on SharedArrayBuffers. The second dependency, vite-plugin-mkcert, generates a local certificate, allowing you to run the local development server with HTTPS enabled.

You then need to load the Vite plugins in your config:

1import { sveltekit } from '@sveltejs/kit/vite';
2import { defineConfig } from 'vite';
3import mkcert from 'vite-plugin-mkcert';
4import crossOriginIsolation from 'vite-plugin-cross-origin-isolation';
5
6export default defineConfig({
7  server: {
8    https: true,
9    proxy: {},
10  },
11
12  plugins: [sveltekit(), mkcert(), crossOriginIsolation()],
13});
14

After installing the dependencies, copy the jswasm folder from the downloaded zip file into the lib/sqlite directory. This folder contains the WASM file and the JavaScript glue code had to run SQLite in your project.

To avoid blocking the main thread, it’s necessary to run SQLite in a Web Worker. This is a general best practice, as it allows background execution of CPU-intensive tasks.

We need to add some overhead for the WebWorker initialization and communication. As I use TypeScript, I set up some type definitions:

1export enum WorkerMessageTypes {
2  INIT_DB,
3  INIT_DB_RESPONSE,
4}
5
6export type WorkerMessage = {
7  type: WorkerMessageTypes;
8};
9

Now we can start creating our Web Worker. We just start with a simple message event listener:

1/* eslint-disable no-case-declarations */
2import { WorkerMessageTypes, type WorkerMessage } from '../types';
3
4console.log('worker loaded');
5
6// event listener for messages from the main thread
7(async function () {
8  addEventListener(
9    'message',
10    async function ({ data }: { data: WorkerMessage }) {
11      console.log('worker received message:', data.type);
12
13      let res: WorkerMessage;
14
15      switch (data.type) {
16        case WorkerMessageTypes.INIT_DB:
17          console.log('todo: init sqlite');
18
19          break;
20
21        default:
22          throw new Error(`Unknown message type: ${data.type}`);
23      }
24    }
25  );
26})();
27

We can now import this worker into our main app and send a message of the type INIT_DB:

1import { WorkerMessageTypes, type WorkerMessage } from './types';
2
3// import the worker script
4const workerImp = await import('./worker/worker.ts?worker');
5
6export default function initWorker() {
7  // initialize the worker
8  const worker = new workerImp.default();
9
10  const msg: WorkerMessage = { type: WorkerMessageTypes.INIT_DB };
11  console.log(`Sending message to worker:`, msg);
12  // we can send messages to the worker with postMessage
13  worker.postMessage(msg);
14
15  // add a message listener to the worker to get the responses
16  worker.addEventListener(
17    'message',
18    async ({ data }: { data: WorkerMessage }) => {
19      console.log('Received message from worker:', data.type);
20    }
21  );
22}
23

We still need to add the logic that actually initializes SQLite. For that, create the following file:

1/* eslint-disable @typescript-eslint/no-explicit-any */
2import type { DB } from 'sqlite3oo1';
3
4const DB_NAME = 'file:///offline-db.sqlite';
5export let db: DB;
6
7declare global {
8  function sqlite3InitModule(options: {
9    print: object;
10    printErr: object;
11  }): Promise<void>;
12}
13
14type InitDbReturn = {
15  ok: boolean;
16  error?: string;
17};
18
19export async function initDb(): Promise<InitDbReturn> {
20  return new Promise((resolve) => {
21    try {
22      self
23        .sqlite3InitModule({ print: console.log, printErr: console.error })
24        .then((sqlite3: any) => {
25          try {
26            console.log('Initialized sqlite3 module.', sqlite3);
27
28            // use the object oriented API: https://sqlite.org/wasm/doc/trunk/api-oo1.md
29            const oo = sqlite3?.oo1 as any;
30            const capi = sqlite3.capi as any;
31            const opfsFound = capi.sqlite3_vfs_find('opfs');
32            console.log(
33              'sqlite3 version',
34              capi.sqlite3_libversion(),
35              capi.sqlite3_sourceid(),
36              `OPFS? ==> ${opfsFound}`
37            );
38            if (opfsFound) {
39              db = new oo.OpfsDb(DB_NAME) as DB;
40              console.log('The OPFS is available.');
41            } else {
42              // fallback to non-persistent opfs storage
43              // you migh not want to do this and instead show a message to the user that the app is not available offline
44              db = new oo.DB(DB_NAME, 'ct') as DB;
45              console.log('The OPFS is not available.');
46            }
47            console.log('transient db =', (db as any).filename);
48
49            // optimize for speed (with safety): https://cj.rs/blog/sqlite-pragma-cheatsheet-for-performance-and-consistency/
50            db.exec([
51              'PRAGMA journal_mode = wal;',
52              'PRAGMA synchronous = normal;',
53            ]);
54
55            resolve({ ok: true });
56          } catch (e: any) {
57            console.error(`Could not initialize database: ${e.message}`);
58            resolve({ ok: false, error: e.message });
59          }
60        });
61    } catch (e: any) {
62      console.error(`Could not initialize database: ${e.message}`);
63      resolve({ ok: false, error: e.message });
64    }
65  });
66}
67

You can get my type definitions (for the sqlite3oo1 module) from GitHub. These are incomplete, but should be enough for a basic app.

We still need to call this function from our worker. So add the following code to the switch statement in src/lib/sqlite/worker.ts:

1// ...
2import { initDb } from './initDb';
3
4// ...
5
6switch (data.type) {
7  case WorkerMessageTypes.INIT_DB:
8    // load the sqlite3 module
9    await import('../jswasm/sqlite3.mjs');
10
11    // call our init db function
12    const initRes = await initDb();
13    console.log('worker initDb result:', initRes);
14    res = { type: WorkerMessageTypes.INIT_DB_RESPONSE };
15    console.log('worker sending message back to main:', res);
16    this.postMessage(res);
17    break;
18
19  default:
20    throw new Error(`Unknown message type: ${data.type}`);
21}
22

To load the Web Worker in the Svelte app, add a global +layout.svelte file, which executes the code on every page.

1<script>
2  import initWorker from '$lib/sqlite/loadWorker';
3  import { onMount } from 'svelte';
4
5  onMount(() => {
6    initWorker();
7  });
8</script>
9

Since SQLite is a file-based database, we can create a small utility to enable users to download or delete the database file, which is useful for development and debugging purposes.

I created a small component that you can find on GitHub. It is based on Thomas Steiner’s opfs-explorer browser add-on. I implemented the component in a modal dialog.

We now have set up SQLite on the client as a storage solution. In the next part of this series, we will explore how to use this database to store data for our app. Currently, the database is initialized but empty.

You can find the code for this part of the series on GitHub.