Drizzle Team•3mo ago

.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.

2 Replies

Mykhailo•3mo ago

Hey @epsilon42! Could you show your createTable function? Btw, try to update to the latest drizzle kit version because your bug might be fixed in the last release https://discord.com/channels/1043890932593987624/1235937884956000377/1240006063881850921

epsilon42•3mo ago

Thanks @Mykhailo I was on the v0.21.1. Update to v0.21.2 fixed it.

Gaming

Programming

.unique() doesn't seem to work