upsert(): Unique constraint on DateTime sqlite

[moishinetzer]

Bug description

model BusinessAnalytics {
  id         String   @id @default(cuid())
  business   Business @relation(fields: [businessId], references: [id])
  businessId String
  startDate  DateTime

  whatsappCount  Int @default(0)
  phoneCount     Int @default(0)
  emailCount     Int @default(0)
  instagramCount Int @default(0)
  websiteCount   Int @default(0)

  @@unique([businessId, startDate])
}

How to reproduce

export async function logBusinessAnalytics({
  id,
  whatsapp,
  phone,
  email,
  instagram,
  website,
}: Pick<Business, "id"> & AnalyticsMethods) {
  // Increments the count of the given method
  return prisma.businessAnalytics.upsert({
    where: {
      businessId_startDate: {
        businessId: id,
        startDate: startOfWeek(new Date()).toISOString(),
      },
    },
    create: {
      businessId: id,
      whatsappCount: whatsapp ? 1 : 0,
      phoneCount: phone ? 1 : 0,
      emailCount: email ? 1 : 0,
      instagramCount: instagram ? 1 : 0,
      websiteCount: website ? 1 : 0,
      startDate: startOfWeek(new Date()).toISOString(),
    },
    update: {
      whatsappCount: whatsapp ? { increment: 1 } : undefined,
      phoneCount: phone ? { increment: 1 } : undefined,
      emailCount: email ? { increment: 1 } : undefined,
      instagramCount: instagram ? { increment: 1 } : undefined,
      websiteCount: website ? { increment: 1 } : undefined,
    },
  });
}

I have an analytics model which increments a count of whenever a user clicks on a certain contact method.

Expected behavior

When calling this upsert function, the correct method is incremented

Prisma information

PrismaClientKnownRequestError: 
Invalid `prisma.businessAnalytics.upsert()` invocation:

  Unique constraint failed on the fields: (`businessId`,`startDate`)
    at RequestHandler.handleRequestError (C:\Users\Moish\Documents\Repos\instabis\node_modules\@p
risma\client\runtime\index.js:28658:13)
    at RequestHandler.request (C:\Users\Moish\Documents\Repos\instabis\node_modules\@prisma\clien
t\runtime\index.js:28640:12)
    at consumer (C:\Users\Moish\Documents\Repos\instabis\node_modules\@prisma\client\runtime\inde
x.js:29618:18)
    at consumer (C:\Users\Moish\Documents\Repos\instabis\node_modules\@prisma\client\runtime\inde
x.js:29618:18)
    at PrismaClient._request (C:\Users\Moish\Documents\Repos\instabis\node_modules\@prisma\client
\runtime\index.js:29639:16)
    at action4 (C:\Users\Moish\Documents\Repos\instabis\app\routes\actions\analytics.tsx:24:3)   
    at C:\Users\Moish\Documents\Repos\instabis\node_modules\@sentry\remix\cjs\utils\instrumentServer.js:130:13
    at Object.callRouteAction (C:\Users\Moish\Documents\Repos\instabis\node_modules\@remix-run\server-runtime\dist\data.js:40:14)
    at handleResourceRequest (C:\Users\Moish\Documents\Repos\instabis\node_modules\@remix-run\server-runtime\dist\server.js:442:14)
    at requestHandler (C:\Users\Moish\Documents\Repos\instabis\node_modules\@remix-run\server-runtime\dist\server.js:42:18)
    at C:\Users\Moish\Documents\Repos\instabis\node_modules\@sentry\remix\cjs\utils\instrumentServer.js:200:16
    at C:\Users\Moish\Documents\Repos\instabis\node_modules\@remix-run\express\dist\server.js:39:22 {
  code: 'P2002',
  clientVersion: '4.1.0',
  meta: { target: [ 'businessId', 'startDate' ] }
}

Environment & setup

• OS: Windows
• Database: SQLite
• Node.js version: v18.6.0

Prisma Version

Shown above

[janpio]

Will calling logBusinessAnalytics once exhibit this behavior?
What is an example function call to reproduce this?

