[Relational Queries] Nullable one-to-one relationship

Hey y'all. I'm using the relational queries feature and have a one-to-one relationship between these two tables.

export const teamsT = mysqlTable("Teams", {
  id: varchar("id", { length: 21 }).primaryKey(),
  name: varchar("name", { length: 40 }).notNull(),
  joinCode: varchar("joinCode", { length: 8 }).notNull(),
});

export const projectsT = mysqlTable("Projects", {
  id: varchar("id", { length: 21 }).primaryKey(),
  teamId: varchar("teamId", { length: 21 }).notNull(),
  name: varchar("name", { length: 40 }).notNull(),
  description: varchar("description", { length: 255 }).notNull(),
  content: text("content").notNull(),
  submitted: boolean("submitted").notNull(),
  submittedTime: timestamp("submittedTime"),
});

export const teamsRelations = relations(teamsT, ({ one, many }) => ({
  project: one(projectsT, {
    fields: [teamsT.id],
    references: [projectsT.teamId],
  }),
}));

export const projectsRelations = relations(projectsT, ({ one, many }) => ({
  team: one(teamsT, {
    fields: [projectsT.teamId],
    references: [teamsT.id],
  }),
}));

export const teamsT = mysqlTable("Teams", {
  id: varchar("id", { length: 21 }).primaryKey(),
  name: varchar("name", { length: 40 }).notNull(),
  joinCode: varchar("joinCode", { length: 8 }).notNull(),
});

export const projectsT = mysqlTable("Projects", {
  id: varchar("id", { length: 21 }).primaryKey(),
  teamId: varchar("teamId", { length: 21 }).notNull(),
  name: varchar("name", { length: 40 }).notNull(),
  description: varchar("description", { length: 255 }).notNull(),
  content: text("content").notNull(),
  submitted: boolean("submitted").notNull(),
  submittedTime: timestamp("submittedTime"),
});

export const teamsRelations = relations(teamsT, ({ one, many }) => ({
  project: one(projectsT, {
    fields: [teamsT.id],
    references: [projectsT.teamId],
  }),
}));

export const projectsRelations = relations(projectsT, ({ one, many }) => ({
  team: one(teamsT, {
    fields: [projectsT.teamId],
    references: [teamsT.id],
  }),
}));

I have this query to fetch a user, their team (if they have one) and their team's project (if they have one).

const result = await db.query.registrationsT.findFirst({
    where: eq(registrationsT.userId, ctx.user.id),
    columns: {
      id: true,
    },
    with: {
      team: {
        columns: {
          id: true,
          name: true,
        },
        with: {
          project: {
            columns: {
              id: true,
            },
          },
        },
      },
    },
  });

const result = await db.query.registrationsT.findFirst({
    where: eq(registrationsT.userId, ctx.user.id),
    columns: {
      id: true,
    },
    with: {
      team: {
        columns: {
          id: true,
          name: true,
        },
        with: {
          project: {
            columns: {
              id: true,
            },
          },
        },
      },
    },
  });

This is the return type of that function.

{
  id: number;
  team: { id: string; name: string; project: { id: string } } | null;
} | undefined

{
  id: number;
  team: { id: string; name: string; project: { id: string } } | null;
} | undefined

The problem is, project could potentially be null if the team does not have a project. I'm not really sure what to do here to tell TS that project can be null. Am I creating the relations correctly or am I doing something wrong? How should I solve this? Thank you in advance for the help and I apologize for the long question.

6 Replies

alexblokh•2y ago

cc: @Dan Kochetov

bomber•2y ago

hi, I ran into the same thing, is there any way to make a nullable one relationship? here's a smaller example schema:

import { sqliteTable, text } from "drizzle-orm/sqlite-core"
import { relations } from "drizzle-orm"

export const user = sqliteTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
})

export const user_settings = sqliteTable("user_settings", {
  id: text("id").primaryKey().references(() => user.id),
  preferred_color_scheme: text("preferred_color_scheme").notNull(),
})

export const user_rel = relations(user, ({ one }) => ({
  // I want to make this relationship nullable
  settings: one(user_settings, {
    fields: [user.id],
    references: [user_settings.id],
  }),
}))

export const user_settings_rel = relations(user_settings, ({ one }) => ({
  user: one(user, {
    fields: [user_settings.id],
    references: [user.id],
  }),
}))

import { sqliteTable, text } from "drizzle-orm/sqlite-core"
import { relations } from "drizzle-orm"

export const user = sqliteTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
})

export const user_settings = sqliteTable("user_settings", {
  id: text("id").primaryKey().references(() => user.id),
  preferred_color_scheme: text("preferred_color_scheme").notNull(),
})

export const user_rel = relations(user, ({ one }) => ({
  // I want to make this relationship nullable
  settings: one(user_settings, {
    fields: [user.id],
    references: [user_settings.id],
  }),
}))

export const user_settings_rel = relations(user_settings, ({ one }) => ({
  user: one(user, {
    fields: [user_settings.id],
    references: [user.id],
  }),
}))

I'm trying to be able to query like this:

const users = db.query.user.findMany({
  with: {
    settings: true,
  },
})

const users = db.query.user.findMany({
  with: {
    settings: true,
  },
})

and get typeof users:

{
    // ...
    settings: { id: string; preferred_color_scheme: string } | undefined;
}[]

{
    // ...
    settings: { id: string; preferred_color_scheme: string } | undefined;
}[]

instead of:

{
    // ...
    settings: { id: string; preferred_color_scheme: string };
}[]

{
    // ...
    settings: { id: string; preferred_color_scheme: string };
}[]

my actual use case is an organization table (the user in the example) that can optionally have a config per country, so there are other tables like argentina_organization and colombia_organization with fks to organization

Dan•2y ago

you should only provide fields and references on the side that "knows" about both sides of the relation - i.e. in your case, on the users_settings side. The one on the users side should be just settings: one(user_settings). I think in that case it'll be recognized as nullable. cc: @sexnine

kane•2y ago

woah thanks it helped

Louistiti•15mo ago

I think you should explain this in the docs...

Dan•15mo ago

yep, we have it on the task list

Gaming

Programming

[Relational Queries] Nullable one-to-one relationship