From 01c0f792484f8f52606eae0e58abe528acef3086 Mon Sep 17 00:00:00 2001
From: Leo Goetz <dev@leogtz.de>
Date: Thu, 22 Jan 2026 09:10:15 +0100
Subject: feat: completed course, added some types and changed output to dist
 folder

---
 backend/src/db.ts | 67 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 67 insertions(+)
 create mode 100644 backend/src/db.ts

(limited to 'backend/src/db.ts')

diff --git a/backend/src/db.ts b/backend/src/db.ts
new file mode 100644
index 0000000..2b124f1
--- /dev/null
+++ b/backend/src/db.ts
@@ -0,0 +1,67 @@
+import DatabaseConstructor, { type Database } from "better-sqlite3";
+import EVENTS from "./data/events.json" with { type: "json" };
+import USERS from "./data/users.json" with { type: "json" };
+import RSVPS from "./data/rsvps.json" with { type: "json" };
+
+const db: Database = new DatabaseConstructor("src/sqlite.db", {
+  verbose: console.log,
+});
+
+console.log(`Initializing database: ${db.name} `);
+
+db.pragma("foreign_keys = ON");
+
+db.exec(`
+    CREATE TABLE IF NOT EXISTS users (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        username TEXT NOT NULL UNIQUE,
+        name TEXT NOT NULL,
+        email TEXT
+    );
+    `);
+db.exec(`
+        CREATE TABLE IF NOT EXISTS events (
+          id INTEGER PRIMARY KEY,
+          title TEXT NOT NULL,
+          description TEXT,
+          image_url TEXT,
+          date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
+          host_id INTEGER REFERENCES users NOT NULL
+          );
+          CREATE INDEX IF NOT EXISTS eventhosts ON events(host_id);
+          `);
+
+const upsertUser = db.prepare(`
+    INSERT INTO users VALUES (@id, @username, @name, @email)
+    ON CONFLICT(id) DO NOTHING
+    `);
+
+USERS.map((user) => upsertUser.run(user));
+
+const upsertEvent = db.prepare(`
+    INSERT INTO events VALUES (@id, @title, @description, @image_url, @date, @host_id)
+    ON CONFLICT(id) DO NOTHING
+    `);
+
+EVENTS.map((event) => {
+  upsertEvent.run(event);
+});
+
+db.exec(`
+    CREATE TABLE IF NOT EXISTS rsvps (
+        event_id INTEGER REFERENCES events NOT NULL,
+        name TEXT NOT NULL,
+        email TEXT NOT NULL,
+        UNIQUE(event_id, email) ON CONFLICT REPLACE
+    );
+    CREATE INDEX IF NOT EXISTS rsvpevents ON rsvps(event_id);
+`);
+
+const upsertRSVP = db.prepare(`
+    INSERT INTO rsvps VALUES (@event_id, @name, @email)
+`);
+RSVPS.map((rsvp) => {
+  upsertRSVP.run(rsvp);
+});
+
+export default db;
-- 
cgit v1.3