Unique Key Schema

I am trying to create an unique key on a table using a custom schema, the generated sql migration file does not produce the correct output and when I execute it the error relation "users" does not exist is thrown. Here is the code: https://codesandbox.io/p/sandbox/competent-faraday-xk9b2q?selection=%5B%7B%22endColumn%22%3A72%2C%22endLineNumber%22%3A19%2C%22startColumn%22%3A72%2C%22startLineNumber%22%3A19%7D%5D&file=%2Fsrc%2Fdata%2Fmigrations%2F0000_military_la_nuit.sql I am using the same example from the drizzle-orm postgres sql docs, the schema.ts file is:
export const mySchema = pgSchema("mySchema");

export const usersTable = mySchema.table(
"users",
{
id: serial("id").primaryKey(),
name: text("name").notNull(),
verified: boolean("verified").notNull().default(false),
jsonb: jsonb("jsonb").$type<string[]>(),
createdAt: timestamp("created_at", { withTimezone: true })
.notNull()
.defaultNow(),
},
(user) => {
return {
userUQ: uniqueIndex("userUQ").on(user.name),
};
}
);
export const mySchema = pgSchema("mySchema");

export const usersTable = mySchema.table(
"users",
{
id: serial("id").primaryKey(),
name: text("name").notNull(),
verified: boolean("verified").notNull().default(false),
jsonb: jsonb("jsonb").$type<string[]>(),
createdAt: timestamp("created_at", { withTimezone: true })
.notNull()
.defaultNow(),
},
(user) => {
return {
userUQ: uniqueIndex("userUQ").on(user.name),
};
}
);
CREATE SCHEMA "mySchema";

CREATE TABLE IF NOT EXISTS "mySchema"."users" (
"id" serial PRIMARY KEY NOT NULL,
"name" text NOT NULL,
"verified" boolean DEFAULT false NOT NULL,
"jsonb" jsonb,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS "userUQ" ON "users" ("name");

/*
When this SQL query is execute the following exception is thrown: `relation "users" does not exist`
Executed on: Postgres Docker (postgres:15.2-alpine3.17) Mac M2

The incorrect line is `CREATE UNIQUE INDEX IF NOT EXISTS "userUQ" ON "users" ("name");`
Expected migration `CREATE UNIQUE INDEX IF NOT EXISTS "userUQ" ON "mySchema"."users" ("name");`
note that the schema musb be added before the table name.
*/
CREATE SCHEMA "mySchema";

CREATE TABLE IF NOT EXISTS "mySchema"."users" (
"id" serial PRIMARY KEY NOT NULL,
"name" text NOT NULL,
"verified" boolean DEFAULT false NOT NULL,
"jsonb" jsonb,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS "userUQ" ON "users" ("name");

/*
When this SQL query is execute the following exception is thrown: `relation "users" does not exist`
Executed on: Postgres Docker (postgres:15.2-alpine3.17) Mac M2

The incorrect line is `CREATE UNIQUE INDEX IF NOT EXISTS "userUQ" ON "users" ("name");`
Expected migration `CREATE UNIQUE INDEX IF NOT EXISTS "userUQ" ON "mySchema"."users" ("name");`
note that the schema musb be added before the table name.
*/
I didn't find in the documentation how to add the schema to the uniqueIndex method
competent-faraday-xk9b2q
CodeSandbox is an online editor tailored for web applications.
3 Replies
Andrii Sherman
Andrii Sherman16mo ago
@liyue thanks a lot! Already aware of this bug Some tracking issues for that https://github.com/drizzle-team/drizzle-orm/issues/440 https://github.com/drizzle-team/drizzle-orm/issues/413
GitHub
[BUG]: References across schemas does not include schema name in ge...
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...
GitHub
[BUG]: postgresql migrations generation: the schema is ignored for ...
What version of drizzle-orm are you using? 0.23.2 Describe the Bug Basically indexes are applied to public schema no matter if you use custom postgresql schema or not Contents of my schema.ts looks...
fjorn
fjorn15mo ago
Any update on this bug? Ran into it again today
liyue0
liyue014mo ago
I can confirm that this is working now. Thanks @Andrii Sherman