Increase one-to-one update call performance

Open

#2,157 opened on Apr 10, 2020 2 comments 6 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty: 5/5
Estimated time: Over a week
Newbie friendliness: 25/100

Issue type: Refactor
Clarity: Mostly clear
Activity status: Stale
Tech stack: postgresql, typescript
Domain: backend, databases

Research direction

Start with the userRepository.update call and the OneToOne relation described in the issue, then reproduce it and inspect the complete query log. Done means the update preserves its result while reducing the unnecessary database queries shown in the log.

Written by the indexing model from the issue text.

Description

status/needs-action topic: performance topic: performance/queries topic: update()

Problem

Imported from #2091

Hi,

I'm using the following query to update a OneToOne relation:

    await userRepository.update({
      where: { id: me.id },
      data: {
        credentials: {
          update: this.buildCredentials(tokenRes),
        },
      },
    });

with the following simplified schema:

model User {
  id            String      @id
  credentials   Credentials @relation(fields: [credentialsId], references: [id])
  credentialsId Int
}

model Credentials {
  id           Int      @default(autoincrement()) @id
  accessToken  String
  refreshToken String
  expires      DateTime
  user         User
}

Not counting SELECT 1, BEGIN and COMMIT, this creates a total of 6 queries. Log:

SELECT 1
BEGIN
SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1
SELECT "public"."User"."id", "public"."User"."credentialsId" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2
SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE (1=1 AND "public"."Credentials"."id" IN ($1)) OFFSET $2
SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE "public"."Credentials"."id" = $1
UPDATE "public"."Credentials" SET "expires" = $1, "refreshToken" = $2, "accessToken" = $3, "updatedAt" = $4 WHERE "public"."Credentials"."id" IN ($5)
SELECT "public"."User"."id", "public"."User"."country", "public"."User"."displayName", "public"."User"."email", "public"."User"."images", "public"."User"."product", "public"."User"."uri", "public"."User"."roles", "public"."User"."credentialsId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
COMMIT

Complete Query Log

2020-04-04T12:38:57.618Z engine stdout {
  timestamp: 'Apr 04 12:38:57.618',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT 1',
    item_type: 'query',
    params: '[]',
    duration_ms: 0
  }
}
prisma:query SELECT 1
2020-04-04T12:38:57.619Z engine stdout {
  timestamp: 'Apr 04 12:38:57.618',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: { query: 'BEGIN', item_type: 'query', params: '[]', duration_ms: 0 }
}
prisma:query BEGIN
2020-04-04T12:38:57.620Z engine stdout {
  timestamp: 'Apr 04 12:38:57.619',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1',
    item_type: 'query',
    params: '["beatportbot"]',
    duration_ms: 0
  }
}
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1
2020-04-04T12:38:57.620Z engine stdout {
  timestamp: 'Apr 04 12:38:57.619',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."User"."id", "public"."User"."credentialsId" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2',
    item_type: 'query',
    params: '["beatportbot",0]',
    duration_ms: 0
  }
}
prisma:query SELECT "public"."User"."id", "public"."User"."credentialsId" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2
2020-04-04T12:38:57.621Z engine stdout {
  timestamp: 'Apr 04 12:38:57.620',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE (1=1 AND "public"."Credentials"."id" IN ($1)) OFFSET $2',
    item_type: 'query',
    params: '[2,0]',
    duration_ms: 0
  }
}
prisma:query SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE (1=1 AND "public"."Credentials"."id" IN ($1)) OFFSET $2
2020-04-04T12:38:57.622Z engine stdout {
  timestamp: 'Apr 04 12:38:57.621',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE "public"."Credentials"."id" = $1',
    item_type: 'query',
    params: '[2]',
    duration_ms: 1
  }
}
prisma:query SELECT "public"."Credentials"."id" FROM "public"."Credentials" WHERE "public"."Credentials"."id" = $1
2020-04-04T12:38:57.623Z engine stdout {
  timestamp: 'Apr 04 12:38:57.621',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'UPDATE "public"."Credentials" SET "expires" = $1, "refreshToken" = $2, "accessToken" = $3, "updatedAt" = $4 WHERE "public"."Credentials"."id" IN ($5)',
    item_type: 'query',
    params: '[2020-04-04 13:38:57.616 UTC,"XYXYX","XYXYX",2020-04-04 12:38:57.618513200 UTC,2]',
    duration_ms: 0
  }
}
prisma:query UPDATE "public"."Credentials" SET "expires" = $1, "refreshToken" = $2, "accessToken" = $3, "updatedAt" = $4 WHERE "public"."Credentials"."id" IN ($5)
2020-04-04T12:38:57.624Z engine stdout {
  timestamp: 'Apr 04 12:38:57.624',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: {
    query: 'SELECT "public"."User"."id", "public"."User"."country", "public"."User"."displayName", "public"."User"."email", "public"."User"."images", "public"."User"."product", "public"."User"."uri", "public"."User"."roles", "public"."User"."credentialsId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3',
    item_type: 'query',
    params: '["beatportbot",1,0]',
    duration_ms: 2
  }
}
prisma:query SELECT "public"."User"."id", "public"."User"."country", "public"."User"."displayName", "public"."User"."email", "public"."User"."images", "public"."User"."product", "public"."User"."uri", "public"."User"."roles", "public"."User"."credentialsId", "public"."User"."createdAt", "public"."User"."updatedAt" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
2020-04-04T12:38:57.627Z engine stdout {
  timestamp: 'Apr 04 12:38:57.627',
  level: 'INFO',
  target: 'quaint::connector::metrics',
  fields: { query: 'COMMIT', item_type: 'query', params: '[]', duration_ms: 2 }
}
prisma:query COMMIT

