bomber Comments - Answer Overflow

bomber

•Created by sexnine on 7/6/2023 in #help

[Relational Queries] Nullable one-to-one relationship

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

8 replies

Gaming

Programming