Bug: Migrations not compatible with D1
Closed this issue · 2 comments
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
For completeness, can you include the generated migration SQL that is causing problems? Thanks.
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;