How to create a nullable/optional one-to-many foreign key relation? / Is drizzle's help bot hallucinating answers?

[j-fdion]

Hi, I was searching the docs on how to create nullable one-to-many relationships and I've asked the bot for help. This was it's answer:

//////

In Drizzle ORM, you can define optional relationships in a one-to-many context by allowing the foreign key on the "many" side to be nullable. This means that the foreign key column can contain null values, indicating that the relationship is optional.

Here's an example of how to set up an optional one-to-many relationship in Drizzle ORM, where the foreign key in the "many" side might be null:

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content'),
  // authorId is nullable, indicating an optional relationship
  authorId: integer('author_id').references(() => users.id).nullable(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

In this example:

• posts.authorId is defined as an optional (nullable) foreign key referencing users.id.
• The relations function is used to define the one-to-many relationship between users and posts (1) .

By allowing authorId to be nullable, you indicate that a post might not have an associated author, making the relationship optional.

: Drizzle Queries

/////

The nullable() method doesn't exist in the docs and is not suggested in vscode intellisense.

How can I define such a relation in drizzle?

[onra2]

got the same problem

[hoangnhatfe]

I got same problem

[j-fdion]

I deleted my database/restarted my migrations then created my initial migration and I don't have this problem anymore. I didn't change my schemas/relations.

Using the latest beta version.

I'm kinda confused about this.

Still the 'help' bot is not working properly. All those A.I help bots are a waste of time for anything more than trivial answers 🤷‍♂️.

The example is working fine:
https://orm.drizzle.team/docs/rqb#one-to-many

[jackkrone]

Same problem. On drizzle-kit v0.23.0 and drizzle-orm v0.32.1. The generated SQL for a one-to-many relation includes NOT NULL.

In the schema: userId: serial("user_id").references(() => user.id)
In the generated SQL: user_id" serial NOT NULL

In the docs here, an example shows explicitly setting a foreign key to notNull().

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  name: text('name'),
  author: integer('author').references(() => users.id, {onDelete: 'cascade'}).notNull(),
});

[j-fdion]

Try with integer instead of serial and let me know if it's making a difference for you.

export const Event = pgTable("Event", { id: integer("id").primaryKey().generatedAlwaysAsIdentity({ startWith: 1 }), ... }

[jackkrone]

That worked. Here's the generated sql from the migration:

ALTER TABLE "my_table" ALTER COLUMN "user_id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "my_table" ALTER COLUMN "user_id" DROP NOT NULL;

[j-fdion]

https://orm.drizzle.team/docs/column-types/pg#identity-columns

I think it's now properly shown in the docs that using int is the proper way to do autoincrement now.

[QastanFIT]

Any fixes for uuid's? I want the xxx_id in YYY to be nullable

Example:

export const XXX = pgTable("xxx", {
    id: uuid("id").primaryKey().defaultRandom().unique(),
    name: text("name").notNull().unique(),
})

export const YYY = pgTable("yyy", {
    ...
    xxx_id:  uuid("xxx_id").references(()=>XXX.id, { onDelete:"restrict" }),
})

[GuiMedeirox]

did u get an answer?

[GuiMedeirox]

did u get an answer?

I found it! If you wanna set a field as nullable, just leave it as it is by default

 name: text('name').notNull(), //not nullable field
 email: text('email'), //nullable field