Introspection fails to generate `@relation` for many-to-many relation in MySQL
Problem
Newer reproduction here: https://github.com/prisma/prisma/issues/14668#issuecomment-1755468605 --- Bug description Introspection (`db pull`) fails to generate `@relation` for relations between tables, that are using compound keys. How to reproduce 1. Create schema from SQL bellow 2. run `npx prisma db pull` 3. See error in relation between tables Expected behavior The resulting schema should create `@relation` with the correct fields. Generated: [code block] Correct: [code block] Prisma information [code block] Environment & setup - OS: Windows - Database: MySQL - Node.js version: v16.13.2 Prisma Version [code block] Extracted from https://github.com/prisma/prisma/issues/14648#issuecomment-1205224365
Error Output
error in relation between tables
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix Introspection for Many-to-Many Relations with Compound Keys in MySQL
The introspection process in Prisma fails to correctly interpret compound keys when generating `@relation` attributes for many-to-many relationships. This is likely due to the way MySQL handles composite primary keys and how Prisma's introspection logic is implemented, leading to missing or incorrect relation mappings.
Awaiting Verification
Be the first to verify this fix
- 1
Define Compound Keys in SQL Schema
Ensure that the SQL schema explicitly defines the compound keys for the tables involved in the many-to-many relationship. This is crucial for Prisma to recognize the relationships correctly.
sqlCREATE TABLE User (id INT PRIMARY KEY, name VARCHAR(255)); CREATE TABLE Role (id INT PRIMARY KEY, title VARCHAR(255)); CREATE TABLE UserRole (userId INT, roleId INT, PRIMARY KEY (userId, roleId), FOREIGN KEY (userId) REFERENCES User(id), FOREIGN KEY (roleId) REFERENCES Role(id)); - 2
Run Prisma Introspection
After defining the schema, run the Prisma introspection command to pull the latest database schema into your Prisma schema file. This should generate the necessary relations.
bashnpx prisma db pull - 3
Check Generated Prisma Schema
Review the generated Prisma schema to verify that the `@relation` attributes are correctly created for the many-to-many relationship. Ensure that the fields match the compound keys defined in the SQL schema.
prisma// Example of expected output: model User { id Int @id name String roles Role[] @relation("UserRoles") } model Role { id Int @id title String users User[] @relation("UserRoles") } model UserRole { userId Int roleId Int user User @relation(fields: [userId], references: [id]) role Role @relation(fields: [roleId], references: [id]) @@id([userId, roleId]) } - 4
Update Prisma Client
If the relations are correctly generated, update the Prisma Client to reflect the changes in your application code. This ensures that the application can interact with the updated database schema.
bashnpx prisma generate - 5
Test Application Functionality
Run your application and test the functionality related to the many-to-many relationship to ensure that data can be created, read, updated, and deleted correctly without any errors.
javascript// Example test code: const userWithRoles = await prisma.user.findUnique({ where: { id: 1 }, include: { roles: true }, }); console.log(userWithRoles);
Validation
Confirm that the `@relation` attributes are correctly generated in the Prisma schema and that the application can successfully interact with the many-to-many relationships without errors.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep