[BUG]: Relations@v2 inserts null for joins that can never result in null.

[cybercoder-naj]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

1.0.0-beta.4

What version of drizzle-kit are you using?

1.0.0-beta.4

Other packages

No response

Describe the Bug

Consider the two schemas below with the relation below:

const user = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unqiue(),
  password: text("password").notNull()
})

const userSession = pgTable("user_sessions", {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => users.id),
  expiresAt: timestamp('expires_at', {
    withTimezone: true,
    mode: 'date'
  }).notNull()
});

const relations = defineRelations({
  user,
  userSession
}, r => ({
  user: {
    sessions: r.many.userSession()
  },
  userSession: {
    user: r.one.user({
      from: r.userSession.userId,
      to: r.user.id
    })
  }
});

When I query the Session with the user,

const query = await db.query.userSession.findFirst({
  where: { id },
  with: { user: true }
})

typeof query === {
  id: string,
  userId: string,
  expiresAt: Date,
  user: {
    id: string,
    email: string,
    password: string,
  } | null  // <- this should not be null
} | undefined

Either

1. The session ID is not found, and the query returns undefined.
2. The session ID is found and the query returns the session ID with a non-null user. Because userId is a non-nullable foreign key to the primary key of another table, we cannot have a session ID for a user that exists, but the user themselves doesn't exist. That is the constraint imposed by the database.

Additional Information

The many case works normally and as expected.

const query = await db.query.user.findFirst({
  where: { id },
  with: { sessions: true }
})

typeof query === {
  id: string,
  email: string,
  password: string,
  sessions: {
    id: string,
    userId: string,
    expiresAt: Date
  }[]
} | undefined