Creating an Offline-Ready SvelteKit Application Using SQLite (Part 2): Messaging and Client Tables

If you're looking for a more visual and interactive way to learn about the topic of this post, check out my YouTube video on the same subject.

Optimize the Messaging System

Problems with the current message system

In the previous instalment of our blog series, we created a Web Worker that manages the SQLite database. The main thread communicates with the Worker through messages. This is an efficient and straightforward method for communication between threads.

When we send a message to the worker we wait for a response that contains the result of the operation. This is implemented with an event listener:

1const msg: WorkerMessage = { type: WorkerMessageTypes.INIT_DB };
2console.log(`Sending message to worker:`, msg);
3worker.postMessage(msg);
4
5worker.addEventListener(
6  'message',
7  async ({ data }: { data: WorkerMessage }) => {
8    console.log('Received message from worker:', data.type);
9  }
10);
11

This approach works effectively when operations run sequentially. But as we enhance the capabilities of the Worker we will introduce multiple concurrent reads, writes, and checks. Then we have the problem of not knowing which response belongs to which request.

Another thing we can do better is to offer an async API for the worker. By doing so, we can just await requests to our worker, resulting in cleaner and more comprehensible code:

1const msg: WorkerMessage = {
2  type: WorkerMessageTypes.QUERY_DATA,
3  storage: 'clients',
4};
5const data = await sendMsgToWorke(msg);
6

MessageBus implementation

I call this new system the MessageBus. It is a simple wrapper for both the postMessage and addEventListener functions, while also providing an asynchronous API for messaging the worker.

We create the function sendMsgToWorker that returns a promise. Now we have the ability to await the response from the worker. Each request is assigned a unique messageId, which we use alongside a compact callback function to resolve once the worker sends a response. To ensure easy access to this callback function, we store it in a Map using the messageId as the key.

src/lib/sqlite/messageBus.ts

1let worker: Worker;
2
3export type WorkerMsgCb = (data: WorkerMessage<unknown>) => unknown;
4// Map that stores the resolve callback for each messageId
5const cbMap = new Map<string, WorkerMsgCb>();
6
7export function sendMsgToWorker({
8  storageId,
9  type,
10  data,
11}): Promise<WorkerMessage<unknown>> {
12  // return a promise that resolves when the worker sends a message with the same messageId
13  return new Promise((resolve, reject) => {
14    // generate a unique message id for this request
15    const messageId = `${storageId}-${randomId()}`;
16
17    // create callback function and store in Map
18    const cb = (data: WorkerMessage<unknown>) => {
19      resolve(data);
20    };
21    cbMap.set(messageId, cb);
22
23    const msgData = <WorkerMessage<unknown>>{ messageId, storageId, type };
24    if (typeof data == 'object') {
25      msgData.data = data;
26    }
27
28    // send message to worker
29    worker.postMessage(msgData);
30  });
31}
32

Within the same file, we have the one and only event listener for the worker. As the worker sends a message, it must return the accompanying messageId from the request. We then retrieve the callback corresponding to that that messageId from the Map. By calling this callback and passing the worker’s data, we successfully resolve the promise initially created in the sendMsgToWorker function.

src/lib/sqlite/messageBus.ts

1export function initMsgBus(_worker: Worker) {
2  worker = _worker;
3
4  worker.addEventListener(
5    'message',
6    ({ data }: { data: WorkerMessage<unknown> }) => {
7      console.log(
8        `Message received from Worker: ${data.messageId} - ${data.type}`,
9        data.data
10      );
11
12      if (cbMap.has(data.messageId)) {
13        const cb = cbMap.get(data.messageId) as WorkerMsgCb;
14        cbMap.delete(data.messageId);
15        cb(data);
16      } else {
17        console.error(
18          `Could not find callback for message: ${data.messageId} - ${data.type}`
19        );
20      }
21    }
22  );
23}
24

You can find the full implementation of the MessageBus on GitHub.

Update the Worker

In the Worker we need to make sure that we send the messageId back to the main thread.

src/lib/sqlite/worker/worker.ts

