import { z, type ZodType } from "zod"; import type { Datasource } from "./types"; import { datasourceSystemProperties } from "./types"; import { nanoid } from "nanoid"; /** * SQL DEFAULT expression for the `$id` primary key. * * Mirrors the TS-side generator in `datasourceSystemProperties` * (`customAlphabet("abcdefghijklmnopqrstuvwxyz0123456789", 16)`): 16 characters * drawn from a subset of that alphabet, so ids produced by SQLite and ids * produced by Zod defaults are interchangeable and indistinguishable in shape. * * `hex(randomblob(8))` yields exactly 16 hex chars; `lower()` maps them into * [0-9a-f] ⊂ [a-z0-9]. The full 36-char alphabet is not reachable in pure SQL — * SQLite forbids subqueries (and thus recursive CTEs) inside DEFAULT * expressions, and custom functions are unavailable on D1. 64 bits of entropy * per id is ample for per-site tables. */ const ID_DEFAULT_SQL = "DEFAULT (lower(hex(randomblob(8))))"; /** * Takes a datasource and converts it to an equivalent SQL table creation statement(s). * Take into account: * - $.id: the name of the table * - $.schema: the schema of the datasource (a JSON schema of type "object", each property becomes a column) * - $.indexes: the indexes defined on the datasource * - $.foreignKeys: the foreign keys defined on the datasource * * Note: $.schema is defined as TAny in the type, but in practice it will always be a TObject */ export function datasourceToSQL(datasource: Datasource): string { const statements: string[] = []; const tableName = escapeIdentifier(datasource.id); // 1. Drop existing tables first so schema changes always take effect. // CREATE TABLE IF NOT EXISTS alone leaves stale columns from previous runs, // which breaks subsequent INSERTs when the schema has changed. Row data is // always reseeded from the API or static examples, so dropping is safe. // FTS5 virtual tables must be dropped before their content table. for (const index of datasource.indexes) { if (index.fulltext) { statements.push(`DROP TABLE IF EXISTS ${escapeIdentifier(`${datasource.id}_${index.name}`)}`); } } statements.push(`DROP TABLE IF EXISTS ${tableName}`); // 2. Build CREATE TABLE statement const columns: string[] = []; // Add system columns from datasourceSystemProperties const systemShape: Record = datasourceSystemProperties.shape; for (const [fieldName, fieldSchema] of Object.entries(systemShape)) { const column = describeColumn(fieldSchema); const sqlType = sqlTypeForColumnKind(column.kind); let constraint = ""; // Apply special constraints for specific system columns if (fieldName === "$id") { constraint = ` NOT NULL PRIMARY KEY ${ID_DEFAULT_SQL}`; } else if (fieldName === "$lastModificationDate" || fieldName === "$creationDate") { constraint = " NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now') || 'Z')"; } columns.push(`${escapeIdentifier(fieldName)} ${sqlType}${constraint}`); } // Add user-defined columns from schema.shape (excluding system properties) const userSchema = datasource.schema; const systemFieldNames = Object.keys(systemShape); const userShape = userSchema.shape; for (const fieldName of Object.keys(userShape)) { // Skip system properties as they're already added if (systemFieldNames.includes(fieldName)) continue; const fieldSchema = userShape[fieldName as keyof typeof userShape]; if (!fieldSchema) continue; const column = describeColumn(fieldSchema as ZodType); const sqlType = sqlTypeForColumnKind(column.kind); const notNull = column.isOptional || column.isNullable ? "" : " NOT NULL"; const defaultClause = column.defaultValue !== undefined ? ` DEFAULT ${formatDefaultValue(column.defaultValue, column.kind)}` : ""; columns.push(`${escapeIdentifier(fieldName)} ${sqlType}${notNull}${defaultClause}`); } // Add foreign key constraints if (datasource.foreignKeys) { for (const fk of datasource.foreignKeys) { const constraint = `FOREIGN KEY (${escapeIdentifier(fk.field)}) REFERENCES ${escapeIdentifier(fk.foreignDatasourceId)}(${escapeIdentifier(fk.foreignField)})`; columns.push(constraint); } } statements.push(`CREATE TABLE IF NOT EXISTS ${tableName} (\n ${columns.join(",\n ")}\n)`); // 2. Build CREATE INDEX statements for (const index of datasource.indexes) { if (index.fulltext) { // FTS5 virtual table for fulltext indexes const fields = index.fields.map((f) => escapeIdentifier(f)).join(", "); const ftsTableName = escapeIdentifier(`${datasource.id}_${index.name}`); statements.push( `CREATE VIRTUAL TABLE IF NOT EXISTS ${ftsTableName} USING fts5(${fields}, content=${tableName})`, ); } else { // Regular or unique index const unique = index.unique ? "UNIQUE " : ""; const indexName = escapeIdentifier(index.name); const fields = index.fields.map((f) => escapeIdentifier(f)).join(", "); statements.push(`CREATE ${unique}INDEX IF NOT EXISTS ${indexName} ON ${tableName} (${fields})`); } } return `PRAGMA defer_foreign_keys = ON; ${statements.join(";\n\n")};`; } /** * The SQL migration removing a datasource: its full-text index tables, then its table. Generated * when a site's datasource file is deleted (pre-commit hook, `upstart-delete-datasource`). */ export function datasourceDropSQL(datasource: Datasource): string { const statements = (datasource.indexes ?? []) .filter((index) => index.fulltext) .map((index) => `DROP TABLE IF EXISTS ${escapeIdentifier(`${datasource.id}_${index.name}`)}`); statements.push(`DROP TABLE IF EXISTS ${escapeIdentifier(datasource.id)}`); return `PRAGMA defer_foreign_keys = ON; ${statements.join(";\n")};`; } /** * Generates a SQL migration from one datasource version to another. * * Simple changes (handled via ALTER TABLE): * - ADD new columns (optional, nullable, or NOT NULL with a default value) * - DROP columns (no indexes/FKs on them) * - ADD new indexes (regular, unique, fulltext) * - DROP indexes * * Complex changes (handled via table recreation — create new table, copy data, drop old, rename): * - CHANGE column types (with smart CAST expressions) * - MAKE column required / optional (optional→required fills NULLs with a fallback value) * - ADD new NOT NULL column without a default (fills existing rows with a type-appropriate fallback) * - REMOVE foreign keys * * @param from - The current datasource schema * @param to - The target datasource schema * @returns SQL migration statements, or empty string if no changes * @throws Error if the table IDs don't match */ export function generateMigration(from: Datasource, to: Datasource) { if (from.id !== to.id) { throw new Error(`Cannot migrate between different tables: "${from.id}" → "${to.id}"`); } const tableName = escapeIdentifier(from.id); const columnChanges = analyzeColumnChanges(from, to); const fromFKs = from.foreignKeys ?? []; const toFKs = to.foreignKeys ?? []; const removedFKs = fromFKs.filter( (fk) => !toFKs.some( (tofk) => tofk.field === fk.field && tofk.foreignDatasourceId === fk.foreignDatasourceId && tofk.foreignField === fk.foreignField, ), ); const addedFKs = toFKs.filter( (fk) => !fromFKs.some( (ffk) => ffk.field === fk.field && ffk.foreignDatasourceId === fk.foreignDatasourceId && ffk.foreignField === fk.foreignField, ), ); const needsRecreation = columnChanges.some( (c) => c.kind === "type_change" || c.kind === "make_required" || c.kind === "make_optional" || (c.kind === "add" && !c.toColumn.isOptional && !c.toColumn.isNullable && c.toColumn.defaultValue === undefined), ) || removedFKs.length > 0 || addedFKs.length > 0; if (needsRecreation) { return generateTableRecreationMigration(from, to, columnChanges); } // ── Simple path ────────────────────────────────────────────────────────── const statements: string[] = []; const droppedIndexNames = new Set(); // Column changes (add / remove) for (const change of columnChanges) { if (change.kind === "add") { const col = change.toColumn; const notNull = col.isOptional || col.isNullable ? "" : " NOT NULL"; const defaultClause = col.defaultValue !== undefined ? ` DEFAULT ${formatDefaultValue(col.defaultValue, col.kind)}` : ""; statements.push( `ALTER TABLE ${tableName} ADD COLUMN ${escapeIdentifier(change.name)} ${sqlTypeForColumnKind(col.kind)}${notNull}${defaultClause}`, ); } else if (change.kind === "remove") { // Drop indexes that reference this column before dropping the column for (const idx of from.indexes) { if (idx.fields.includes(change.name)) { if (idx.fulltext) { const ftsName = escapeIdentifier(`${from.id}_${idx.name}`); statements.push(`DROP TABLE IF EXISTS ${ftsName}`); } else { statements.push(`DROP INDEX IF EXISTS ${escapeIdentifier(idx.name)}`); } droppedIndexNames.add(idx.name); } } statements.push(`ALTER TABLE ${tableName} DROP COLUMN ${escapeIdentifier(change.name)}`); } // unchanged / make_optional / make_required: handled via recreation — never reached here } // Index changes const fromIndexes = from.indexes; const toIndexes = to.indexes; for (const fromIndex of fromIndexes) { if (droppedIndexNames.has(fromIndex.name)) continue; // already dropped above const exists = toIndexes.find((idx) => idx.name === fromIndex.name); if (!exists) { if (fromIndex.fulltext) { statements.push(`DROP TABLE ${escapeIdentifier(`${from.id}_${fromIndex.name}`)}`); } else { statements.push(`DROP INDEX ${escapeIdentifier(fromIndex.name)}`); } } } for (const toIndex of toIndexes) { const fromIndex = fromIndexes.find((idx) => idx.name === toIndex.name); if ( !fromIndex || JSON.stringify(fromIndex.fields) !== JSON.stringify(toIndex.fields) || fromIndex.unique !== toIndex.unique || fromIndex.fulltext !== toIndex.fulltext ) { if (fromIndex && !droppedIndexNames.has(fromIndex.name)) { if (fromIndex.fulltext) { statements.push(`DROP TABLE ${escapeIdentifier(`${from.id}_${fromIndex.name}`)}`); } else { statements.push(`DROP INDEX ${escapeIdentifier(fromIndex.name)}`); } } if (toIndex.fulltext) { const fields = toIndex.fields.map((f) => escapeIdentifier(f)).join(", "); const ftsTableName = escapeIdentifier(`${to.id}_${toIndex.name}`); statements.push(`CREATE VIRTUAL TABLE ${ftsTableName} USING fts5(${fields}, content=${tableName})`); } else { const unique = toIndex.unique ? "UNIQUE " : ""; const fields = toIndex.fields.map((f) => escapeIdentifier(f)).join(", "); statements.push( `CREATE ${unique}INDEX ${escapeIdentifier(toIndex.name)} ON ${tableName} (${fields})`, ); } } } return statements.length > 0 ? `PRAGMA defer_foreign_keys = ON;\n${statements.join(";\n\n")};` : null; } function generateTableRecreationMigration( from: Datasource, to: Datasource, columnChanges: ColumnChange[], ): string { const origTableName = escapeIdentifier(from.id); const migId = `${from.id}__mig_${nanoid(8)}`; const migTableName = escapeIdentifier(migId); const statements: string[] = []; // 1. Drop all existing indexes on the original table (recreated after rename) for (const idx of from.indexes) { if (idx.fulltext) { statements.push(`DROP TABLE IF EXISTS ${escapeIdentifier(`${from.id}_${idx.name}`)}`); } else { statements.push(`DROP INDEX IF EXISTS ${escapeIdentifier(idx.name)}`); } } // 2. Create new table with temp name using the target schema statements.push(buildTableDDL(to, migId)); // 3. Build INSERT INTO ... SELECT with smart CASTs const systemFieldNames = Object.keys(datasourceSystemProperties.shape); const toShape = to.schema.shape; const insertCols: string[] = []; const selectExprs: string[] = []; // System columns: always copied as-is for (const sysCol of systemFieldNames) { insertCols.push(escapeIdentifier(sysCol)); selectExprs.push(escapeIdentifier(sysCol)); } // User columns present in the target schema for (const fieldName of Object.keys(toShape)) { if (systemFieldNames.includes(fieldName)) continue; const toFieldSchema = toShape[fieldName as keyof typeof toShape] as ZodType | undefined; if (!toFieldSchema) continue; const toColumn = describeColumn(toFieldSchema); const change = columnChanges.find((c) => c.name === fieldName); if (!change || change.kind === "add") { // New column: use the schema's default if available, fallback for required, omit if optional if (toColumn.defaultValue !== undefined) { insertCols.push(escapeIdentifier(fieldName)); selectExprs.push(formatDefaultValue(toColumn.defaultValue, toColumn.kind)); } else if (!toColumn.isOptional && !toColumn.isNullable) { // Required new column with no default: use type-appropriate fallback insertCols.push(escapeIdentifier(fieldName)); selectExprs.push(fallbackForKind(toColumn.kind)); } // Optional new column with no default: omit (will be NULL) } else if (change.kind === "type_change") { insertCols.push(escapeIdentifier(fieldName)); const cast = castExpression(fieldName, change.fromColumn.kind, change.toColumn.kind); // If the target column is NOT NULL, COALESCE the cast result with a fallback to avoid NULL violations. // Prefer the declared defaultValue over the generic type fallback. if (!toColumn.isOptional && !toColumn.isNullable) { const fallback = toColumn.defaultValue !== undefined ? formatDefaultValue(toColumn.defaultValue, toColumn.kind) : fallbackForKind(toColumn.kind); selectExprs.push(`COALESCE(${cast}, ${fallback})`); } else { selectExprs.push(cast); } } else if (change.kind === "make_required") { // Was optional/nullable, now required: fill NULLs with default or fallback const fallback = toColumn.defaultValue !== undefined ? formatDefaultValue(toColumn.defaultValue, toColumn.kind) : fallbackForKind(toColumn.kind); insertCols.push(escapeIdentifier(fieldName)); selectExprs.push(`COALESCE(${escapeIdentifier(fieldName)}, ${fallback})`); } else { // unchanged, make_optional: copy as-is insertCols.push(escapeIdentifier(fieldName)); selectExprs.push(escapeIdentifier(fieldName)); } // "remove": field not in toShape, so it's never iterated here } statements.push( `INSERT INTO ${migTableName} (${insertCols.join(", ")})\nSELECT ${selectExprs.join(", ")}\nFROM ${origTableName}`, ); // 4. Drop original table statements.push(`DROP TABLE ${origTableName}`); // 5. Rename migration table to original name statements.push(`ALTER TABLE ${migTableName} RENAME TO ${escapeIdentifier(from.id)}`); // 6. Recreate all indexes from the target schema for (const idx of to.indexes) { if (idx.fulltext) { const fields = idx.fields.map((f) => escapeIdentifier(f)).join(", "); const ftsTableName = escapeIdentifier(`${to.id}_${idx.name}`); statements.push( `CREATE VIRTUAL TABLE IF NOT EXISTS ${ftsTableName} USING fts5(${fields}, content=${escapeIdentifier(to.id)})`, ); // Populate the FTS index from the newly-renamed content table statements.push(`INSERT INTO ${ftsTableName}(${ftsTableName}) VALUES ('rebuild')`); } else { const unique = idx.unique ? "UNIQUE " : ""; const fields = idx.fields.map((f) => escapeIdentifier(f)).join(", "); statements.push( `CREATE ${unique}INDEX IF NOT EXISTS ${escapeIdentifier(idx.name)} ON ${escapeIdentifier(to.id)} (${fields})`, ); } } return `PRAGMA defer_foreign_keys = ON;\n${statements.join(";\n\n")};`; } /** * Generates SQL INSERT statements from the examples in a datasource schema. * * @param datasource - The datasource containing examples in $.schema via .meta({ examples: [...] }) * @returns SQL INSERT statements for all examples, or empty string if no examples */ export function datasourceExamplestoSQL(datasource: Datasource): string | null { // In Zod v4, examples are stored via .meta() method const meta = datasource.schema.meta?.() || {}; const examples = (meta.fixtures || []) as Record[]; if (examples.length === 0) { return null; } const tableName = escapeIdentifier(datasource.id); const statements: string[] = []; for (const example of examples) { const columns: string[] = []; const values: string[] = []; // Process all fields in the example (both system properties and user-defined) for (const [fieldName, value] of Object.entries(example)) { // check if column exists in case the fixture is out of date if (Object.keys(datasource.schema.shape).includes(fieldName) === false) { console.warn( `Example fixture contains field "${fieldName}" which does not exist in the datasource schema. Skipping this field.`, example, ); continue; } columns.push(escapeIdentifier(fieldName)); values.push(formatValue(value)); } if (columns.includes("$id") === false) { columns.push(escapeIdentifier("$id")); values.push(formatValue(nanoid(6))); } if (columns.includes("$lastModificationDate") === false) { columns.push(escapeIdentifier("$lastModificationDate")); values.push(formatValue(new Date().toISOString())); } if (columns.includes("$creationDate") === false) { columns.push(escapeIdentifier("$creationDate")); values.push(formatValue(new Date().toISOString())); } statements.push(`INSERT INTO ${tableName} (${columns.join(", ")}) VALUES (${values.join(", ")})`); } return `${statements.join(";\n\n")};`; } /** * Generates SQL INSERT statements from real datasource items fetched from the API. * Each item's `data` JSONB field is spread into the table columns. */ export function datasourceItemsToSQL( datasource: Datasource, items: Array<{ id: string; data: Record; createdAt?: string | null; updatedAt?: string | null; }>, ): string | null { if (items.length === 0) return null; const tableName = escapeIdentifier(datasource.id); const schemaFields = Object.keys(datasource.schema.shape); const statements: string[] = []; for (const item of items) { console.log( `Generating SQL for datasource item ${item.id} of datasource ${datasource.id}: ${item.data ? JSON.stringify(item.data) : "no data"}`, ); const columns: string[] = []; const values: string[] = []; // User-defined fields from item.data for (const [fieldName, value] of Object.entries(item.data ?? {})) { if (!schemaFields.includes(fieldName)) continue; columns.push(escapeIdentifier(fieldName)); values.push(formatValue(value)); } if (!columns.includes(escapeIdentifier("$id"))) { columns.push(escapeIdentifier("$id")); values.push(formatValue(item.id ?? nanoid(6))); } if (!columns.includes(escapeIdentifier("$creationDate"))) { columns.push(escapeIdentifier("$creationDate")); values.push(formatValue(item.createdAt ?? new Date().toISOString())); } if (!columns.includes(escapeIdentifier("$lastModificationDate"))) { columns.push(escapeIdentifier("$lastModificationDate")); values.push(formatValue(item.updatedAt ?? new Date().toISOString())); } statements.push(`INSERT INTO ${tableName} (${columns.join(", ")}) VALUES (${values.join(", ")})`); } return statements.length > 0 ? `${statements.join(";\n")};` : null; } /** * Reorders the given array of sqlite statements to ensure that tables are created * before they are referenced by foreign keys. * @param statements array of sqlite statements string like "CREATE TABLE IF NOT EXISTS ..." * @todo Implement proper reordering of statements to handle foreign key dependencies * @returns */ export function reorderStatements(statements: string[]): string[] { type StatementNode = { table: string; statement: string; dependencies: Set; dependents: Set; index: number; }; const nodes = new Map(); const passthrough: { statement: string; index: number }[] = []; statements.forEach((statement, index) => { const tableMatch = /CREATE TABLE IF NOT EXISTS\s+"([^"]+)"/i.exec(statement); if (!tableMatch) { passthrough.push({ statement, index }); return; } const table = tableMatch[1]; const dependencies = new Set(); const referencesRegex = /REFERENCES\s+"([^"]+)"/gi; let match: RegExpExecArray | null; while ((match = referencesRegex.exec(statement)) !== null) { const referencedTable = match[1]; if (referencedTable !== table) { dependencies.add(referencedTable); } } nodes.set(table, { table, statement, dependencies, dependents: new Set(), index, }); }); // Connect dependents and drop dependencies we cannot satisfy with the provided statements. for (const node of nodes.values()) { for (const dependency of Array.from(node.dependencies)) { const dependencyNode = nodes.get(dependency); if (!dependencyNode) { node.dependencies.delete(dependency); } else { dependencyNode.dependents.add(node.table); } } } const ready = Array.from(nodes.values()) .filter((node) => node.dependencies.size === 0) .sort((a, b) => a.index - b.index); const ordered: string[] = []; const visited = new Set(); while (ready.length > 0) { const current = ready.shift(); if (!current || visited.has(current.table)) continue; visited.add(current.table); ordered.push(current.statement); for (const dependentTable of current.dependents) { const dependent = nodes.get(dependentTable); if (!dependent) continue; dependent.dependencies.delete(current.table); if (dependent.dependencies.size === 0) { ready.push(dependent); ready.sort((a, b) => a.index - b.index); } } } if (visited.size !== nodes.size) { const remaining = Array.from(nodes.values()) .filter((node) => !visited.has(node.table)) .sort((a, b) => a.index - b.index) .map((node) => node.statement); ordered.push(...remaining); } if (passthrough.length === 0) { return ordered; } // Merge pass-through statements back in their original relative order. const passthroughByIndex = new Map(); for (const { statement, index } of passthrough) { passthroughByIndex.set(index, statement); } const merged: string[] = []; let orderedIdx = 0; for (let i = 0; i < statements.length; i++) { if (passthroughByIndex.has(i)) { merged.push(passthroughByIndex.get(i)!); } else { merged.push(ordered[orderedIdx++] ?? statements[i]); } } // Ensure any remaining ordered statements (if passthrough consumed some slots) are appended. while (orderedIdx < ordered.length) { merged.push(ordered[orderedIdx++]); } return merged; } function escapeIdentifier(identifier: string): string { // Escape with double quotes for SQLite return `"${identifier.replace(/"/g, '""')}"`; } type ColumnKind = "string" | "datetime" | "boolean" | "integer" | "bigint" | "number" | "json"; type ColumnDescriptor = { kind: ColumnKind; isOptional: boolean; isNullable: boolean; defaultValue?: unknown; }; type ColumnChange = | { kind: "add"; name: string; toColumn: ColumnDescriptor } | { kind: "remove"; name: string; fromColumn: ColumnDescriptor } | { kind: "type_change"; name: string; fromColumn: ColumnDescriptor; toColumn: ColumnDescriptor } | { kind: "make_required"; name: string; fromColumn: ColumnDescriptor; toColumn: ColumnDescriptor } | { kind: "make_optional"; name: string; fromColumn: ColumnDescriptor; toColumn: ColumnDescriptor } | { kind: "unchanged"; name: string; fromColumn: ColumnDescriptor; toColumn: ColumnDescriptor }; type LiteralPrimitive = string | number | bigint | boolean | null | undefined; type ColumnSchemaDef = { type?: string; innerType?: ZodType; defaultValue?: unknown; catchValue?: unknown; getter?: () => ZodType; in?: ZodType; schema?: ZodType; }; function describeColumn(schema: ZodType): ColumnDescriptor { const { baseSchema, isOptional, isNullable, defaultValue } = unwrapColumnSchema(schema); return { kind: inferColumnKind(baseSchema), isOptional, isNullable, defaultValue, }; } function unwrapColumnSchema(schema: ZodType): { baseSchema: ZodType; isOptional: boolean; isNullable: boolean; defaultValue?: unknown; } { let current: ZodType = schema; let isOptional = false; let isNullable = false; let defaultValue: unknown; const resolveDefault = (value: unknown): unknown => typeof value === "function" ? (value as () => unknown)() : value; const advance = (next: ZodType | undefined): boolean => { if (!next) { return false; } current = next; return true; }; while (true) { const def = current?.def as ColumnSchemaDef | undefined; const defType = typeof def?.type === "string" ? def.type : undefined; if (!def || !defType) { break; } switch (defType) { case "optional": isOptional = true; if (!advance(def.innerType as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "nullable": isNullable = true; if (!advance(def.innerType as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "default": isOptional = true; defaultValue = resolveDefault(def.defaultValue); if (!advance(def.innerType as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "prefault": isOptional = true; defaultValue = resolveDefault(def.defaultValue); if (!advance(def.innerType as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "catch": isOptional = true; defaultValue = resolveDefault(def.catchValue); if (!advance(def.innerType as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "readonly": if (!advance(def.innerType as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "lazy": if (!advance(def.getter?.())) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "pipe": if (!advance(def.in as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; case "effects": if (!advance(def.schema as ZodType | undefined)) { return { baseSchema: current, isOptional, isNullable, defaultValue }; } continue; default: return { baseSchema: current, isOptional, isNullable, defaultValue }; } } return { baseSchema: current, isOptional, isNullable, defaultValue }; } function inferColumnKind(schema: ZodType): ColumnKind { if (schema instanceof z.ZodISODateTime) { return "datetime"; } if (schema instanceof z.ZodString) { return isStringDatetime(schema) ? "datetime" : "string"; } if (schema instanceof z.ZodDate) { return "datetime"; } if (schema instanceof z.ZodNumber) { return isIntegerNumber(schema) ? "integer" : "number"; } if (schema instanceof z.ZodBigInt) { return "bigint"; } if (schema instanceof z.ZodBoolean) { return "boolean"; } if (schema instanceof z.ZodLiteral) { return inferLiteralKind(schema); } if (schema instanceof z.ZodEnum) { return "string"; } if ( schema instanceof z.ZodArray || schema instanceof z.ZodObject || schema instanceof z.ZodRecord || schema instanceof z.ZodMap || schema instanceof z.ZodSet || schema instanceof z.ZodTuple || schema instanceof z.ZodUnion || schema instanceof z.ZodDiscriminatedUnion || schema instanceof z.ZodIntersection || schema instanceof z.ZodPromise || schema instanceof z.ZodAny || schema instanceof z.ZodUnknown || schema instanceof z.ZodNull ) { return "json"; } return "json"; } function inferLiteralKind(schema: z.ZodLiteral): ColumnKind { const value = schema.def.values?.[0]; if (value === undefined) { return "json"; } if (typeof value === "string") { return "string"; } if (typeof value === "number") { return Number.isInteger(value) ? "integer" : "number"; } if (typeof value === "boolean") { return "boolean"; } if (typeof value === "bigint") { return "bigint"; } return "json"; } function isIntegerNumber(schema: z.ZodNumber): boolean { const checks = schema.def.checks ?? []; return checks.some((check) => { if (typeof check !== "object" || check === null || !("isInt" in check)) { return false; } return (check as { isInt?: boolean }).isInt === true; }); } function isStringDatetime(schema: z.ZodString): boolean { return schema.def.checks?.some((check) => hasTemporalFormat(check)) ?? false; } function hasTemporalFormat(check: unknown): boolean { if (!check || typeof check !== "object") { return false; } const maybeFormat = (check as { format?: string }).format; if (maybeFormat === "datetime" || maybeFormat === "date") { return true; } const nestedFormat = (check as { def?: { format?: string } }).def?.format; return nestedFormat === "datetime" || nestedFormat === "date"; } function sqlTypeForColumnKind(kind: ColumnKind): string { switch (kind) { case "string": return "TEXT"; case "datetime": return "DATETIME"; case "boolean": return "BOOLEAN"; case "integer": case "bigint": return "INTEGER"; case "number": return "REAL"; case "json": default: return "TEXT"; } } function formatDefaultValue(value: unknown, kind: ColumnKind): string { if (value === null) { return "NULL"; } switch (kind) { case "string": return `'${String(value).replace(/'/g, "''")}'`; case "datetime": return `'${value instanceof Date ? value.toISOString() : String(value)}'`; case "boolean": return value ? "TRUE" : "FALSE"; case "integer": return `${Number(value)}`; case "bigint": return typeof value === "bigint" ? value.toString() : `${BigInt(value as number)}`; case "number": return `${Number(value)}`; case "json": default: return `'${JSON.stringify(value).replace(/'/g, "''")}'`; } } function formatValue(value: unknown): string { // Handle null/undefined if (value === null || value === undefined) { return "NULL"; } // Handle boolean if (typeof value === "boolean") { return value ? "1" : "0"; } // Handle number if (typeof value === "number") { return String(value); } // Handle string if (typeof value === "string") { return `'${value.replace(/'/g, "''")}'`; } // Handle arrays and objects - serialize as JSON return `'${JSON.stringify(value).replace(/'/g, "''")}'`; } function analyzeColumnChanges(from: Datasource, to: Datasource): ColumnChange[] { const systemFieldNames = Object.keys(datasourceSystemProperties.shape); const fromProps = from.schema.shape; const toProps = to.schema.shape; const changes: ColumnChange[] = []; // Columns removed (in from but not in to) for (const fieldName of Object.keys(fromProps)) { if (systemFieldNames.includes(fieldName)) continue; if (!(fieldName in toProps)) { const fromColumn = describeColumn(fromProps[fieldName as keyof typeof fromProps] as ZodType); changes.push({ kind: "remove", name: fieldName, fromColumn }); } } // Columns added or modified for (const fieldName of Object.keys(toProps)) { if (systemFieldNames.includes(fieldName)) continue; const toFieldSchema = toProps[fieldName as keyof typeof toProps] as ZodType | undefined; if (!toFieldSchema) continue; const toColumn = describeColumn(toFieldSchema); const fromFieldSchema = fromProps[fieldName as keyof typeof fromProps] as ZodType | undefined; if (!fromFieldSchema) { changes.push({ kind: "add", name: fieldName, toColumn }); continue; } const fromColumn = describeColumn(fromFieldSchema); if (fromColumn.kind !== toColumn.kind) { changes.push({ kind: "type_change", name: fieldName, fromColumn, toColumn }); continue; } const wasOptional = fromColumn.isOptional || fromColumn.isNullable; const isOptional = toColumn.isOptional || toColumn.isNullable; if (wasOptional && !isOptional) { changes.push({ kind: "make_required", name: fieldName, fromColumn, toColumn }); } else if (!wasOptional && isOptional) { changes.push({ kind: "make_optional", name: fieldName, fromColumn, toColumn }); } else { changes.push({ kind: "unchanged", name: fieldName, fromColumn, toColumn }); } } return changes; } function castExpression(colName: string, fromKind: ColumnKind, toKind: ColumnKind): string { const col = escapeIdentifier(colName); if (fromKind === toKind) return col; if (fromKind === "string" && toKind === "integer") { return `CASE WHEN (${col} GLOB '[0-9]*' OR ${col} GLOB '-[0-9]*') THEN CAST(${col} AS INTEGER) ELSE NULL END`; } if (fromKind === "string" && toKind === "bigint") { return `CASE WHEN (${col} GLOB '[0-9]*' OR ${col} GLOB '-[0-9]*') THEN CAST(${col} AS INTEGER) ELSE NULL END`; } if (fromKind === "string" && toKind === "number") { return `CASE WHEN (${col} GLOB '[0-9]*' OR ${col} GLOB '-[0-9]*') THEN CAST(${col} AS REAL) ELSE NULL END`; } if (fromKind === "string" && toKind === "boolean") { return `CASE WHEN lower(${col}) IN ('true','1','yes') THEN 1 WHEN lower(${col}) IN ('false','0','no') THEN 0 ELSE NULL END`; } if (fromKind === "string" && toKind === "datetime") { return col; // Both stored as TEXT in SQLite } if ((fromKind === "integer" || fromKind === "bigint" || fromKind === "number") && toKind === "string") { return `CAST(${col} AS TEXT)`; } if ((fromKind === "integer" || fromKind === "bigint") && toKind === "number") { return `CAST(${col} AS REAL)`; } if (fromKind === "number" && (toKind === "integer" || toKind === "bigint")) { return `CAST(${col} AS INTEGER)`; } if ((fromKind === "integer" || fromKind === "bigint") && toKind === "boolean") { return `CASE WHEN ${col} != 0 THEN 1 ELSE 0 END`; } if (fromKind === "boolean" && (toKind === "integer" || toKind === "bigint")) { return col; // Stored as INTEGER already } if (fromKind === "boolean" && toKind === "number") { return `CAST(${col} AS REAL)`; } if (fromKind === "boolean" && toKind === "string") { return `CASE WHEN ${col} = 1 THEN 'true' ELSE 'false' END`; } if (fromKind === "datetime" && toKind === "string") { return col; // Both stored as TEXT } if (toKind === "json") { return `CAST(${col} AS TEXT)`; } if (fromKind === "json") { return `CAST(${col} AS TEXT)`; } // Fallback return `CAST(${col} AS ${sqlTypeForColumnKind(toKind)})`; } function fallbackForKind(kind: ColumnKind): string { switch (kind) { case "string": case "datetime": case "json": return "''"; case "integer": case "bigint": case "boolean": return "0"; case "number": return "0.0"; } } /** * Builds just the CREATE TABLE statement for a datasource, using an explicit table name. * Used by the migration table recreation path. */ function buildTableDDL(datasource: Datasource, tableName: string): string { const escapedTableName = escapeIdentifier(tableName); const columns: string[] = []; // System columns const systemShape: Record = datasourceSystemProperties.shape; for (const [fieldName, fieldSchema] of Object.entries(systemShape)) { const column = describeColumn(fieldSchema); const sqlType = sqlTypeForColumnKind(column.kind); let constraint = ""; if (fieldName === "$id") { constraint = " NOT NULL PRIMARY KEY"; } else if (fieldName === "$lastModificationDate" || fieldName === "$creationDate") { constraint = " NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now') || 'Z')"; } columns.push(`${escapeIdentifier(fieldName)} ${sqlType}${constraint}`); } // User columns const userShape = datasource.schema.shape; const systemFieldNames = Object.keys(systemShape); for (const fieldName of Object.keys(userShape)) { if (systemFieldNames.includes(fieldName)) continue; const fieldSchema = userShape[fieldName as keyof typeof userShape]; if (!fieldSchema) continue; const column = describeColumn(fieldSchema as ZodType); const sqlType = sqlTypeForColumnKind(column.kind); const notNull = column.isOptional || column.isNullable ? "" : " NOT NULL"; const defaultClause = column.defaultValue !== undefined ? ` DEFAULT ${formatDefaultValue(column.defaultValue, column.kind)}` : ""; columns.push(`${escapeIdentifier(fieldName)} ${sqlType}${notNull}${defaultClause}`); } // Foreign key constraints if (datasource.foreignKeys) { for (const fk of datasource.foreignKeys) { columns.push( `FOREIGN KEY (${escapeIdentifier(fk.field)}) REFERENCES ${escapeIdentifier(fk.foreignDatasourceId)}(${escapeIdentifier(fk.foreignField)})`, ); } } return `CREATE TABLE ${escapedTableName} (\n ${columns.join(",\n ")}\n)`; }