Drizzle Team•2y ago

Foreign Key Reference to auth Schema Not Generated in Code

I'm having an issue with my Drizzle-ORM code where the foreign key reference to the 'auth' schema is not being generated as expected. The issue lies in this block of code:

import { index, jsonb, pgSchema, pgTable, text, uniqueIndex, uuid } from 'drizzle-orm/pg-core'

export const USER_TABLE = 'user'

const authSchema = pgSchema('auth')
const authUser = authSchema.table('users', { id: uuid('id').primaryKey().unique().notNull() })

export const user = pgTable(
  USER_TABLE,
  {
    id: uuid('id')
      .primaryKey()
      .unique()
      .references(() => authUser.id),
    firstName: text('firstName'),
    lastName: text('lastName'),
    email: text('email').unique(),
    image: text('image'),
    customData: jsonb('customData'),
    notificationKeys: jsonb('notificationKeys')
  },
  (table) => {
    return {
      emailKey: uniqueIndex('user_email_key').on(table.email),
      userIdEmailIdx: index('user_id_email_idx').on(table.id, table.email)
    }
  }
)

import { index, jsonb, pgSchema, pgTable, text, uniqueIndex, uuid } from 'drizzle-orm/pg-core'

export const USER_TABLE = 'user'

const authSchema = pgSchema('auth')
const authUser = authSchema.table('users', { id: uuid('id').primaryKey().unique().notNull() })

export const user = pgTable(
  USER_TABLE,
  {
    id: uuid('id')
      .primaryKey()
      .unique()
      .references(() => authUser.id),
    firstName: text('firstName'),
    lastName: text('lastName'),
    email: text('email').unique(),
    image: text('image'),
    customData: jsonb('customData'),
    notificationKeys: jsonb('notificationKeys')
  },
  (table) => {
    return {
      emailKey: uniqueIndex('user_email_key').on(table.email),
      userIdEmailIdx: index('user_id_email_idx').on(table.id, table.email)
    }
  }
)

The output SQL generated is:

DO $$ BEGIN
 ALTER TABLE "user" ADD CONSTRAINT "user_id_users_id_fk" FOREIGN KEY ("id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "user" ADD CONSTRAINT "user_id_users_id_fk" FOREIGN KEY ("id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

The problem is, it should reference the auth schema that has a users table, not just the users table. The 'auth' schema is not reflected in the ALTER TABLE SQL statement generated. I am unsure if this is a bug in the Drizzle-ORM package or if there is something wrong with my code. Please advise.

2 Replies

Andrii Sherman•2y ago

what version of drizzle-kit are you using?

LivogOP•2y ago

I was using drizzle-orm: 0.27.2 and drizzle-kit: 0.19.4

Gaming

Programming

Foreign Key Reference to auth Schema Not Generated in Code