Support for Safari

[shinshin86]

Summary

When deployed to GitHub Pages and Netlify, the application does not function properly in Safari.

Netlify has found that it can be used by setting the appropriate headers using the _headers file. The following PR reflects this in the documentation
#9

We use coi-serviceworker to enable SQLite Wasm + OPFS to work correctly on GitHub Pages. However, this mechanism does not work properly in Safari, which prevents us from using it.

Why do we use coi-serviceworker?

On GitHub Pages, you cannot use OPFS because you cannot set the Cross-Origin-Opener-Policy: same-origin and Cross-Origin-Embedder-Policy: require-corp headers.

The coi-serviceworker is used to overcome this limitation.

However, this service worker mechanism does not function correctly in Safari, resulting in Safari's incompatibility with the GitHub Pages environment.

How should we address Safari compatibility?

We recommend using a platform other than GitHub Pages (if it works correctly there).

Inform users not to use Safari when accessing the application on GitHub Pages.

Additionally, if the environment is not functioning properly, the following check returns false. We could detect this and display an appropriate message to users.

console.log('SharedArrayBuffer' in window);

Alternative Attempt (Unsuccessful): Using IndexedDB

For Safari, we attempted to use IndexedDB, but it did not work as expected. When a process related to IndexedDB is called, it remains in a pending state without returning any value.

Code

Here's the code as it was actually tryed.

/// <reference path="./index.d.ts" />
import { sqlite3Worker1Promiser } from "@sqlite.org/sqlite-wasm";
import {
  ExecuteResult,
  QueryResult,
  NeverChangeDB as INeverChangeDB,
  Migration,
} from "./types";
import { initialMigration } from "./migrations";
import { loadFromIndexedDB, syncToIndexedDB } from "./indexeddb";

export class NeverChangeDB implements INeverChangeDB {
  private dbPromise: Promise<
    (command: string, params: any) => Promise<any>
  > | null = null;
  private dbId: string | null = null;
  private migrations: Migration[] = [];
  private useIndexedDB: boolean = false;

  constructor(
    private dbName: string,
    private options: { debug?: boolean; isMigrationActive?: boolean } = {},
  ) {
    this.options.debug = options.debug ?? false;
    this.options.isMigrationActive = options.isMigrationActive ?? true;

    if (this.options.isMigrationActive) {
      this.addMigrations([initialMigration]);
    }
  }

  private log(...args: any[]): void {
    if (this.options.debug) {
      console.log(...args);
    }
  }

  async init(): Promise<void> {
    if (this.dbPromise) return;

    try {
      this.dbPromise = this.initializeDatabase();
      await this.dbPromise;

      if (this.options.isMigrationActive) {
        await this.createMigrationTable();
        await this.runMigrations();
      }
    } catch (err) {
      console.error("Failed to initialize database:", err);
      throw err;
    }
  }

  private async initializeDatabase(): Promise<
    (command: string, params: any) => Promise<any>
  > {
    this.log("Loading and initializing SQLite3 module...");

    const promiser = await this.getPromiser();
    this.log("Done initializing. Opening database...");

    const openResponse = await this.openDatabase(promiser);
    this.dbId = openResponse.result.dbId;

    if (this.useIndexedDB) {
      await syncToIndexedDB(
        this.dbId,
        this.dbName,
        () => this.dumpDatabase(),
        this.options.debug,
      );
    }

    this.log("Database initialized successfully");
    return promiser;
  }

  private async getPromiser(): Promise<
    (command: string, params: any) => Promise<any>
  > {
    return new Promise<(command: string, params: any) => Promise<any>>(
      (resolve) => {
        sqlite3Worker1Promiser({
          onready: (promiser: (command: string, params: any) => Promise<any>) =>
            resolve(promiser),
        });
      },
    );
  }

  private async openDatabase(
    promiser: (command: string, params: any) => Promise<any>,
  ): Promise<any> {
    try {
      const response = await promiser("open", {
        filename: `file:${this.dbName}.sqlite3?vfs=opfs`,
      });
      this.log("OPFS database opened:", response.result.filename);
      return response;
    } catch (opfsError) {
      console.warn(
        "OPFS is not available, falling back to in-memory database:",
        opfsError,
      );

      this.useIndexedDB = true;

      // Try to load the database from IndexedDB
      const savedData = await loadFromIndexedDB(
        this.dbName,
        this.options.debug,
      );
      if (savedData) {
        const response = await promiser("open", { filename: `:memory:` });
        await promiser("exec", {
          sql: "PRAGMA foreign_keys = OFF; BEGIN TRANSACTION;",
        });
        await promiser("exec", { sql: savedData, dbId: this.dbId });
        await promiser("exec", { sql: "COMMIT;" });
        this.log("Database loaded from IndexedDB");
        return response;
      } else {
        const response = await promiser("open", { filename: ":memory:" });
        this.log("In-memory database opened (no data in IndexedDB)");
        return response;
      }
    }
  }

