epsilon42 Posts - Answer Overflow

epsilon42

Explore posts from servers

•Created by epsilon42 on 5/15/2024 in #help

.unique() doesn't seem to work

I am coming from a front end background so am in unfamiliar territory with DB/Postgres stuff so apologies if I've missed something obvious. I started with the following table in Drizzle (removed irrelevant columns):

 
export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  }
);

 
export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  }
);

I then wanted to ensure that the username column was unique so changed it to:

 
export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 }).unique()
  }
);

 
export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 }).unique()
  }
);

After doing a drizzle-kit push I was expecting it to say that changes needed to be applied but to my surprise it just said "No changes detected". I then checked for uniqueness by going to Drizzle Studio and found I was still able to have multiple rows with the same username. The only way I was able to get the unique constraint to work properly was by doing:

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  },
  (table) => {
    return {
      usernameIndex: uniqueIndex("usernameIndex").on(table.username),
    };
  },
);

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  },
  (table) => {
    return {
      usernameIndex: uniqueIndex("usernameIndex").on(table.username),
    };
  },
);

Am I missing something with how .unique() should be used or what it's for? Thanks.

4 replies

Gaming

Programming