1case WorkerMessageTypes.INIT_DB:
2    await import('../jswasm/sqlite3.mjs');
3
4    const initRes = await initDb();
5
6  // send back messageId
7    const initResult: WorkerMessage<undefined> = {
8        type: WorkerMessageTypes.INIT_DB_RESPONSE,
9        messageId: data.messageId,
10        storageId: data.storageId,
11        data: undefined
12    };
13    this.postMessage(initResult);
14
15    break;
16

Creating the Client Data Tables

We need sample data

To enable offline data accessibility, we first require a data source. For this purpose, we’ll utilize the Northwind dataset, which encompasses structures such as customers, orders, and products. Our initial focus will be on making the customers table available for offline use.

As for the backend, I am also using SQLite. While this choice isn’t dictated by the client database being SQLite, it allows us to avoid managing a seperate database server and still fully meets our requirements. Feel free to use any other data storage system that suits your preferences.

You can download the SQLite Northwind database from this GitHub repo.

Schema API

To maintain loosely coupled code, we aim to avoid having the backend send a create table statement to the client directly. Instead, we’ll transmit a generic JSON containing column information, enabling the client to generate the create table statement.

To achieve this, we’ll establish an API endpoint by creating the file src/routes/api/data/customers_v1/structure/+server.ts. Notice the storage is labeled as customers_v1, which anticipates potential future structural changes. Should that occur, we can simply create a new storage called customers_v2 and retain the old one for compatibility or until all clients have synchronized their data modifications.

src/routes/api/data/customers_v1/structure/+server.ts

1import { json, type RequestHandler } from '@sveltejs/kit';
2import type { TableStructure } from '../../types';
3
4export const GET = (() => {
5  // https://localhost:5173/api/data/customers_v1/structure
6
7  const data: TableStructure = {
8    columns: [
9      { name: 'id', type: 'string' },
10      { name: 'company', type: 'string' },
11      { name: 'contact', type: 'string' },
12      { name: 'contactTitle', type: 'string' },
13      { name: 'address', type: 'string' },
14      { name: 'city', type: 'string' },
15      { name: 'region', type: 'string' },
16      { name: 'postalCode', type: 'string' },
17      { name: 'country', type: 'string' },
18      { name: 'phone', type: 'string' },
19      { name: 'fax', type: 'string' },
20    ],
21    pkColumn: 'id',
22  };
23  return json(data);
24}) satisfies RequestHandler;
25

Checking if the SQLite client table already exists

After initializing the database we need to setup all our storages. The first step is to create the client table if it does not exist yet:

src/lib/sqlite/initStorages.ts

1const storages = ['customers_v1'];
2
3export default async function initStorages() {
4  for (const storageId of storages) {
5    const res = (await sendMsgToWorker({
6      storageId,
7      type: WorkerMessageTypes.TABLE_EXISTS,
8      expectedType: WorkerMessageTypes.TABLE_EXISTS_RESPONSE,
9      data: undefined,
10    })) as WorkerMessage<TableExistsResponseData>;
11
12    if (res.data.errorMsg) throw new Error(res.data.errorMsg);
13
14    if (res.data.tableExists) {
15      console.log(`Table ${storageId} exists. Has data?: ${res.data.hasData}`);
16    } else {
17      console.log(`Table ${storageId} does not exist.`);
18    }
19  }
20}
21

Too answer this request we need to extend the worker. In there we create two utility functions that check if a table exists and if it has data:

src/lib/sqlite/worker/util

1export function tableExists(table: string) {
2  const sql = `
3    SELECT count(*) as "cnt" 
4      FROM sqlite_master
5     WHERE type='table' 
6       AND name= $table`;
7
8  const res = db.selectObject(sql, {
9    $table: table,
10  }) as { cnt: number };
11
12  return res.cnt > 0;
13}
14
15export function tableHasData(table: string) {
16  const sql = `SELECT count(*) as "cnt" FROM ${table}`;
17
18  const res = db.selectObject(sql) as { cnt: number };
19
20  return res.cnt > 0;
21}
22

We can use these to answer the TABLE_EXISTS request:

1export function handleTableExists(
2  data: WorkerMessage<unknown>
3): TableExistsResponseData {
4  try {
5    const { storageId } = data;
6
7    const resData = <TableExistsResponseData>{};
8    resData.tableExists = tableExists(storageId);
9
10    if (resData.tableExists) {
11      resData.hasData = tableHasData(storageId);
12    } else {
13      resData.hasData = false;
14    }
15
16    return resData;
17  } catch (err) {
18    const msg = `Error checking if table exists: ${err}`;
19    console.error(msg);
20
21    return {
22      tableExists: false,
23      hasData: false,
24      errorMsg: msg,
25    };
26  }
27}
28

