1// The site's databases, as drizzle on `node:sqlite`. Models are plain modules
2// that define their tables, call `migrate`, and export query functions.
3const databases = new Map<string, Database>();
4
5export interface Database extends NodeSQLiteDatabase {
6 /** path of the database file */
7 location: string;
8 /**
9 * bring `table` in the database to `version`, which is bumped whenever its
10 * definition changes. the columns it lacks are added first, without their
11 * constraints. then `steps` run in order, from the stored version up, to
12 * fill in or rewrite rows. then the table is rebuilt with every constraint.
13 * if any of that fails, nothing changed, and the module does not load.
14 */
15 migrate(table: SQLiteTable, version: number, steps?: Record<number, (db: NodeSQLiteDatabase) => void>): void;
16 /**
17 * replace every row with those of a downloaded copy. the file stays the same,
18 * so connections that have it open read the new rows, and steps that read
19 * the old ones run again.
20 */
21 restore(copy: ReadableStream<Uint8Array>): Promise<void>;
22}
23
24/** a database in `CLOVER_DB`, opened once. rows that steps read from it are tracked */
25export function getDatabase(file: string): Database {
26 let db = databases.get(file);
27 if (db) return db;
28
29 const dir = process.env.CLOVER_DB ?? ".clover";
30 fs.mkdirSync(dir, { recursive: true });
31 const location = path.join(dir, file.includes(".") ? file : file + ".sqlite");
32 const client = incremental.trackSqlite(open(location));
33
34 db = Object.assign(drizzle({ client }), {
35 location,
36 migrate: (table: SQLiteTable, version: number, steps = {}) => migrate(location, table, version, steps),
37 restore: (copy: ReadableStream<Uint8Array>) => restore(location, copy),
38 });
39 databases.set(file, db);
40 return db;
41}
42
43// wal so the source of truth can serve reads + db snapshots while the indexer
44// writes. foreign keys are per-connection.
45function open(location: string) {
46 const client = new DatabaseSync(location);
47 client.exec("pragma journal_mode = wal; pragma foreign_keys = on;");
48 return client;
49}
50
51/**
52 * a column of names that are stored as integers
53 *
54 * const kind = sqliteNumericEnum({ file: 0, directory: 1 });
55 * sqliteTable("files", { kind: kind().notNull() });
56 */
57export function sqliteNumericEnum<const Values extends Record<string, number>>(values: Values) {
58 const names = new Map(Object.entries(values).map(([name, value]) => [value, name as keyof Values & string]));
59 return customType<{ data: keyof Values & string; driverData: number }>({
60 dataType: () => "integer",
61 toDriver: (name) => UNWRAP<number>(values[name]),
62 fromDriver: (value) => UNWRAP(names.get(value), () => `no name for ${value} in ${Object.keys(values)}`),
63 });
64}
65
66// A migration reads the schema while a module loads, which a tracked
67// connection refuses. It has a connection of its own, which the steps get.
68function migrate(
69 location: string,
70 table: SQLiteTable,
71 version: number,
72 steps: Record<number, (db: NodeSQLiteDatabase) => void>,
73) {
74 const { name, columns } = getTableConfig(table);
75 using client = open(location);
76 client.exec(
77 "create table if not exists clover_migrations (key text not null primary key, version integer not null);",
78 );
79 const row = client.prepare("select version from clover_migrations where key = ?").get(name);
80 const stored = Number(row?.version ?? 0);
81 if (stored === version) return;
82 ASSERT(stored < version, () => `${location} has ${name} at version ${stored}, newer than this code's ${version}`);
83
84 // rebuilding a table would cascade to the rows that reference it
85 client.exec("pragma foreign_keys = off; begin immediate;");
86 try {
87 const existing = client.prepare("select name from pragma_table_info(?)").all(name).map((column) => column.name);
88 const bare = (column: SQLiteColumn) => `${quote(column.name)} ${column.getSQLType()}`;
89 if (existing.length === 0) {
90 client.exec(`create table ${quote(name)} (${columns.map(bare).join(", ")});`);
91 } else {
92 for (const column of columns) {
93 if (!existing.includes(column.name)) client.exec(`alter table ${quote(name)} add column ${bare(column)};`);
94 }
95 }
96
97 const db = drizzle({ client });
98 for (let step = stored + 1; step <= version; step++) steps[step]?.(db);
99
100 // the new table takes the old one's name last, so tables that reference it by name still do
101 const kept = columns.map((column) => quote(column.name)).join(", ");
102 const [create, ...indexes] = schema(table, "clover_migrating");
103 client.exec(UNWRAP(create));
104 client.exec(`insert into clover_migrating (${kept}) select ${kept} from ${quote(name)};`);
105 client.exec(`drop table ${quote(name)};`);
106 client.exec(`alter table clover_migrating rename to ${quote(name)};`);
107 for (const index of indexes) client.exec(index);
108
109 const broken = client.prepare("pragma foreign_key_check;").all();
110 ASSERT(broken.length === 0, () => `${name} has rows that reference missing rows: ${JSON.stringify(broken)}`);
111 client.prepare("insert or replace into clover_migrations (key, version) values (?, ?)").run(name, version);
112 client.exec("commit;");
113 } catch (error) {
114 client.exec("rollback;");
115 exception.rethrowWithMetadata(error, { table: name, database: location });
116 }
117}
118
119async function restore(location: string, copy: ReadableStream<Uint8Array>) {
120 // another restore can be landing at once, from this process or another
121 const temp = `${location}.${crypto.randomUUID()}.tmp`;
122 try {
123 await fs.promises.writeFile(temp, copy);
124 using snapshot = new DatabaseSync(temp, { readOnly: true });
125 await backup(snapshot, location);
126 } finally {
127 await fs.promises.rm(temp, { force: true });
128 }
129}
130
131/** the statements that create a table as drizzle defines it, the table first and then its indexes */
132export function schema(table: SQLiteTable, as?: string): string[] {
133 const { name, columns, indexes, foreignKeys, primaryKeys, uniqueConstraints, checks } = getTableConfig(table);
134 const dialect = new SQLiteDialect({});
135 const render = (fragment: SQL) => {
136 const { sql, params } = dialect.sqlToQuery(fragment);
137 ASSERT(params.length === 0, () => `${name}: sql in a table definition can't have parameters: ${sql}`);
138 // a table's own definition names its columns without the table
139 return sql.replaceAll(`${quote(name)}.`, "");
140 };
141 const list = (columns: Array<{ name: string }>) => columns.map((column) => quote(column.name)).join(", ");
142 const literal = (column: SQLiteColumn) => {
143 if (is(column.default, SQL)) return `(${render(column.default)})`;
144 const value = column.mapToDriverValue(column.default);
145 return typeof value === "string" ? `'${value.replaceAll("'", "''")}'` : String(value);
146 };
147
148 const definitions = [
149 ...columns.map((column) =>
150 [
151 quote(column.name),
152 column.getSQLType(),
153 column.primary
154 ? "primary key" + ("autoIncrement" in column && column.autoIncrement ? " autoincrement" : "")
155 : "",
156 column.notNull ? "not null" : "",
157 column.default !== undefined ? `default ${literal(column)}` : "",
158 column.isUnique ? "unique" : "",
159 ].filter(Boolean).join(" ")
160 ),
161 ...primaryKeys.map((key) => `primary key (${list(key.columns)})`),
162 ...uniqueConstraints.map((unique) => `unique (${list(unique.columns)})`),
163 ...checks.map((check) => `check (${render(check.value)})`),
164 ...foreignKeys.map((key) => {
165 const { columns, foreignTable, foreignColumns } = key.reference();
166 const actions = [key.onDelete && `on delete ${key.onDelete}`, key.onUpdate && `on update ${key.onUpdate}`];
167 return [
168 `foreign key (${list(columns)}) references ${quote(getTableName(foreignTable))} (${list(foreignColumns)})`,
169 ...actions.filter(Boolean),
170 ].join(" ");
171 }),
172 ];
173 return [
174 `create table ${quote(as ?? name)} (${definitions.join(", ")});`,
175 ...indexes.map(({ config }) => {
176 const on = config.columns.map((column) => is(column, SQL) ? render(column) : quote(column.name)).join(", ");
177 const where = config.where ? ` where ${render(config.where)}` : "";
178 return `create ${config.unique ? "unique " : ""}index ${quote(config.name)} on ${quote(name)} (${on})${where};`;
179 }),
180 ];
181}
182
183function quote(name: string) {
184 return `"${name.replaceAll("\"", "\"\"")}"`;
185}
186
187import { ASSERT, UNWRAP } from "@clo/lib/assert";
188import * as exception from "@clo/lib/exception";
189import * as incremental from "@clo/lib/incremental";
190import { getTableName, is, SQL } from "drizzle-orm";
191import { drizzle, type NodeSQLiteDatabase } from "drizzle-orm/node-sqlite";
192import {
193 customType,
194 getTableConfig,
195 type SQLiteColumn,
196 SQLiteDialect,
197 type SQLiteTable,
198} from "drizzle-orm/sqlite-core";
199import * as fs from "node:fs";
200import * as path from "node:path";
201import { backup, DatabaseSync } from "node:sqlite";