  async execute(sql: string, params: any[] = []): Promise<ExecuteResult> {
    try {
      const promiser = await this.getPromiserOrThrow();
      const result = await promiser("exec", {
        sql,
        bind: params,
        dbId: this.dbId,
      });

      if (this.useIndexedDB) {
        await syncToIndexedDB(
          this.dbId,
          this.dbName,
          () => this.dumpDatabase(),
          this.options.debug,
        );
      }

      return result;
    } catch (error) {
      console.error("Error executing SQL:", error);
      throw error;
    }
  }

  async query<T = any>(
    sql: string,
    params: any[] = [],
  ): Promise<QueryResult<T>> {
    const promiser = await this.getPromiserOrThrow();
    const result = await promiser("exec", {
      sql,
      bind: params,
      rowMode: "object",
      dbId: this.dbId,
    });

    if (this.useIndexedDB) {
      await syncToIndexedDB(
        this.dbId,
        this.dbName,
        () => this.dumpDatabase(),
        this.options.debug,
      );
    }

    return result.result.resultRows || [];
  }

  async close(): Promise<void> {
    if (this.dbId) {
      const promiser = await this.getPromiserOrThrow();
      await promiser("close", { dbId: this.dbId });
      this.dbId = null;
      this.dbPromise = null;
    }
  }

  addMigrations(migrations: Migration[]): void {
    this.migrations.push(...migrations);
    this.migrations.sort((a, b) => a.version - b.version);
  }

  private async createMigrationTable(): Promise<void> {
    await this.execute(`
      CREATE TABLE IF NOT EXISTS migrations (
        version INTEGER PRIMARY KEY,
        applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  private async getCurrentVersion(): Promise<number> {
    const tables = await this.query<{ name: string }>(
      "SELECT name FROM sqlite_master WHERE type='table'",
    );
    if (!tables.some((t) => t.name === "migrations")) return 0;

    const result = await this.query<{ version: number }>(
      "SELECT MAX(version) as version FROM migrations",
    );
    return result[0]?.version || 0;
  }

  private async runMigrations(): Promise<void> {
    const currentVersion = await this.getCurrentVersion();
    const pendingMigrations = this.migrations.filter(
      (m) => m.version > currentVersion,
    );

    for (const migration of pendingMigrations) {
      this.log(`Running migration to version ${migration.version}`);
      await migration.up(this);
      await this.execute("INSERT INTO migrations (version) VALUES (?)", [
        migration.version,
      ]);
      this.log(`Migration to version ${migration.version} completed`);
    }
  }

  private async getPromiserOrThrow(): Promise<
    (command: string, params: any) => Promise<any>
  > {
    if (!this.dbPromise) {
      throw new Error("Database not initialized. Call init() first.");
    }
    return this.dbPromise;
  }

  private escapeBlob(blob: Uint8Array): string {
    return `X'${Array.from(new Uint8Array(blob), (byte) => byte.toString(16).padStart(2, "0")).join("")}'`;
  }

  async dumpDatabase(
    options: { compatibilityMode?: boolean; table?: string } = {},
  ): Promise<string> {
    const { compatibilityMode = false, table } = options;

    let dumpOutput = "";

    if (compatibilityMode) {
      dumpOutput += `PRAGMA foreign_keys = OFF;\nBEGIN TRANSACTION;\n\n`;
    }

    // Get all database objects or just the specified table
    const objectsQuery = table
      ? `SELECT type, name, sql FROM sqlite_master WHERE type='table' AND name = ?`
      : `SELECT type, name, sql FROM sqlite_master WHERE sql NOT NULL AND name != 'sqlite_sequence'`;

    const objects = await this.query<{
      type: string;
      name: string;
      sql: string | null;
    }>(objectsQuery, table ? [table] : []);

    // Dump table contents
    for (const obj of objects) {
      if (obj.type === "table") {
        dumpOutput += `${obj.sql};\n`;

        // Dump table contents
        const rows = await this.query(`SELECT * FROM ${obj.name}`);
        for (const row of rows) {
          const columns = Object.keys(row).join(", ");
          const values = Object.values(row)
            .map((value) => {
              if (value instanceof Uint8Array) {
                return this.escapeBlob(value);
              } else if (value === null) {
                return "NULL";
              } else if (typeof value === "string") {
                return `'${value.replace(/'/g, "''")}'`;
              }
              return value;
            })
            .join(", ");

          dumpOutput += `INSERT INTO ${obj.name} (${columns}) VALUES (${values});\n`;
        }
        dumpOutput += "\n";
      }
    }

    // Handle sqlite_sequence separately if no specific table is specified
    if (!table) {
      try {
        const seqRows = await this.query<{ name: string; seq: number }>(
          `SELECT * FROM sqlite_sequence`,
        );
        if (seqRows.length > 0) {
          dumpOutput += `DELETE FROM sqlite_sequence;\n`;
          for (const row of seqRows) {
            dumpOutput += `INSERT INTO sqlite_sequence VALUES('${row.name}', ${row.seq});\n`;
          }
          dumpOutput += "\n";
        }
      } catch (error) {
        this.log("sqlite_sequence table does not exist, skipping...");
      }
    }

    // Add other database objects (views, indexes, triggers) if no specific table is specified
    if (!table) {
      for (const obj of objects) {
        if (obj.type !== "table") {
          dumpOutput += `${obj.sql};\n\n`;
        }
      }
    }

    if (compatibilityMode) {
      dumpOutput += `COMMIT;\n`;
    }

    return dumpOutput;
  }

  async importDump(
    dumpContent: string,
    options: { compatibilityMode?: boolean } = {},
  ): Promise<void> {
    const { compatibilityMode = false } = options;
    const statements = dumpContent
      .split(";")
      .map((stmt) => stmt.trim())
      .filter(Boolean);

    if (!compatibilityMode) {
      await this.execute("PRAGMA foreign_keys=OFF");
      await this.execute("BEGIN TRANSACTION");
    }

    try {
      // Drop all existing tables, views, and indexes
      const existingObjects = await this.query<{ type: string; name: string }>(`
          SELECT type, name FROM sqlite_master WHERE type IN ('table', 'view', 'index') AND name != 'sqlite_sequence'
        `);
      for (const { type, name } of existingObjects) {
        await this.execute(`DROP ${type} IF EXISTS ${name}`);
      }

      // Execute all statements from the dump
      for (const statement of statements) {
        if (statement !== "COMMIT") {
          // Skip the final COMMIT statement
          await this.execute(statement);
        }
      }

      if (!compatibilityMode) {
        await this.execute("COMMIT");
        await this.execute("PRAGMA foreign_keys = ON");
      }
    } catch (error) {
      if (!compatibilityMode) {
        await this.execute("ROLLBACK");
        await this.execute("PRAGMA foreign_keys = ON");
      }

      throw error;
    }
  }
}

