Convert SQL Junction Tables to Prisma Many-to-Many Relations

Many-to-Many Relations

SQL models many-to-many relationships via a junction (join) table with two foreign keys. Prisma supports both implicit many-to-many (no junction model) and explicit many-to-many (with a junction model).

Example SQL

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE article_tags (
  article_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

Implicit Many-to-Many (Simple Junction)

model Article {
  id    Int    @id @default(autoincrement())
  title String
  tags  Tag[]

  @@map("articles")
}

model Tag {
  id       Int       @id @default(autoincrement())
  name     String    @unique
  articles Article[]

  @@map("tags")
}

Prisma manages the junction table automatically. The converter uses this pattern when the junction table has only the two foreign key columns and a composite primary key, with no extra data columns.

Explicit Many-to-Many (Junction with Extra Data)

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  grade CHAR(2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

model Student {
  id          Int          @id @default(autoincrement())
  enrollments Enrollment[]

  @@map("students")
}

model Course {
  id          Int          @id @default(autoincrement())
  enrollments Enrollment[]

  @@map("courses")
}

model Enrollment {
  studentId  Int      @map("student_id")
  courseId    Int      @map("course_id")
  enrolledAt DateTime @default(now()) @map("enrolled_at")
  grade      String?  @db.Char(2)
  student    Student  @relation(fields: [studentId], references: [id])
  course     Course   @relation(fields: [courseId], references: [id])

  @@id([studentId, courseId])
  @@map("enrollments")
}

Choosing Implicit vs Explicit

Criterion	Implicit	Explicit
Junction has only FK columns	Yes	No
Extra fields on the relationship	Not possible	Supported
Prisma manages junction table	Automatically	You manage it
Query the junction directly	Not possible	Yes

Converter Detection Logic

1. Table has exactly two foreign key columns forming a composite PK: implicit candidate.
2. If the table has additional non-FK columns (timestamps, metadata): explicit relation.
3. If either FK references the same table (self-referencing M:N): always explicit.

Migration Note

When converting from SQL to Prisma's implicit M:N, Prisma expects the junction table to be named _ArticleToTag (alphabetical order, prefixed with underscore). The converter handles renaming or generates a migration step.