Drizzle Team•2y ago

"Cannot parse date or time" using AWS Data API, aurora postgres

I'm trying drizzle-orm (with sst) and aurora pg thru aws data api, when trying to insert a Date value in a column (timestamp with timezone), I'm getting this error. I wonder if the problem is the way Date is being serialized (looks like it's not using ISO date string?) this shows the response coming from aws-data-api

{
    "errorType": "BadRequestException",
    "errorMessage": "Cannot parse date or time \"Sat, 15 Jul 2023 05:58:12 GMT\"",
    "name": "BadRequestException",
    "$fault": "client",
    "$metadata": {
        "httpStatusCode": 400,
        "requestId": "ef9415d4.......64414",
        "attempts": 1,
        "totalRetryDelay": 0
    },
    "stack": [
        "BadRequestException: Cannot parse date or time \"Sat, 15 Jul 2023 05:58:12 GMT\"",
        "    at de_BadRequestExceptionRes (file:///var/task/packages/functions/src/auth.mjs:32546:21)",
        "    at de_ExecuteStatementCommandError (file:///var/task/packages/functions/src/auth.mjs:32452:19)",
        "    at processTicksAndRejections (node:internal/process/task_queues:96:5)",
        "    at async file:///var/task/packages/functions/src/auth.mjs:26732:20",
        "    at async file:///var/task/packages/functions/src/auth.mjs:25599:18",
        "    at async file:///var/task/packages/functions/src/auth.mjs:27222:38",
        "    at async file:///var/task/packages/functions/src/auth.mjs:24576:22",
        "    at async AwsDataApiPreparedQuery.values (file:///var/task/packages/functions/src/auth.mjs:33520:20)",
        "    at async AwsDataApiPreparedQuery.execute (file:///var/task/packages/functions/src/auth.mjs:33499:18)",
        "    at async upsertUser (file:///var/task/packages/functions/src/auth.mjs:38670:18)"
    ]
}

{
    "errorType": "BadRequestException",
    "errorMessage": "Cannot parse date or time \"Sat, 15 Jul 2023 05:58:12 GMT\"",
    "name": "BadRequestException",
    "$fault": "client",
    "$metadata": {
        "httpStatusCode": 400,
        "requestId": "ef9415d4.......64414",
        "attempts": 1,
        "totalRetryDelay": 0
    },
    "stack": [
        "BadRequestException: Cannot parse date or time \"Sat, 15 Jul 2023 05:58:12 GMT\"",
        "    at de_BadRequestExceptionRes (file:///var/task/packages/functions/src/auth.mjs:32546:21)",
        "    at de_ExecuteStatementCommandError (file:///var/task/packages/functions/src/auth.mjs:32452:19)",
        "    at processTicksAndRejections (node:internal/process/task_queues:96:5)",
        "    at async file:///var/task/packages/functions/src/auth.mjs:26732:20",
        "    at async file:///var/task/packages/functions/src/auth.mjs:25599:18",
        "    at async file:///var/task/packages/functions/src/auth.mjs:27222:38",
        "    at async file:///var/task/packages/functions/src/auth.mjs:24576:22",
        "    at async AwsDataApiPreparedQuery.values (file:///var/task/packages/functions/src/auth.mjs:33520:20)",
        "    at async AwsDataApiPreparedQuery.execute (file:///var/task/packages/functions/src/auth.mjs:33499:18)",
        "    at async upsertUser (file:///var/task/packages/functions/src/auth.mjs:38670:18)"
    ]
}

11 Replies

beneidelOP•2y ago

turned the logger on, and effectively it shows this:

{
    "name": "15",
    "value": {
        "stringValue": "Sat, 15 Jul 2023 06:51:54 GMT"
    },
    "typeHint": "TIMESTAMP"
}
,

{
    "name": "15",
    "value": {
        "stringValue": "Sat, 15 Jul 2023 06:51:54 GMT"
    },
    "typeHint": "TIMESTAMP"
}
,

the query (the updatedAt is the offending line, error goes away if I comment that one)

  const result = await db
    .insert(userTable)
    .values(newUser)
    .onConflictDoUpdate({
      target: userTable.email,
      set: {
        ...newUser,
        email: undefined,
        id: undefined,
        updatedAt: new Date(),
        updatedBy: newUser.updatedBy || newUser.createdBy,
      },
      where: eq(userTable.email, newUser.email),
    })
    .returning()
    .execute();

  const result = await db
    .insert(userTable)
    .values(newUser)
    .onConflictDoUpdate({
      target: userTable.email,
      set: {
        ...newUser,
        email: undefined,
        id: undefined,
        updatedAt: new Date(),
        updatedBy: newUser.updatedBy || newUser.createdBy,
      },
      where: eq(userTable.email, newUser.email),
    })
    .returning()
    .execute();

"drizzle-orm": "^0.27.2",

Jon R•2y ago

I'm getting the same error with the same set up. Did you end up finding a solution?

beneidelOP•17mo ago

nope, I just skipped that field bc it was a small pet project, but have no solution yet using AWS Data API (maybe related to this specific driver)

beneidelOP•16mo ago

I created this as github issue here: https://github.com/drizzle-team/drizzle-orm/issues/1164

GitHub

[BUG]: update timestamp field (using AWS Data API) · Issue #1164 · ...

What version of drizzle-orm are you using? 0.27.2 What version of drizzle-kit are you using? 0.19.5 Describe the Bug I'm trying drizzle-orm (with sst) and aurora pg thru aws data api, when tryi...

kyen99•16mo ago

I just submitted a PR that I think solves this issue. I'd love some feedback if it could be better: https://github.com/drizzle-team/drizzle-orm/pull/1316

GitHub

[AwsDataApi] Handle timestamp by kyen99 · Pull Request #1316 · driz...

Convert ISO or UTC datetime string to AWS Data API date format AWS Data API requires a specific date formatting that is different than the ISO or UTC date string. Per this document: https://docs.aw...

beneidelOP•15mo ago

thank you, let’s hope it makes it into next version. I moved on but happy to at least help test when ready

Dan Claroni•11mo ago

this was working for me for a while, but now this same issue appears to exist with between query filters when the value is a date object and when inserting again:

    return await db.query.table.findMany({
        where: (table, { eq, between }) =>
            and(
                eq(table.fkId, fkId),
                between(table.time, startDate, endDate),
            ),
    });

    return await db.query.table.findMany({
        where: (table, { eq, between }) =>
            and(
                eq(table.fkId, fkId),
                between(table.time, startDate, endDate),
            ),
    });

export const upsert = async (itemToUpsert: NewItem[]) => {
    const upserted = await db
        .insert(table)
        .values(itemToUpsert)
        .returning();

    return upserted;
};

export const upsert = async (itemToUpsert: NewItem[]) => {
    const upserted = await db
        .insert(table)
        .values(itemToUpsert)
        .returning();

    return upserted;
};

i'm using a pg table with the following column: time: timestamp('time', { mode: 'date', withTimezone: true }).notNull(), BadRequestException: Cannot parse date or time " hu, 22 Feb 2024 12:20:00 GMT"

Kapatid•11mo ago

export const sessionTable = pgTable("session", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => userTable.id),
  expiresAt: timestamp("expires_at", {
    mode: "date"
  }).notNull()
})

export const sessionTable = pgTable("session", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => userTable.id),
  expiresAt: timestamp("expires_at", {
    mode: "date"
  }).notNull()
})

I removed the withTimezone property in my timestamp and now everything works and I encountered this problem when trying to use Nextjs + Drizzle + Lucia Auth.

// package.json
"lucia": "^3.0.1",
"drizzle-orm": "^0.29.4",
"@lucia-auth/adapter-drizzle": "^1.0.2"

// package.json
"lucia": "^3.0.1",
"drizzle-orm": "^0.29.4",
"@lucia-auth/adapter-drizzle": "^1.0.2"

Dan Claroni•11mo ago

I’m not sure I want to remove the with timestamp. It appears drizzle is setup to convert date objects to ISO strings and then replace the T and the Z but for me it’s just using toString and still replacing the T, which you see when the T in Thurs is replaced with a space

Kapatid•11mo ago

Yeah I thought about the consequences of removing withTimezone too. Currently I am trying to find a solution in which I don't remove the withTimezone.

Dan Claroni•11mo ago

The problem appears to be in drizzle-orm/pg-core/columns/timestamp.js. The offending code is:

  mapToDriverValue = (value) => {
    return this.withTimezone ? value.toUTCString() : value.toISOString();
  };

  mapToDriverValue = (value) => {
    return this.withTimezone ? value.toUTCString() : value.toISOString();
  };

the toUTCString() doesnt work for the data api, so I am going to make a patch where I just always congvert it to an ISO string. I'm assuming this has other implications for other drivers, but I don't know enough about that

Gaming

Programming

"Cannot parse date or time" using AWS Data API, aurora postgres