Relations handling with explicit many to many relation

Question

I’m using PlanetScale to host my database and Fastify with Prisma to build a simple API.

I read the documentation about the things to consider, most notably the missing support of foreign keys.

I followed the guide to create indexes on foreign keys and also the guide to create explicit many-to-many relations but then I can’t use anymore all the cool features of Prisma.

I currently have the following schema:

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

datasource db {
  provider     = "mysql"
  url          = env("DB_URL")
  relationMode = "prisma"
}

model Owner {
  id        Int      @id @default(autoincrement())
  name      String   @unique @db.VarChar(255)
  pets      Pet[]
  createdBy String   @db.VarChar(255)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Pet {
  id        Int      @id @default(autoincrement())
  name      String   @unique @db.VarChar(255)
  owners    Owner[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

After the explicit many-to-many relation is introduced, here is the schema:

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

datasource db {
  provider     = "mysql"
  url          = env("DB_URL")
  relationMode = "prisma"
}

model Owner {
  id           Int            @id @default(autoincrement())
  name         String         @unique @db.VarChar(255)
  pets         OwnersOfPets[]
  createdBy    String         @db.VarChar(255)
  createdAt    DateTime       @default(now())
  updatedAt    DateTime       @updatedAt
}

model Pet {
  id           Int            @id @default(autoincrement())
  name         String         @unique @db.VarChar(255)
  owners       OwnersOfPets[]
  createdAt    DateTime       @default(now())
  updatedAt    DateTime       @updatedAt
}

model OwnersOfPets {
  pet     Pet   @relation(fields: [petId], references: [id])
  petId   Int
  owner   Owner @relation(fields: [ownerId], references: [id])
  ownerId Int

  @@id([petId, ownerId])
  @@index([petId])
  @@index([ownerId])
}

For reference, here is the generated OwnersOfPetsWhereUniqueInput type:

export type OwnersOfPetsWhereUniqueInput = Prisma.AtLeast<{
  ownerId_petId?: OwnersOfPetsOwnerIdPetIdCompoundUniqueInput
  AND?: OwnersOfPetsWhereInput | OwnersOfPetsWhereInput[]
  OR?: OwnersOfPetsWhereInput[]
  NOT?: OwnersOfPetsWhereInput | OwnersOfPetsWhereInput[]
  ownerId?: IntFilter<"OwnersOfPets"> | number
  petId?: IntFilter<"OwnersOfPets"> | number
  owner?: XOR<OwnerRelationFilter, OwnerWhereInput>
  pet?: XOR<PetRelationFilter, PetWhereInput>
}, "ownerId_petId">

The issue is that I have some pretty simple existing code to connect owners to pets which is now broken as I need to transform several things to a new much more complicated syntax:

const pet = await request.server.prisma.pet.findUnique({
  where: { id },
  // Before schema change
  // include: { owners: true },
  // After schema change
  include: { owners: { include: { owner: true } } },
})

// Some more code

await request.server.prisma.pet.update({
  where: { id },
  data: {
    // Some new data
    ...omit(request.body, 'owners'),
    owners: {
      // Before schema change
      // connect: [{ name: 'Henry' }, { name: 'James' }],
      // After schema change
      connect: [
        { petId_ownerId: { petId: id, ownerId: 1 } },
        { petId_ownerId: { petId: id, ownerId: 2 } },
      ],
    },
  },
})

As you can see, the code is much more complicated with a very simple use case… Plus, I can’t use the unique name field of Owners to connect to the Pet.

Also, the connectOrCreate feature is inherently broken with the introduction of the explicit many-to-many relation table, because it requires to provide both a petId and ownerId, which I obviously don’t have yet in the create case.

Am I doing something wrong ? Is it expected that I don’t get the same DX as with implicit many-to-many relations ?

Answer 1

This question has been answered in a Github discussion:

Using an explicit many-to-many relation instead of an implicit one can make the Prisma Client API more complex. However, the advantage of using explicit many-to-many relations is that you can define extra fields on the relation table, which can be useful for storing additional information about the relation.

In your case, you're now connecting Pet to Owner through the OwnersOfPets model, which requires you to specify the petId and ownerId in the connect clause. This is why you can't use the unique name field of Owner to connect to the Pet anymore, because the connection is now being managed through the OwnersOfPets model. While the developer experience (DX) might be different when using explicit many-to-many relations compared to implicit ones, this is the expected behavior and not something you're doing wrong.