[Jolg42]

Probably related to
#9972
#3242

[pimeys]

I cannot reproduce this in any way. That doesn't mean it is not an issue, because I can for sure reproduce other similar issues. I tried to take the inputs from here and use them in the reproduction of the other similar issue. Never got any errors with this data model.

I still highly consider this bug to exist. Whoever is fixing it should take a reproduction from the other similar issues for testing.

[garrensmith]

Prisma Upserts are very powerful and support very nested upserts. This can lead to a Unique constraint error being thrown. We have updated our docs with why this happens and what to do in that situation.
We have also added support for INSERT .. ON CONFLICT .. UPDATE see the docs on when it will be used.

[Bassadin]

I believe I have a similar case:

model DeviceGPSDatapoint {
    id                 Int                  @id @default(autoincrement())
    timestamp          DateTime
    device             Device               @relation(fields: [deviceId], references: [deviceId])
    deviceId           String
    latitude           Float
    longitude          Float
    altitude           Float
    hdop               Float
    ttnMapperDatapoint TtnMapperDatapoint[]

    @@unique([deviceId, timestamp])
    @@index([deviceId, timestamp])
}

const deviceGPSDatapoint = await this.prisma.deviceGPSDatapoint.upsert({
                    where: {
                        deviceId_timestamp: {
                            deviceId: eachDeviceSubscription.deviceId,
                            timestamp: eachTTNMapperAPIDatapoint.time,
                        },
                    },
                    create: {
                        device: { connect: { deviceId: eachDeviceSubscription.deviceId } },
                        timestamp: eachTTNMapperAPIDatapoint.time,
                        latitude: eachTTNMapperAPIDatapoint.latitude,
                        longitude: eachTTNMapperAPIDatapoint.longitude,
                        altitude: eachTTNMapperAPIDatapoint.altitude,
                        hdop: eachTTNMapperAPIDatapoint.hdop,
                    },
                    update: {},
                });

Error:

Unique constraint failed on the fields: (`deviceId`,`timestamp`)
    at fn.handleRequestError (C:\Users\basti\Desktop\ttn-grafana-stack\ttnmapper-reader\node_modules\.pnpm\@prisma+client@4.12.0_prisma@4.12.0\node_modules\@prisma\client\runtime\library.js:174:6477)
    at fn.handleAndLogRequestError (C:\Users\basti\Desktop\ttn-grafana-stack\ttnmapper-reader\node_modules\.pnpm\@prisma+client@4.12.0_prisma@4.12.0\node_modules\@prisma\client\runtime\library.js:174:5907)
    at fn.request (C:\Users\basti\Desktop\ttn-grafana-stack\ttnmapper-reader\node_modules\.pnpm\@prisma+client@4.12.0_prisma@4.12.0\node_modules\@prisma\client\runtime\library.js:174:5786)
    at async t._request (C:\Users\basti\Desktop\ttn-grafana-stack\ttnmapper-reader\node_modules\.pnpm\@prisma+client@4.12.0_prisma@4.12.0\node_modules\@prisma\client\runtime\library.js:177:10477)
    at async Function.getNewTTNMapperDataForSubscribedDevices (C:\Users\basti\Desktop\ttn-grafana-stack\ttnmapper-reader\src\scheduledFunctions\getNewTTNMapperData.ts:29:44) {
  code: 'P2002',
  clientVersion: '4.12.0',
  meta: { target: [ 'deviceId', 'timestamp' ] }
}

[Bassadin]

I fixed it by adding @db.Timestamp(6) to my timestamp field.
My timestamps have precision 6 (2023-04-02T10:13:25.915541Z) while Prisma's default precision for Postgres is 3.
Seems like the where part of the upsert() query checks only the non-truncated timestamp whereas the actually generated query inserts the timestamp as-is, making it run into the uniqueness constraint.
I'm not totally sure though.
If needed, I can try to create a reproduction code.

[janpio]

That would be amazing. Please post it as a new issue, that is much easier for us to handle. Thanks!