概要
SQLite in a PWA with FileSystemAccessAPIを参考に、File_System_Access_APIを使って、SQLITEの保存・復元を行ったメモ。
メモリ上にSQLiteが作成されるので、巨大なDBの管理には工夫が必要そう。
なお、File_System_Access_API
が動くブラウザは限られており、今回はchromeで動作確認している。
環境
- chorome 103.0.5060.53
- sql.js 1.7.0
ソースコード
- sql.jsはWebAssemblyとしてSQLiteを読み込む必要がある。
src/domain/sqlite/initSQL.ts
import initSqlJs from 'sql.js'
export const initSQL = async (wasmFileDirPath: string) => {
const SQL = await initSqlJs({
locateFile: (file) => `${wasmFileDirPath}/${file}`,
})
return SQL
}
src/domain/sqlite/constants.ts
export const WASM_FILE_PATH =
'https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.7.0'
- showDirectoryPickerでデータベースファイルの保存先を選んでいる。
src/domain/sqlite/OnMomorySQLite.ts
import { WASM_FILE_PATH } from './constants'
import { initSQL } from './initSQL'
import type { Database } from 'sql.js'
const EXPORT_FILE_NAME = 'my_data.db'
export class OnMemorySQLite {
#dirHandle: FileSystemDirectoryHandle | null = null
constructor(private db: Database) {}
run(sql: string): void {
this.db.run(sql)
}
exec(sql: string) {
return this.db.exec(sql)
}
async export() {
const binaryArray: Uint8Array = this.db.export()
const dirHandle = await this.#getDirectoryPicker()
if (!dirHandle) return
try {
const fileHandle = await dirHandle.getFileHandle(EXPORT_FILE_NAME, {
create: true,
})
const writable = await fileHandle.createWritable()
await writable.write(binaryArray)
await writable.close()
} catch (e) {
// キャンセル時AbortError
console.log(e)
}
}
async load() {
const dirHandle = await this.#getDirectoryPicker()
if (!dirHandle) return
const fileHandle = await dirHandle.getFileHandle(EXPORT_FILE_NAME)
const file = await fileHandle.getFile()
const arrayBuffer = await file.arrayBuffer()
const dbAsUint8Array = new Uint8Array(arrayBuffer)
const SQL = await initSQL(WASM_FILE_PATH)
this.db = new SQL.Database(dbAsUint8Array)
}
async #getDirectoryPicker() {
if (this.#dirHandle) return this.#dirHandle
try {
const dirHandle = await window.showDirectoryPicker()
this.#dirHandle = dirHandle
return dirHandle
} catch (e: any) {
console.log(e)
}
}
}
src/domain/sqlite/index.ts
import { WASM_FILE_PATH } from './constants'
import { initSQL } from './initSQL'
import { OnMemorySQLite } from './OnMemorySQLite'
import type { Database } from 'sql.js'
let client: OnMemorySQLite | null = null
export const getOnMemoryDB = async () => {
if (client) return client
const SQL = await initSQL(WASM_FILE_PATH)
const db = new SQL.Database()
client = new OnMemorySQLite(db)
return client
}
- 動作を確認するだけの単純なページ。
src/contents/Top.tsx
import React, { useEffect, useState } from 'react'
import styled from 'styled-components'
import { getOnMemoryDB } from '@/domain/sqlite'
import type { OnMemorySQLite } from '@/domain/sqlite/OnMemorySQLite'
const Wrapper = styled.div`
position: relative;
width: 100%;
min-height: 100%;
padding-bottom: 100px;
`
const Top: React.FC = () => {
const [db, setDB] = useState<OnMemorySQLite | null>(null)
const [ret, setRet] = useState('')
useEffect(() => {
;(async () => {
setDB(await getOnMemoryDB())
})()
}, [])
return (
<Wrapper>
{!db ? (
<></>
) : (
<div>
<button
onClick={() => {
db.run(` CREATE TABLE IF NOT EXISTS people (name TEXT, age INT);`)
db.run(
`
INSERT INTO people VALUES ('Michel',3);
INSERT INTO people VALUES ('JOHNSON',4);`,
)
}}
>
seed
</button>
<button
onClick={() => {
const result = db.exec(`SELECT * from people;`)
setRet(JSON.stringify(result))
}}
>
select
</button>
<button onClick={() => db.export()}>export</button>
<button onClick={() => db.load()}>load</button>
<div>{ret}</div>
</div>
)}
</Wrapper>
)
}
export default Top