"Not Authorised" when directly applying Prisma generated migrations to Cloudflare D1 with PRAGMA foreign_key_check;

[sakettawde]

Bug description

When we change a column in a table with foreign keys, Prisma CLI generates the migrations properly when we run migrate diff but a particular line is inserted in the sql statements that are output:
PRAGMA foreign_key_check;

When you try to apply these migrations to either Cloudflare D1 local or remote databases, it fails with the error "not authorized". The above line is where this error is happening. The rest of the sql statements generated work perfectly.

How to reproduce

Create a simple prisma schema with 2 tables dependent on each other (maybe User and Session tables)
Generate migrations and apply to local and remote databases of Cloudflare D1
You can use this link for detailed instructions on how to do that: https://www.prisma.io/docs/orm/overview/databases/cloudflare-d1

Change something in the session table like a column name from userId to user_id
And regenerate migrations. Again, details on how here: https://www.prisma.io/docs/orm/overview/databases/cloudflare-d1#3-generate-sql-statements-using-prisma-migrate-diff-1

View the generated migrations sql file. If you try and apply the migrations, you will get the error.

It looks like this in the console:

Expected behavior

It should ideally allow us to run PRAGMA foreign_key_check;

Current workaround, apply migrations line by line manually or remove the line causing issues.

Prisma information

Initial Prisma Schema:

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

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

model User {
  id    String  @id 
  email String  @unique
  name  String?
  sessions Session[]
}

model Session {
  id        String   @id
  userId    String
  expires_at DateTime
  user      User     @relation(references: [id], fields: [userId], onDelete: Cascade)
}

Modified Prisma Schema:

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

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

model User {
  id    String  @id 
  email String  @unique
  name  String?
  sessions Session[]
}

model Session {
  id        String   @id
  user_id    String
  expires_at DateTime
  user      User     @relation(references: [id], fields: [user_id], onDelete: Cascade)
}

Environment & setup

• OS: macOS
• Database: Cloudflare D1
• Node.js version: v 20.11.0
• Wrangler version: v 3.47.0

Prisma Version

5.12.1

[sakettawde]

Considering this may be related to wrangler cli from Cloudflare not giving us the necessary permissions, have added an issue there: cloudflare/workers-sdk#5598

[janpio]

PRAGMA foreign_key_check is not one of the supported pragmas by Cloudflare D1: https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#pragma-statements

Currently, the workaround is to remove this from the generated migration SQL file. That should allow you to apply the migration successfully and will most probably not cause any problems.

We are currently investigating how we can avoid this problem.

[jkomyno]

Hi, this should be fixed now in a dev (unstable) version of Prisma: 5.14.0-dev.53.
@sakettawde, can you please give it a try as well?

For production usage, we recommend waiting for the next stable version, Prisma 5.14.0, scheduled for release in ~7 days.

I will close the issue for now, but you're still welcome to add comments below.
Thanks!

[sakettawde]

Thank you 🙏🏼