hearthforge

hearthforge/tests/db-schema.unit.test.ts
db-schema.unit.test.ts
⎇
Raw
1import { Database } from "bun:sqlite";
2import { describe, expect, test } from "bun:test";
3import { readFileSync } from "node:fs";
4import path from "node:path";
5
6const SCHEMA = readFileSync(
7 path.resolve(import.meta.dir, "../src/db/schema.sql"),
8 "utf-8",
9);
10
11function open() {
12 const db = new Database(":memory:");
13 db.run("PRAGMA foreign_keys=ON");
14 return db;
15}
16
17function tables(db: Database): string[] {
18 return db
19 .query<{ name: string }, []>(
20 "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name",
21 )
22 .all()
23 .map((r) => r.name);
24}
25
26function columns(db: Database, table: string): string[] {
27 return db
28 .query<{ name: string }, []>(`PRAGMA table_info(${table})`)
29 .all()
30 .map((r) => r.name)
31 .sort();
32}
33
34describe("schema.sql", () => {
35 test("creates every table the Kysely Database interface declares", () => {
36 const db = open();
37 db.run(SCHEMA);
38 expect(tables(db)).toEqual([
39 "ci_artifacts",
40 "ci_run_counters",
41 "ci_runs",
42 "ci_secrets",
43 "ci_steps",
44 "issue_comments",
45 "issue_labels",
46 "issue_reactions",
47 "issues",
48 "labels",
49 "passkeys",
50 "patch_comments",
51 "patch_labels",
52 "patch_reactions",
53 "patches",
54 "release_assets",
55 "releases",
56 "repositories",
57 "sessions",
58 "ssh_keys",
59 "users",
60 ]);
61 db.close();
62 });
63
64 test("includes the columns that used to be added by ALTER migrations", () => {
65 const db = open();
66 db.run(SCHEMA);
67 expect(columns(db, "users")).toContain("is_pending");
68 expect(columns(db, "users")).toContain("register_application");
69 expect(columns(db, "patches")).toContain("version");
70 expect(columns(db, "repositories")).toContain("allow_user_labels");
71 expect(columns(db, "ci_runs")).toContain("repo_run_id");
72 db.close();
73 });
74
75 test("creates the query indexes", () => {
76 const db = open();
77 db.run(SCHEMA);
78 const idx = db
79 .query<{ name: string }, []>(
80 "SELECT name FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'",
81 )
82 .all()
83 .map((r) => r.name);
84 expect(idx).toContain("idx_issues_repo_status");
85 expect(idx).toContain("idx_patches_repo_status");
86 expect(idx).toContain("idx_sessions_expires_at");
87 expect(idx).toContain("idx_ci_runs_repo_id");
88 db.close();
89 });
90
91 test("is idempotent — re-running changes nothing", () => {
92 const db = open();
93 db.run(SCHEMA);
94 const before = tables(db);
95 const userCols = columns(db, "users");
96 db.run(SCHEMA);
97 db.run(SCHEMA);
98 expect(tables(db)).toEqual(before);
99 expect(columns(db, "users")).toEqual(userCols);
100 db.close();
101 });
102
103 test("applies cleanly over a pre-CI database, as an upgrade would", () => {
104 const db = open();
105 // Only the oldest core tables, mimicking a database created before
106 // the CI and label features existed.
107 db.run(`CREATE TABLE users (
108 id INTEGER PRIMARY KEY AUTOINCREMENT,
109 username TEXT NOT NULL UNIQUE,
110 password_hash TEXT,
111 created_at TEXT NOT NULL,
112 avatar_version INTEGER NOT NULL DEFAULT 0
113 )`);
114 db.run(SCHEMA);
115 expect(tables(db)).toContain("ci_runs");
116 expect(tables(db)).toContain("labels");
117 // The pre-existing table keeps its old shape; index.ts ALTERs the
118 // missing columns in.
119 expect(columns(db, "users")).not.toContain("is_pending");
120 db.close();
121 });
122});
123