Prisma Client upsert returns count instead of data

[gt-codes]

Bug description

When using the upsert method to do a findOrCreate implementation, count: { 1 } is returned rather than the data itself.

How to reproduce

Using the following code returns count rather than the user object

const user = await prisma.user.upsert({
  where: { email: emails[0].value },
  update: { lastLoggedIn: new Date() },
  create: {
    email: emails[0].value,
    name: displayName,
    avi: photos[0].value,
    profile: {
      create: {
        name: displayName,
        avi: photos[0].value,
        email: emails[0].value,
      },
    },
  },
});

console.log({ user });

Expected behavior

I expect the user's data to be returned.

Prisma information

model User {
  id           String   @id @default(uuid())
  email        String   @unique
  name         String
  avi          String?
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  lastLoggedIn DateTime @default(now())

  log           Log[]
  items         Item[]
  rsvps         Rsvp[]   @relation("rsvp_user")
  createdEvents Event[]  @relation(name: "created_event")
  profile       Profile? @relation("profile_user")
}

Environment & setup

• OS: Mac OS
• Database: MySQL
• Node.js version: v14.17.3

Prisma Version

prisma                  : 3.6.0
@prisma/client          : 3.7.0

[janpio]

Indeed documented different: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#upsert + https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#return-type-5

[gt-codes]

@janpio Yes, I referenced this documentation. What do I need to do on my end to fix this issue?

[pantharshit00]

I am unable to reproduce this. Can you please make sure that you are using upsert and not upsertMany? That is the method which returns the count.

[gt-codes]

@pantharshit00 My exact code is in the issue description, I'm not using upsertMany.

[pantharshit00]

As I previously mentioned, I am unable to reproduce this:

Can you share a reproduction inside of a git repository so that we can have a more reliable example of the problem?

[gt-codes]

From a conversation I had with @ruheni, here are the logs from the query in this issues description. I got these logs from new PrismaClient({ log: ['query', 'info'] }). I hope this provides more inisght into what's going on @pantharshit00

prisma:info Starting a mysql pool with 17 connections.
prisma:query BEGIN
prisma:query SELECT `venir`.`User`.`id` FROM `venir`.`User` WHERE `venir`.`User`.`email` = ?
prisma:query SELECT `venir`.`Item`.`id`, `venir`.`Item`.`ownerId` FROM `venir`.`Item` WHERE (1=1 AND `venir`.`Item`.`ownerId` IN (?))
prisma:query SELECT `venir`.`Log`.`id`, `venir`.`Log`.`userId` FROM `venir`.`Log` WHERE (1=1 AND `venir`.`Log`.`userId` IN (?))
prisma:query SELECT `venir`.`Rsvp`.`id`, `venir`.`Rsvp`.`userId` FROM `venir`.`Rsvp` WHERE (1=1 AND `venir`.`Rsvp`.`userId` IN (?))
prisma:query SELECT `venir`.`Event`.`id`, `venir`.`Event`.`hostId` FROM `venir`.`Event` WHERE (1=1 AND `venir`.`Event`.`hostId` IN (?))
prisma:query SELECT `venir`.`Item`.`id`, `venir`.`Item`.`eventId` FROM `venir`.`Item` WHERE (1=1 AND `venir`.`Item`.`eventId` IN (?,?))
prisma:query SELECT `venir`.`Log`.`id`, `venir`.`Log`.`eventId` FROM `venir`.`Log` WHERE (1=1 AND `venir`.`Log`.`eventId` IN (?,?))
prisma:query SELECT `venir`.`Rsvp`.`id`, `venir`.`Rsvp`.`eventId` FROM `venir`.`Rsvp` WHERE (1=1 AND `venir`.`Rsvp`.`eventId` IN (?,?))
prisma:query SELECT `venir`.`Profile`.`id`, `venir`.`Profile`.`userId` FROM `venir`.`Profile` WHERE (1=1 AND `venir`.`Profile`.`userId` IN (?))
prisma:query SELECT `venir`.`User`.`id` FROM `venir`.`User` WHERE `venir`.`User`.`email` = ?
prisma:query UPDATE `venir`.`User` SET `lastLoggedIn` = ?, `updatedAt` = ? WHERE `venir`.`User`.`id` IN (?)
prisma:query SELECT `venir`.`User`.`id`, `venir`.`User`.`email`, `venir`.`User`.`name`, `venir`.`User`.`avi`, `venir`.`User`.`createdAt`, `venir`.`User`.`updatedAt`, `venir`.`User`.`lastLoggedIn` FROM `venir`.`User` WHERE `venir`.`User`.`id` = ? LIMIT ? OFFSET ?
prisma:query COMMIT
{ user: { count: 0 } }