new file: indexeddb.ts

const INDEXED_DB_NAME = "neverchange";

export const saveToIndexedDB = async (
  dbName: string,
  data: Uint8Array,
  showLog: boolean | undefined,
): Promise<void> => {
  return new Promise((resolve, reject) => {
    const request = indexedDB.open(INDEXED_DB_NAME);

    request.onupgradeneeded = (event) => {
      const db = (event.target as IDBOpenDBRequest).result;

      if (!db.objectStoreNames.contains(dbName)) {
        db.createObjectStore(dbName);
        showLog && console.log(`Object store '${dbName}' created`);
      }
    };
    request.onsuccess = (event) => {
      const db = (event.target as IDBOpenDBRequest).result;
      const transaction = db.transaction(dbName, "readwrite");
      const store = transaction.objectStore(dbName);
      store.put(data, dbName);
      transaction.oncomplete = () => resolve();
      transaction.onerror = (err) => reject(err);
    };

    request.onerror = (err) => reject(err);
  });
};

export const loadFromIndexedDB = async (
  dbName: string,
  showLog: boolean | undefined,
): Promise<Uint8Array | null> => {
  return new Promise((resolve, reject) => {
    const request = indexedDB.open(INDEXED_DB_NAME);

    request.onupgradeneeded = (event) => {
      const db = (event.target as IDBOpenDBRequest).result;

      if (!db.objectStoreNames.contains(dbName)) {
        db.createObjectStore(dbName);
        showLog && console.log(`Object store '${dbName}' created`);
      }
    };

    request.onsuccess = (event) => {
      const db = (event.target as IDBOpenDBRequest).result;
      const transaction = db.transaction(dbName, "readonly");
      const store = transaction.objectStore(dbName);
      const getRequest = store.get(dbName);
      getRequest.onsuccess = () => {
        resolve(getRequest.result || null);
      };
      getRequest.onerror = (err) => reject(err);
    };

    request.onerror = (err) => reject(err);
  });
};

export const syncToIndexedDB = async (
  dbId: string | null,
  dbName: string,
  dumpDatabase: Function,
  showLog: boolean | undefined,
): Promise<void> => {
  if (dbId) {
    const dump = await dumpDatabase();
    const uint8Array = new TextEncoder().encode(dump);
    await saveToIndexedDB(dbName, uint8Array, showLog);

    if (showLog) {
      console.log("Database synchronized to IndexedDB");
    }
  }
};

[shinshin86]

Since we figured out how to make Safari work, we've reflected this in the documentation.
We are closing this issue.