drizzle-team / drizzle-team/drizzle-orm
[BUG]: Date with a `.toISOString()` causes an error in the `typeHint` with PostgreSQL
- 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
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