prisma: How to convert a database table with more that one FK to prisma schema?

Question

I'm new to Prisma and relational databases in general. I'm a little bit confused about how to create many-to-many relations between tables, especially relation fields.

For example, imagine I want to convert this database model to Prisma schema.

This is the answare that I came up with:

model User {
  id             String         @id
  comments       Comment[]
  posts          Post[]
}

model Post {
  id             Int            @default(autoincrement()) @id
  author         User           @relation(fields: [authorId], references: [id])
  authorId       String
  comments       Comment[]
}

model Comment {
  id             Int            @default(autoincrement()) @id
  post           Post           @relation(fields: [postId], references: [id])
  postId         Int       
  author         User           @relation(fields: [authorId], references: [id])
  authorId       String
  text           String
}

The Comment table has two foreign keys to both User and Post tables. Do I need to have the comments field in both models? And in general, Am I doing it right?

Answer 1

Based on the database model you posted, your Prisma Schema seems perfectly fine. Let me go relation by relation.

One-to-many relation between User and Post

This is perfectly fine. The Post table should hold a foreign key for the User.id field.

Many-to-many relation between User and Post (via the Comment relation table).

This is again, perfectly fine. You are also correct that the comments field needs to be in both models.

Some notes on explicit vs implicit many-to-many relations

In Prisma terminology, the way you have explicitly defined the many-to-many Comment table is called an explicit many-to-many relation. Since the Comment table has a separate text field, it makes sense to use an explicit many-to-many relation.

If the text field did not exist, you could have used an implicit many-to-many relation. You can optionally read up more on this in the Many-to-many relations section of the Relations Concept Article in the prisma docs.

As a side note, your diagram seems to have the authorId foreign-key incorrectly listed as an INT type. I beleive this should be TEXT.