sql.ts80 lines · main

1	import type { ColumnDef } from './columns.js';
2	import type { SchemaDef } from './schema.js';
3	import type { TableDef } from './table.js';
4
5	/**
6	* Render a `SchemaDef` to Postgres DDL. Output is idempotent — every
7	* statement uses `IF NOT EXISTS` so the CLI can safely run it against a
8	* fresh project schema.
9	*
10	* This function does NOT compute diffs against a deployed schema; that
11	* lives in `diff.ts` and consumes the output of `parseInformationSchema`
12	* (reserved for a later milestone).
13	*/
14	export function generateSql(def: SchemaDef): string {
15	const out: string[] = [];
16	for (const [name, tableDef] of Object.entries(def.tables)) {
17	out.push(renderCreateTable(name, tableDef));
18	out.push(...renderIndexes(name, tableDef));
19	}
20	return out.join('\n\n') + '\n';
21	}
22
23	export function renderCreateTable(name: string, table: TableDef): string {
24	const pkColumns = Object.entries(table.columns)
25	.filter(([, def]) => def.primaryKey)
26	.map(([colName]) => colName);
27	// 2+ PK columns → composite key rendered as a table-level constraint.
28	// Single PK stays inline on the column for the simpler/expected SQL
29	// shape (every existing migration uses the inline form).
30	const composite = pkColumns.length > 1;
31
32	const lines: string[] = [];
33	for (const [colName, colDef] of Object.entries(table.columns)) {
34	lines.push(` "${colName}" ${renderColumn(colDef, composite)}`);
35	}
36
37	if (composite) {
38	const colList = pkColumns.map((c) => `"${c}"`).join(', ');
39	lines.push(` PRIMARY KEY (${colList})`);
40	}
41
42	const fkLines: string[] = [];
43	for (const [colName, colDef] of Object.entries(table.columns)) {
44	if (!colDef.references) continue;
45	const ref = colDef.references;
46	const onDelete = ref.onDelete ? ` ON DELETE ${ref.onDelete.toUpperCase()}` : '';
47	fkLines.push(
48	` FOREIGN KEY ("${colName}") REFERENCES "${ref.table}" ("${ref.column}")${onDelete}`,
49	);
50	}
51	lines.push(...fkLines);
52
53	return `CREATE TABLE IF NOT EXISTS "${name}" (\n${lines.join(',\n')}\n);`;
54	}
55
56	function renderColumn(def: ColumnDef, inCompositeKey: boolean): string {
57	const parts: string[] = [def.sqlType];
58	// Inline PRIMARY KEY only when the table has exactly one PK column;
59	// composite keys are emitted as a single constraint at the table level.
60	if (def.primaryKey && !inCompositeKey) parts.push('PRIMARY KEY');
61	// PK columns are implicitly NOT NULL in Postgres regardless of how
62	// the key is declared — keep the existing "skip explicit NOT NULL on
63	// PK" behaviour so composite keys produce the same minimal output.
64	if (!def.nullable && !def.primaryKey) parts.push('NOT NULL');
65	if (def.unique && !def.primaryKey) parts.push('UNIQUE');
66	if (def.default !== undefined) parts.push(`DEFAULT ${def.default}`);
67	return parts.join(' ');
68	}
69
70	function renderIndexes(tableName: string, table: TableDef): string[] {
71	const out: string[] = [];
72	for (const idx of table.indexes) {
73	const unique = idx.unique ? 'UNIQUE ' : '';
74	const colList = idx.columns.map((c) => `"${c}"`).join(', ');
75	const nameSuffix = idx.columns.join('_');
76	const indexName = `${tableName}_${nameSuffix}_idx`;
77	out.push(`CREATE ${unique}INDEX IF NOT EXISTS "${indexName}" ON "${tableName}" (${colList});`);
78	}
79	return out;
80	}