create() fails with DateTime type column as part of PRIMARY KEY in mssql server

[shankuljain]

Bug description

When db table has datetime type column as part of primary key, create fails because datetime in Javascript contains milliseconds which are ignored by mssql. Now stored field (without milliseconds) doesn't match with the datetime with milliseconds hence the operation fails.

How to reproduce

Assume db table like which has datetime as PRIMARY KEY

create table test_table (
   time1 datetime not null PRIMARY KEY
)

and prisma insert operation like below -

await prisma.test_table.create(
  {
     data: {time1: new Date()}
  }
)

new Date contains millisecond information which is not supported by mssql server (or perhaps other databases as well). Now, mssql ignores these milliseconds and saves the time after setting milliseconds to zero. Now while trying to fetch the newly inserted record by the date (which contains milliseconds) it doesn't find the match hence rollback the transaction.

A quick hack to make it work for this is to have insert operation like below -

await prisma.test_table.create(
 {
    data: {time1: new Date(new Date().setMilliseconds(0))} // setting milliseconds to zero, so it matches at the time of fetching  newly inserted row.
 }
)

Expected behavior

Prisma should get rid of milliseconds and try to fetch field without milliseconds.

Prisma information

Environment & setup

• OS: Linux
• Database: MSSQL Server
• Node.js version: 16

Prisma Version

3.4.2

[pimeys]

This is correct. It even crashes when just creating a record with milliseconds, because we try to load it back from the database and cannot find any records.

[janpio]

2 similar cases for MySQL: #12783 + #8982

[martin31821]

I'm running into the same issue with a postgresql server, also with a Date as my primary key.