[BUG]: References across schemas does not include schema name in generated migrations

[erickreutz]

What version of drizzle-orm are you using?

0.23.10

Describe the Bug

When creating a reference to a table in a different schema the foreign key constraint generated in the migration does not include the custom schema name resulting in an error.

const authSchema = pgSchema("auth");
const publicSchema = pgSchema("public");

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

const userProfiles = publicSchema.table("user_profiles", {
  id: uuid("id").defaultRandom().primaryKey(),
  userId: uuid("user_id").references(() => users.id),
});

results in the below foreign key constraint

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

adding the schema name "auth"."users"("id") allows the migration to run successfully.

[RRikor]

This also happens when defining only a single pgSchema() attribute. Table creation works, but all ALTER TABLE statements are missing the schema reference.

"node_modules/drizzle-orm": {
  "version": "0.25.3",

"drizzle-kit": {
  "version": "0.17.6",

[AndriiSherman]

Fixed in drizzle-kit@0.18.1

[rphlmr]

Hello,

drizzle-kit: v0.19.12
drizzle-orm: v0.28.2

The bug seems to be back :(

With this schema:

const authSchema = pgSchema("auth")

const authUsers = authSchema.table("users", {
	id: uuid("id").primaryKey().notNull(),
})

export const users = pgTable("users", {
	id: uuid("id").primaryKey().notNull().references(() =>authUsers.id),
})

Generated SQL:

CREATE TABLE IF NOT EXISTS "users" (
	"id" uuid PRIMARY KEY NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "users" ADD CONSTRAINT "users_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 $$;

I can open a new issue if you want ;)

[AndriiSherman]

@rphlmr, I now understand why you see an issue here. We have one final major issue with drizzle-kit in PostgreSQL only. If different schemas have tables with the same name, drizzle-kit will not function as expected. We are aware that this is the primary purpose of schemas in PostgreSQL. We will work on fixing it as soon as possible

For example, this one will work as expected

const authSchema = pgSchema("auth")

const authUsers = authSchema.table("auth_users", {
	id: uuid("id").primaryKey().notNull(),
})

export const users = pgTable("users", {
	id: uuid("id").primaryKey().notNull().references(() =>authUsers.id),
})

CREATE TABLE IF NOT EXISTS "users" (
	"id" uuid PRIMARY KEY NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "users" ADD CONSTRAINT "users_id_auth_users_id_fk" FOREIGN KEY ("id") REFERENCES "auth_users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

[rtkevin]

drizzle-kit: v0.19.12
drizzle-orm: v0.28.2

const tableToNameWithSchema = statement.schema ? `"${statement.schema}"."${tableTo}"` : `"${tableTo}"`;
const alterStatement = `ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT "${name}" FOREIGN KEY (${fromColumnsString}) REFERENCES ${tableToNameWithSchema}(${toColumnsString})${onDeleteStatement}${onUpdateStatement}`

in my case, generates

ALTER TABLE "core"."login_log" ADD CONSTRAINT "login_log_app_site_id_app_site_id_fk" FOREIGN KEY ("app_site_id") REFERENCES "core"."app_site"("id") ON DELETE set null ON UPDATE cascade

which should be

REFERENCES "test"."app_site"("id") 

since app_site is in test schema, not core.

seems like need a statement.toSchema to use in tableToNameWithSchema when it's different from statement.schema.

seems like the author's case would still be broken also, or at least any case where from table and to table are in schemas other than public. and it's not because the tables are the same name, because mine are not.