Drizzle TeamDT
Drizzle Team•2y ago
epsilon42

.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 })
  }
);

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()
  }
);

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),
    };
  },
);


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

Thanks.
Was this page helpful?