DT
Drizzle Team•16mo ago
d1ge

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
Angelelz•16mo ago
d1ge
d1geOP•16mo 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
Angelelz•16mo 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
d1geOP•16mo 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
Andrii Sherman•16mo 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

Did you find this page helpful?