sqlite: `where` queries with `Date` are broken

[reiv]

Describe the Bug

TL;DR: When using the sqlite adapter, where queries involving JavaScript Dates do not work when using the Local or GraphQL API.

Core problem

The sqlite adapter creates timestamp-type fields as text (ISO 8601 date string) columns in the database schema. However, when a Date gets passed as a parameter for an SQL expression in drizzle-orm/libsql, it gets converted to an integer.

You can verify this behavior like this:

const db = drizzle({ connection: {
    url: ':memory:',
}});

const debugQuery = db.run(
  sql`SELECT typeof(${new Date('2025-01-01')}) AS type, ${new Date('2025-01-01')} AS value;`
);

console.log(await debugQuery);

This mismatch produces wrong results when comparing the value from the where against the database value. For greater_than, the expression always evaluates to true. In all other cases, it evaluates to false.

Local API

Due to the partially untyped nature of the Local API, it is possible to pass anything into a where clause. Dates do not receive special treatment and are passed as-is to the underlying database adapter.

GraphQL

The built-in DateTimeResolver parses ISO 8601 date strings into JavaScript Dates.

Link to the code that reproduces this issue

https://github.com/payloadcms/payload

Reproduction Steps

Reproducible with the _community test project with no modifications.

PAYLOAD_DATABASE="sqlite" pnpm dev

GraphQL

• Go to the GraphQL playground
• Create a query like this:

query {
  Posts(where: {createdAt: {less_than: "2030-01-01T12:00:00.000Z"}}) {
    docs {
      title
      createdAt
    }
    totalDocs
  }
}

• Observe that no results are returned

Local API

Create a page which accesses the local API like this:

const posts = await payload.find({
  collection: 'posts',
  where: {createdAt: {less_than: new Date('2030-01-01T12:00:00.000Z')}}
});

Which area(s) are affected? (Select all that apply)

db-sqlite

Environment Info

Payload: 3.28.1
Node.js: 22.14.0
Next.js: 15.2.2

[DanRibbens]

Thanks for the issue and opening a PR @reiv.

I think we covered this one by #12656 which needed to query by dates.

Let me know if I'm overlooking anything and we can get this reopened and looked at.