Update: Rephrased the question in a reply. ------ Hi all I have a question about joining data from another database when querying a parent-children tree. For the sake of simplicity, I have mapped our actual model to a folder-file structure. Where each node in the tree can either be a folder or a file. This works perfectly fine even when querying the tree (we can ignore #4562 in this question) Now, if there’s another database (an existing one that we can’t change), contains data about each

File

. This data is changing every few seconds. With every query to

prisma.directoryNode.findFirst

, we need to join the data from the other database to the query. I have already connected to the remote database using

postgres_fdw

and can query the data with

$queryRaw.

Can anyone help us with performing a join from a remote database while using

prisma.directoryNode.findFirst

to maintain type safety. Performing a join using prisma’s type safety would be ideal if it’s supported.

enum NodeType {
  FOLDER
  FILE
}

model DirectoryNode {
  id   Int      @id @default(autoincrement())
  type NodeType

  folder   Folder? @relation(fields: [folderId], references: [id])
  folderId Int?    @unique

  file   File? @relation(fields: [fileId], references: [id])
  fileId Int?  @unique

  children DirectoryNode[] @relation("directoryNode")
  parent   DirectoryNode?  @relation("directoryNode", fields: [parentId], references: [id])
  parentId Int?

  // timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Folder {
  id   Int    @id @default(autoincrement())
  name String

  node DirectoryNode?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model File {
  id             Int            @id @default(autoincrement())
  name           String
  extension      String
  defaultAppName String
  node           DirectoryNode?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Hi 👋

postgres_fdw

isn’t supported natively yet so queryRaw would be the only workaround for now. Here’s the Feature Request: #8614 Please consider leaving a 👍 to the request so that we could prioritize it.

Thanks Nurul