D1 DateTime type does not work

[brunocascio]

Bug description

By using DateTime field I get the following error when I make a query:

PrismaClientKnownRequestError: 
Invalid `prisma.example.findMany()` invocation:


Inconsistent column data: Conversion failed: number must be an integer in column 'createdAt', got '1709246273880.0'
    at In.handleRequestError (/Users/auser/projects/myproject/myproject-remix/node_modules/@prisma/client/runtime/library.js:122:6854)
    at In.handleAndLogRequestError (/Users/auser/projects/myproject/myproject-remix/node_modules/@prisma/client/runtime/library.js:122:6188)
    at In.request (/Users/auser/projects/myproject/myproject-remix/node_modules/@prisma/client/runtime/library.js:122:5896)
    at async l (/Users/auser/projects/myproject/myproject-remix/node_modules/@prisma/client/runtime/library.js:127:10871)

In fails both locally and at the edge (cloudflare)

How to reproduce

1. Define the model

model Example {
  id            String         @id @default(cuid())
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt
}

2. Apply migrations
3. Load some data without overriding createdAt updatedAt fields
4. Run the query

prisma.example
  .findMany()
  .then(console.log)

5. Error will be thrown

Expected behavior

Timestamp should be properly parsed as a Date

Prisma information

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

datasource db {
  provider = "sqlite"
  url = env("DATABASE_URL")
}

model Example {
  id            String         @id @default(cuid())
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt
}

import { PrismaClient } from '@prisma/client'
import { PrismaD1 } from '@prisma/adapter-d1'
import { DefaultArgs } from '@prisma/client/runtime/library'

let prisma: PrismaClient<{ adapter: PrismaD1 }, never, DefaultArgs>

export default (d1: D1Database) => {
  if (!prisma) {
    const adapter = new PrismaD1(d1, 'true')
    prisma = new PrismaClient({ 
      adapter,
    })
  }
  return prisma
}

Environment & setup

• OS: Cloudflare pages+worker (wrangler deploy)
• Stack: Remix
• Database: Cloudlare D1
• Node.js version: 20

Prisma Version

prisma                  : 5.11.0
@prisma/client          : 5.11.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v20.11.1
Query Engine (Node-API) : libquery-engine efd2449663b3d73d637ea1fd226bafbcf45b3102 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli efd2449663b3d73d637ea1fd226bafbcf45b3102 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.11.0-15.efd2449663b3d73d637ea1fd226bafbcf45b3102
Default Engines Hash    : efd2449663b3d73d637ea1fd226bafbcf45b3102
Studio                  : 0.499.0
Preview Features        : driverAdapters

[Jolg42]

@brunocascio I'm curious to know what data you have in your database and what's the SQL you executed to create the database schema.

Because I could not reproduce this 🤔

See https://d1-tutorial.prisma.workers.dev/
I tried with the following on a Worker (I could try again with Pages, but that should technically be the same)

		await prisma.example.create({
			data: {},
		});
		const result = await prisma.example.findMany();
		console.debug(result);

-- CreateTable
CREATE TABLE "Example" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL
);

# locally
rm ./schema.sql && npx prisma migrate diff --from-empty --to-schema-datamodel ./prisma/schema.prisma --script > ./schema.sql
npx wrangler d1 execute DB --local --file=./schema.sql
wrangler dev

# and remotely
npx wrangler d1 execute DB --remote --file=./schema.sql
wrangler deploy

[Jolg42]

Here is the data from D1 interface

[brunocascio]

Thanks @Jolg42 for taking a look into it!

Aparrently I had an issue with the way I was seeding the database. So by moving the seed script I've built to an endpoint (for testing purposes) seems to solve the problem. Now is working correctly!

Thanks!

EDIT: Here's my data before solving the issue 🤦 (datetime was stored as integers rather than datetimes)

[Jolg42]

@brunocascio Nice! I want to understand something here, how did you have integers here?

I'm curious to know what was the seed script you used for example.

[brunocascio]

I was using `Date.now()` in the seed script El El jue, 14 mar 2024 a la(s) 06:15, Joël Galeran ***@***.***> escribió:

…

@brunocascio <https://github.com/brunocascio> Nice! I want to understand something here, how did you have integers here? I'm curious to know what was the seed script you used for example. — Reply to this email directly, view it on GitHub <#23479 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AASJNMYRLMBHQVG5IXZOXUTYYFTEJAVCNFSM6AAAAABEUOKDYCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJWHE4DINRTG4> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[Jolg42]

@brunocascio Ok, but what was the code doing? Using the D1 adapter & prisma.model.create(.... date: new Date() ....)?

Or using the D1 database API directly, maybe?

[brunocascio]

@brunocascio Ok, but what was the code doing? Using the D1 adapter & prisma.model.create(.... date: new Date() ....)?

Or using the D1 database API directly, maybe?

I was not able to use prisma.model.create because didn't get how to do it with prisma mgirate/seed commands. I basically run it locally and then upload the data by using the d1 --import.

Sorry, it was like 2 weeks ago and didn't versionate that part so I lost the test script :(

So, don't think it was an issue with prisma/adapter itself

[Jolg42]

Thanks @brunocascio! I was thinking something along these lines, but I wanted to check with you.