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