Hello! Does anyone know what is the best way to describe an undirected graph with Prisma, for example a social network where the users can be friends. Here is what I have so far but I am really unhappy with it:

model User {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  relationships1 Relationship[] @relation("UserRelationship1")
  relationships2 Relationship[] @relation("UserRelationship2")
}

model Relationship {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  user1   User   @relation("UserRelationship1", fields: [user1Id], references: [id])
  user1Id String
  user2   User   @relation("UserRelationship2", fields: [user2Id], references: [id])
  user2Id String
}

If you require a separate model, you could do something like this:

model User {
  id        String         @id @default(cuid())
  createdAt DateTime       @default(now())
  updatedAt DateTime       @updatedAt
  friends   Relationship[]
}

model Relationship {
  id        String   @id @default(cuid())
  user      User?    @relation(fields: [userId], references: [id])
  userId    String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

thanks @Ryan but I don’t understand... Where is the relation to the other user?

That would be in the

Relationship

model. For e.g. if

user1

and

user2

are friends, You can create a relationship and add

user2

in the relationship of

user1

and vice versa.

Yes but then I will need to add an other foreign key to the

Relationship

to describe “the user a user is linked to” and it will also add a “back relation” to the

User

model and it will end up exactly like the first one I sent. Or am I missing something? What I am trying to do is a many-to-many self relation with a model in between so I can attach more data to the “edges” of the graph

One way you could add the relation in a separate model would be like this:

model User {
  id        String         @id @default(cuid())
  createdAt DateTime       @default(now())
  updatedAt DateTime       @updatedAt
  friends   Relationship[] @relation("friends")
}

model Relationship {
  id        String   @id @default(cuid())
  users     User[]   @relation("friends")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Instead of adding 2 separate fields, you can create this so that it can also be directly queried for.

hmmm okay thanks. I think my problem is that I don’t use the right tool for the job. Do you think I should stick to this solution or should I use a graph database instead? And if so, should I use both postgres and a graph database or migrate all of my data in the new one?

why are you unhappy with the schema you have?