drizzle-team / drizzle-team/drizzle-orm

[BUG]: Date with a `.toISOString()` causes an error in the `typeHint` with PostgreSQL

Open
#2,364 2 comments 1 reaction 0 assignees View on GitHub
bug driver/aws-data-api priority
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### What version of `drizzle-orm` are you using?

0.30.10

### What version of `drizzle-kit` are you using?

0.21.4

### Describe the Bug

I'm trying to update a workout with the Date objects startTime and endTime. When I just pass in the dates as they are, they are parsed as a timestamp without the timezone (which is not what my schema needs). When I pass them in as `toISOString()` as below, it throws a 500 error and parses it as a UUID.
```ts
export const tblWorkouts = pgTable("tblWorkouts", {
workoutId: uuid("workoutId").primaryKey().notNull(),
workoutName: text("workoutName").notNull(),
organizationId: uuid("organizationId")
.notNull()
.references(() => tblOrganizations.organizationId),
createdAt: timestamp("createdAt", { withTimezone: true }).notNull(),
updatedAt: timestamp("updatedAt", { withTimezone: true }).notNull(),
deletedAt: timestamp("deletedAt", { withTimezone: true }),
startTime: timestamp("startTime", {
withTimezone: true,
mode: "string",
}).notNull(),
endTime: timestamp("endTime", {
withTimezone: true,
mode: "string",
}).notNull(),
});
```

```ts
const workoutArray = await db
.update(tblWorkouts)
.set({
workoutName,
startTime: sql`${startTime.toISOString()}::timestamptz`,
endTime: sql`${endTime.toISOString()}::timestamptz`,
})
.where(
and(
eq(tblWorkouts.organizationId, organizationId),
eq(tblWorkouts.workoutId, workoutId),
),
)
.returning({
workoutId: tblWorkouts.workoutId,
organizationId: tblWorkouts.organizationId,
workoutName: tblWorkouts.workoutName,
startTime: tblWorkouts.startTime,
endTime: tblWorkouts.endTime,
});
```

```
Workout before save 2024-04-12T17:00:00.000Z 2024-04-13T03:00:00.000Z
Query: update "tblWorkouts" set "workoutName" = :1, "startTime" = :2::timestamptz, "endTime" = :3::timestamptz where ("tblWorkouts"."organizationId" = :4 and "tblWorkouts"."workoutId" = :5) returning "workoutId", "organizationId", "workoutName", "startTime", "endTime" -- params: [{"name":"1","value":{"stringValue":"Random Workout 270"}}, {"name":"2","value":{"stringValue":"2024-04-12T17:00:00.000Z"},"typeHint":"UUID"}, {"name":"3","value":{"stringValue":"2024-04-13T03:00:00.000Z"},"typeHint":"UUID"}, {"name":"4","value":{"stringValue":"09f500b2-14d0-43fa-aaa7-3872935e40ed"}}, {"name":"5","value":{"stringValue":"f4edfbde-2193-4028-971e-b3e3d661aa3e"}}]
500: UnknownError
```

### Expected behavior

I'd expect either mode for the timestamp to work correctly, as in, the timezone should be part of the value portion of the update query on its own, and that it shouldn't parse it as a UUID when it's got the timezone at the correct place.

### Environment & setup

Locally with my local data api layer.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the update using the shown pgTable timestamp definitions and sql interpolation, then inspect the query parameter handling that assigns typeHint. Done means ISO timestamp values with timezone are not classified as UUIDs and timestamp updates work correctly in both modes.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, typescript
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.