1 files changed, 45 insertions, 0 deletions
diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
new file mode 100644
index 0000000..6aa9313
--- /dev/null
+++ b/app/lib/db.server.ts
@@ -0,0 +1,45 @@
+import Database from "better-sqlite3";
+import path from "path";
+
+let db: Database.Database | null = null;
+
+export function getDb(): Database.Database {
+  if (!db) {
+    const dbPath = path.resolve("whois.db");
+    db = new Database(dbPath);
+    db.pragma("journal_mode = WAL");
+    db.pragma("foreign_keys = ON");
+    initSchema(db);
+  }
+  return db;
+}
+
+function initSchema(db: Database.Database) {
+  db.exec(`
+    CREATE TABLE IF NOT EXISTS bands (
+      id        TEXT PRIMARY KEY,
+      name      TEXT NOT NULL,
+      name_kana TEXT,
+      formed_at TEXT,
+      area      TEXT,
+      genre     TEXT,
+      url       TEXT,
+      created_at TEXT NOT NULL DEFAULT (datetime('now')),
+      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
+
+    CREATE TABLE IF NOT EXISTS members (
+      id         TEXT PRIMARY KEY,
+      band_id    TEXT NOT NULL REFERENCES bands(id),
+      name       TEXT NOT NULL,
+      name_kana  TEXT,
+      role       TEXT,
+      joined_at  TEXT,
+      left_at    TEXT,
+      created_at TEXT NOT NULL DEFAULT (datetime('now')),
+      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
+
+    CREATE INDEX IF NOT EXISTS idx_members_band_id ON members(band_id);
+  `);
+}