DT
Drizzle Team•3y ago
sexnine

[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
alexblokh•3y ago
cc: @Dan Kochetov
bomber
bomber•3y 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
Dan•3y 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
kane•3y ago
woah thanks it helped
Louistiti
Louistiti•3y ago
I think you should explain this in the docs...
Dan
Dan•3y ago
yep, we have it on the task list

Did you find this page helpful?