As discussed, I think this can be optimized by reducing the amount of queries and take all infos from the given query into account.

Dominant language: TypeScript
Stars: 47.6k
Forks: 2.5k
Avg merge: 21h 59m
Merged PRs (30d): 95

Contributor guide

Open the contributing guide

First steps

Read the whole issue, then the project's contributing guide.
Comment on the issue to say you are picking it up — it saves two people doing the same work.
Fork the repository and make your change on a branch.
Open a pull request that references the issue number.

Similar issues

[Bug]: Session row status indicator sits flush against the selection pill's rounded corner Open

clawsweeper:fix-shape-clear clawsweeper:queueable-fix clawsweeper:source-repro impact:ux-friction issue-rating: 🦞 diamond lobster no-stale P3

Difficulty 2/5 1-3 hours Newbie friendliness 78/100

openclaw/openclaw#154145 · 2 comments · 1 reaction · Opened Sep 20, 2026
[good first issue] 🀄 Add new Grammar Point 265 (good-first-issue) Open

community first-timers-only good first issue hacktoberfest help wanted low hanging fruit up-for-grabs

Difficulty 1/5 Under an hour Newbie friendliness 76/100

lingdojo/kana-dojo#30632 · 1 comment · 5 reactions · Opened Sep 20, 2026
[Duplicate Code] Repeated filtered model-alias assembly in proxy server Open

code-quality refactoring

Difficulty 2/5 1-3 hours Newbie friendliness 84/100

github/gh-aw-firewall#8816 · Opened Sep 20, 2026
quickjs: support `timeout=None` (no per-call deadline) in `CodeInterpreterMiddleware` Open

integration:quickjs org:external priority:backlog topic:code-interpreter topic:middleware type:feature

Difficulty 2/5 1-3 hours Newbie friendliness 74/100

langchain-ai/deepagents#6450 · Opened Sep 20, 2026
`QuotedTweet.self_thread` is optional in syndication responses Open

Difficulty 1/5 Under an hour Newbie friendliness 88/100

vercel/react-tweet#225 · Opened Sep 20, 2026

More TypeScript issues