oppi.li
1const { Database } = require("bun:sqlite");
2const db = new Database("lurker.db", {
3 strict: true,
4});
5
6function runMigration(name, migrationFn) {
7 const exists = db
8 .query("SELECT * FROM migrations WHERE name = $name")
9 .get({ name });
10
11 if (!exists) {
12 migrationFn();
13 db.query("INSERT INTO migrations (name) VALUES ($name)").run({ name });
14 }
15}
16
17// users table
18db.run(`
19 CREATE TABLE IF NOT EXISTS users (
20 id INTEGER PRIMARY KEY AUTOINCREMENT,
21 username TEXT UNIQUE,
22 password_hash TEXT
23 )
24`);
25
26// subs table
27db.run(`
28 CREATE TABLE IF NOT EXISTS subscriptions (
29 id INTEGER PRIMARY KEY AUTOINCREMENT,
30 user_id INTEGER,
31 subreddit TEXT,
32 FOREIGN KEY(user_id) REFERENCES users(id),
33 UNIQUE(user_id, subreddit)
34 )
35`);
36
37db.run(`
38 CREATE TABLE IF NOT EXISTS invites (
39 id INTEGER PRIMARY KEY AUTOINCREMENT,
40 token TEXT NOT NULL,
41 createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
42 usedAt TIMESTAMP
43 )
44`);
45
46// migrations table
47db.query(`
48 CREATE TABLE IF NOT EXISTS migrations (
49 id INTEGER PRIMARY KEY AUTOINCREMENT,
50 name TEXT UNIQUE
51 )
52`).run();
53
54runMigration("add-isAdmin-column", () => {
55 db.query(`
56 ALTER TABLE users
57 ADD COLUMN isAdmin INTEGER DEFAULT 0
58 `).run();
59
60 // first user is admin
61 db.query(`
62 UPDATE users
63 SET isAdmin = 1
64 WHERE id = (SELECT MIN(id) FROM users)
65 `).run();
66});
67
68module.exports = { db };