From d4d104cd604741ac0c308efe5050a0eec69bb389 Mon Sep 17 00:00:00 2001
From: yyamashita <yyamashita@mosquit.one>
Date: Fri, 8 May 2026 23:37:51 +0900
Subject: Initial scaffold: React Router v7 + SQLite + Tailwind CSS v4

---
 app/lib/db.server.ts | 45 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 45 insertions(+)
 create mode 100644 app/lib/db.server.ts

(limited to 'app/lib/db.server.ts')

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);
+  `);
+}
-- 
cgit v1.2.3