Is it possible to do circular dependencies between tables?

[knight10-ux]

In mikro-orm I can import entity as type. How can I do this with drizzle-orm?

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: varchar('full_name', { length: 256 }),
  // FIXME: how can I do this??
  authOtpId: integer('auth_otp_id').references(() => authOtps.id),
}, (users) => ({
  nameIdx: index('name_idx').on(users.fullName),
}));

export const authOtps = pgTable('auth_otp', {
  id: serial('id').primaryKey(),
  phone: varchar('phone', { length: 256 }),
  userId: integer('user_id').references(() => users.id),
});

[AndriiSherman]

@mkieblesz Yes!

export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    fullName: varchar("full_name", { length: 256 }),
    authOtpId: integer("auth_otp_id")
  },
  (users) => ({
    nameIdx: index("name_idx").on(users.fullName),
    fk: foreignKey({
      columns: [users.authOtpId],
      foreignColumns: [authOtps.id],
    }),
  })
);

export const authOtps = pgTable("auth_otp", {
  id: serial("id").primaryKey(),
  phone: varchar("phone", { length: 256 }),
  userId: integer("user_id").references((): AnyPgColumn => users.id),
});

[beeirl]

@AndriiSherman is there a way to achieve this just by using foreignKey operators instead of references? I am kinda forced to use foreignKey operators in order to change the name of the foreign keys - otherwise I'd run into identifier name max length issues...

[udoniyor]

@beeirl Where you able to work around this issue? I have the same problem as the references syntax doesn't allow passing the FK name, the generated SQL tries to drop and recreate the FKs

[beeirl]

@udoniyor the best solution I found is to define the foreignKey for at least one of the tables outside of said table. Requires to be explicit with the types, at least for that foreign key.

MySQL example:

import { AnyMySqlColumn, char, foreignKey, mysqlTable } from 'drizzle-orm/mysql-core'

// Need to define this because it's not exported from @drizzle-orm/mysql-core
export type ColumnsWithTable<
  TTableName extends string,
  TForeignTableName extends string,
  TColumns extends AnyMySqlColumn<{ tableName: TTableName }>[],
> = { [Key in keyof TColumns]: AnyMySqlColumn<{ tableName: TForeignTableName }> }

export const userTable = mysqlTable(
  'user',
  {
    id: char('id', { length: 25 }).notNull().primaryKey(),
    defaultEmailId: char('default_email_id', { length: 25 }).notNull(),
  },
  () => ({
    defaultEmailIdFk: foreignKey(defaultEmailIdFk)
       .onUpdate('restrict')
       .onDelete('restrict'),
  })
)

export const emailTable = mysqlTable(
  'email',
  {
    id: char('id', { length: 25 }).notNull().primaryKey(),
    userId: char('user_id', { length: 25 }).notNull(),
  },
  (columns) => ({
    userIdForeignKey: foreignKey({
      columns: [columns.userId],
      foreignColumns: [userTable.id],
    })
      .onUpdate('restrict')
      .onDelete('cascade'),
  })
)

const defaultEmailIdFk: {
  columns: [AnyMySqlColumn<{ tableName: 'user' }>]
  foreignColumns: ColumnsWithTable<'user', 'email', [AnyMySqlColumn<{ tableName: 'user' }>]>
} = {
  columns: [userTable.defaultEmailId],
  foreignColumns: [emailTable.id],
}

Postgres example:

import { AnyPgColumn, char, ColumnsWithTable, foreignKey, pgTable } from 'drizzle-orm/pg-core'

export const userTable = pgTable(
  'user',
  {
    id: char('id', { length: 25 }).notNull().primaryKey(),
    defaultEmailId: char('default_email_id', { length: 25 }).notNull(),
  },
  () => [
    {
      defaultEmailIdFk: foreignKey(defaultEmailIdFk)
         .onUpdate('restrict')
         .onDelete('restrict'),
    },
  ]
)

export const emailTable = pgTable(
  'email',
  {
    id: char('id', { length: 25 }).notNull().primaryKey(),
    userId: char('user_id', { length: 25 }).notNull(),
  },
  (columns) => [
    {
      userIdForeignKey: foreignKey({
        columns: [columns.userId],
        foreignColumns: [userTable.id],
      })
        .onUpdate('restrict')
        .onDelete('cascade'),
    },
  ]
)

const defaultEmailIdFk: {
  columns: [AnyPgColumn<{ tableName: 'user' }>]
  foreignColumns: ColumnsWithTable<'user', 'email', [AnyPgColumn<{ tableName: 'user' }>]>
} = {
  columns: [userTable.defaultEmailId],
  foreignColumns: [emailTable.id],
}

[udoniyor]

@beeirl Thanks!, That works, not the pretties way but hopefully the new version will resolve this issue. Would be good if foreignKey and references had feature equivalence

[milamer]

Here is what I came up with

import {
  pgTable,
  serial,
  integer,
  foreignKey,
  AnyPgColumn,
  type PgTableExtraConfigValue,
} from "drizzle-orm/pg-core";

const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    authOtp: integer("auth_otp_id"),
  },
  (columns): PgTableExtraConfigValue[] => [
    foreignKey({
      columns: [columns.authOtp],
      foreignColumns: [authOtps.id],
      name: "users_auth_otp_id_fk",
    }).onDelete("set null"),
  ],
);

const authOtps = pgTable(
  "auth_otps",
  {
    id: serial("id").primaryKey(),
    user: integer("user_id"),
  },
  (columns): PgTableExtraConfigValue[] => [
    foreignKey({
      columns: [columns.user],
      foreignColumns: [users.id],
      name: "auth_otps_user_id_fk",
    }).onDelete("set null"),
  ],
);

The explicit PgTableExtraConfigValue[] return type prevents circular (or self) reference issues.