-
Notifications
You must be signed in to change notification settings - Fork 1.5k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Bug: Migrations not compatible with D1 #24208
Labels
bug/2-confirmed
Bug has been reproduced and confirmed.
domain/schema
Issue in the "Schema" domain: Prisma Schema, Introspection, Migrations etc.
kind/bug
A reported bug.
topic: d1
Issues related to Cloudflare D1
topic: migrate
topic: @prisma/adapter-d1
Milestone
Comments
hrueger
added a commit
to hrueger/prisma-24208
that referenced
this issue
May 15, 2024
For completeness, can you include the generated migration SQL that is causing problems? Thanks. |
Sure! -- 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; |
jkomyno
added a commit
to prisma/prisma-engines
that referenced
this issue
May 16, 2024
This was referenced May 16, 2024
jkomyno
added a commit
to prisma/prisma-engines
that referenced
this issue
May 28, 2024
* fix(schema-engine): make "migrate diff" deterministic * ok * fix(schema-engine): combine foreign_keys + defer_foreign_keys PRAGMAs to fix prisma/prisma#24208
This was referenced Jul 17, 2024
This was referenced Jul 19, 2024
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Labels
bug/2-confirmed
Bug has been reproduced and confirmed.
domain/schema
Issue in the "Schema" domain: Prisma Schema, Introspection, Migrations etc.
kind/bug
A reported bug.
topic: d1
Issues related to Cloudflare D1
topic: migrate
topic: @prisma/adapter-d1
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;
andPRAGMA foreign_keys=ON;
.However, D1 seems to need
PRAMGA defer_foreign_keys=ON;
andPRAGMA defer_foreign_keys=OFF;
(also notice the swappedON
andOFF
).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
Environment & setup
Prisma Version
The text was updated successfully, but these errors were encountered: