DT
Drizzle Teambeneidel

"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)"
    ]
}
B
beneidel•304d 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",
JR
Jon R•298d ago
I'm getting the same error with the same set up. Did you end up finding a solution?
B
beneidel•258d 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)
B
beneidel•255d 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...
K
kyen99•229d 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...
B
beneidel•217d ago
thank you, let’s hope it makes it into next version. I moved on but happy to at least help test when ready
DC
Dan Claroni•81d 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"
K
Kapatid•81d 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"
DC
Dan Claroni•81d 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
K
Kapatid•80d 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.
DC
Dan Claroni•79d 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
Want results from more Discord servers?
Add your server
More Posts
Filtering against a relationI have a orgs table that have a many to many relation with a users table. while querying with the fGet type for select query?Hey guys, is there a way to infer the "select" type for a given table? for example: ```ts async getorderBy related table columnGiven a relational query such as ``` const matchResult = await db.query.matches.findMany({ Soft Delete StrategyI'm a person of eloquent taste; one who prefers the soft delete. With the current feature set of driColumns that not allowed to updatedHi guys, I'm looking for a proper way to declare SQL schema and its `InferModel` types when there'rExecute sql with '?' as parameterHello everyone. Please, how do I execute a sql that uses as a parameter the character '?' ? For examForeign Key Reference to auth Schema Not Generated in CodeI'm having an issue with my Drizzle-ORM code where the foreign key reference to the 'auth' schema isHow can I add a Prefix Index?I migrated from Prisma, and I have a column with `varchar(2000)` which I indexed with `@@index([coluconnect to local postgresDoes anyone knows how to connect to a local pg with neon? I tried: ``` import { neon, neonConfig } defaultNow(), onUpdateNow() not available on the datetime data typeI'm able to build this into my database, but I'm not able to express it through drizzle. What gives?NeonDbError: db error: ERROR: permission denied for schema drizzleCreated a new database on neon.tech today and trying to run migrations ``` // src/db/migrate.ts impHow can I filter on a joined table field (nested value) with the relational query syntax?☝🏻How to implement a where clause on a joined table with the new relation builder?This was my original query, the big problem is that it returns as many rows as there are messages inError when deploying migrations with GH Action in Cloudflare D!I'm trying to run migrations within a turborepo against a Cloudflare D1 instance. The command to run0.27.2 mysql:pushmysql:push in 0.27.2 is trying to alter my primaryKey with no schema changes. it also seems to try aCreating an abstract base repositoryHi, I'm trying to create a base repository for my application but can't make it work. Is it even posXCannot be named without a reference to '../../../../../db/node_modules/drizzle-orm/driver.d-f4e534I have a monorepo with a dedicated database package that handles everything around the database, andHow to do conditional joins with the query builder?Hi, I'm looking for a way to do conditional join's without working with AnyPgSelect. Thanks in advanCustom column type with default not working?I tried to add a new column with a custom type and a default value. but for some reason when I triedtesting best practicesCan anyone share how they're writing tests that involve drizzle? Looking for something similar to th