Prisma: Optimize SQL queries for "fluent API"

[RobertBroersma]

Problem

I'm playing around with resolvers in my GraphQL backend and came up with two options for resolving nested fields.

This is my schema.prisma (left out some fields and irrelevant models for brevity)

model Story {
  id          String @id @default(cuid())
  title       String
  tags        Tag[]
}

model Tag {
  id          String @id @default(cuid())
  label       String @unique
  stories     Story[]
}

These are my options for resolving Story:

// Option 1 - Separate Query
export const Story = {
  tags: (_args, parent) =>
    db.tag.findMany({ where: { story: { id: parent.id } } })
};

// Option 2 - Fluent API
export const Story = {
  tags: (_args, parent) =>
    db.story.findOne({ where: { id: parent.id } }).tags()
};

I posed the question about which one is more efficient/performant in the Slack channel over here: https://prisma.slack.com/archives/CKQTGR6T0/p1585390183147300 ad @nikolasburk asked me to generate the queries for both options and open an issue.

Result
Option 1 generates 1 query (other than the main query for Story data):

SELECT
  `dev`.`Tag`.`id`,
  `dev`.`Tag`.`label`,
FROM `dev`.`Tag`
WHERE
  (`dev`.`Tag`.`id`) IN (
    SELECT
      `t0`.`B`
    FROM `dev`.`_StoryToTag` AS `t0`
    INNER JOIN `dev`.`Story` AS `j0` ON (`j0`.`id`) = (`t0`.`A`)
    WHERE
      `j0`.`id` = ?
  )
LIMIT
  ? OFFSET ?

Option 2 generates 3 small queries:

SELECT
  `dev`.`Story`.`id`
FROM `dev`.`Story`
WHERE
  `dev`.`Story`.`id` IN (?)
LIMIT
  ? OFFSET ?

SELECT
  `A`,
  `B`
FROM `dev`.`_StoryToTag`
WHERE
  `A` IN (?)

SELECT
  `dev`.`Tag`.`id`,
  `dev`.`Tag`.`label`,
FROM `dev`.`Tag`
WHERE
  `dev`.`Tag`.`id` IN (?, ?)
LIMIT
  ? OFFSET ?

Solution

According to @nikolasburk the multiple queries are typically more expensive, therefore it would be great to optimize the queries generated by the fluent API!

Additional context

I abstracted my use case a little more for brevity. In reality I use an additional Model layer to handle authorization like only allowing user's to read a Story that has a DRAFT status if it's their own. This is currently not possible using Option 2, because of the limitations of .findOne().

In my Story model I expose a findOne method that actually looks like this:

async findOne({ id }) {
  const stories = await db.story.findMany({
    where: { OR: [
            { id, status: 'Published' },
            { id, AND: { owner: { id: user.id } } },
          ], },
    first: 1,
  })

  return stories[0]
},

I need to resolve the query before I can return the first value, disabling the fluent API of course! Therefore until db.x.findOne becomes more flexible, I'll be using Option 1 anyway, which is already optimized!

Hope this helps!

[pantharshit00]

I can reproduce this. Schema to match beta.1 changes:

model Story {
  id          String @id @default(cuid())
  title       String
  tags        Tag[]  @relation(references: [id])
}

model Tag {
  id          String @id @default(cuid())
  label       String @unique
  stories     Story[]  @relation(references: [id])
}

The first query you posted is wrong and I think it got wrong when you abstracted your use case example for brevity. Correct query:

 const data = await prisma.tag.findMany({
    where: {
      stories: {
        some: {
          id: "something",
        },
      },
    },
  });

A join instead of small queries would be ideal for fluent API.

Ready to go reproduction for future reference: https://github.com/harshit-test-org/prisma2-1984

[nrxus]

This might be a separate feature request, but could it be possible to add syntax that allows for:

parent.tags()

which would "expand" to:

prisma.tag.findMany({ where: { story: { id: parent.id } } })

This would make it a lot simpler to fetch relations after you have the parent model already fetched, without having to write up some boiler plate code for every relation we need to fetch lazily (not using the include syntax).

[pantharshit00]

@nrxus You can open a separate feature request for this but I am personally against having multiple overloads for same thing as it increases the API surface and makes it more confusing to use.