DEV Community

Tushar Patil
Tushar Patil

Posted on

Handle Many to Many Relations in PostgreSQL using Prisma ORM

#postgres #prisma #relations #manytomany

Handling many-to-many relationships in PostgreSQL using Prisma is straightforward and efficient. Prisma can manage both implicit and explicit many-to-many relations.

1. Implicit Many-to-Many Relation 

model User {
  id       Int      @id @default(autoincrement())
  name     String
  projects Project[] // Many-to-many relation
}

model Project {
  id    Int    @id @default(autoincrement())
  title String
  users User[] // Many-to-many relation
}
Enter fullscreen mode Exit fullscreen mode

By declaring each side as a list of the other, Prisma knows to spin up a hidden join table for you.

Generated SQL Migration

When you run prisma migrate dev, Prisma will produce SQL akin to:

-- 1. Create the two main tables
CREATE TABLE "User" (
  "id"   SERIAL PRIMARY KEY,
  "name" TEXT   NOT NULL
);

CREATE TABLE "Project" (
  "id"    SERIAL PRIMARY KEY,
  "title" TEXT    NOT NULL
);

-- 2. Create the hidden join table
CREATE TABLE "_UserToProject" (
  "A" INTEGER NOT NULL,
  "B" INTEGER NOT NULL,

  PRIMARY KEY ("A","B"),

  FOREIGN KEY ("A") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY ("B") REFERENCES "Project"("id") ON DELETE CASCADE ON UPDATE CASCADE
);
Enter fullscreen mode Exit fullscreen mode

Key points:

The join table is named by alphabetically sorted model names with an underscore prefix: _To.

Columns are auto‑named "A"/"B"; you never interact with them directly in Prisma Client.

ON DELETE CASCADE ensures that if a User (or Project) is deleted, all linking rows vanish automatically.

Example 2
Here you can see many to many relation in between User and Team,
Team and Project. the joining table for this schema will be created by prisma automatically.

model User {
    id       String    @id @default(cuid())
    email    String    @unique
    username String    @unique
    password String
    projects Project[]
    teams    Team[]
}

model Project {
    id        String @id @default(cuid())
    name      String
    creatorId String @map("creator_id")
    creator   User   @relation(fields: [creatorId], references: [id])

    teams Team[]
}

model Team {
    id        String    @id @default(cuid())
    name      String?
    creatorId String    @map("creator_id")
    users     User[]
    projects  Project[]
}
Enter fullscreen mode Exit fullscreen mode

implicit handling of many to many relations in prisma is more easier than manually creating a joining table.
but it lacks in flexibility, like in above example user and team have many to many relation, we can not add more field describing additional information about this relation like role of the user in related team.

2.Explicit Many to Many Relations

In Explicit Many to Many Relations you have to manually create a join table for tow models.
in this example i created two join tables UserTeam and ProjectTeam.

model User {
    id       String     @id @default(cuid())
    email    String     @unique
    username String     @unique
    password String
    projects Project[]
    teams    UserTeam[]
}

model Project {
    id        String @id @default(cuid())
    name      String
    creatorId String @map("creator_id")
    creator   User   @relation(fields: [creatorId], references: [id])

    teams ProjectTeam[]
}

model Team {
    id        String        @id @default(cuid())
    name      String?
    creatorId String        @map("creator_id")
    users     UserTeam[]
    projects  ProjectTeam[]
}

model UserTeam {
    id     String @id @default(cuid())
    teamId String
    userId String
    team   Team   @relation(fields: [teamId], references: id)
    user   User   @relation(fields: [userId], references: id)
}

model ProjectTeam {
    id      String  @id @default(cuid())
    teamId  String
    userId  String
    team    Team    @relation(fields: [teamId], references: id)
    Project Project @relation(fields: [userId], references: id)
}
Enter fullscreen mode Exit fullscreen mode

this manual implementation for joining tables add more flexibility in defining schema, since we can add more fields in join table that does not possible with implicit relations.

example we can add role for the user in team

model UserTeam {
    id     String @id @default(cuid())
    teamId String
    userId String
    role   String

    team   Team   @relation(fields: [teamId], references: id)
    user   User   @relation(fields: [userId], references: id)
}
Enter fullscreen mode Exit fullscreen mode

💡Conclusion:

choosing between implicit and explicit relational handling is depend upon you project requirement. if you require simpler many to many link between two models or don't need extra data on relation itself then you should go with implicit handling.
if your project require flexibility in schema , to add more data on relation itself, you should try creating a joining table explicitly to handle many to many relation.

Reference:

Prisma Documentation: https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.