Bug: Migrations not compatible with D1

[hrueger]

Bug description

Generated migrations for D1 (following this guide) are not compatible with D1. When changing a table with relations, foreign keys are disabled and re-enabled with PRAGMA foreign_keys=OFF; and PRAGMA foreign_keys=ON;.
However, D1 seems to need PRAMGA defer_foreign_keys=ON; and PRAGMA defer_foreign_keys=OFF; (also notice the swapped ON and OFF).

When applied, the migration fails with ✘ [ERROR] FOREIGN KEY constraint failed.

How to reproduce

Minimal reproduction: https://github.com/hrueger/prisma-24208

Expected behavior

Migrations are generated with PRAGMA defer_foreign_keys instead so that the migration can be applied.

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  posts     Post[]
  // rename this field, let's say to `newField`
  oldField  String?
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

Environment & setup

• OS: macOS
• Database: Cloudflare D1 / SQLite
• Node.js version: 21.6.1

Prisma Version

prisma                  : 5.14.0
@prisma/client          : 5.14.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v21.6.1
Query Engine (Node-API) : libquery-engine e9771e62de70f79a5e1c604a2d7c8e2a0a874b48 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli e9771e62de70f79a5e1c604a2d7c8e2a0a874b48 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.14.0-25.e9771e62de70f79a5e1c604a2d7c8e2a0a874b48
Default Engines Hash    : e9771e62de70f79a5e1c604a2d7c8e2a0a874b48
Studio                  : 0.500.0
Preview Features        : driverAdapters

[janpio]

For completeness, can you include the generated migration SQL that is causing problems? Thanks.

[hrueger]

Sure!
generated & not working:

-- RedefineTables
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "newField" TEXT
);
INSERT INTO "new_User" ("createdAt", "email", "id", "name") SELECT "createdAt", "email", "id", "name" FROM "User";
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
PRAGMA foreign_key_check("User");
PRAGMA foreign_keys=ON;

expected & working:

-- RedefineTables
PRAGMA defer_foreign_keys=ON;
CREATE TABLE "new_User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "newField" TEXT
);
INSERT INTO "new_User" ("createdAt", "email", "id", "name") SELECT "createdAt", "email", "id", "name" FROM "User";
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
PRAGMA foreign_key_check("User");
PRAGMA defer_foreign_keys=OFF;