Bug (D1 migrations): IDs of optional relations are lost quietly during migration about prisma HOT 3 OPEN

// @ts-check
import fs from 'fs/promises';

const mirationFile = process.argv[2];
if (!mirationFile) {
    console.error('Please provide a migration file');
    process.exit(1);
}

let migrationContent = await fs.readFile(mirationFile, 'utf-8');
if (!migrationContent.includes("PRAGMA defer_foreign_keys=ON;") && !migrationContent.includes("PRAGMA foreign_keys=OFF;")) {
    console.error('Migration file does not contain the expected PRAGMA defer_foreign_keys=ON; and PRAGMA foreign_keys=OFF;');
    process.exit(1);
}

const prismaSchema = await fs.readFile('prisma/schema.prisma', 'utf-8');
const prismaLines = prismaSchema.split('\n');
const changedTables = [...migrationContent.matchAll(/CREATE TABLE "new_(.*)"/g)].map((match) => match[1]);

const D1_FIX_START = "-- D1_FIX_START";
const D1_FIX_END = "-- D1_FIX_END";

// delete everything between all D1_FIX_START and D1_FIX_END
let inFix = false;
const newMigrationLines = [];
for (const line of migrationContent.split('\n')) {
    if (line.includes(D1_FIX_START)) {
        inFix = true;
    }
    if (!inFix) {
        newMigrationLines.push(line);
    }
    if (line.includes(D1_FIX_END)) {
        inFix = false;
    }
}
migrationContent = newMigrationLines.join('\n');

let sqlTop = "";
let sqlBottom = "";

for (const changedTable of changedTables) {
    const relations = [];
    let currentModelName = "";
    const changedModel = prismaSchema.match(new RegExp(`model ${changedTable} {[\\S\\s]*?}`))?.[0];
    if (!changedModel) {
        console.error(`Could not find model ${changedTable}`);
        process.exit(1);
    }
    for (const line of prismaLines) {
        if (line.startsWith('model ')) {
            currentModelName = line.split(' ')[1];
            continue;
        }
        if (line.includes(` ${changedTable}?`) && currentModelName && currentModelName !== changedTable) {
            // find the @relation of the line (@relation(fields: [orderId], references: [id])) and read the fields[0]
            const field = line.match(/@relation\(fields: \[(.*?)\]/)?.[1];
            if (!field) {
                throw new Error(`Could not find relation field for ${currentModelName} -> ${changedTable}`);
            }
            relations.push({
                currentModel: currentModelName,
                relation: field,
            });
        }
        if (line.includes(` ${changedTable}[]`)) {
            if (changedModel.includes(` ${currentModelName}[]`)) {
                throw new Error(`Many to many relations are not yet supported by this script: ${currentModelName} <-> ${changedTable}`);
            }
        }
    }
    for (const relation of relations) {
        sqlTop += `-- Since D1 currently looses ids when the table is connected, copy the id / ${relation.relation} pairs from ${relation.currentModel} to a temporary table (only if ${relation.relation} is not null)\n`;
        const tableName = `__temp_${relation.currentModel}_${relation.relation}`;
        sqlTop += `CREATE TABLE "${tableName}" ("id" TEXT NOT NULL PRIMARY KEY, "relationId" TEXT NOT NULL);\n`;
        sqlTop += `INSERT INTO "${tableName}" ("id", "relationId") SELECT "id", "${relation.relation}" FROM "${relation.currentModel}" WHERE "${relation.relation}" IS NOT NULL;\n`;
        sqlBottom += `-- Copy the id / ${relation.relation} pairs back to the table\n`;
        sqlBottom += `UPDATE "${relation.currentModel}" SET "${relation.relation}" = (SELECT "relationId" FROM "${tableName}" WHERE "${tableName}"."id" = "${relation.currentModel}"."id") WHERE "id" IN (SELECT "id" FROM "${tableName}");\n`;
        sqlBottom += `DROP TABLE "${tableName}";\n`;
    }
}

sqlTop = `-- D1_FIX_START\n${sqlTop}-- D1_FIX_END\n`;
sqlBottom = `-- D1_FIX_START\n${sqlBottom}-- D1_FIX_END\n`;

// after comments, PRAGMAs and empty lines
const migrationLines = migrationContent.split('\n');
for (let i = 0; i < migrationLines.length; i++) {
    if (migrationLines[i].startsWith('--') || migrationLines[i].startsWith('PRAGMA') || migrationLines[i].trim() === '') {
        continue;
    }
    migrationLines.splice(i, 0, sqlTop);
    break;
}

for (let i = migrationLines.length - 1; i >= 0; i--) {
    if (migrationLines[i].startsWith('--') || migrationLines[i].startsWith('PRAGMA') || migrationLines[i].trim() === '') {
        continue;
    }
    migrationLines.splice(i + 1, 0, sqlBottom);
    break;
}
// remove consecutive empty lines
for (let i = migrationLines.length - 1; i >= 0; i--) {
    if (migrationLines[i].trim() === '' && migrationLines[i - 1].trim() === '') {
        migrationLines.splice(i, 1);
    }
}
await fs.writeFile(mirationFile, migrationLines.join('\n'));