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