From 4c42c4fda1616f2fc96bb566e1e2200ea756f0d5 Mon Sep 17 00:00:00 2001
From: Julien Dessaux
Date: Fri, 28 Oct 2022 23:51:18 +0200
Subject: Added a basic database schema and migration function

---
 .gitignore            |  1 +
 database/000_init.sql | 10 ++++++++++
 database/db.js        | 26 ++++++++++++++++++++++++++
 3 files changed, 37 insertions(+)
 create mode 100644 database/000_init.sql
 create mode 100644 database/db.js

diff --git a/.gitignore b/.gitignore
index d329f4e..599dd97 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,2 +1,3 @@
+jdm.db
 node_modules
 sessions.db
diff --git a/database/000_init.sql b/database/000_init.sql
new file mode 100644
index 0000000..b9f4f23
--- /dev/null
+++ b/database/000_init.sql
@@ -0,0 +1,10 @@
+CREATE TABLE schema_version (
+	version INTEGER NOT NULL
+);
+CREATE TABLE users (
+	id INTEGER PRIMARY KEY,
+	username TEXT NOT NULL UNIQUE,
+	hash TEXT,
+	email TEXT,
+	created_at DATE DEFAULT (datetime('now'))
+);
diff --git a/database/db.js b/database/db.js
new file mode 100644
index 0000000..d849429
--- /dev/null
+++ b/database/db.js
@@ -0,0 +1,26 @@
+import fs from "fs";
+import Database from "better-sqlite3";
+
+const allMigrations = [
+	"database/000_init.sql",
+];
+
+const db = new Database("jdm.db");
+db.pragma("foreign_keys = ON");
+
+db.transaction(function migrate() {
+	let version;
+	try {
+		version = db.prepare("SELECT version FROM schema_version").all()[0].version;
+	} catch {
+		version = 0;
+	}
+	if (version === allMigrations.length) return;
+	while (version < allMigrations.length) {
+		db.exec(fs.readFileSync(allMigrations[version], "utf8"));
+		version++;
+	}
+	db.exec(`DELETE FROM schema_version; INSERT INTO schema_version (version) VALUES (${version});`);
+})();
+
+export default db;
-- 
cgit v1.2.3