ChromeでRDBを使ってみたかっただけ

# はじめに
ChromeでWebSQLを使っていたがChromeのバージョンアップとともに使えなくなり，そこそこ時間が経って「さすがに別の手段を考えるか」と思った．
その解決策の１つとして「軽量RDBMSであるSQLite3をWebAssemblyにすればどうだろうか」と思い，探してみたらSQLite-WASMを見つけたので調べてみた．

闇雲に調べるのは非効率のため，目標を「SQLite-WASMを使ったサンプルを作ること」としてSQLite-WASMの範囲では

* 違和感なくSQLが発行できること
    * CREATE文やSELECT文など
* データのimport/export機能が出来るとベスト
    * 事前に用意していたデータの活用，SQLiteのデータファイル配布など

を実装最低ラインとしてサンプルコードを仕立てた．

## サンプルコード
やることリストアプリ

https://github.com/ryouka0122/vue-sqlite3-wasm

調査が終わった2024/08/04時点で利用したもの

* TypeScript: 5.5.4
* Vue: 3.4.29
* Vite: 5.3.1
* Vuetify: 3.6.13
* SQLite-WASM: 3.46.0-build2


## SQLite-WASM
SQLite-WASMとはSQLite3をWebAssembly化したもので，Javascriptから利用できるようにパッケージ化されてある．

https://sqlite.org/wasm/doc/trunk/index.md


# SQLite-WASMのインストール
SQLite-WASMのパッケージ名は `@sqlite.org/sqlite-wasm` なので，これを指定してプロジェクトに追加する．
先頭に`@`がついているためPowerShellでは誤認識されるので，ダブルクォーテーションで回避させる．

```powershell
PS D:\Projects\workspace\vue-sqlite-wasm-sample> npm install "@sqlite.org/sqlite-wasm"

added 1 package, and audited 68 packages in 3s

7 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities
PS D:\Projects\workspace\vue-sqlite-wasm-sample>
```

SQLite-WASMが正常に動かせるよう `vite.config.js` に以下の設定を追加する．

```diff_javascript
export default defineConfig({
  plugins: [
    vue(),
  ],
  resolve: {
    alias: {
      '@': fileURLToPath(new URL('./src', import.meta.url))
    }
+  },
+  server: {
+    headers: {
+      'Cross-Origin-Opener-Policy': 'same-origin',
+      'Cross-origin-Embedder-Policy': 'require-corp',
+    },
+  },
+  optimizeDeps: {
+    exclude: ['@sqlite.org/sqlite-wasm']
  }
})
```

# SQLite-WASMの初期化
Promiseベースで実行したいため `sqlite3Worker1Promiser` を利用する．
`sqlite3Worker1Promiser` は，SQLite3を動かしているWorkerとの通信をラップして非同期処理としてPromiseを返却してくれるクラスである．
`sqlite3Worker1Promiser` は`open`や`exec`などSQLiteで実行するコマンドに対応しているらしい．


```javascript
import { sqlite3Worker1Promiser } from '@sqlite.org/sqlite-wasm'

// Promiserの生成
// これを使って，様々なSQLを発行していく．
const promiser = await new Promise((resolve) => {
    const p = sqlite3Worker1Promiser({
        // debug: console.log,     // デバッグログを出力したいときはここで設定する
        onready: () => {
            resolve(p)
        }
    })
})
```


# DBを開く

ここでは，OPFS上のファイルを対象にDBを作っていく．
`sample.sqlite3` をファイル名としてDBを開きます．

```javascript
let dbId
promiser('open', {
    filename: 'file:sample.sqlite3?fps=opfs'
}).then((response) => {
  dbId = response.dbId
})
```

`await` を使って次のように書くこともできる．

```javascript
const response = await promiser('open', {
    filename: 'file:sample.sqlite3?fps=opfs'
})
const { dbId } = response
```

以降の操作は，この `dbId` を指定して実行します．


## OPFSとは

Origin Private File Systemのことで，オリジン単位で仮想のファイルシステムを利用できるAPIである．ファイルの実体を見ることができず，APIとしても「見る必要がない」としている．このファイルシステムの容量はlocalStorageやsessionStorageなどと共有しており，ストレージの削除の際にともに消えてしまう．

詳細はこちら

https://developer.mozilla.org/ja/docs/Web/API/File_System_API/Origin_private_file_system

Chromeの拡張機能を使えば，OFPS上のファイルをツリー表示・ダウンロードできる．

https://chromewebstore.google.com/detail/opfs-explorer/acndjpgkpaclldomagafnognkcgjignd?authuser=3&hl=ja


# DBを閉じる
DBを開いたら，最後は閉じる．
SPAであれば `unmounted` などで実施する処理となる．
```javascript
promiser('close', {
    dbId: dbId
}).then((response) => {
    // DB閉じた後の処理
})
```


# DMLの発行
テーブルを作成します．
`IF NOT EXISTS` をつけることで，テーブルがないときだけ生成する．
`mounted` などで「テーブルある時は～」などの面倒なことを考えずにCREATE文を発行できる．

```javascript
promiser('exec', {
    dbId: dbId,
    sql: `CREATE TABLE IF NOT EXISTS M_USER(
        USER_ID INTEGER NOT NULL,
        NAME TEXT NOT NULL
    )`
}).then((response) => {
    // 発行後の処理
})
```

## INSERT文の発行
`sql` のところにINSERT文を指定する．

```javascript
promiser('exec', {
    dbId: dbId,
    sql: `INSERT INTO M_USER VALUES (1, 'Alice')`
}).then((response) => {
    // 発行後の処理
})
```