prisma:info Starting a mysql pool with 17 connections.
prisma:query SELECT `venir`.`Event`.`id`, `venir`.`Event`.`hostId`, `venir`.`Event`.`name`, `venir`.`Event`.`settingsId`, `venir`.`Event`.`isoDate`, `venir`.`Event`.`location`, `venir`.`Event`.`createdAt`, `venir`.`Event`.`updatedAt` FROM `venir`.`Event` WHERE 1=1 ORDER BY `venir`.`Event`.`updatedAt` DESC
prisma:query SELECT `venir`.`Rsvp`.`id`, `venir`.`Rsvp`.`userId`, `venir`.`Rsvp`.`eventId`, `venir`.`Rsvp`.`response`, `venir`.`Rsvp`.`createdAt`, `venir`.`Rsvp`.`updatedAt` FROM `venir`.`Rsvp` WHERE `venir`.`Rsvp`.`eventId` IN (?,?)
There was an error running the data loader for route routes/__auth
Error: 
Invalid `prisma.user.findUnique()` invocation in
./api/build/index.js:679:34

./app/routes/__auth.tsx
   677 var loader = async ({ request }) => {
   678   const _user = await auth(request, { failureRedirect: "/login" });
→  679   const user = await prisma.user.findUnique({
           where: {
         ?   id?: String,
         ?   email?: String
           },
           include: {
             profile: true
           }
         })

Argument where of type UserWhereUniqueInput needs at least one argument. Available args are listed in green.

Note: Lines with ? are optional.

    at Object.validate (./node_modules/@prisma/client/runtime/index.js:34755:20)
    at PrismaClient._executeRequest (./node_modules/@prisma/client/runtime/index.js:39749:17)
    at consumer (./node_modules/@prisma/client/runtime/index.js:39690:23)
    at ./node_modules/@prisma/client/runtime/index.js:39694:49
    at AsyncResource.runInAsyncScope (node:async_hooks:199:9)
    at PrismaClient._request (./node_modules/@prisma/client/runtime/index.js:39694:27)
    at request (./node_modules/@prisma/client/runtime/index.js:39799:77)
    at _callback (./node_modules/@prisma/client/runtime/index.js:40007:14)
    at PrismaPromise.then (./node_modules/@prisma/client/runtime/index.js:40014:23)

[uvalus]

I've discovered that if you use upsert (when creating a record) it behaves as expected.
However if you use upsert to update a record it returns the { count: number } response.

This was first noted under Prisma 3.6.0 and still persists under 3.8.0.
Version 3.5.0 behaves as expected so something must have changed between those versions.

const dbUser = await prismaClient.user.upsert({
  where: {
    email: args.email
  },
  update: {
    name: args.name,
    email: args.email,
  },
  create: {
    name: args.name,
    email: args.email,
  },
});

console.log(dbUser);

Returns

{ count: 0 }

on an update

But it returns:

{
  id: 169,
  created_at: 2022-01-11T22:59:13.844Z,
  updated_at: 2022-01-11T22:59:13.844Z,
  name: 'test user 2',
  email: 'test2@test.com'
}

When creating data for the first time

[pantharshit00]

I am still unable to reproduce this. I tried last 3 versions and still wasn't able to reproduce. Can you please maybe share a git repo with a script and the schema which reproduces this? I think it might be related to schema structure as well.

[uvalus]

What sort of schema structures would likely cause this ?
Question - would the response be erroneously computing the number of relations the user is a part of instead of returning the affected record ?

BTW I've had to work around the issue by doing an explicit find / create / update so my code works as expected now.

Even if I changed a value with the update (using the upsert command) e.g. the updated_at property to be new Date() it still behaved incorrectly (as mentioned in my previous post).

[flybayer]

We're also experiencing this issue in production with v3.8.1. It was working fine on v3.5.0.

I can share my schema privately if it would help.

[nkrekow]

also running into this issue (v3.8.1, mysql)...when creating a new entry upsert returns the expected data. if a record already exists matching there where it returns a count. however, if the record already exists and i use findUnique() with the same where: and select: as upsert i get the data im expecting to get

export async function login(body) {
    let { email, name, image } = body

    // the return statement below works as expected if a new record is created, 
    // otherwise if a matching record exists im just getting count back instead of what is in select
    // like i do if its a new record created
    return await db.user.upsert({
        where: { email: email },
        select: { id: true, name: true, image: true },
        update: { name, image },
        create: { email, name, image }
    })

    // if the record already exists and i use findUnique instead the correct information is 
    // returned instead of "count"
    return await db.user.findUnique({
        where: { email },
        select: { id: true, name: true, image: true }
    })
}

here is the model its pulling from

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String    @unique
  image         String?
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  sessions      Session[]
}

