liyue Posts - Answer Overflow

liyue

•Created by liyue on 4/13/2023 in #help

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

4 replies

Gaming

Programming