Comments (3)
For completeness: Here's the bug report on D1's side: cloudflare/workers-sdk#5438
from prisma.
Here's a possible fix (not sure if it is the most efficient way to do this, but it works fine for me):
The id pairs are copied to temporary tables and then copy them back after the migration. Example migration file to test (also in the readme):
-- RedefineTables
PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
-- Since D1 currently looses ids when the table is connected, copy the id / authorId pairs from Post to a temporary table (only if authorId is not null)
CREATE TABLE "__temp_post_authorId" (
"id" TEXT NOT NULL,
"authorId" INTEGER NOT NULL
);
INSERT INTO "__temp_post_authorId" ("id", "authorId") SELECT "id", "authorId" FROM "Post" WHERE "authorId" IS NOT NULL;
CREATE TABLE "new_User" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"email" TEXT NOT NULL,
"name" TEXT NOT NULL DEFAULT 'Anonymous'
);
INSERT INTO "new_User" ("email", "id") SELECT "email", "id" FROM "User";
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
-- Copy the data from the temporary table back to Post
UPDATE "Post" SET "authorId" = (SELECT "authorId" FROM "__temp_post_authorId" WHERE "__temp_post_authorId"."id" = "Post"."id") WHERE "id" IN (SELECT "id" FROM "__temp_post_authorId");
DROP TABLE "__temp_post_authorId";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;
If prisma now could find all optional relations (and many to many relations!) and generate that sql, that would quite cool ;-)
from prisma.
For a temporary workaround, here's a script (run with a path to a migration sql file as the first argument) which patches the migration file:
Edit: does not yet work with many to many relations and onDelete: CASCADE relations.
// @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'));
from prisma.
Related Issues (20)
- Support nested queries in `query` extension
- Not possible to omit optional fields in Mongo HOT 2
- Inconsistent execution times for Prisma Generate command HOT 8
- called `Option::unwrap()` on a `None` value HOT 2
- prisma migrate dev fails with Error: P1017 (prisma > 5.9.1) HOT 2
- MySQL migration invalid default value @default(now())
- Bug: [D1] Error in performIO: Error: D1_ERROR: too many SQL variables HOT 1
- Support Couchbase HOT 1
- prisma-cli should correctly prompt the current loaded ENV file HOT 3
- Running queries in Promise.all() leads to idle zombie database sessions HOT 2
- Enable parameters for data source to be provided as fields on `datasource` instead of connection string arguments
- prisma migrate * commands not working HOT 3
- Schema enum Support Chinese HOT 3
- transaction: bulk ops already in a transaction should not be executed in a new sub transaction HOT 2
- Could not figure out an ID in create HOT 4
- Cannot read properties of undefined (reading 'kind') HOT 4
- Prisma is returning the DateTime in ISOstring() HOT 2
- Generalize and Enhance Readability of "RecordNotFound" Error Message in Prisma Client HOT 1
- Log query params are not escaped
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from prisma.