[pantharshit00]

@nkrekow

I tried the above and still wasn't able to reproduce this. Can you please tell me the exact MySQL version you are using?

[pantharshit00]

@uvalus It might be schema parser which is creating the wrong query graph so I was asking for this. I still unable to trigger this so if you share a minimal example which can reproduce the problem consistently, please share it.

[nkrekow]

@pantharshit00 this is on PlanetScale and it looks like they're using mysql version 8.0.23

[uvalus]

Can confirm, I am using PlanetScale as well

[flybayer]

Also using Planetscale

[janpio]

(Noice, seems we are getting somewhere here)
Do you also all have the referentialIntegrity preview feature with referentialIntegrity = prisma enabled?

[uvalus]

Yes

[nkrekow]

yup!

[janpio]

I can not reproduce this on PlanetScale with referentialIntegrity = "prisma" enabled:

C:\Users\Jan\Documents\throwaway\10935>node script.js     
users before []
upsert1 { id: 'ckyx13p1q0002dgs2vi5ifpmy', name: 'test', image: 'sf' }
upsert2 { id: 'ckyx13p1q0002dgs2vi5ifpmy', name: '123', image: '456' }
users after [
  {
    id: 'ckyx13p1q0002dgs2vi5ifpmy',
    name: '123',
    email: 'test',
    image: '456',
    createdAt: 2022-01-27T13:45:17.198Z,
    updatedAt: 2022-01-27T13:45:18.121Z
  }
]

For this code:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function login(body) {

  let { email, name, image } = body

  return await prisma.user.upsert({
    where: { email: email },
    select: { id: true, name: true, image: true },
    update: { name, image },
    create: { email, name, image }
  })
}

async function main() {

  console.log("users before", await prisma.user.findMany({}))

  let body1 = { email: "test", name: "test", image: "sf" }
  let body2 = { email: "test", name: "123", image: "456" }

  console.log("upsert1", await login(body1))
  console.log("upsert2", await login(body2))

  console.log("users after", await prisma.user.findMany({}))

}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

and schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

datasource db {
  provider             = "mysql"
  url                  = env("DATABASE_URL")
  referentialIntegrity = "prisma"
}

