.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
Mykhailo2w 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
epsilon422w ago
Thanks @Mykhailo I was on the v0.21.1. Update to v0.21.2 fixed it.