Prisma Many-to-Many Relation to SQL Join Tables

Many-to-Many Relation Conversion

Many-to-many relationships in Prisma can be either implicit (Prisma manages the join table) or explicit (you define the join table model yourself). When converting to SQL, you always need an explicit join table.

Explicit Many-to-Many (Recommended for SQL Conversion)

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

  @@map("students")
}

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

  @@map("courses")
}

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

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

Generated SQL

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

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

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students (id),
  FOREIGN KEY (course_id) REFERENCES courses (id),
  PRIMARY KEY (student_id, course_id)
);

Composite Primary Key

The @@id([studentId, courseId]) in Prisma generates a PRIMARY KEY (student_id, course_id) constraint. This ensures that each student-course combination is unique and serves as the table's primary key.

Implicit Many-to-Many

Prisma also supports implicit many-to-many where you use array fields on both sides without a join model. However, this approach creates a hidden _ModelAToModelB join table that Prisma manages internally. When you need full control over the SQL output, always use an explicit join model.

Additional Columns on the Join Table

One major advantage of explicit join tables is that you can add extra columns like enrolledAt, role, status, etc. These columns appear in the SQL output as regular columns on the join table.