Multiple nested query calls rather than joins

[dhairyamodi2]

Bug description

You create separate nested queries rather than joins which adds to network latency when calling remote db! What JOINS returned in 24ms, and 421ms on remote db calls, with prisma it takes 87ms on local, and 3s on remote db calls.

How to reproduce

1. Connect to remote db
2. Create 1-n and n-n relations
3. Insert dumb data, and query data where multiple tables are involved and join/nested query is required
4. See your average response time to be 1-3s when multiple tables are involved.

Expected behavior

No response

Prisma information

schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql" // Adjust based on your DB (MySQL, SQLite, etc.)
  url      = env("DATABASE_URL")
}

model User {
  id                 Int                  @id @default(autoincrement())
  email              String               @unique
  name               String
  userOrganizations  UserOrganization[]
}

model Organization {
  id                   Int                  @id @default(autoincrement())
  name                 String
  userOrganizations    UserOrganization[]
  organizationPermissions OrganizationPermission[]
  formalities          Formality[]
}

model Permission {
  id                   Int                  @id @default(autoincrement())
  permissionName       String
  organizationPermissions OrganizationPermission[]
}

model UserOrganization {
  user          User         @relation(fields: [userId], references: [id])
  userId        Int
  organization  Organization @relation(fields: [organizationId], references: [id])
  organizationId Int

  @@id([userId, organizationId]) // Composite primary key
}

model OrganizationPermission {
  organization    Organization @relation(fields: [organizationId], references: [id])
  organizationId  Int
  permission      Permission   @relation(fields: [permissionId], references: [id])
  permissionId    Int

  @@id([organizationId, permissionId]) // Composite primary key
}

model Formality {
  id             Int         @id @default(autoincrement())
  organization   Organization @relation(fields: [organizationId], references: [id])
  organizationId Int
  violated       Boolean     @default(false)
`}`

Query:

const result = await prisma.user.findUnique({
    where: { id: userId },
    select: {
      userOrganizations: {
        select: {
          organization: {
            select: {
              name: true,
              formalities: {
                where: {
                  violated: false
                },
                select: {
                  id: true,
                  violated: true
                }
              },
              organizationPermissions: {
                where: {
                  permission: {
                    permissionName: {
                      in: ['read_formalities', 'read_organization_metadata']
                    }
                  }
                },
                select: {
                  permission: {
                    select: {
                      permissionName: true
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  });

Environment & setup

• OS: Debian
• Database: PostgreSQL
• Node.js version: 20.11.1

Prisma Version

5.12.1

[janpio]

Take a look at the relationJoins preview feature that was included in 5.8.0: https://github.com/prisma/prisma/releases/tag/5.8.0 Does that solve the problem you are reporting?

[dhairyamodi2]

Thanks, it's down to average 800ms now

[janpio]

That looks much more reasonable.
If you find any problem with this preview feature enabled, please open a new bug issue. Thanks.