SQLは文字列での指定となるため変数を使いたい場合は，次のように書く．
文字列結合は行わず次のbindを利用する．（SQLインジェクション対策）

```javascript
for (let i = 0; i < 10; i++) {
    await promiser('exec', {
        dbId: dbId,
        sql: `INSERT INTO M_USER (USRE_ID, NAME) VALUES (?, ?)`,
        bind: [i, `User${1 + i}`]
    })
}
```

また，プレスホルダーを使うときは以下のように書く．
bindに指定するオブジェクトのキーにコロンを付け忘れないこと
```javascript
for (let i = 0; i < 10; i++) {
    await promiser('exec', {
        dbId: dbId,
        sql: `INSERT INTO M_USER (USRE_ID, NAME) VALUES (:id, :name)`,
        bind: {
            ':id': i,
            ':name': `User${1 + i}`
        }
    })
}
```
UPDATE文やDELETE文は同じ使い方のため割愛する．

## SELECT文の発行
Sqlite-WASMのSELECT文は，1行ずつのコールバックで受け取るので，配列に格納してから使うようにする．
コールバック関数の引数(`result`)の値もちょっと癖があるため，SELECTした結果の配列が欲しいときは工夫が必要である．

```javascript
// SELECTした結果を格納する配列
const resultList = []

// SELECT文発行
await promiser('exec', {
    dbId: dbId,
    sql: `SELECT USER_ID, NAME FROM M_USER`,
    callback: (result) => {
        if (!result.row) {
            // 最後はrowがnullなので早期リターン
            return
        }
        resultList.push({
            id: result.row[0],
            name: result.row[1]
        })
    }
})

// 検索結果の利用
console.log(resultList)
```

`result.row` にはSELECT句の順番に値が格納されていて， `result.columnNames`にはその時のカラム名が格納されている．
今回であれば，以下のようなオブジェクトになっている．
```javascript
result = {
  row: [1, 'Alice'],
  columnNames: ['USER_ID', 'NAME']
}
```


# DBデータのimport/export機能
ファイルをアップロード/ダウンロードする感覚でSQLite-WASMのファイルをインポート/エクスポートする仕組みを組み立てる．


## ダウンロードの仕組み
SQLite-WASMからファイルのバイナリデータを受け取り，aタグを使ったダウンロード処理を用意する．

```javascript
// ダウンロード時のファイル名
const filename = 'sample.sqlite3'

// Promiser経由でSQLiteのデータを取得
promiser('export', {
    dbId: dbId
}).then((response) => {
    // 実行結果からBlobを生成
    const blob = new Blob([response.result.byteArray], {
        type: 'application/x-sqlite3'
    })

    // アンカータグを使ってファイルをダウンロード
    const a = document.createElement('a')
    a.href = URL.createObjectURL(blob)
    a.download = filename
    document.body.appendChild(a)

    // ダウンロード後の処理(後片付け)を設定
    a.addEventListener('click', () => {
        // 0.5秒後に設定
        setTimeout(() => {
            URL.revokeObjectURL(a.href)
            a.remove()
        }, 500)
    })

    // ファイルダウンロード処理をブラウザに任せる
    a.click()
})
```

## アップロードの仕組み
アップロードしたいファイルをファイル選択ダイアログで指定してもらい，SQLite-WASMが参照しているOPFS上のファイルを上書きして，promiserで再読み込みする処理を用意する．
この方法が正しいか分からないが，モックやプロトタイプ程度の「クライアントが動いて雰囲気が分かれば良いや」のレベルで問題ないと判断すれば十分な処理である．

```javascript
// file: File ファイル選択ダイアログで選択されたファイル

// ファイルを読み込むインスタンスを制し得
const reader = new FileReader()

// 読み込み完了後のイベントを設定
reader.adEventListener('load', async () => {
    // OPFSのルートディレクトリの取得
    const root = await navigator.storage.getDirectory()

    // SQLiteのDBファイルのハンドル取得
    const sqliteFileHandle = await root.getFileHandle(
        'sample.sqlite3', {
            create: true
        }
    )

    // ファイルの書き込み用ハンドルを取得して読み込んだデータを上書きする
    const accessHandle = await swqliteFileHandle.createWritable()
    await accessHandle.write(reader.result)
    await accessHandle.close()

    // promiserで再度DBを開きなおす
    promiser('open', {
        filename: 'file:sample.sqlite3?vfs=opfs'
    }).then((response) => {
        // 開きなおしたら変数で保持していたdbIdを更新する
        dbId = response.dbId
    })
})

// ファイルからデータ読み込み実行
reader.readAsArrayBuffer(file)
```


# あとがき
ほとんど思い付きのままに調べてサンプルを作ってみたが，プロトタイプ開発やWebAPIができるまでの一時しのぎなど容量や処理速度を考えなければ，無難に使えるものだろうと思う．

また，サンプルコードで実装しているような
1. SQLite-WASMを管理・実行するDriverクラス
1. SQL発行やデータの詰め直し・型変換など行うClientクラス
1. Vueコンポーネント

のように分けることでSQLite-WASMを意識せずにSQLの発行ができて，使い勝手の良いものになった．
強いて言えば，WebSQLのようにSQLiteの中身を開発者ツールで表示出来たら良いなと思った．

まぁ，そこまでしてJavascriptでRDBを扱いたいのか？プロトタイプ開発の段階でテーブルが決まっているのか？などの疑問は横に置いておく．

今回は興味本位での調査のため，このくらいにしておく．

# 参考資料
Promiserの引数と戻り値の詳細

https://sqlite.org/wasm/doc/trunk/api-worker1.md