Drizzle Team•14mo ago

drizzle-kit generate:pg - generates incorrect reference to table in another schema?

As im not super comfortable in pgsql, so im unsure if this is intended, or if its a bug with drizzle-kit. Im trying to create a fk reference to "auth"."users", inside my "public"."profiles" table. But drizzle-kit seems to not generate the script with correct reference to the database schema -, am i missing something? Here is the .ts file:

import { sql } from "drizzle-orm";
import {
  index,
  pgSchema,
  pgTable,
  primaryKey,
  timestamp,
  uuid,
} from "drizzle-orm/pg-core";

import { customers } from "./customers";

export const authSchema = pgSchema("auth");

export const users = authSchema.table("users", { id: uuid("id") });

export const profiles = pgTable(
  "profiles",
  {
    id: uuid("id").default(sql`gen_random_uuid()`),
    user_id: uuid("user_id")
      .notNull()
      .references(() => users.id), // <- This references users table inside auth schema
    customer_id: uuid("customer_id")
      .notNull()
      .references(() => customers.id, { onDelete: "cascade" }),
    created_at: timestamp("created_at", { withTimezone: true })
      .default(sql`now()`)
      .notNull(),
  },
  (table) => ({
    pk: primaryKey(table.id, table.user_id),
    customer_idx: index("customer_idx").on(table.customer_id),
  }),
);

import { sql } from "drizzle-orm";
import {
  index,
  pgSchema,
  pgTable,
  primaryKey,
  timestamp,
  uuid,
} from "drizzle-orm/pg-core";

import { customers } from "./customers";

export const authSchema = pgSchema("auth");

export const users = authSchema.table("users", { id: uuid("id") });

export const profiles = pgTable(
  "profiles",
  {
    id: uuid("id").default(sql`gen_random_uuid()`),
    user_id: uuid("user_id")
      .notNull()
      .references(() => users.id), // <- This references users table inside auth schema
    customer_id: uuid("customer_id")
      .notNull()
      .references(() => customers.id, { onDelete: "cascade" }),
    created_at: timestamp("created_at", { withTimezone: true })
      .default(sql`now()`)
      .notNull(),
  },
  (table) => ({
    pk: primaryKey(table.id, table.user_id),
    customer_idx: index("customer_idx").on(table.customer_id),
  }),
);

And here is the generated script by drizzle kit:

CREATE SCHEMA "auth";
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "profiles" (
    "id" uuid DEFAULT gen_random_uuid(),
    "user_id" uuid NOT NULL,
    "customer_id" uuid NOT NULL,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT profiles_id_user_id PRIMARY KEY("id","user_id")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "auth"."users" (
    "id" uuid
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "customer_idx" ON "profiles" ("customer_id");--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action; --> Would expect this to reference "auth"."users"("id"), not "users"("id")
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "profiles" ADD CONSTRAINT "profiles_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE SCHEMA "auth";
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "profiles" (
    "id" uuid DEFAULT gen_random_uuid(),
    "user_id" uuid NOT NULL,
    "customer_id" uuid NOT NULL,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT profiles_id_user_id PRIMARY KEY("id","user_id")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "auth"."users" (
    "id" uuid
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "customer_idx" ON "profiles" ("customer_id");--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action; --> Would expect this to reference "auth"."users"("id"), not "users"("id")
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "profiles" ADD CONSTRAINT "profiles_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

5 Replies

Angelelz•14mo ago

This is a known bug on the drizzle-kit side: https://orm.drizzle.team/kit-docs/faq#using-multiple-schemas-in-postgresql

FAQ & Troubleshooting - DrizzleORM

Drizzle ORM | %s

d1ge•14mo ago

I looked at that one as well, but tought that this part:

However, you can use schemas in PostgreSQL on the ORM level without any issues and with drizzle-kit, without naming tables the same across schemas.

However, you can use schemas in PostgreSQL on the ORM level without any issues and with drizzle-kit, without naming tables the same across schemas.

Meant that all would be "ok ish" as long as i dont name tables the same. I guess this is not true when referencing tables in other schemas then? Postgres throws up when trying to migrate that file, cause i guess its looking for the users table, in its default search_path?

Angelelz•14mo ago

I guess that line assumed you wouldn't be referencing tables from one schema on another schema. One workaround would be to just edit the migration derectly before applying it Pinging @a_sherman for visibility

d1ge•14mo ago

Yeah, thats what i will do for now. And with some automated checks/rules in my migrate.ts file, i guess its to overcome! Appreciate your guidance very much 🙂

Andrii Sherman•13mo ago

yes, need to update docs about that, reference across schemas is also a bug, that we are aware of but I guess we will prioritize open-sourcing drizzle-kit and then we will work on fixing those issues

Gaming

Programming

drizzle-kit generate:pg - generates incorrect reference to table in another schema?