digitalcatalog/server/db.ts

import fs from "node:fs";
import path from "node:path";
import { createRequire } from "node:module";
import initSqlJs, { type Database as SqlJsDatabase } from "sql.js";
import { drizzle, type SQLJsDatabase } from "drizzle-orm/sql-js";
import { catalogsTable } from "./schema";

const require = createRequire(import.meta.url);

export type DbBundle = {
  sqlite: SqlJsDatabase;
  drizzle: SQLJsDatabase;
  persist: () => void;
};

export async function openDatabase(databasePath: string): Promise<DbBundle> {
  fs.mkdirSync(path.dirname(databasePath), { recursive: true });

  const sqlJsDist = path.dirname(require.resolve("sql.js/dist/sql-wasm.js"));
  const SQL = await initSqlJs({
    locateFile: (file) => path.join(sqlJsDist, file),
  });

  const sqlite = fs.existsSync(databasePath)
    ? new SQL.Database(fs.readFileSync(databasePath))
    : new SQL.Database();

  sqlite.run(`
    CREATE TABLE IF NOT EXISTS catalogs (
      id TEXT PRIMARY KEY,
      delete_token TEXT NOT NULL,
      title TEXT NOT NULL,
      effect TEXT NOT NULL,
      page_count INTEGER NOT NULL,
      flip_ms INTEGER NOT NULL DEFAULT 1000,
      frame TEXT NOT NULL DEFAULT 'none',
      created_at TEXT NOT NULL
    )
  `);

  const info = sqlite.exec("PRAGMA table_info(catalogs)");
  const colNames = new Set((info[0]?.values ?? []).map((row) => String(row[1])));
  if (!colNames.has("flip_ms")) {
    sqlite.run("ALTER TABLE catalogs ADD COLUMN flip_ms INTEGER NOT NULL DEFAULT 1000");
  }
  if (!colNames.has("frame")) {
    sqlite.run("ALTER TABLE catalogs ADD COLUMN frame TEXT NOT NULL DEFAULT 'none'");
    console.log("[db] added catalogs.frame column");
  }

  const persist = () => {
    fs.mkdirSync(path.dirname(databasePath), { recursive: true });
    fs.writeFileSync(databasePath, Buffer.from(sqlite.export()));
  };

  persist();

  return {
    sqlite,
    drizzle: drizzle(sqlite, { schema: { catalogsTable } }),
    persist,
  };
}