https://www.prisma.io/ logo
Join Slack
Powered by
Hello, I was wondering how can I do a many-to-many...
# orm-help
a
Hello, I was wondering how can I do a many-to-many relationship between a User and a Team model. Basically I want to have a string array with the user ids in the Team schema.
Copy code
model User {
  id       String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email    String @unique
  username String @unique @db.VarChar(255)
  name     String
  password String
  teams Team[]

  @@map("user")
}

model Team {
  id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name String @unique
  displayName String
  description String @db.VarChar(500)
  membersId String[] @db.Uuid
  adminsID String[]
  members User[] @relation(fields: [membersId], references: [id])

  @@map("team")
}
I've tried a few other things to do it, but every time I get this error 
Key columns "membersId" and "id" are of incompatible types: uuid[] and uuid.
Any help would be appreciated.
j
Hi @AlexSPx I not sure about this but from your error, it seems like one is an 
array
of 
uuid's
while the other is just one 
uuid
I think you are trying to define an 
explicit
many-to-many relationship. For this you’d need to define an additional model
a
@Joey Can I somehow make it without an additional model?
s
@AlexSPx your model is incorrect. You cannot specify arrays of primitives in this connector.
Copy code
model User {
  id       String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email    String @unique
  username String @unique @db.VarChar(255)
  name     String
  password String
  teams Team[]
  @@map("user")
}

model Team {
  id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name String @unique
  displayName String
  description String @db.VarChar(500)
  adminsID String
  members User[]
  @@map("team")
}
Try this out
Also, I'd highly suggest installing the Prisma plugin for VS Code (or for your editor if available) as it will point out errors 🙂
a
What will this store in the Teams column @Samrith Shankar?
@Samrith Shankar I tried the code you sent and it's still giving me the same error
Copy code
Error: P3006

Migration `20210614155639_init` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: db error: ERROR: foreign key constraint "team_membersId_fkey" cannot be implemented
DETAIL: Key columns "membersId" and "id" are of incompatible types: uuid[] and uuid.
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine\connectors\sql-migration-connector\src\flavour\<http://postgres.rs:280|postgres.rs:280>
   1: sql_migration_connector::sql_database_migration_inferrer::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\<http://sql_database_migration_inferrer.rs:88|sql_database_migration_inferrer.rs:88>
   2: migration_core::api::DevDiagnostic
             at migration-engine\core\src\<http://api.rs:95|api.rs:95>
j
teams
field isn’t gonna be in the 
User
table as well as 
members
in the 
Team
table. Those are relation(virtual) fields, relation between these models are stored in the separate table(JOIN), you won’t need to worry about it. With prisma client you are able to query those fields even though they are technically absent in the table like this: 
prisma.team.findUnique({ where: { name: 'some name' }).members()
and it will return all members records that are “stored” in the 
Team
table.
a
@jasci Alright, so Samrith's code should be fine, right? If so could it be that it's not refreshing the file changes?
j
Did you remove 
@db.Uuid
?
Copy code
membersId String[] @db.Uuid
->
Copy code
membersId String[]
?
This error occur after 
npx prisma migrate dev
command ?
Moreover, you don’t need this field at all
a
I just completely removed it
And added this 
members User[]
j
You used the exact schema Samrith Shankar posted ?
a
Yes
j
This error occur after  
npx prisma migrate dev
 command ?
a
Also yes
j
Then I think you’ll need to manually adjust the db, because the changes to the prisma schema are conflicting with you current db set up, I guess. But, I’m not an expert, so maybe someone else knows what exactly to do.
a
So after playing for a while 
npx prisma db push
seems to work, It pushed the changes
j
I’d recommend you throughly read the tutorial, because if I remember correctly it’s not a good practice to combine 
db push
and 
migrate dev
interchangeably (choose one for your needs) https://www.prisma.io/docs/concepts/components/prisma-migrate/db-push#choosing-db-push-or-prisma-migrate
a
Alright, thanks for your time
👍 1
s
@AlexSPx 
db push
was my second suggestion. But like @jasci mentioned, it is not good practise to merge 
db push
and 
migrate
. One is for dev another for prod
👍 1
3 Views
Open in Slack
PreviousNext
Powered by