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?

Tasin Ishmam · Accepted Answer · 2021-07-12 16:29:44Z

0

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.

answered Jul 12, 2021 at 16:29

Tasin Ishmam

7,4882 gold badges29 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arman Over a year ago

Thank you, sir. You answered all of my questions. And about authorId data type, you are right, it should be TEXT.

Tasin Ishmam Over a year ago

Happy to help :D. As a side note, if you need help or suggestions with Prisma, we have a very active community on Slack. Feel free to join if you'd like!

Collectives™ on Stack Overflow

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

1 Answer 1

One-to-many relation between User and Post

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

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

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

One-to-many relation between User and Post

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

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

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related