Avoid intermediary reads when doing nested updates

[Weakky]

Overview

Consider the following Prisma Schema

model A {
  id   Int     @id
  name String?

  bs B[]
}

model B {
  id   Int     @id
  name String?

  A   A?   @relation(fields: [aId], references: [id])
  aId Int?
  
  cId Int?
  c   C?   @relation(fields: [cId], references: [id])
}

model C {
  id   Int     @id
  name String?
  B    B[]
}

And the following query:

mutation {
  updateOneA(
    where: { id: 1 }
    data: {
      name: "a"
      bs: {
        update: {
          where: { id: 1 }
          data: {
            name: "b"
            c: {
              update: {
                where: { id: 1 }
                data: { name: "tata" }
              }
            }
          }
        }
      }
    }
  ) {
    id
  }
}

Here are the queries we currently generate:

-- before
BEGIN;
-- update A
UPDATE "A" SET "name" = $1 WHERE ("A"."id" = $2) RETURNING "A"."id";
-- read B
SELECT "B"."id", "B"."aId" FROM "B" WHERE ("B"."id" = $1 AND "B"."aId" IN ($2)) OFFSET $3;
-- update B
UPDATE "B" SET "name" = $1 WHERE ("B"."id" = $2 AND "B"."id" = $3) RETURNING "B"."id", "B"."cId";
-- read C
SELECT "C"."id" FROM "C" WHERE ("C"."id" = $1 AND "C"."id" IN ($2)) OFFSET $3;
-- update C
UPDATE "C" SET "name" = $1 WHERE ("C"."id" = $2 AND "C"."id" = $3) RETURNING "C"."id";
-- read updated A
SELECT "t1"."id" FROM "A" AS "t1" WHERE "t1"."id" = $1 LIMIT $2;
COMMIT;

And here are the queries we could generate instead:

-- after
BEGIN;
-- update A
UPDATE "A" SET "name" = $1 WHERE ("A"."id" = $2) RETURNING "A"."id";
-- update B using results from A's update
UPDATE "B" SET "name" = $1 WHERE ("B"."id" = $2 AND "B"."aId" IN ($N)) RETURNING "B"."id", "B"."cId";
-- update C using results from B's update
UPDATE "C" SET "name" = $1 WHERE ("C"."id" = $2) RETURNING "C"."id";
-- read updated A
SELECT "t1"."id" FROM "A" AS "t1" WHERE "t1"."id" = $1 LIMIT $2;
COMMIT;

Saving 1 read per traversed relation.

This is now possible thanks to the support of UPDATE ... RETURNING for arbitrary field selection. In many cases, there's an opportunity to save an intermediary read when the parent update is able to return the linking fields for the subsequent update.

I believe this could be all done as a post-processing pass on the graph, by removing intermediary read nodes if a parent update node already fulfills the selection set of all children of these intermediary read nodes.

Before

Parent update -> read children -> children update

After

Parent update -> children update

[jkomyno]

Hi there,
As part of our effort to clean up our GitHub repo, we’re closing this issue to help focus on features and improvements that align with our focus areas and those of our community.

This issue was originally created for internal tracking purposes, but if you feel this is important to you, feel free to reopen the issue and get like-minded users to upvote as we will prioritize fixing issues based on community interest.

For more insight into our priorities and how we’re thinking about the future of Prisma, please see our blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and support!