The request is initially handled in the switch statement in worker.ts:

src/lib/sqlite/worker/worker.ts

1case WorkerMessageTypes.TABLE_EXISTS:
2    const tableExistData = handleTableExists(data);
3
4    const tableExistsResult: WorkerMessage<TableExistsResponseData> = {
5        type: WorkerMessageTypes.TABLE_EXISTS_RESPONSE,
6        messageId: data.messageId,
7        storageId: data.storageId,
8        data: tableExistData
9    };
10    sendMsgToMain(tableExistsResult);
11    break;
12

Creating the Table

In case the table has not been created, we must establish it by obtaining the structure from the backend. This can be accomplished through a fetch call to our API, followed by requesting the Worker to create the table:

src/lib/sqlite/initStorages.ts

1async function getStructure(storage: string): Promise<TableStructure> {
2  const res = await fetch(`/api/data/${storage}/structure`);
3  const data = (await res.json()) as TableStructure;
4  return data;
5}
6
7async function createStorage(storage: string, structure: TableStructure) {
8  const res = (await sendMsgToWorker({
9    storageId: storage,
10    type: WorkerMessageTypes.CREATE_TABLE,
11    expectedType: WorkerMessageTypes.CREATE_TABLE_RESPONSE,
12    data: { structure } as CreateTableRequestData,
13  })) as WorkerMessage<TableExistsResponseData>;
14
15  if (res.data.errorMsg) throw new Error(res.data.errorMsg);
16}
17
18export default async function initStorages() {
19  for (const storageId of storages) {
20    const res = (await sendMsgToWorker({
21      storageId,
22      type: WorkerMessageTypes.TABLE_EXISTS,
23      expectedType: WorkerMessageTypes.TABLE_EXISTS_RESPONSE,
24      data: undefined,
25    })) as WorkerMessage<TableExistsResponseData>;
26
27    if (res.data.errorMsg) throw new Error(res.data.errorMsg);
28
29    if (res.data.tableExists) {
30      console.log(`Table ${storageId} exists. Has data: ${res.data.hasData}`);
31    } else {
32      // get structure from API
33      const structure = await getStructure(storageId);
34      console.log(`Table ${storageId} does not exist. Creating...`, structure);
35      await createStorage(storageId, structure);
36    }
37  }
38}
39

To convert the structure into table source SQL, we can employ the following utility function. It utilizes the storageId as the table name, the pkColumn as the primary key and the columns to generate the column definitions:

src/lib/sqlite/worker/util

1import type {
2  ColType,
3  TableStructure,
4} from '../../../../routes/api/data/types';
5
6function getDataType(coltype: ColType): string {
7  switch (coltype) {
8    case 'string':
9      return 'text';
10    case 'number':
11      return 'real';
12    default:
13      throw new Error(`Unknown column type: ${coltype}`);
14  }
15}
16
17export default function genTabSrc(
18  storageId: string,
19  structure: TableStructure
20) {
21  let statement = `Create Table ${storageId} (`;
22
23  const atomics: string[] = [];
24
25  for (const col of structure.columns) {
26    atomics.push(`${col.name} ${getDataType(col.type)}`);
27  }
28
29  atomics.push(`PRIMARY KEY (${structure.pkColumn})`);
30
31  statement += `   ${atomics.join(', ')}    ) strict;`;
32
33  return statement;
34}
35

We can just use that and call db.exec:

src/lib/sqlite/worker/storageHandlers.ts

1export function handleCreateTable(
2  msg: WorkerMessage<CreateTableRequestData>
3): CreateTableResponseData {
4  try {
5    const src = genTabSrc(msg.storageId, msg.data.structure);
6    console.log('Creating table:', src);
7
8    db.exec(src);
9
10    return {};
11  } catch (err) {
12    const msg = `Error creating table: ${err}`;
13    console.error(msg);
14
15    return {
16      errorMsg: msg,
17    };
18  }
19}
20

Conclusion

We now we have set up a table in the database and can insert data into it in the next part.

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