[BUG]: timestamp with mode string is returned as Date object instead of string

[noskovvkirill]

What version of drizzle-orm are you using?

0.27.0

What version of drizzle-kit are you using?

0.19.2

Describe the Bug

timestamps aren't being inferred as string
Table Schema that we use

export const itemTable = pgTable(
  'item',
  {
    id: bigint('id', { mode: 'number' }).primaryKey(),
    createdAt: timestamp('createdAt', {
      mode: 'string',
      precision: 6,
      withTimezone: true,
    }).notNull(),
    userId: uuid('userId')
      .references(() => userTable.id)
      .notNull(),
    customData: jsonb('customData').default(sql`'{}'::jsonb`),
    media3Id: bigint('media3Id', { mode: 'number' }).references(
      () => media3Table.id,
    ),
    newId: uuid('newId'),
    blockId: bigint('blockId', { mode: 'number' }).references(
      () => blockTable.id,
    ),
    parentItemId: bigint('parentItemId', { mode: 'number' }),
  },
  (item) => {
    return {
      parentReference: foreignKey({
        columns: [item.parentItemId],
        foreignColumns: [item.id],
      }),
      // indexes
      newIdIndex: uniqueIndex('idx_item_newid').on(item.newId),
    };
  },
);

Query

  const result = await db
        .select({
          item: {
            id: itemTable.id,
            createdAt: itemTable.createdAt,
            customData: itemTable.customData,
            newId: itemTable.newId,
            parentItemId: itemTable.parentItemId,
          },
          user: {
            id: userTable.id,
            username: userTable.username,
          },
          media3: {
            ...media3Table,
          },
          block: {
            ...blockTable,
          },
        })
        .from(itemTable)
        .where(eq(itemTable.id, input.id))
        .leftJoin(userTable, eq(userTable.id, itemTable.userId))
        .leftJoin(media3Table, eq(media3Table.id, itemTable.media3Id))
        .leftJoin(blockTable, eq(blockTable.id, itemTable.blockId))
        .limit(1);

Returns createdAt as a date object instead of string.

Expected behavior

Returns createdAt as string

Environment & setup

Vercel / Serverless function

[frostzt]

Hey @noskovvkirill new here wanted to start contributing, so wanted to pick this up! Also it would be fine if I asked for a little guidance if I ever get stuck? 😅

[noskovvkirill]

@frostzt sure! let me know how I can help

[bestickley]

@noskovvkirill, I've run into this issue too. Could you clarify the title and change it to: "[BUG]: timestamp with mode string is returned as Date object instead of string"? The current title makes it seem like it's only a TS issue. This is a runtime issue though.

[noskovvkirill]

@noskovvkirill, I've run into this issue too. Could you clarify the title and change it to: "[BUG]: timestamp with mode string is returned as Date object instead of string"? The current title makes it seem like it's only a TS issue. This is a runtime issue though.

@bestickley good suggestion! I did that!

[pablomikel]

I'm actually seeing the opposite of this!

created_at: timestamp('created_at', { mode: 'date' })
    .notNull(),

^ this is returning an ISO string instead of a date object which doesn't match the expected Date type

I'm wondering if this has something to do with the Planetscale serverless driver...

[bestickley]

@frostzt, I think the fix needs to be made somewhere in here if you have time to take a look.

[bestickley]

My temporary workaround is to use the connection library (postgre.js for me) to transform the dates. See example below of what I'm adding to my Options.

transform: {
    value(value) {
      if (value instanceof Date) {
        return value.toISOString();
      } else {
        return value;
      }
    },
  },

[stunaz]

I have the exact same bug....

[patrikengborg]

I have the same problem as well. The workaround from @bestickley is working, but a fix for this would be nice.

[K-Mistele]

I ran into this same issue - timestamp types with {mode: 'string'}are returned as Date objects. It's inferred as a string by TypeScript, but it's clearly a Date at runtime. This gives me issues when trying to return an object from the schema via getServerSideProps in Next.js, since Dates are not JSON-serializable (a string would be though) - I have to delete the date property from the object before returning it.

[AndriiSherman]

Should be fixed in drizzle-orm@beta. I would appreciate some feedback to confirm whether this issue has been resolved in this tag.

I plan to release it in version 0.30.0 tomorrow or within the next few days; I simply aim to address this substantial set of issues we're encountering. I'll be duplicating this message across all similar issues we're facing.

[AndriiSherman]

Fixed in drizzle-orm@0.30.0

Please check release notes for more info