model User {
  id        String   @id @default(cuid())
  name      String?
  email     String   @unique
  image     String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Am I missing something?
Can you double check and compare with your schemas what could be different?

@gt-codes Is your Invalid prisma.user.findUnique() invocation error in your log related, and a follow up of it not returning the data? Can you add console.log of the upsert result as I did to your app and run these logs again please?

[ramiel]

I can confirm I have the same issue. My configuration is for planetscale, but locally I'm using normal Mysql and can expeience the same problem

[ramiel]

So, yesterday I wasn't able to reproduce that behavior, as long as I tried. I checked with the same version I'm using (3.9.2). I'll keep trying but maybe @ItzDerock code is enough?

[pantharshit00]

Can other people as well here try the latest version and report back?

[ramiel]

So, here there's a reproduction repository based on @ItzDerock code but with the configuration for Mysql/Planet scale.
The issue is present when the update is executed, so to reproduce it you need to run npm run dev twice. I used version 3.10.0

https://github.com/ramiel/prisma-issue-10935

[ramiel]

Any news on this?

[janpio]

We are still waiting for a confirmed reproduction. (Feel free to try the one posted in the comment above and let us know if this works - that will save us some time.)

[ramiel]

I provided a repository with the reproducible error a couple of comments above. Haven't you tried it? Did you try and it's working for you instead?

[janpio]

Oh sorry, I missed that it was you who posted that - this way my message of course does not make a lot of sense.
We did not have the time to appraoch that reproduction yet unfortunately. If anyone else can confirm the reproduction reproduces, that would be helpful.

[cdinu]

@janpio, I can confirm that the reproduction reproduces. Is it helpful?

[janpio]

I can indeed confirm this as well now in the context of referentialIntegrity = "prisma" for the referentialIntegrity preview feature - when a child model is connected to the model you are updating.

[ponei]

Can also confirm this; using PlanetScale on production but also happens locally with MySQL
Returns fine if the identifier doesn't exist, but if it does it just returns {"count": number}

datasource db {
  provider             = "mysql"
  url                  = env("DATABASE_URL")
  referentialIntegrity = "prisma"
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity", "interactiveTransactions"]
}

[ramiel]

Any news on this?

[dpetrick]

I'm unable to reproduce this issue on Prisma 4.1. Reopen please if you still run into the same issue after upgrading.

[janpio]

I can confirm that this does not reproduce any more with 4.1:

PS C:\Users\Jan\Documents\throwaway\prisma-issue-10935> npm run dev

> dev
> ts-node ./script.ts

{
  id: 1,
  email: 'email@person.com',
  name: 'Derock',
  bio: 'cool person'
}
PS C:\Users\Jan\Documents\throwaway\prisma-issue-10935> npm run dev

> dev
> ts-node ./script.ts

{
  id: 1,
  email: 'email@person.com',
  name: 'Derock',
  bio: 'cool person'
}

PS C:\Users\Jan\Documents\throwaway\prisma-issue-10935> npx prisma -v
Environment variables loaded from .env
prisma                  : 4.1.1
@prisma/client          : 4.1.1
Current platform        : windows
Query Engine (Node-API) : libquery-engine 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Migration Engine        : migration-engine-cli 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules\@prisma\engines\migration-engine-windows.exe)       
Introspection Engine    : introspection-core 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules\@prisma\engines\introspection-engine-windows.exe)     
Format Binary           : prisma-fmt 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash    : 8d8414deb360336e4698a65aa45a1fbaf1ce13d8
Studio                  : 0.469.0
Preview Features        : referentialIntegrity

Can you please confirm @ramiel @ponei @cdinu @ItzDerock etc?

[sarthaksaklecha]

The issue still reproduces. Upsert, when updating is returning { count : number }

[janpio]

Please open a new issue with a full reproduction if you still get this problem with a recent version of Prisma. We will not further investigate this in this closed issue from 2022. Thank you.

[GilbertoTomasone]

I faced a similar problem. However, in my experience, the return format { count: number } occurred exclusively when the update object included the primary key. Works fine with unique keys.

[joshbedo]

Running into the same issue using mysql. if it doesnt exist it returns the record if it exists it returns a count of updated records.

Prisma ^5.9.1

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

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

[janpio]

Please open a new issue with a full reproduction if you still get this problem with a recent version of Prisma. We will not further investigate this in this closed issue from 2022. Thank you.