1 files changed, 50 insertions, 0 deletions
diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
new file mode 100644
index 0000000..b076ec7
--- /dev/null
+++ b/app/lib/db.server.ts
@@ -0,0 +1,50 @@
+import Database from "better-sqlite3";
+import path from "path";
+
+let db: Database.Database | null = null;
+
+export function getDb(): Database.Database {
+  if (!db) {
+    const dbPath = process.env.DB_PATH ?? path.resolve("microblog.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 posts (
+      id         TEXT PRIMARY KEY,
+      content    TEXT NOT NULL,
+      created_at TEXT NOT NULL DEFAULT (datetime('now'))
+    );
+    CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at DESC);
+  `);
+}
+
+export interface Post {
+  id: string;
+  content: string;
+  created_at: string;
+}
+
+export interface CreatePostInput {
+  id: string;
+  content: string;
+}
+
+export function listPosts(): Post[] {
+  return getDb().prepare("SELECT * FROM posts ORDER BY created_at DESC").all() as Post[];
+}
+
+export function getPostById(id: string): Post | null {
+  return getDb().prepare("SELECT * FROM posts WHERE id = ?").get(id) as Post | null;
+}
+
+export function createPost(input: CreatePostInput): Post {
+  getDb().prepare("INSERT INTO posts (id, content) VALUES (?, ?)").run(input.id, input.content);
+  return getPostById(input.id)!;
+}