Drizzle Team•15mo ago

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•15mo 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•14mo ago

Any update on this bug? Ran into it again today

liyue0•13mo ago

I can confirm that this is working now. Thanks @Andrii Sherman

Gaming

Programming

Unique Key Schema