Blame · Line-by-line history
schema-single-source.test.ts
Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.
| 45ac593 | 1 | /** |
| 2 | * One Drizzle definition per physical table. | |
| 3 | * | |
| 4 | * `organizations` was declared TWICE — in schema.ts (slug + created_by_id | |
| 5 | * NOT NULL, matching migration 0003 and therefore the real database) and in | |
| 6 | * schema-extensions.ts (display_name/website/location/is_verified, neither | |
| 7 | * NOT NULL column). Both mapped to the same physical table with irreconcilable | |
| 8 | * shapes. | |
| 9 | * | |
| 10 | * drizzle.config.ts includes only schema.ts and deploys run `db:migrate`, | |
| 11 | * never `db:push`, so the schema-extensions version was never migrated: it | |
| 12 | * described a table that has never existed. routes/orgs.tsx imported it, so | |
| 13 | * POST /orgs/new wrote columns Postgres does not have while omitting two that | |
| 14 | * are NOT NULL. Organization creation could never have succeeded, and because | |
| 15 | * each file typechecked against its own definition, tsc could not see it. | |
| 16 | * | |
| 17 | * The duplicate-table check below is the one that matters: it fails the build | |
| 18 | * for ANY table declared in more than one place, so this class cannot recur. | |
| 19 | */ | |
| 20 | ||
| 21 | import { describe, expect, it } from "bun:test"; | |
| 22 | import { readFileSync, readdirSync } from "fs"; | |
| 23 | import { join } from "path"; | |
| 24 | ||
| 25 | const DB_DIR = "src/db"; | |
| 26 | ||
| 27 | /** | |
| 28 | * Strip comments before scanning. Without this the check matches its own | |
| 29 | * documentation: these files explain the duplicate-table bug in prose that | |
| 30 | * necessarily contains `pgTable("organizations"`, and a naive scan then | |
| 31 | * reports the very thing the comment says was fixed. | |
| 32 | */ | |
| 33 | function code(src: string): string { | |
| 34 | return src | |
| 35 | .replace(/\/\*[\s\S]*?\*\//g, "") | |
| 36 | .replace(/^\s*\/\/.*$/gm, ""); | |
| 37 | } | |
| 38 | ||
| 39 | function schemaFiles(): string[] { | |
| 40 | return readdirSync(DB_DIR) | |
| 41 | .filter((f) => f.endsWith(".ts") && f.includes("schema")) | |
| 42 | .map((f) => join(DB_DIR, f)); | |
| 43 | } | |
| 44 | ||
| 45 | describe("no physical table is declared twice", () => { | |
| 46 | it("every pgTable name is unique across all schema files", () => { | |
| 47 | const seen = new Map<string, string[]>(); | |
| 48 | for (const f of schemaFiles()) { | |
| 49 | const src = code(readFileSync(f, "utf8")); | |
| 50 | for (const m of src.matchAll(/pgTable\(\s*["'`]([^"'`]+)["'`]/g)) { | |
| 51 | const table = m[1]; | |
| 52 | if (!seen.has(table)) seen.set(table, []); | |
| 53 | seen.get(table)!.push(f.replace(/\\/g, "/")); | |
| 54 | } | |
| 55 | } | |
| 56 | const dupes = [...seen.entries()] | |
| 57 | .filter(([, files]) => files.length > 1) | |
| 58 | .map(([t, files]) => `${t} declared in ${files.join(" AND ")}`); | |
| 59 | expect(dupes).toEqual([]); | |
| 60 | }); | |
| 61 | ||
| 62 | it("schema-extensions re-exports the org/team tables rather than redeclaring them", () => { | |
| 63 | const src = code(readFileSync("src/db/schema-extensions.ts", "utf8")); | |
| 64 | // Matched loosely on purpose: asserting an exact import string breaks the | |
| 65 | // moment the list wraps across lines, which says nothing about the bug. | |
| 66 | expect(src).toMatch(/from\s+["']\.\/schema["']/); | |
| 67 | for (const t of ["organizations", "teams", "team_members", "team_repos", "org_members", "branch_protection"]) { | |
| 68 | expect(src).not.toMatch(new RegExp(`pgTable\\(\\s*["'\`]${t}["'\`]`)); | |
| 69 | } | |
| 70 | }); | |
| 71 | }); | |
| 72 | ||
| 73 | describe("the organizations table matches its migration", () => { | |
| 74 | const schema = readFileSync("src/db/schema.ts", "utf8"); | |
| 75 | const table = schema.slice( | |
| 76 | schema.indexOf('export const organizations = pgTable("organizations"'), | |
| 77 | schema.indexOf('export const orgMembers') | |
| 78 | ); | |
| 79 | ||
| 80 | it("keeps the NOT NULL columns migration 0003 created", () => { | |
| 81 | expect(table).toContain('slug: text("slug").notNull()'); | |
| 82 | expect(table).toContain('createdById: uuid("created_by_id")'); | |
| 83 | expect(table).toContain(".notNull()"); | |
| 84 | }); | |
| 85 | ||
| 86 | it("carries the columns migration 0113 added", () => { | |
| 87 | // Added rather than dropped, so the create form's display name and | |
| 88 | // website are not silently discarded. | |
| 89 | for (const col of ["display_name", "website", "location", "is_verified"]) { | |
| 90 | expect(table).toContain(col); | |
| 91 | } | |
| 92 | }); | |
| 93 | ||
| 94 | it("has a migration that adds those columns", () => { | |
| 95 | const sql = readFileSync("drizzle/0113_orgs_reconcile.sql", "utf8"); | |
| 96 | for (const col of ["display_name", "website", "location", "is_verified"]) { | |
| 97 | expect(sql).toContain(`ADD COLUMN IF NOT EXISTS "${col}"`); | |
| 98 | } | |
| 99 | // Additive and idempotent — safe against a populated table and a re-run. | |
| 100 | expect(sql).not.toMatch(/DROP COLUMN|DROP TABLE/i); | |
| 101 | }); | |
| 102 | }); | |
| 103 | ||
| 104 | describe("POST /orgs/new supplies every required column", () => { | |
| 105 | const src = readFileSync("src/routes/orgs.tsx", "utf8"); | |
| 106 | const insert = src.slice( | |
| 107 | src.indexOf(".insert(organizations)"), | |
| 108 | src.indexOf(".insert(organizations)") + 700 | |
| 109 | ); | |
| 110 | ||
| 111 | it("provides slug and createdById", () => { | |
| 112 | expect(insert).toContain("slug:"); | |
| 113 | expect(insert).toContain("createdById:"); | |
| 114 | }); | |
| 115 | ||
| 116 | it("derives the slug from the validated name", () => { | |
| 117 | // `name` is already constrained to /^[a-zA-Z0-9._-]+$/ and collision- | |
| 118 | // checked against both users and orgs, so lowercasing it is a safe slug. | |
| 119 | expect(insert).toContain("slug: name.toLowerCase()"); | |
| 120 | }); | |
| 121 | }); | |
| 122 | ||
| 123 | describe("teams feature matches the database", () => { | |
| 124 | const schema = readFileSync("src/db/schema.ts", "utf8"); | |
| 125 | ||
| 126 | it("teams carries the permission column the UI renders", () => { | |
| 127 | // orgs.tsx renders `team.permission` and the create form collects it, but | |
| 128 | // the column existed only in the rival definition and was never migrated. | |
| 129 | // Anchored with a regex, not a literal newline — these files are CRLF. | |
| 130 | const t = schema.slice(schema.search(/pgTable\(\s*"teams"/), schema.indexOf("export const teamMembers")); | |
| 131 | expect(t).toContain('permission: text("permission")'); | |
| 132 | }); | |
| 133 | ||
| 134 | it("team_repos is declared in schema.ts so it gets a migration", () => { | |
| 135 | // It previously lived ONLY in schema-extensions, which drizzle.config.ts | |
| 136 | // does not include — so the table never existed, while orgs.tsx selected | |
| 137 | // and joined it to render a team's repo list. | |
| 138 | expect(schema).toMatch(/pgTable\(\s*"team_repos"/); | |
| 139 | }); | |
| 140 | ||
| 141 | it("migration 0114 creates team_repos and adds teams.permission", () => { | |
| 142 | const sql = readFileSync("drizzle/0114_teams_reconcile.sql", "utf8"); | |
| 143 | expect(sql).toContain('CREATE TABLE IF NOT EXISTS "team_repos"'); | |
| 144 | expect(sql).toContain('ALTER TABLE "teams" ADD COLUMN IF NOT EXISTS "permission"'); | |
| 145 | expect(sql).not.toMatch(/DROP COLUMN|DROP TABLE/i); | |
| 146 | }); | |
| 147 | ||
| 148 | it("team creation supplies the NOT NULL slug", () => { | |
| 149 | const orgs = readFileSync("src/routes/orgs.tsx", "utf8"); | |
| 150 | const insert = orgs.slice(orgs.indexOf(".insert(teams)"), orgs.indexOf(".insert(teams)") + 400); | |
| 151 | expect(insert).toContain("slug,"); | |
| 152 | }); | |
| 153 | }); | |
| fc2c3bf | 154 | |
| 155 | // ───────────────────────────────────────────────────────────────────────── | |
| 156 | // Schema / migration drift | |
| 157 | // ───────────────────────────────────────────────────────────────────────── | |
| 158 | ||
| 159 | /** | |
| 160 | * Every column Drizzle declares must exist in the migrated database. | |
| 161 | * | |
| 162 | * This is the class that produced five silent runtime failures: org creation, | |
| 163 | * team creation, notify.ts inserts, the notification read-state split, and | |
| 164 | * `team_repos` — a table declared in code that had no migration at all, while | |
| 165 | * routes/orgs.tsx selected and joined it. | |
| 166 | * | |
| 167 | * The database is built solely by applying drizzle/*.sql in filename order | |
| 168 | * (drizzle.config.ts names only schema.ts; deploys run db:migrate, never | |
| 169 | * db:push), so the migrations ARE the schema of record. Replaying their DDL | |
| 170 | * gives the real shape to compare against. | |
| 171 | * | |
| 172 | * Only one direction is enforced: a column in code but not in the database is | |
| 173 | * a runtime error waiting to happen. The reverse — a column in the database | |
| 174 | * that Drizzle no longer declares — is harmless and common after a cleanup, so | |
| 175 | * it is reported by the scratch analyzer but not failed here. | |
| 176 | */ | |
| 177 | function declaredTables(): Map<string, Set<string>> { | |
| 178 | const out = new Map<string, Set<string>>(); | |
| 179 | for (const f of schemaFiles()) { | |
| 180 | const src = code(readFileSync(f, "utf8")); | |
| 181 | // Split on pgTable( boundaries. Brace-matching is not safe here: a nested | |
| 182 | // `{ onDelete: "cascade" }` or a template literal makes a depth counter | |
| 183 | // run away and swallow the following tables' columns. | |
| 184 | for (const part of src.split(/pgTable\(/).slice(1)) { | |
| 185 | const name = part.match(/^\s*["'`]([a-z0-9_]+)["'`]/); | |
| 186 | if (!name) continue; | |
| 187 | const end = part.search(/\n\);/); | |
| 188 | const body = end > -1 ? part.slice(0, end) : part; | |
| 189 | if (!out.has(name[1])) out.set(name[1], new Set()); | |
| 190 | for (const c of body.matchAll(/:\s*\w+\(\s*["'`]([a-z0-9_]+)["'`]/g)) { | |
| 191 | out.get(name[1])!.add(c[1]); | |
| 192 | } | |
| 193 | } | |
| 194 | } | |
| 195 | return out; | |
| 196 | } | |
| 197 | ||
| 198 | function migratedTables(): Map<string, Set<string>> { | |
| 199 | const dir = "drizzle"; | |
| 200 | const out = new Map<string, Set<string>>(); | |
| 201 | for (const f of readdirSync(dir).filter((x) => x.endsWith(".sql")).sort()) { | |
| 202 | const sql = readFileSync(join(dir, f), "utf8").replace(/--[^\n]*/g, ""); | |
| 203 | ||
| 204 | for (const m of sql.matchAll( | |
| 205 | /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?"?([a-z0-9_]+)"?\s*\(([\s\S]*?)\n\s*\);/gi | |
| 206 | )) { | |
| 207 | if (!out.has(m[1])) out.set(m[1], new Set()); | |
| 208 | const set = out.get(m[1])!; | |
| 209 | for (const line of m[2].split("\n")) { | |
| 210 | const t = line.trim(); | |
| 211 | if (!t || /^(CONSTRAINT|PRIMARY\s+KEY|FOREIGN\s+KEY|UNIQUE|CHECK)\b/i.test(t)) continue; | |
| 212 | const c = t.match(/^"?([a-z0-9_]+)"?\s+/); | |
| 213 | if (c) set.add(c[1]); | |
| 214 | } | |
| 215 | } | |
| 216 | ||
| 217 | for (const m of sql.matchAll(/ALTER\s+TABLE\s+"?([a-z0-9_]+)"?\s+([\s\S]*?);/gi)) { | |
| 218 | if (!out.has(m[1])) out.set(m[1], new Set()); | |
| 219 | const set = out.get(m[1])!; | |
| 220 | for (const a of m[2].matchAll(/ADD\s+COLUMN\s+(?:IF\s+NOT\s+EXISTS\s+)?"?([a-z0-9_]+)"?/gi)) set.add(a[1]); | |
| 221 | for (const d of m[2].matchAll(/DROP\s+COLUMN\s+(?:IF\s+EXISTS\s+)?"?([a-z0-9_]+)"?/gi)) set.delete(d[1]); | |
| 222 | for (const r of m[2].matchAll(/RENAME\s+COLUMN\s+"?([a-z0-9_]+)"?\s+TO\s+"?([a-z0-9_]+)"?/gi)) { | |
| 223 | set.delete(r[1]); set.add(r[2]); | |
| 224 | } | |
| 225 | } | |
| 226 | } | |
| 227 | return out; | |
| 228 | } | |
| 229 | ||
| 230 | describe("schema matches the migrations", () => { | |
| 231 | const declared = declaredTables(); | |
| 232 | const migrated = migratedTables(); | |
| 233 | ||
| 234 | it("parses a plausible number of tables (guards against the scan breaking)", () => { | |
| 235 | // A broken parser would make the assertions below vacuously pass. | |
| 236 | expect(declared.size).toBeGreaterThan(150); | |
| 237 | expect(migrated.size).toBeGreaterThan(150); | |
| 238 | }); | |
| 239 | ||
| 240 | it("every declared table has a migration that creates it", () => { | |
| 241 | // `team_repos` failed this: declared, joined by orgs.tsx, never created. | |
| 242 | const missing = [...declared.keys()].filter((t) => !migrated.has(t)).sort(); | |
| 243 | expect(missing).toEqual([]); | |
| 244 | }); | |
| 245 | ||
| 246 | it("every declared column exists in the migrated schema", () => { | |
| 247 | const gaps: string[] = []; | |
| 248 | for (const [table, cols] of [...declared].sort()) { | |
| 249 | const have = migrated.get(table); | |
| 250 | if (!have) continue; // covered by the previous assertion | |
| 251 | const missing = [...cols].filter((c) => !have.has(c)).sort(); | |
| 252 | if (missing.length) gaps.push(`${table}: ${missing.join(", ")}`); | |
| 253 | } | |
| 254 | expect(gaps).toEqual([]); | |
| 255 | }); | |
| 256 | }); |