高性能ブラウザストレージ：SQLite Wasm、Origin Private File System (OPFS) とWeb Workers

.
├── public/
│   └── sqlite3.wasm
│   └── sqlite3-opfs-async-proxy.js
├── src/
│   ├── db.worker.ts
│   ├── db.ts
│   └── main.ts
├── package.json
└── tsconfig.json

// src/db.worker.ts
import * as Comlink from 'comlink';
import { SQLite3, SQLite3JS } from '@sqlite.org/sqlite-wasm';

// Declare global types for SQLite3, as it's loaded dynamically
declare global {
  interface Window {
    sqlite3Worker1: SQLite3JS;
  }
}

let db: SQLite3.DB | null = null;
let sqlite3: SQLite3JS | null = null;
const DB_NAME = 'my_app_db.sqlite';
const LOCK_NAME = 'my_app_db_lock';

/**
 * Initializes the SQLite Wasm module and opens the database on OPFS.
 * This function must be called before any database operations.
 */
async function initDb(): Promise<void> {
  if (db) {
    console.warn('Database already initialized.');
    return;
  }

  // Acquire a Web Lock to ensure single-tab access during initialization
  // and subsequent operations. This prevents multiple tabs from trying
  // to open/modify the database simultaneously, which would lead to corruption.
  await navigator.locks.request(LOCK_NAME, { mode: 'exclusive' }, async (lock) => {
    if (!lock) {
      console.error('Failed to acquire Web Lock. Another tab might be holding it.');
      throw new Error('Failed to acquire database lock.');
    }

    console.log('Web Lock acquired.');

    try {
      // Dynamically import the SQLite Wasm module.
      // The `sqlite3-opfs-async-proxy.js` acts as a bridge for OPFS access.
      // It expects `sqlite3.wasm` to be in the same directory or configured via `url`.
      sqlite3 = await window.sqlite3Worker1.sqlite3.init(
        {
          // Path to the sqlite3.wasm file relative to the worker script.
          // In a typical setup, this would be in the public directory.
          url: '/sqlite3-opfs-async-proxy.js',
          wasmUrl: '/sqlite3.wasm',
          // Use OPFS for persistent storage
          opfs: true,
        }
      );

      // Open the database file on OPFS.
      // The 'c' flag creates the database if it doesn't exist.
      db = new sqlite3.oo1.DB(DB_NAME, 'c');
      console.log(`Database ${DB_NAME} opened successfully.`);

      // Example: Create a table if it doesn't exist
      db.exec(`
        CREATE TABLE IF NOT EXISTS users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          email TEXT UNIQUE NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
      `);
      console.log('Users table ensured.');

    } catch (e) {
      console.error('Failed to initialize SQLite Wasm or open database:', e);
      // Ensure db is null if initialization fails
      db = null;
      throw e;
    } finally {
      // The lock is automatically released when the callback finishes.
      console.log('Web Lock released.');
    }
  });
}

/**
 * Executes a SQL query with optional parameters.
 * @param sql The SQL query string.
 * @param params Optional parameters for the query.
 * @returns An array of result rows.
 */
function exec(sql: string, params: SQLite3.Value[] = []): SQLite3.Row[] {
  if (!db) {
    throw new Error('Database not initialized. Call initDb() first.');
  }
  console.log(`Executing SQL: ${sql} with params:`, params);
  const rows: SQLite3.Row[] = [];
  db.exec({
    sql: sql,
    bind: params,
    rowMode: 'object', // Return rows as objects
    callback: (row: SQLite3.Row) => rows.push(row),
  });
  return rows;
}

/**
 * Executes a SQL query that does not return rows (e.g., INSERT, UPDATE, DELETE).
 * @param sql The SQL query string.
 * @param params Optional parameters for the query.
 */
function run(sql: string, params: SQLite3.Value[] = []): void {
  if (!db) {
    throw new Error('Database not initialized. Call initDb() first.');
  }
  console.log(`Running SQL: ${sql} with params:`, params);
  db.exec({
    sql: sql,
    bind: params,
  });
}

/**
 * Closes the database connection.
 */
function closeDb(): void {
  if (db) {
    db.close();
    db = null;
    console.log('Database closed.');
  }
}

// Expose the API via Comlink
Comlink.expose({
  initDb,
  exec,
  run,
  closeDb,
});

// src/db.ts
import * as Comlink from 'comlink';

// Define the type for our database worker API
export interface DbWorkerApi {
  initDb(): Promise<void>;
  exec(sql: string, params?: Comlink.Remote<any[]>): Promise<Comlink.Remote<any[]>>;
  run(sql: string, params?: Comlink.Remote<any[]>): Promise<void>;
  closeDb(): Promise<void>;
}

// Create a new Web Worker instance
const worker = new Worker(new URL('./db.worker.ts', import.meta.url), {
  type: 'module',
});

// Wrap the worker with Comlink to get a proxy object
export const dbWorker: Comlink.Remote<DbWorkerApi> = Comlink.wrap(worker);

// Optional: Handle worker errors
worker.onerror = (event) => {
  console.error('Database Worker Error:', event.message, event);
};

// Optional: Terminate worker on page unload
window.addEventListener('beforeunload', () => {
  dbWorker.closeDb().then(() => {
    worker.terminate();
    console.log('Database worker terminated.');
  });
});

// src/main.ts
import { dbWorker } from './db';

async function initializeAndUseDb() {
  try {
    console.log('Initializing database...');
    await dbWorker.initDb();
    console.log('Database initialized successfully.');

    // Insert data
    await dbWorker.run(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      ['Alice', 'alice@example.com']
    );
    await dbWorker.run(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      ['Bob', 'bob@example.com']
    );
    console.log('Users inserted.');

    // Query data
    const users = await dbWorker.exec('SELECT * FROM users');
    console.log('All users:', users);

    const specificUser = await dbWorker.exec(
      'SELECT * FROM users WHERE name = ?',
      ['Alice']
    );
    console.log('Specific user (Alice):', specificUser);

    // Update data
    await dbWorker.run(
      'UPDATE users SET email = ? WHERE name = ?',
      ['alice.updated@example.com', 'Alice']
    );
    console.log('User Alice updated.');

    const updatedUsers = await dbWorker.exec('SELECT * FROM users');
    console.log('Users after update:', updatedUsers);

    // Delete data
    await dbWorker.run('DELETE FROM users WHERE name = ?', ['Bob']);
    console.log('User Bob deleted.');

    const remainingUsers = await dbWorker.exec('SELECT * FROM users');
    console.log('Remaining users:', remainingUsers);

  } catch (error) {
    console.error('Application error:', error);
  }
}

initializeAndUseDb();