d1ge Posts - Answer Overflow

d1ge

•Created by d1ge on 9/23/2023 in #help

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 $$;

9 replies

Gaming

Programming