1

I am following the official docs to setup next-auth with prisma: https://next-auth.js.org/adapters/prisma. After copying these models (Session, Account, VerificationToken) into my schema.prisma I get the following error when I try to do a prisma db push.

Am I missing something? How do I correct this issue?

npx prisma db push:

Error: foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/
   0: sql_migration_connector::apply_migration::migration_step
           with step=AddForeignKey { foreign_key_id: ForeignKeyId(0) }
             at migration-engine/connectors/sql-migration-connector/src/apply_migration.rs:21
   1: sql_migration_connector::apply_migration::apply_migration
             at migration-engine/connectors/sql-migration-connector/src/apply_migration.rs:10
   2: migration_core::state::SchemaPush
             at migration-engine/core/src/state.rs:381

server/prisma/schema.prisma:

datasource db {
  provider = "mysql"
  // NOTE: When using postgresql, mysql or sqlserver, uncomment the @db.Text annotations in model Account below
  // Further reading:
  // https://next-auth.js.org/adapters/prisma#create-the-prisma-schema
  // https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#string
  url      = env("DATABASE_URL")
  // relationMode = "prisma"
}

generator client {
  provider = "prisma-client-js"
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

enum Role {
  hacker
  mentor
  sponsor
}

model User {
  id       String    @id @default(cuid())
  email    String    @unique
  password String
  role     Role      @default(hacker)
  accounts  Account[]
  sessions  Session[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

Adding relationMode = "prisma" datasource db allows me to run npx prisma db push successfully but then my @relations show errors in vscode and when I hover my cursor over it shows:

With `relationMode = "prisma"`, no foreign keys are used, so relation fields will not benefit from the index usually created by the relational database under the hood. This can lead to poor performance when querying these fields. We recommend adding an index manually. Learn more at https://pris.ly/d/relation-mode-prisma-indexes"

I'm not too sure what this means but I think I want to keep these @relations rather than doing it "manually" as the error suggests.

Shadow
  • 33,525
  • 10
  • 51
  • 64
currenthandle
  • 1,038
  • 2
  • 17
  • 34

2 Answers2

2

Solution

  1. uncomment your relationMode = "prisma" in the database node.
  2. run npx prisma format → you'll notice a bunch of warnings
  3. add @unique to userId in Account and Session
  4. apply with npx prisma db push

Resources

  • https://www.prisma.io/docs/guides/database/planetscale#how-to-emulate-relations-in-prisma-client

    PlanetScale does not allow foreign keys in its database schema. By default, Prisma uses foreign keys in the underlying database to enforce relations between fields in your Prisma schema. In Prisma versions 3.1.1 and later, you can emulate relations in Prisma Client with the prisma relation mode, which avoids the need for foreign keys in the database.

    To enable emulation of relations in Prisma Client, set the relationMode field to "prisma" in the datasource block

  • https://www.prisma.io/docs/guides/database/planetscale#differences-to-consider

    When emulating relations in Prisma, you will need to create indexes on foreign keys. In a standard MySQL database, if a table has a column with a foreign key constraint, an index is automatically created on that column. Because PlanetScale does not support foreign keys, these indexes are currently not created when Prisma Client emulates relations, which can lead to issues with queries not being well optimised. To avoid this, you can create indexes in Prisma. For more information, see How to create indexes on foreign keys.

  • https://www.prisma.io/docs/concepts/components/prisma-schema/relations/relation-mode#indexes

    The prisma relation mode does not use foreign keys, so no indexes are created when you use Prisma Migrate or db push to apply changes to your database. You instead need to manually add an index on your relation scalar fields with the @@index attribute (or the @unique, @@unique or @@id attributes, if applicable).

y_nk
  • 2,157
  • 2
  • 19
  • 29
0

PlanetScale does not support the use of Foreign keys. See this docs.

With Prisma you can maintain these relationships in your data and allow the use of referential actions by using Prisma's ability to emulate relations in Prisma Client with the prisma relation mode. For more information, see How to emulate relations in Prisma Client.

Raphael Etim
  • 612
  • 5
  • 8