diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | database/000_init.sql | 10 | ||||
| -rw-r--r-- | database/db.js | 26 |
3 files changed, 37 insertions, 0 deletions
@@ -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; |