[BUG]: Nullable self-reference table relation

[pedro757]

What version of drizzle-orm are you using?

0.26.5

What version of drizzle-kit are you using?

0.18.1

Describe the Bug

I can't query by parentId equal null in a self-reference relation, this is the code:

schema.ts

import { relations } from "drizzle-orm";
import { int, mysqlTable, serial, varchar } from "drizzle-orm/mysql-core";

export const account = mysqlTable("account", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }),
  parentId: int("parentId"),
});

export const accountRelations = relations(account, ({ one }) => ({
  parent: one(account, {
    fields: [account.parentId],
    references: [account.id],
  }),
}));

Index.ts

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

import * as schema from "./schema";

const poolConnection = mysql.createPool({
  host: "host",
  user: "user",
  database: "database",
  password: "password",
});

export const db = drizzle(poolConnection, { schema });

Anywhere in my app

await db.query.account.findMany({
  where: (account, { eq }) => eq(account.parentId, null)  // ERRORS HERE
})

typescript: No overload matches this call.
     Overload 1 of 2, '(left: Aliased<null>, right: AnyColumn | Placeholder<string, any> | SQLWrapper | null): SQL<unknown>', gave the following error.
       Argument of type 'MySqlInt<{ tableName: "cuentas"; name: "padre_id"; data: number; driverParam: string | number; notNull: false; hasDefault: false; }>' is not assignable to parameter of type 'Aliased<null>'.
         Type 'MySqlInt<{ tableName: "cuentas"; name: "padre_id"; data: number; driverParam: string | number; notNull: false; hasDefault: false; }>' is missing the following properties from type 'Aliased<null>': sql, fieldAlias, getSQL
     Overload 2 of 2, '(left: MySqlInt<{ tableName: "cuentas"; name: "padre_id"; data: number; driverParam: string | number; notNull: false; hasDefault: false; }>, right: number | AnyColumn | Placeholder<...> | SQLWrapper): SQL<...>', gave the following error.
       Argument of type 'null' is not assignable to parameter of type 'number | AnyColumn | Placeholder<string, any> | SQLWrapper'. [2769]

Expected behavior

I haven't declared parentId field to be .notNull() so It should let me find those who are null.

[mees-van-wel]

When I try create a "Self-relation" everything works as expected except for the typings: user implicitly has type any because it does not have a type annotation and is referenced directly or indirectly in its own initializer. ts(7022)

If I remove the .references(() => user.id, { onDelete: "set null", }) the typings does seem to work but I'm dependent on the referential actions. Maybe it's an idea to move the foreign key constraints and their referential actions like onDelete, onUpdate to the relations object.

I use the same drizzle versions as specified above.

schema.ts

export const user = pgTable("User", {
  id: serial("id").primaryKey(),
  createdById: integer("createdById").references(() => user.id, {
    onDelete: "set null",
  }),
  updatedById: integer("updatedById").references(() => user.id, {
    onDelete: "set null",
  }),
  firstName: text("first_name"),
  lastName: text("last_name"),
});

export const userRelations = relations(user, ({ one }) => ({
  createdBy: one(user, {
    fields: [user.createdById],
    references: [user.id],
  }),
  updatedBy: one(user, {
    fields: [user.updatedById],
    references: [user.id],
  }),
}));

Proposed solution

export const user = pgTable("User", {
  id: serial("id").primaryKey(),
  createdById: integer("createdById"),
  updatedById: integer("updatedById"),
  firstName: text("first_name"),
  lastName: text("last_name"),
});

export const userRelations = relations(user, ({ one }) => ({
  createdBy: one(user, {
    fields: [user.createdById],
    references: [user.id],
    actions: {
      onDelete: "set null"
    }
  }),
  updatedBy: one(user, {
    fields: [user.updatedById],
    references: [user.id],
    actions: {
      onDelete: "set null"
    }
  }),
}));

[pedro757]

I believe it should be nullable unless we explicitly set .notNull().

[eljou]

Trying to define actions on relations should not be the solution to this case because according to docs:

On the other hand, relations are a higher level abstraction, they are used to define relations between tables on the application level only. They do not affect the database schema in any way and do not create foreign keys implicitly.

[pedro757]

I believe this issue could be closed since the team already helped me understand that the right way to query null values is using the helper function isNull

https://discord.com/channels/1043890932593987624/1084576896039260202/threads/1115269692274249839