Drizzle Team•11mo ago

Can't edit datetime column in drizzle studio "Error: value.toISOString is not a function"

So I'm seeing my database with some data, and for the two datetime fields I've had a strange issue where I can ONLY submit as a javascript date object, even though in the schema it shows multiple options. And then when I save as a JS Date and go into Drizzle Studio and try to edit it (even changing a single hour or minute) I get this error when I try and save:

Error
value.toISOString is not a function

Error
value.toISOString is not a function

And here's an example value of what is saved in the column 2024-02-07T08:17:36.000Z Using PlanetScale as a DB, project started with create-t3-app 2 weeks ago from my package.json "drizzle-orm": "^0.29.3", "drizzle-kit": "^0.20.9", schema file (excerpt of bookings table)

export const bookings = createTable(
  "bookings",
  {
    id: varchar("id", { length: 36 }).primaryKey(),
    locationId: varchar("location_id", { length: 36 }).notNull(),
    customerName: varchar("customer_name", { length: 256 }).notNull(),
    customerEmail: varchar("customer_email", { length: 256 }).notNull(),
    customerPhone: varchar("customer_phone", { length: 50 }).notNull(),
    startTime: datetime("start_time").notNull(),
    endTime: datetime("end_time").notNull(),
    totalCost: decimal("total_cost", { precision: 10, scale: 2 }),
    taxAmount: decimal("tax_amount", { precision: 10, scale: 2 }),
    status: varchar("status", { length: 50 }),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updated_at").onUpdateNow(),
  },
  (table) => ({
    locationIdIdx: index("location_id_idx").on(table.locationId),
  }),
);

export const bookings = createTable(
  "bookings",
  {
    id: varchar("id", { length: 36 }).primaryKey(),
    locationId: varchar("location_id", { length: 36 }).notNull(),
    customerName: varchar("customer_name", { length: 256 }).notNull(),
    customerEmail: varchar("customer_email", { length: 256 }).notNull(),
    customerPhone: varchar("customer_phone", { length: 50 }).notNull(),
    startTime: datetime("start_time").notNull(),
    endTime: datetime("end_time").notNull(),
    totalCost: decimal("total_cost", { precision: 10, scale: 2 }),
    taxAmount: decimal("tax_amount", { precision: 10, scale: 2 }),
    status: varchar("status", { length: 50 }),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updated_at").onUpdateNow(),
  },
  (table) => ({
    locationIdIdx: index("location_id_idx").on(table.locationId),
  }),
);

When I hover over datetime I see

(alias) datetime<"start_time", "string" | "date">(name: "start_time", config?: MySqlDatetimeConfig<"string" | "date"> | undefined): MySqlDateTimeBuilderInitial<"start_time">
import datetime

(alias) datetime<"start_time", "string" | "date">(name: "start_time", config?: MySqlDatetimeConfig<"string" | "date"> | undefined): MySqlDateTimeBuilderInitial<"start_time">
import datetime

However, when I try and save this in any way other than a javascript date (either new Date() or using luxon .toJSDate()) I get an error in my db insert values, here's where I've tried to return them as .toISO with luxon for example. For some reason the error shows under the id key and it took me a long time to debug that it was related to the startTime and endTime values.

No overload matches this call.
  Overload 2 of 2, '(values: { id: string | SQL<unknown> | Placeholder<string, any>; locationId: string | SQL<unknown> | Placeholder<string, any>; customerName: string | SQL<unknown> | Placeholder<...>; ... 8 more ...; status?: string | ... 3 more ... | undefined; }[]): MySqlInsertBase<...>', gave the following error.
    Object literal may only specify known properties, and 'id' does not exist in type '{ id: string | SQL<unknown> | Placeholder<string, any>; locationId: string | SQL<unknown> | Placeholder<string, any>; customerName: string | SQL<unknown> | Placeholder<...>; ... 8 more ...; status?: string | ... 3 more ... | undefined; }[]'.ts(2769)
Codeium: Explain Problem

(property) id: string | SQL<unknown> | Placeholder<string, any>

No overload matches this call.
  Overload 2 of 2, '(values: { id: string | SQL<unknown> | Placeholder<string, any>; locationId: string | SQL<unknown> | Placeholder<string, any>; customerName: string | SQL<unknown> | Placeholder<...>; ... 8 more ...; status?: string | ... 3 more ... | undefined; }[]): MySqlInsertBase<...>', gave the following error.
    Object literal may only specify known properties, and 'id' does not exist in type '{ id: string | SQL<unknown> | Placeholder<string, any>; locationId: string | SQL<unknown> | Placeholder<string, any>; customerName: string | SQL<unknown> | Placeholder<...>; ... 8 more ...; status?: string | ... 3 more ... | undefined; }[]'.ts(2769)
Codeium: Explain Problem

(property) id: string | SQL<unknown> | Placeholder<string, any>

I'm very confused here, from what I've read online I should be able to store ISO inside of datetime?

4 Replies

hopesixOP•11mo ago

I realized that I never specified mode on the datetime columns, but they should have defaulted to 'string' anyways. I also connected via Sequel Ace and was able to edit and save a change in start_time, the display of the columns is quite different in Sequel Ace (see my next post in this thread for screenshots) However, if I have mode: "string" set for startTime: datetime("start_time", { mode: "string" }).notNull() Then I can't seem to insert into my db as a new Date(), Luxon's toJSDate() or as an toISO() or even just a string like temp. As soon as I specified mode: string I seem to have lost the ability to insert bookings at all with Drizzle.

hopesixOP•11mo ago

After looking at how it was displayed in Sequel Ace, I realized the values that I was seeing in Drizzle Studio weren't correct for some reason. I've been able to save it if I change the format to match sequel ace before I run the insert. IE. '2024-02-07 20:06:52' instead of '2024-02-07T20:06:52.000Z' 3 screenshots attached. Of Sequel Ace (correct), Drizzle Studio (wrong?), and the format that lets me insert as a string instead of JS Date value. Also worth mentioning, I tried updating an existing column in Drizzle Studio with the working format 2024-02-07 20:06:52 and it still gave me the ISOString error.

blakelockbrown•10mo ago

I'm having the same issue, and the format @hopesix showed doesn't work for me either :/

hopesixOP•10mo ago

So I managed to edit the datetime inside of sequel ace connecting it to the db It's specifically an issue with how Drizzle Studio is displaying the Date column, it's altering it via JS

Gaming

Programming

Can't edit datetime column in drizzle studio "Error: value.toISOString is not a function"