[BUG]: Invalid date from relational queries

[joris-gallot]

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.17.6

Describe the Bug

All dates from relational queries inside with are Invalid date

const project = await db.query.projects.findFirst({
  columns: {
    id: true,
  },
  with: {
    deployments: {
      columns: {
        id: true,
        startedAt: true,
      },
      with: {
        commit: {
          columns: {
            id: true,
            createdAt: true,
          },
        },
      },
    },
  },
})

const deployment = await db.query.deployments.findFirst({
  where: eq(deployments.id, project.deployments[0].id),
  with: {
    commit: {
      columns: {
        id: true,
        createdAt: true,
      },
    },
  },
  columns: {
    id: true,
    startedAt: true,
  },
});

// console.log(project);
{
  id: 1,
  createdAt: '2023-07-15T19:20:18.694Z',
  deployments: [{ 
    id: 1,
    startedAt: Invalid Date,
    commit: {
      id: 1,
      createdAt: Invalid Date
    }
  }]
}

// console.log(deployment);
{
  id: 1,
  startedAt: '2023-07-15T19:20:19.490Z',
  commit: {
    id: 1,
    createdAt: Invalid Date
  }
}

Expected behavior

// console.log(project);
{
  id: 1,
  createdAt: '2023-07-15T19:20:18.694Z',
  deployments: [{ 
    id: 1,
    startedAt: '2023-07-15T19:20:19.490Z',
    commit: {
      id: 1,
      createdAt: '2023-05-10T16:15:21.912Z'
    }
  }]
}

// console.log(deployment);
{
  id: 1,
  startedAt: '2023-07-15T19:20:19.490Z',
  commit: {
    id: 1,
    createdAt: '2023-05-10T16:15:21.912Z'
  }
}

Environment & setup

Projects model

import { users } from '@/db/models/users';
import { InferModel } from 'drizzle-orm';
import { pgTable, serial, timestamp, varchar, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { projectEnvironments } from './project_environments';
import { repositories } from './repositories';

const PROJECT_TYPE = ['client', 'server'] as const;

export type ProjectType = (typeof PROJECT_TYPE)[number];

export const projects = pgTable('projects', {
  id: serial('id').primaryKey(),
  name: varchar('name').notNull(),
  slug: varchar('slug').notNull(),
  type: varchar('type', { enum: PROJECT_TYPE }).notNull(),
  userId: integer('user_id')
    .references(() => users.id)
    .notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export type Project = InferModel<typeof projects>;

export const projectsRelations = relations(projects, ({ many }) => ({
  deployments: many(deployments),
}));

Deployments model

import { integer, pgTable, serial, text, timestamp, varchar } from 'drizzle-orm/pg-core';
import { projects } from './projects';

import { InferModel, relations } from 'drizzle-orm';
import { commits } from './commits';

const DEPLOYMENT_STATUS = ['doing', 'error', 'success'] as const;
export type DeploymentStatus = (typeof DEPLOYMENT_STATUS)[number];

export const deployments = pgTable('deployments', {
  id: serial('id').primaryKey(),
  slug: varchar('slug').notNull(),
  status: varchar('status', { enum: DEPLOYMENT_STATUS }).notNull().default('doing'),
  projectId: integer('project_id')
    .references(() => projects.id)
    .notNull(),
  commitId: integer('commit_id').references(() => commits.id),
  endedAt: timestamp('ended_at').defaultNow(),
  startedAt: timestamp('started_at').defaultNow().notNull(),
});

export type Deployment = InferModel<typeof deployments>;

export const deploymentRelations = relations(deployments, ({ one }) => ({
  project: one(projects, {
    fields: [deployments.projectId],
    references: [projects.id],
  }),
  commit: one(commits, {
    fields: [deployments.commitId],
    references: [commits.id],
  }),
}));

Commits model

import { integer, pgTable, serial, timestamp, varchar } from 'drizzle-orm/pg-core';
import { deployments } from './deployments';
import { InferModel, relations } from 'drizzle-orm';
import { users } from './users';

export const commits = pgTable('commits', {
  id: serial('id').primaryKey(),
  htmlUrl: varchar('html_url').notNull(),
  message: varchar('message').notNull(),
  sha: varchar('sha').notNull(),
  author: varchar('author').notNull(),
  userId: integer('user_id').references(() => users.id),
  branch: varchar('branch').notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export type Commit = InferModel<typeof commits>;

export const commitRelations = relations(commits, ({ many, one }) => ({
  deployments: many(deployments),
}));

[mhchen]

Also seeing this! My first impression of Drizzle has been great overall but this feels like it might be a showstopper for my ability to migrate over.

[Angelelz]

Can you please confirm this is still an issue? if it is, a reproduction repo would be great!

[mhchen]

@Angelelz thanks for responding, and sorry for the late response! This was user error, but I would love better error messages. I was using knex/Objection.js before this, which did not distinguish between timestamp in postgres and timestamp with time zone. I updated all my timestamps to use { withTimezone: true } and this problem went away.

@joris-gallot FYI it looks like this might be your issue as well!

[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