Bug description

When using @prisma/adapter-better-sqlite3 with timestampFormat: "unixepoch-ms", all DateTime operations return Invalid Date (not just aggregates, but basic CRUD operations like create(), findMany(), findFirst(), etc.)

The same operations work correctly with the default timestampFormat: "iso8601".

This effectively makes the timestampFormat: "unixepoch-ms" option unusable.

Severity

🚨 Critical: Data loss, app crash, security issue

Reproduction

https://github.com/mmvsk/prisma-adapter-better-sqlite3-datetime-unixepoch-bug-reproduction

git clone https://github.com/mmvsk/prisma-adapter-better-sqlite3-datetime-unixepoch-bug-reproduction
cd prisma-adapter-better-sqlite3-datetime-unixepoch-bug-reproduction
npm install
npm test

Output:

@prisma/adapter-better-sqlite3 DateTime Bug Reproduction
unixepoch-ms format returns Invalid Date

Test: timestampFormat: "iso8601"
  create() -> {"id":1,"title":"test","createdAt":"2025-12-09T07:00:00.000Z"}
  PASS createdAt is valid Date
  aggregate() -> {"_min":{"createdAt":"2025-12-09T07:00:00.000Z"},...}
  PASS _min/_max createdAt are valid Dates

Test: timestampFormat: "unixepoch-ms"
  create() -> {"id":1,"title":"test","createdAt":null}
  FAIL createdAt is Invalid Date
  aggregate() -> {"_min":{"createdAt":null},"_max":{"createdAt":null}}
  FAIL _min.createdAt is Invalid Date

Expected vs. Actual Behavior

Expected: All DateTime fields return valid Date objects when using timestampFormat: "unixepoch-ms", matching the behavior of iso8601 (default).

Actual: All DateTime operations return Invalid Date:

• create() returns Invalid Date
• findMany(), findFirst(), findUnique() return Invalid Date
• aggregate() with _min/_max returns Invalid Date

Frequency

Consistently reproducible

Does this occur in development or production?

Both development and production

Is this a regression?

Unknown. I haven't tested previous versions. The timestampFormat: "unixepoch-ms" option may have never worked correctly with actual INTEGER columns storing unix timestamps.

Workaround

No workaround found.

The adapter cannot determine that an INTEGER column should be treated as DateTime; it lacks schema information from the Query Engine.

I've opened a feature request to address this architectural limitation: #28891.

Prisma Schema & Queries

generator client {
  provider        = "prisma-client-js"
  output          = "./generated/client"
  previewFeatures = ["driverAdapters"]
}

datasource db {
  provider = "sqlite"
}

model Post {
  id        Int      @id
  title     String
  createdAt DateTime @default(now())
}

import { PrismaBetterSqlite3 } from '@prisma/adapter-better-sqlite3'
import { PrismaClient } from './prisma/generated/client'

const adapter = new PrismaBetterSqlite3(
  { url: ':memory:' },
  { timestampFormat: 'unixepoch-ms' }
)
const prisma = new PrismaClient({ adapter })

// Create table with INTEGER column for unix epoch ms
await prisma.$executeRaw`
  CREATE TABLE Post (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    createdAt INTEGER NOT NULL DEFAULT (unixepoch('now') * 1000)
  )
`

const post = await prisma.post.create({ data: { id: 1, title: 'test' } })
console.log(post.createdAt) // Invalid Date

Prisma Config

N/A: using driver adapter directly.

Logs & Debug Info

Not needed: issue is clearly visible in reproduction output above.

Environment & Setup

• OS: Linux (also reproducible on macOS/Windows)
• Database: SQLite (in-memory via better-sqlite3)
• Node.js version: 24.x LTS

Prisma Version

prisma                  : 7.1.0
@prisma/client          : 7.1.0
@prisma/adapter-better-sqlite3 : 7.1.0

Root Cause Analysis

Looking at the adapter's conversion.ts, the mapRow function correctly converts numeric timestamps to ISO strings when columnTypes[i] === ColumnTypeEnum.DateTime:

// packages/adapter-better-sqlite3/src/conversion.ts:175-178
if (['number', 'bigint'].includes(typeof value) && columnTypes[i] === ColumnTypeEnum.DateTime) {
  result[i] = new Date(Number(value)).toISOString()
  continue
}

However, the column type detection relies on SQLite's declared type metadata. When using unixepoch-ms:

1. The column is declared as INTEGER in SQLite (to store unix timestamps)
2. mapDeclType("INTEGER") returns ColumnTypeEnum.Int32, not DateTime
3. The DateTime conversion is skipped
4. The BigInt is converted to string: "1736939400000"
5. Prisma tries to parse this string as a date, resulting in Invalid Date

The adapter has no way to know that an INTEGER column should be treated as DateTime; it lacks schema information from the Query Engine.

Possible Solutions

1. Schema-aware column mapping: If the adapter knew which columns are DateTime fields from the Prisma schema, it could override the SQLite declared type. However, adapters currently don't receive schema information.
2. Interface enhancement: The Query Engine knows the expected output types from the Prisma schema. If this information were passed to adapters via SqlQuery.columnTypes, they could correctly handle all columns regardless of the underlying SQLite storage type. I've described this in detail in Add output columnTypes to SqlQuery for reliable type handling in Driver Adapters #28891.
3. Heuristic detection: Detect INTEGER values in timestamp range and convert to DateTime when unixepoch-ms is set. However, this could cause issues with actual integer columns.

I'd be curious to hear if the team sees other solutions.

Related

• Feature request for interface enhancement: Add output columnTypes to SqlQuery for reliable type handling in Driver Adapters #28891
• Similar issue in community adapter: docs: document aggregate DateTime limitation with unixepoch-ms mmvsk/prisma-adapter-bun-sqlite#2